RE: 11.2.0.3 SQL Profile Confusion

  • From: "Larry Elkins" <elkinsl@xxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 03 Nov 2013 09:25:53 -0600

I'm curious how others use the sql tuning advisor since we don't see a lot of 
discussions of the topic on this list. I use it as a
*supporting* tool, just another tool in the toolbox if you will. I was slow to 
warm up to the STA. But over time I've found myself
using it more often, as a supporting tool.

For example, in spite of numerous questionable recommendations we'll see, I'll 
sometimes use it as a second set of eyes, a "second
opinion" if you will, to sanity check my thoughts, no different than if I run 
some ideas by a colleague. I certainly don't allow the
automatic STA to automatically apply recommendations. But whether used with 
STS's I manually create, or gather from AWR data, or
used for specific statements, there are numerous times it has been useful, 
especially when dealing with large sets of statements on
systems I'm unfamiliar with. It can make it faster, in some cases, to pick up 
on patterns where the CBO is *consistently* making a
poor choice, thus pointing to some areas where I may need to focus on and tweak 
the stats gathering process. These are things I
would pick up on myself (I'd hope to think), but sometimes it can get me there 
faster.

I also make a point of routinely reviewing the automatic SQL tuning advisor 
output, including index recommendations, to see if some
of them would make sense and be beneficial overall. And though I'll normally 
try to get to the desired plan via CBO approaches,
sometimes that's simply not feasible, for a variety of reasons, and I may go 
ahead and accept some of the recommendations, using
calls to the tuning packages to rename and add sensible descriptions. 

In short, I see value in it as a supporting tool, while taking the 
recommendations with a *big* grain of salt. But it can sometimes
get me to a point and solution(s) that may have taken me longer to get there on 
my own. Not so much so when dealing with apps and
DB's I heavily support on a daily basis, but more so with ones that are "new" 
to me, or I deal with less frequently. 

I haven't work with it in 12c, so kind of curious what people are seeing there.


Larry G. Elkins
elkinsl@xxxxxxxxxxx
Cell: 214.695.8605

> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
> Behalf Of Jared Still
> Sent: Saturday, November 02, 2013 4:46 PM
> To: Andy Klock
> Cc: Chris Taylor; oracle-l@xxxxxxxxxxxxx
> Subject: Re: 11.2.0.3 SQL Profile Confusion
> 
> 
> On Fri, Nov 1, 2013 at 12:34 PM, Andy Klock <andy@xxxxxxxxxxxxxxx> wrote:
> 
> 
>       SQL Tuning Advisor was grasping at straws? :)
> 
> 
> 
> Not an unknown phenomenon...
> 
> I believe there are folks in Oracle however that are quite interested in 
> making the SQL Tuning Adviser
> more useful.
> 
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: 
> http://jkstill.blogspot.com Home
> Page: http://jaredstill.com

--
//www.freelists.org/webpage/oracle-l


Other related posts: