RE: SQL Profiles and Performance
- From: "Tefft, Michael J" <Michael.J.Tefft@xxxxxxxxxx>
- To: <dombrooks@xxxxxxxxxxx>, <jed_walker@xxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 16 Apr 2012 06:56:44 -0500
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
> From: Jed_Walker@xxxxxxxxxxxxxxxxx
> To: oracle-l@xxxxxxxxxxxxx
> Subject: SQL Profiles and Performance
> Date: Fri, 13 Apr 2012 13:06:53 +0000
>
> Has anyone had experience with whether using SQL Profiles
> (optimizer_capture_sql_plan_baselines= TRUE,
> optimizer_use_sql_plan_baselines=TRUE) is detrimental to overall
> performance. I'm talking about problems I've heard with plan changing
> unexpectedly, but with overall performance. We have a system that has
> this set and I'm wondering if it might perform better without it.
> (This is 11.2.0.3.0 6-node RAC on RedHat 5.6) What I'm wondering
> though is if SQL profiles functioanality has much overhead that might
> impact overall performance? This is OLTP and I wonder if things might
> be better without them? (I have production only so testing means
> getting maintenance and turning it off)
>
> Thanks,
>
> Jed S. Walker, OCP
> Principal Engineer, Databases
> National Video Advanced Services
> Office: 303.267.6759
> P Please only print this email if necessary. Consider the environment and
> cost.
>
> CONFIDENTIAL NOTICE
> This electronic mail transmission and any accompanying documents
> contain information belonging to the sender, which may be confidential
> and legally privileged. If you are not the intended recipient, any
> disclosure, copying, distribution or action taken in reliance on the
> message is strictly prohibited. If you have received this message in
> error, please delete it immediately. Thank You
>
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
