OraFAQ Forum: Performance Tuning » Tunning

Home » RDBMS Server » Performance Tuning » Tunning - urgent

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

Tunning - urgent [message #110527]

Tue, 08 March 2005 08:00

kavi
Messages: 2
Registered: March 2005
Location: B'Lore

Junior Member

Hi Frends,

Can Anybody Kindly tell me how shud I tune a Query which is using inline views...

Following is the query

select <set of columns>
from table1 t1,
(select * from tablename p1
where p1.seqno=
(select max(seqno) from table p2 where p1.x=p2.x and p1.y=p2.y and p2.st_date <='20050102' and (p2.ed_dt is null or
p2.ed_dt >='20050102'))) t2,

(select * from tablename p3
where p3.seqno=
(select max(seqno) from table p4 where p3.x=p4.x and p3.y=p4.y and p4.st_date <='20050102' and (p4.ed_dt is null or
p4.ed_dt >='20050102'))) t3,

(select * from tablename p5
where p5.seqno=
(select max(seqno) from table p5 where p5.x=p5.x and p5.y=p5.y and p6.st_date <='20050102' and (p6.ed_dt is null or
p6.ed_dt >='20050102'))) t4,

(select <col name> from <Synonym/View name> where <condition>)X

--> (the abv syn/view is accessed from Remote after adding this We are unable to Push the predicate and the query is taking nearly 9 hrs to get the output..)

where <some condition>
and t1.<col name>=t2.<col name>(+)
and t1.<col name>=t3.<col name>(+)
and t1.<col name>=t4.<col name>(+)
order by
t1.<column>
t2.<column>
t3.<column>
t4.<column>

** Here tables T2, T3, T4 contain millions of records...
how shud I rewrite the queries to tune them...
or what is the method I have to use...
I hope based on the query we have to take execution plan.
I took the execution plan for the above query (which is strictly prohibited to display the actual query & execution plan, but the abv query format is exactly same as the actual query format)
and still unable to tune it...
Any body plz do guide me how shud I tune it..

Kavi

Report message to a moderator

Re: Tunning - urgent [message #110555 is a reply to message #110527]

Tue, 08 March 2005 11:47

avdba_22
Messages: 5
Registered: March 2005

Junior Member

Are the tables analyzed?

Report message to a moderator

Re: Tunning - urgent [message #110623 is a reply to message #110555]

Wed, 09 March 2005 03:05

kavi
Messages: 2
Registered: March 2005
Location: B'Lore

Junior Member

Hi frend,

Yes...All the table and the dependencies are analyzed

Report message to a moderator

Re: Tunning - urgent [message #111448 is a reply to message #110527]

Wed, 16 March 2005 08:17

skmishra
Messages: 2
Registered: March 2005
Location: New Delhi

Junior Member

Hi,

1. Check whether Table & Index are in different Tablespace or Not.

2. Use Parallel(degree) hint to speed up the Process.

Thanks
S.K.Mishra
DBA

Report message to a moderator

Re: Tunning - urgent [message #111669 is a reply to message #110527]

Fri, 18 March 2005 09:23

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

1. If these are millions of records the tables might should be partitioned, probably by date, which in itself might solve the execution time.

2. It isn't the inline views that is the problem, it is the numerous correlated subqueries inside them. See if you can't rewrite each inline view to actually have another inline view nested inside it to avoid the correlateds.

Basically get all of your date comparisons in inline views of themselves (or use the with clause) so that your correlated comparisons don't have to compare as many rows.

3. Look into using some analytic functions, I don't know what you are trying to do, but it has the looks of something that analytics could be used to simplify and speed up.

Report message to a moderator

Previous Topic:	Undo Tablespace Issue - Archiving Case
Next Topic:	PL/SQL code is conusimg alot of shared memory

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 23:54:18 CST 2024