Re: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in Production
Date: Mon, 8 Oct 2012 13:32:15 +0100
Message-ID: <CAGdek=zn1LjecSsOpYLf3zsEfcgmquju_1=fsYaWsu4L4GZ3AQ_at_mail.gmail.com>
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-lReceived on Mon Oct 08 2012 - 14:32:15 CEST