Home » RDBMS Server » Performance Tuning » PL SQL performance tuning (Oracle 10g)
PL SQL performance tuning [message #399192] Tue, 21 April 2009 01:37 Go to next message
vinod_tiwari
Messages: 4
Registered: April 2009
Location: New Delhi
Junior Member
Hi,

I need some help to reduce the cost of below query.

I have a query as under which is causing some performance issues:

select DISTINCT b.dDocName,a.xwebsites from docmeta a, revisions b where
( xOriginalContentId in (select xOriginalContentId from docmeta
where did in (select DISTINCT did from revisions where dDocname like N'COLT_020485' )) // repeated twice

or dDocName in (select xOriginalContentId from docmeta
where did in (select DISTINCT did from revisions where dDocname like N'COLT_020485' )) // repeated twice
)
and a.did = b.did order by a.xwebsites

COST: 1240 (where cost of inner query in blue is 602 = 301 * 2)

Note- All columns used in above query are indexed and both tables are of equal size

currently the query is like a in (x) or b in (x)

I want to change it as (a or b) in (x) so as to avoid second time computation of x (x being inner query)


I modified the above query with alias as below but the COST shot up drastically Sad


select DISTINCT b.dDocName,a.xwebsites
from docmeta a, revisions b ,
(select xOriginalContentId from docmeta
where did in (select DISTINCT did from revisions where dDocname like N'COLT_020485' )) c


where
a.xOriginalContentId in (c.xOriginalContentId) or b.dDocName in (c.xOriginalContentId)

-- (a.xOriginalContentId or b.dDocName) in c.xOriginalContentId)

and a.did = b.did order by a.xwebsites

COST: 5272457

Pls. give some pointers as to how I can improve the performance?

Thanks
Vinod

Re: PL SQL performance tuning [message #399232 is a reply to message #399192] Tue, 21 April 2009 03:43 Go to previous messageGo to next message
user2004
Messages: 33
Registered: April 2009
Member
LIKE N'COLT_020485' 


wrong!
Re: PL SQL performance tuning [message #399243 is a reply to message #399232] Tue, 21 April 2009 04:55 Go to previous messageGo to next message
vinod_tiwari
Messages: 4
Registered: April 2009
Location: New Delhi
Junior Member
That is correct. We are using Oracle UCM (Stallent content management server) where this query is being fired.

Thanks
Vinod
Re: PL SQL performance tuning [message #399379 is a reply to message #399243] Tue, 21 April 2009 22:01 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
WITH q AS (
    select /*+ MATERIALIZE*/ DISTINCT did 
    from revisions 
    where dDocname like N'COLT_020485'
)
SELECT ...
FROM ...
WHERE ... IN (select did FROM q)
OR    ... IN (select did FROM q)


Ross Leishman
Previous Topic: The difference of execution plans
Next Topic: Query Rewrite Failed After RLS policy applied(QSM-01284)
Goto Forum:
  


Current Time: Sat Dec 10 10:40:02 CST 2016

Total time taken to generate the page: 0.17822 seconds