RE: SQL Profiles and Performance

  • From: "Tefft, Michael J" <Michael.J.Tefft@xxxxxxxxxx>
  • To: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
  • Date: Mon, 16 Apr 2012 14:37:19 -0500

Re: 'selectively applying' - We originally thought we would scrutinize every 
proposed new plan, but that quickly proves to be horribly unrealistic. Let 
Oracle do its EVOLVE  process and there will be few worries.
 

You can not both collect and 'selectively use' baselines. Remember that the 
first captured baseline for any given SQL will be in an 'accepted' state. 
Unless you actively change their state, these will be the plans that will be 
used, if you are 'using' baselines. 

 

FYI, here is a link to a presentation I gave at our local Oracle users group:

http://www.neooug.org/generaltab/downloads/SQL_Plan_Management_for_11g_upgrade.ppt
 <http://www.ppt2txt.com/r/7f46d50f/> 

 

Mike

 

From: Niall Litchfield [mailto:niall.litchfield@xxxxxxxxx] 
Sent: Monday, April 16, 2012 1:52 PM
To: Tefft, Michael J
Cc: jed_walker@xxxxxxxxxxxxxxxxx; dombrooks@xxxxxxxxxxx; Kerry Osborne; 
oracle-l@xxxxxxxxxxxxx
Subject: RE: SQL Profiles and Performance

 

I'm with Kerry, I see a lot more use in capturing all baselines and selectively 
applying rather than automatically applying all existing baselines. I'd also 
suggest that one might want to be on as recent a release as possible, given the 
newness of the feature. 

On Apr 16, 2012 5:40 PM, "Tefft, Michael J" <Michael.J.Tefft@xxxxxxxxxx> wrote:

The default value for OPTIMIZER_USE_SQL_PLAN_BASELINES is TRUE.
The default value for OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is FALSE.

There is little harm in using the default settings. If you have no baselines, 
then USE has no real effect. If your application vendor provides baselines with 
their installation, your database can make use of them.

The caution applies to changing CAPTURE to YES.

Mike


-----Original Message-----
From: Kerry Osborne [mailto:kerry.osborne@xxxxxxxxxxx]
Sent: Monday, April 16, 2012 10:14 AM
To: Tefft, Michael J
Cc: dombrooks@xxxxxxxxxxx; jed_walker@xxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: SQL Profiles and Performance

Great replies Michael and Dominic. I meant to send a note earlier as well but 
got distracted. Here are my somewhat random thoughts on the matter.

Jed,

 I think you are in a small minority of shops with the use=true. I've been 
asking people about it for years and so far only a handful of people have given 
a qualified yes to question of whether they are using the capture feature. It 
is certainly not a scientific survey but gives a general feeling that most 
shops do not have it enabled. This means you are more likely to uncover issues 
that haven't already been dealt with. That said, it's about time that the 
feature started to take hold. It is a very good idea as long as there is some 
attention paid to evolving plans when new (better) ones are discovered. If no 
evolving is done then performance may begin to suffer later on as data changes. 
And there is also the issue that Michael mentioned about not getting a good 
plan when a new statement is introduced. I'd agree with Dominic though that I 
probably would be hesitant to disable it (given you have no other environment 
in which to test) unless you can point to specific issues th
 at it is causing. I do think we'll see it become much more main stream in 12. 
So if that's the case, learning as much as you can about how it works in your 
current system will be of value.

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com






On Apr 16, 2012, at 6:56 AM, Tefft, Michael J wrote:

> With SQL Plan Baselines, I advise caution on setting 
> optimizer_capture_sql_plan_baselines=TRUE. It works, but there can be subtle 
> consequences.
>
> Here is what I wrote on this topic, in an IOUG article I wrote 2 years ago:
>
> optimizer_capture_sql_plan_baselines
>
> This parameter causes new plans generated by the optimizer to be added to the 
> baselines, either as accepted plans (for new SQL) or as non-accepted plans 
> (for new plans for existing SQL). The default setting for this parameter is 
> FALSE.
>
> The decision to set this parameter to TRUE may have a more subtle impact. The 
> first plan captured for each SQL statement will be created as an accepted 
> baseline plan, while those that follow will be created as non-accepted. If 
> the non-accepted plans have no opportunity to be evaluated, then the plan 
> selection is not even "whatever seemed good at the time" (as it would have 
> been without baselines being used) but is limited to "whatever seemed good 
> the first time".  This can be a particular problem when implementing new 
> application functions with new SQL and new, growing tables: plans that 
> perform well the first day, when new tables are practically empty, can 
> rapidly lead to degraded performance.
>
> If the decision is made to both use and capture baselines, then an EVOLVE 
> process should be in place. This will allow new plans to become accepted 
> plans if appropriate, and ensure that a satisfactory set of plans are 
> available.
>
> Since this parameter will cause creation of baseline plans, the advice of 
> application vendors should be sought and carefully considered.
>
> If a mixture of behaviors is desired (such as enabling capture for some 
> applications in the database, while disabling it for others), the issue 
> becomes more difficult. These parameters may only be set at the system level 
> or at the session level; they can not be set by parsing schema, for example. 
> To force different session-level configurations for different applications, 
> login triggers may be a workable mechanism.
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
> Behalf Of Dominic Brooks
> Sent: Friday, April 13, 2012 3:50 PM
> To: jed_walker@xxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> Subject: RE: SQL Profiles and Performance
>
> Jed,
>
> This concerns sql plan baselines not sql profiles - they both use the same 
> underlying tables (sqlobj$, sqlobj$data, sqlobj$auxdata) but there are some 
> significant differences.
> SQL Profiles are designed to provide statistical adjustments to the optimizer.
> Baselines are there to enforce a particular plan.
>
>
>
> There were some nasty overheads related to the recursive merge of executions 
> statistics into the tables mentioned above in version 11.2.0.2 but those are 
> fixed in 11.2.0.3.
>
> With that bug fixed, the overhead should be minimal.
> What happens is that the optimizer still does the optimisation steps it 
> normally does but if the best (by cost) plan that it comes up with does not 
> match the plan hash in the baseline then it will store the other plan for 
> future evolution and revert to the baselined plan - the idea being that it 
> gives you stability, let's you know if it has come up with something that 
> looks better and then later can verify whether it actually is better or not 
> in reality.
> This extra work does not come for free obviously, but really it should be 
> minimal.
>
> Bottom line - if these features are not causing any problems then if it were 
> me then I'd stick with it for the moment, particularly as you only have a 
> prod environment.
> (Having said that, having gone through the pain of the recursive merge issue 
> in 11.2.0.2, I'm running with use=TRUE and captureúLSE so that I only have a 
> select few baselined plans.)
>
> Cheers,
> Dominic
>
>
>

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




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


Other related posts: