DomainKey-Signature: a=rsa-sha1; c=nofws;        d=gmail.com; s=gamma;        h=mime-version:date:message-id:subject:from:to:content-type;        b=NrLMgbj6ihi3FCXV3y3SNxCmL1uEYZjkM0zxl1HYnDn7csN4LQboTWN09hmjqT3G1o         n5l8UbCTTP+papgHTJURvRE6ivAq83c6bVAFC9ciSS/N9rSwylqZm+2oJbE+/694WeV3         ROstyOwceyFg0MRHRXUGzy/MJAP/2ES9zS87g=
MIME-Version: 1.0
Date: Wed, 7 Jul 2010 13:36:24 -0700
Message-ID: <AANLkTimm4Hi20FaySC3wDvHBPGE64d0y55PMGgbf6lOY@mail.gmail.com>
Subject: SQL Tuning Advisor - how well does it work?
From: kyle Hailey <kylelf@gmail.com>
To: ORACLE-L <Oracle-L@freelists.org>
Content-Type: multipart/alternative; boundary=0016363100c793cece048ad21f06
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: kylelf@gmail.com
--0016363100c793cece048ad21f06
Content-Type: text/plain; charset=ISO-8859-1

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

--0016363100c793cece048ad21f06
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<br><div>I&#39;m wondering what peoples experiences with Oracle&#39;s SQL T=
uning Advisor (STA) are.</div><div>My personal experiences have been less t=
han stellar. These experiences I initially wrote off to my bad luck.</div>
<div>I tried 3 recommended profiles at a customer site 3 years ago when con=
sulting with customer who had license the STA, and 2 out of the 3 cases the=
 new plans were considerable worse than the original. =A0I immediately back=
 them out and didn&#39;t think about it much other than I somehow had the b=
ad luck to stumble upon the 2 cases where STA didn&#39;t work well.</div>
<div>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=
&#39;s better than giving a bad plan. The only trick is that by using the b=
rute force method of just hint injection to get a list of different plans, =
I managed to get a much better plan in seconds.</div>
<div>After this recent experience I started asking around and a surprising =
number of people I talked to seem to have similar experiences, thus I&#39;m=
 curious get =A0a little more solid feedback from more people what their ex=
perience are.</div>
<div><br></div><div>I&#39;ve blogged about a some of my experiences here:</=
div><div><br></div><div><a href=3D"http://db-optimizer.blogspot.com/2010/07=
/oracles-sql-tuning-pack-how-well-does.html">http://db-optimizer.blogspot.c=
om/2010/07/oracles-sql-tuning-pack-how-well-does.html</a></div>
<div><a href=3D"http://db-optimizer.blogspot.com/2010/07/oracles-sql-tuning=
-pack-part-2.html">http://db-optimizer.blogspot.com/2010/07/oracles-sql-tun=
ing-pack-part-2.html</a></div><div><a href=3D"http://db-optimizer.blogspot.=
com/2010/07/oracles-sql-tuning-pack-part-3.html">http://db-optimizer.blogsp=
ot.com/2010/07/oracles-sql-tuning-pack-part-3.html</a></div>
<div><br></div><div>Best=A0</div><div>Kyle Hailey</div><div><a href=3D"http=
://db-optimizer.blogspot.com">http://db-optimizer.blogspot.com</a></div><di=
v><br></div><div><br></div>

--0016363100c793cece048ad21f06--
--
http://www.freelists.org/webpage/oracle-l