Re: Unexplained Plan Change

From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Date: Mon, 21 Jun 2010 18:09:48 -0500
Message-Id: <1FDFC804-FD20-49AB-8C71-B14091420CA5_at_enkitec.com>



Bind variable peeking is often a cause of unexpected plan changes. I'm not sure why you think this issue has been resolved in 10.2.0.4 because it has not been resolved, even as of 11.2 (although ACS is a step in the right direction). Generally with bind variable peeking issues the plans have a tendency to flip flop (i.e. go back and forth between 2 or more plans). But not always. Stephane's idea about an out of bounds value is a good one. Maybe someone put in a ridiculous value right after the statement had been flushed.

By the way, explain plan doesn't always tell the truth. Better to actually run the statement and see what plan actually gets picked (via dbms_xplan.display_cursor).

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

On Jun 21, 2010, at 12:15 PM, Stephane Faroult wrote:

> Not sure this is the case here, but I have seen in the past something
> very similar coming from a "peeked" bind variable the value of which was
> outside the bounds known by Oracle (low_value/high_value in
> dba_tab_columns - stored in hexa, utl_raw and the like may be useful for
> converting).
>
> HTH
>
>
> Stephane Faroult
> RoughSea Ltd <http://www.roughsea.com>
> Konagora <http://www.konagora.com>
> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
>
>
> LB wrote:

>> I've got a query that ran this morning that for some reason decided to
>> jump ship off its normal plan and use a different plan.  This is a
>> very straightforward single table query.  When the query runs normally
>> it uses the correct index and returns results very quickly.  This
>> table and its 2 indexes (one PK and one non-unique) have their
>> respective statistics gathered nightly at 1AM.  At 8AM this morning,
>> Oracle decided that using the PK index was the best choice and so it
>> gobbled up 22 seconds doing single block I/O.  Now I understand that
>> gathering statistics can cause a SQL plan to change but that plan
>> change should remain effect all things being equal.  

>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jun 21 2010 - 18:09:48 CDT

Original text of this message