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

Is the sql really "the same query is run from a stored procedure" or is it perhaps using in place of the '%TATA.COM' a plsql variable (which is set to %TATA.COM)?

At 04:44 AM 1/27/2004, you wrote:
>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).

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.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).