RE: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in Production
- From: "Tefft, Michael J" <Michael.J.Tefft@xxxxxxxxxx>
- To: <cdunscombe@xxxxxxxxx>, "Marcin Przepiorowski" <pioro1@xxxxxxxxx>
- Date: Mon, 8 Oct 2012 10:32:42 -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 Chris Dunscombe
Sent: Monday, October 08, 2012 9:01 AM
To: Marcin Przepiorowski
Cc: Oracle-L Freelists
Subject: Re: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in
Production
Hi Marcin,
The reason for using plan stability is ensure predictable performance,
we have experienced a number of instances where execution plans have
"flipped" from good to bad and caused a number of significant issues.
The "plan" is to use SPBs to prevent these issues from happening.
Thanks,
Chris
________________________________
From: Marcin Przepiorowski <pioro1@xxxxxxxxx>
To: cdunscombe@xxxxxxxxx
Cc: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, 8 October 2012, 13:32
Subject: Re: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in
Production
On Mon, Oct 8, 2012 at 9:57 AM, Chris Dunscombe <cdunscombe@xxxxxxxxx>
wrote:
Hi,
>We've got a large RAC database where we've been using SPBs for about 6
months. The SPBs were generated in the perf test environment and then
migrated across. There are still plenty of SQL statements in live that
aren't using SPBs. Now people are asking about setting
>
>OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE
>
>in production, we've run with setting in perf test with full volume
testing and not seen any obvious bad side effects. My natural reaction
is to be cautious and say no.
>
>Am I being sensible, overly cautious or what? Anybody's experiences in
this area would be most welcome.
>
>Version 11.2.0.3 on RHEL 5.6
>
>
Hi,
There is couple of gotchas :
- SPB is consuming space in SYSAUX
- you need good evolve and check procedure - automatic evolve procedure
could activate a non-optimal plan and you need to procedure to capture
that easily and fix it
- bugs like after dropping index Oracle is always using FTS even if
other indexes are available
But number one is WHY do you want to use plan stability feature ? If all
your SQL are optimized by CBO in wrong way it is very likely that your
statistics and/or system parameters are not describing your data in
correct way and CBO can't produce proper plans.
Did you reviewed problem from that perspective ? Of course it is
possible that your data / application need forced plan stability but in
my opinion it should be used in limited scope only for queries that
misbehave due to strange/unknown circumstances.
regards,
--
Marcin Przepiorowski
http://oracleprof.blogspot.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
