Re: Unexplained Plan Change

From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>

To: sfaroult@xxxxxxxxxxxx

Date: Mon, 21 Jun 2010 18:09:48 -0500

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