Home » RDBMS Server » Performance Tuning » SQL Tuning Advisor (11g)
SQL Tuning Advisor [message #603291] Fri, 13 December 2013 00:53 Go to next message
srikanthreddy001
Messages: 3
Registered: December 2013
Location: Hyderabad
Junior Member
I have a doubt on SQL Tuning Advisor tool..



I have a long running SQL script which runs for more than 15 hrs.. When I login to OEM and run SQl tuning advisor on this SQL it says stats are stale on some objects and recommends to run the Gather stats on those objects.



My question is that my sql already started running from last 6hrs and now SQL tuning advisor recommends to run the stats on some objects...But I have already ran the gather stats on all the objects before I have started the SQL script...suppose if I run gather stats on these objects in a different session.. will there be any benefit in the SQL script performance for the current Run....Because I am not sure as already execution plan is prepared and it is being used...how will the Gather stats help the performance...



Some reply on this will be of great help to me..
Re: SQL Tuning Advisor [message #603293 is a reply to message #603291] Fri, 13 December 2013 02:07 Go to previous messageGo to next message
martijn
Messages: 268
Registered: December 2006
Location: Netherlands
Senior Member
No. The current run has allready an execution plan associated with it.
Now gathering stats will not effect the excecution plan anymore.

I do not think that the SQL tuning advisor was developed with "tuning running sql" in mind.
If you apply the advice of the tuning advisor, then the next run will be faster, slower or the same.
Re: SQL Tuning Advisor [message #603295 is a reply to message #603293] Fri, 13 December 2013 03:20 Go to previous messageGo to next message
srikanthreddy001
Messages: 3
Registered: December 2013
Location: Hyderabad
Junior Member
Ok will running gather stats in the middle of a long running SQl help in improving the performance in any other way...
Re: SQL Tuning Advisor [message #603301 is a reply to message #603295] Fri, 13 December 2013 04:12 Go to previous message
martijn
Messages: 268
Registered: December 2006
Location: Netherlands
Senior Member
srikanthreddy001@gmail.co wrote on Fri, 13 December 2013 10:20
Ok will running gather stats in the middle of a long running SQl help in improving the performance in any other way...

Not of the running query.
If you have to gather stats of a very large schema,a stats job can even slow things a little down I guess (if not to many CPU's)

But the next run may very well be impacted by the newly updated statistics.
Previous Topic: how avoid merge join cartesian.
Next Topic: Optimizing Select from VIEW
Goto Forum:
  


Current Time: Mon Jul 28 23:49:52 CDT 2014

Total time taken to generate the page: 0.10510 seconds