Home » RDBMS Server » Performance Tuning » Tunning required (Oracle 9.2.0.7.0)
Tunning required [message #305164] Sun, 09 March 2008 20:13 Go to next message
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…





Re: Tunning required [message #305165 is a reply to message #305164] Sun, 09 March 2008 20:19 Go to previous messageGo to next message
BlackSwan
Messages: 25042
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
Re: Tunning required [message #305166 is a reply to message #305164] Sun, 09 March 2008 20:25 Go to previous messageGo to next message
BlackSwan
Messages: 25042
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
Re: Tunning required [message #305167 is a reply to message #305166] Sun, 09 March 2008 21:22 Go to previous messageGo to next message
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....
Re: Tunning required [message #305168 is a reply to message #305164] Sun, 09 March 2008 22:15 Go to previous messageGo to next message
BlackSwan
Messages: 25042
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.
Re: Tunning required [message #305308 is a reply to message #305168] Mon, 10 March 2008 06:39 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: optimized search for max() function
Next Topic: AWR Report or ADDM Report?
Goto Forum:
  


Current Time: Tue Dec 06 16:29:25 CST 2016

Total time taken to generate the page: 0.05845 seconds