Re: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in Production

From: Chris Dunscombe <cdunscombe_at_yahoo.com>
Date: Mon, 8 Oct 2012 14:00:39 +0100 (BST)
Message-ID: <1349701239.61307.YahooMailNeo_at_web29703.mail.ird.yahoo.com>

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

Received on Mon Oct 08 2012 - 15:00:39 CEST