OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Tunning required (Oracle 9.2.0.7.0)

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

Tunning required [message #305164]

Sun, 09 March 2008 20:13

gksenthilkumar
Messages: 23
Registered: November 2007
Location: india

Junior Member

I have the following attached query to be tuned..

It is taking around 4hrs in my real environment due to improper tunning… help me out of this…

Attachment: Tunning_posting.txt
(Size: 5.34KB, Downloaded 1267 times)

Report message to a moderator

Re: Tunning required [message #305165 is a reply to message #305164]

Sun, 09 March 2008 20:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/84315/74940/

which of the suggestions in URL above have you done & what were the results

Report message to a moderator

Re: Tunning required [message #305166 is a reply to message #305164]

Sun, 09 March 2008 20:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

eliminate tables txn & er out of the FROM clause because they contribute no data to the SELECT clause

They can & should be subordinated into the WHERE clause

Report message to a moderator

Re: Tunning required [message #305167 is a reply to message #305166]

Sun, 09 March 2008 21:22

gksenthilkumar
Messages: 23
Registered: November 2007
Location: india

Junior Member

Could you please explain me about how to remove from from clause to place into where clause....

Report message to a moderator

Re: Tunning required [message #305168 is a reply to message #305164]

Sun, 09 March 2008 22:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Could you please explain me about how to remove from from clause to place into where clause....

Use any text editor to construct the improved SQL statement.

Why were TNX & ER included in the FROM clause when they contribute no data to the SELECT clause?
In other word no data is coming FROM ER or TXN tables.
Therefore these 2 tables should not be in the FROM clause.

Report message to a moderator

Re: Tunning required [message #305308 is a reply to message #305168]

Mon, 10 March 2008 06:39

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

You have not formatted the Explain Plan, so its a bit hard to read.

This is a bit complex. You could really help us help you by tracing the job and posting the Tkprof output. Then we could see which tables/indexes are contributing the most load to the SQL.

Ross Leishman

Report message to a moderator

Previous Topic:	optimized search for max() function
Next Topic:	AWR Report or ADDM Report?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 01:28:39 CST 2024