Re: [SPAM] SQL Tuning Advisor - how well does it work?
- From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
- To: kylelf@xxxxxxxxx
- Date: Wed, 7 Jul 2010 20:07:54 -0500
Kyle,
Hmm, interesting topic. I think the degree of success depends on the kind of
problem the SQL statement is having. It seems to be geared primarily at testing
the statistics and fixing them. So if the root cause is that stats are wrong,
it can often do a pretty good job of coming up with a better plan. As you know,
it's way of fixing a statement is via a SQL Profile which is basically a
collection of hints (like an Outline). Unfortunately, the options at it's
disposal are primarily limited to hints that change the stats and or
calculations that the optimizer makes. (by the way, I did a survey of several
databases that had implemented quite a few STA generated profiles to see what
hints they included and posted the info on my blog last year sometime) The
opt_estimate hint is the main one. It allows a fudge factor to be applied to a
number of calculations. So where stats are incorrect or calculations are
incorrect (like correlated columns) they can be pretty effective. A second
major draw back with them is that the stats change fairly often, which means
the fudge factors applied by the opt_estimate hint often don't make sense
later. I think this is why a number of people implement a suggested SQL profile
with good results, only to have the statement behaving badly fairly shortly
thereafter. By the way, if you accept the SQL Profile with the recommended
syntax, it gets put in the DEFAULT category which means it kicks in
immediately. A slight change to the syntax allows it to be created in a
different category which provides a couple of options. First, you can test it
without turning it on for the whole database. Second, you can have a look at
the hints that got generated. These hints provide a very powerful tool for
identifying where the optimizer is having a problem. (i.e. anywhere there is an
opt_estimate hint with a really large or really small fudge factor, the
optimizer is having a problem)
So to answer your question without blathering on anymore. I use it fairly
regularly. I'd say that the majority of the time it doesn't come up with a
better plan. But it often can provide helpful information anyway. Especially
for statements with a large number of objects where there are a bunch of stats
to check. I must admit that I rarely (maybe never) leave an STA generated SQL
Profile in production though, because of the tendency for it to go south.
Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com
On Jul 7, 2010, at 3:36 PM, kyle Hailey wrote:
>
> I'm wondering what peoples experiences with Oracle's SQL Tuning Advisor (STA)
> are.
> My personal experiences have been less than stellar. These experiences I
> initially wrote off to my bad luck.
> I tried 3 recommended profiles at a customer site 3 years ago when consulting
> with customer who had license the STA, and 2 out of the 3 cases the new plans
> were considerable worse than the original. I immediately back them out and
> didn't think about it much other than I somehow had the bad luck to stumble
> upon the 2 cases where STA didn't work well.
> Well recently I gave the STA a query to show some people how it worked in
> 10gR2, and again the profile was worse. I then tried the same query on 11gR2
> and in this case after a half an hour the STA times out. I guess that's
> better than giving a bad plan. The only trick is that by using the brute
> force method of just hint injection to get a list of different plans, I
> managed to get a much better plan in seconds.
> After this recent experience I started asking around and a surprising number
> of people I talked to seem to have similar experiences, thus I'm curious get
> a little more solid feedback from more people what their experience are.
>
> I've blogged about a some of my experiences here:
>
> http://db-optimizer.blogspot.com/2010/07/oracles-sql-tuning-pack-how-well-does.html
> http://db-optimizer.blogspot.com/2010/07/oracles-sql-tuning-pack-part-2.html
> http://db-optimizer.blogspot.com/2010/07/oracles-sql-tuning-pack-part-3.html
>
> Best
> Kyle Hailey
> http://db-optimizer.blogspot.com
>
>
Other related posts:
