RE: SQL Tuning Advisor - how well does it work?

We logged a call with Oracle for version 11.2.0.1 on Linux-64 because we regularly received ORA-700 (apparently slightly less alarming then ORA-600 :-) ) in the alert log.  

After some digging it appears this was caused by SYS_AUTO_SQL_TUNING_TASK. We just turned this off for the time being; I haven't tested wether or not running this thing manually could give the same issues.  

mvg / regards,

Jo Holvoet    

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of kyle Hailey Sent: woensdag 7 juli 2010 22:36
To: ORACLE-L
Subject: SQL Tuning Advisor - how well does it work?    

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-wel l-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-l