OraFAQ Forum: Performance Tuning » PL SQL performance tuning

Home » RDBMS Server » Performance Tuning » PL SQL performance tuning (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PL SQL performance tuning [message #399192]

Tue, 21 April 2009 01:37

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

Report message to a moderator

Re: PL SQL performance tuning [message #399232 is a reply to message #399192]

Tue, 21 April 2009 03:43

user2004
Messages: 33
Registered: April 2009

Member

LIKE N'COLT_020485'

wrong!

Report message to a moderator

Re: PL SQL performance tuning [message #399243 is a reply to message #399232]

Tue, 21 April 2009 04:55

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

Report message to a moderator

Re: PL SQL performance tuning [message #399379 is a reply to message #399243]

Tue, 21 April 2009 22:01

rleishman
Messages: 3728
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 18:37:06 CST 2024