Re: What else can cause different optimizer plan?

From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Date: Thu, 8 Dec 2011 14:21:02 -0600
Message-Id: <D35065B8-D897-4902-884E-727B0B05CCDE_at_enkitec.com>

It's a long list. Here are a few more to come readily to mind.

Different optimizer environment (i.e. any of a whole bunch of parameters is different) Different underlying objects (your MYDATA table is not the same as Joe's MYDATA table) Data changes (range of value significantly exceeds what stats says it is) Bind Variable Peeking kicks in
Cardinality Feedback feature kicks in
Adaptive Cursor Sharing kicks in
Someone creates an Outline, Profile, or Baseline on the statement Size of memory structures (can determine whether a statement gets run with PX plan or not)

The most common cause of plan instability is probably the combination of histograms and bind variables (i.e. bind variable peeking).

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com

On Dec 8, 2011, at 2:04 PM, Gwen Shapira wrote:

> different statistics on objects, different system statistics or
> different bind variables.
>
> Gwen
>
> On Thu, Dec 8, 2011 at 10:39 AM, <paul.baumgartel_at_ubs.com> wrote:

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

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

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

Received on Thu Dec 08 2011 - 14:21:02 CST