Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: query plan is bad when it is run inside a pl/sql stored procedure

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

From: Mladen Gogala <mgogala_at_adelphia.net>
Date: Tue, 27 Jan 2004 04:04:25 -0800
Message-ID: <F001.005DE332.20040127040425@fatcity.com>


It's a bad query that could probably be resolved throuh an analytic function but I don't normally delve into things like that before having finished my 2nd coffee. You can use hints, in particular, there is a hint to force hash join. On 01/27/2004 06:44:25 AM, S.Sarkar 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).
>

-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

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 - 06:04:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US