I want to optimize a stored procedure that uses IN clause and a regex_str function. I am not sure that how I should optimize it more?

1. If your server supports it (seems you want it), change the hints into /*+ PARALLEL(S 8) PARALLEL(MOFF 8)*/. When you have aliases you must use the aliases in the hints.
2. you should try the compound index suggested by APC(STORES(STOREID, LAT, LNG))
3. Please respond to the questions: For the example presented, how many distinct stores you get (select count(distinct storeid) from (your_query)) and how many stores are in the STORES table? (Select count(*) from Stores)?
4. Have you analysed the table with dbms_stats.gather_table_stats?
5. I believe the connect by query is NOT the problem. It runs in 0.02 seconds.

If you look at you explain plan the timings for each step are the same: there is no obvious candidate to focus on tuning.

The sample you posted has fifty tokens for OFFERID. Is that representative? They map to 276 STORES - is that a representative ratio? Do any offers hit more than one Store?

276 rows is about 2.7% of the rows which is a small-ish sliver: however, as STORES seems to be a very compact table it's marginal as to whether indexed reads would be faster than a full table scan.

The only obvious thing you could do to squeeze more juice out of the database would be to build a compound index on STORES(STOREID, LAT, LNG); presumably it's not a table which sees much DML so the overhead of an additional index wouldn't be much.

One last point: your query executes in 0.2s. So how much faster do you want it to go?

Consider dropping the regex on the join, so the join can happen fast.
If there are indexes on the join columns, chances are the join may move from nested loopsto a hashed join of some sort.

Once you have that result set (with hopefully fewer rows), then filter it with your regex.

You may find that the WITH statement helpful in this scenerio.

Something on the order of this. ( untested example )

WITHbase AS(    SELECT /*+ PARALLEL(STORES 5) PARALLEL(MERCHANTOFFERS 5) */           moff.OFFERID,              s.STOREID,              s.LAT,              s.LNG    FROM MERCHANTOFFERS moff    INNER JOIN STORES s     ON MOFF.STOREID = S.STOREID),offers AS(    SELECT REGEXP_SUBSTR(LISTOFOFFERIDS,'[^,]+', 1, LEVEL) offerid    FROM DUAL     CONNECT BY REGEXP_SUBSTR(LISTOFOFFERIDS, '[^,]+', 1, LEVEL) IS NOT NULL)SELECT base.*FROM base,     offersWHERE base.offerid = offers.offerid

Oracle may execute the two views into in memory tables, then join.

No guarentees. Your milage may vary. You were looking for ideas. This is an idea.The very best of luck to you.

If I recall a hints chapter correctly, when you alias your table names, you need to use that alias in your hint. /*+ PARALLEL(s 5) PARALLEL(moff 5) */

I would be curious as to why you decided on the value 5 for your hints. I was under the impression that Oracle would chose a best value for it, depending on system load and other mysterious conditions.