SQL Tuning Advisor - how well does it work?
From: kyle Hailey <kylelf_at_gmail.com>
Date: Wed, 7 Jul 2010 13:36:24 -0700
Message-ID: <AANLkTimm4Hi20FaySC3wDvHBPGE64d0y55PMGgbf6lOY_at_mail.gmail.com>
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.
Date: Wed, 7 Jul 2010 13:36:24 -0700
Message-ID: <AANLkTimm4Hi20FaySC3wDvHBPGE64d0y55PMGgbf6lOY_at_mail.gmail.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 07 2010 - 15:36:24 CDT