OraFAQ Forum: Performance Tuning » SQL Tuning Advisor

Home » RDBMS Server » Performance Tuning » SQL Tuning Advisor (11g)

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

SQL Tuning Advisor [message #603291]

Fri, 13 December 2013 00:53

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..

Report message to a moderator

Re: SQL Tuning Advisor [message #603293 is a reply to message #603291]

Fri, 13 December 2013 02:07

martijn
Messages: 278
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.

Report message to a moderator

Re: SQL Tuning Advisor [message #603295 is a reply to message #603293]

Fri, 13 December 2013 03:20

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...

Report message to a moderator

Re: SQL Tuning Advisor [message #603301 is a reply to message #603295]

Fri, 13 December 2013 04:12

martijn
Messages: 278
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.

Report message to a moderator

Previous Topic:	how avoid merge join cartesian.
Next Topic:	Optimizing Select from VIEW

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 31 16:41:51 CST 2015

Total time taken to generate the page: 0.03895 seconds