Re: Using OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE in Production
From: Marcin Przepiorowski <pioro1_at_gmail.com>
Date: Mon, 8 Oct 2012 16:39:09 +0100
Message-ID: <CAGdek=yTb0yxk0k8sLTDANBQj8n7Nx=UGyxnmhY0PKdN9bDsow_at_mail.gmail.com>
On Mon, Oct 8, 2012 at 2:00 PM, Chris Dunscombe <cdunscombe_at_yahoo.com>wrote:
> 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.
>
>
Looks like problems with bind variable peeking - again if you have problem with couple of SQL you can think about SPB if you have problems with all probably you should find other solution. Did you try to disable bind peeking or get rid of histograms on all columns and gather histograms only for columns with skew data distribution ?
Date: Mon, 8 Oct 2012 16:39:09 +0100
Message-ID: <CAGdek=yTb0yxk0k8sLTDANBQj8n7Nx=UGyxnmhY0PKdN9bDsow_at_mail.gmail.com>
On Mon, Oct 8, 2012 at 2:00 PM, Chris Dunscombe <cdunscombe_at_yahoo.com>wrote:
> 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.
>
>
Looks like problems with bind variable peeking - again if you have problem with couple of SQL you can think about SPB if you have problems with all probably you should find other solution. Did you try to disable bind peeking or get rid of histograms on all columns and gather histograms only for columns with skew data distribution ?
Like Michael mentioned in his mail - first plan captured will be accepted until you run evolve - keep this in mind when you implement your solution.
-- Marcin Przepiorowski http://oracleprof.blogspot.com -- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 08 2012 - 17:39:09 CEST