query plan is bad when it is run inside a pl/sql stored procedure

From: S.Sarkar <ssarkar97_at_yahoo.com>
Date: Tue, 27 Jan 2004 03:44:25 -0800
Message-ID: <F001.005DE32F.20040127034425@fatcity.com>

All,

i have this query:

SELECT count(1)
FROM ats.emktg_members t1
WHERE NOT EXISTS ( SELECT 'x'
FROM gcd_data_source_details t2
WHERE t2.universal_id = t1.universal_id AND t2.data_source_id = 13 )
AND upper(t1.email) NOT LIKE '%TATA.COM';

This query finishes in about 5 minutes. The plan is:

Operation Object Name Rows Bytes Cost Object Node SELECT STATEMENT Hint=CHOOSE 1 14919
SORT AGGREGATE 1 75
HASH JOIN ANTI 272 K 19 M 14919
TABLE ACCESS FULL EMKTG_MEMBERS 274 K 14 M 14444 TABLE ACCESS BY INDEX ROWID GCD_DATA_SOURCE_DETAILS 1 K 21 K 391
INDEX RANGE SCAN DSD_DSRC_FKI 23 K 27 However, when the same query is run from a stored procedure, it

picks up a bad plan (with nested loops join) and does not complete even after 6 hours ! Giving HASH_AJ hint did not change
the plan.

Any ideas how we can fix this (without using stored outlines) ?

The database is 9204 on sun solaris.

regards,
Sumant

---

Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: S.Sarkar
  INET: ssarkar97_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jan 27 2004 - 05:44:25 CST