RE: SQL Profiles and Performance
From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Mon, 16 Apr 2012 18:51:46 +0100
Message-ID: <CABe10sYidOdw25y0pPvgw2xfgNomQR+SCKjWEKQRG2aguSVveg_at_mail.gmail.com>
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_at_snapon.com> 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_at_enkitec.com]
> Sent: Monday, April 16, 2012 10:14 AM
> To: Tefft, Michael J
> Cc: dombrooks_at_hotmail.com; jed_walker_at_cable.comcast.com;
> oracle-l_at_freelists.org
> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] On Behalf Of Dominic Brooks
> > Sent: Friday, April 13, 2012 3:50 PM
> > To: jed_walker_at_cable.comcast.com; oracle-l_at_freelists.org
> > 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
> >
> >
> >
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
Date: Mon, 16 Apr 2012 18:51:46 +0100
Message-ID: <CABe10sYidOdw25y0pPvgw2xfgNomQR+SCKjWEKQRG2aguSVveg_at_mail.gmail.com>
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_at_snapon.com> 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_at_enkitec.com]
> Sent: Monday, April 16, 2012 10:14 AM
> To: Tefft, Michael J
> Cc: dombrooks_at_hotmail.com; jed_walker_at_cable.comcast.com;
> oracle-l_at_freelists.org
> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] On Behalf Of Dominic Brooks
> > Sent: Friday, April 13, 2012 3:50 PM
> > To: jed_walker_at_cable.comcast.com; oracle-l_at_freelists.org
> > 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
> >
> >
> >
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Apr 16 2012 - 12:51:46 CDT