RE: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in Production

From: Tefft, Michael J <Michael.J.Tefft_at_snapon.com>
Date: Mon, 8 Oct 2012 10:32:42 -0500
Message-ID: <8C0087E93F6A9A459051869F5D574891F3A3E3_at_LISL-XBCK-C1-V2.snaponglobal.com>

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 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_at_gmail.com> To: cdunscombe_at_yahoo.com
Cc: Oracle-L Freelists <oracle-l_at_freelists.org> 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_at_yahoo.com> 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
--
http://www.freelists.org/webpage/oracle-l


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

Received on Mon Oct 08 2012 - 17:32:42 CEST