[oracle-l] 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 22:45:35 -0500
Message-ID: <20040128034535.GA2590@medo.adelphia.net>

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'

---

can be re-written like this:

---

select count(*) from (
select * from ats.emktg_members
minus
select * from ats.emktg_members t1,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')

---

That can further be reduced to :

---

NUM1=select /*+ parallel(m,6) */ count(*) from ats.emktg_member m;

NUM2=select * from ats.emktg_members t1,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';
*******************************************************************

The desired count would then be NUM1 - NUM2.

This way, you can execute 2 completely unrelated queries, without any correlation and then simply subtract the numbers. The first query can be optimized using the "brute force" approach (parallel query, choose the degree that your HW can tolerate) and a highly indexed query which is likely to complete in seconds. I've also sent the query to the oracle list, if someone else has anything to add, subtract, divide or multiply.

On 01/27/2004 07:25:32 PM, S.Sarkar wrote:
> could u please tell me how i can improve the query ?
> the hash_aj hint was ignored by oracle.
> moreover, the query works fine from sql*plus.
> it chooses a bad plan when run from a stored procedure.
>
> sumant
>
> --- Mladen Gogala <mgogala_at_adelphia.net> wrote:
> > 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/

>

-- 
Mladen Gogala
Oracle DBA
-------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
-------------------------------------------------------------

Received on Tue Jan 27 2004 - 21:45:35 CST