Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Mutating (SQL) execution plan!? that possible...strangest thing I have every seen

RE: Mutating (SQL) execution plan!? that possible...strangest thing I have every seen

From: Marquez, Chris <>
Date: Thu, 10 Mar 2005 10:40:52 -0500
Message-ID: <>

Thank you Wolfgang...I am forever grateful.

I'm going to rebuild the stats without the use of histograms / "skewonly" options.

This goes to my core belief that Oracle has many options and we don't need to use them all.
Unfortunately developers see an article about a new index, or a new feature, and they think all of there problems will be solved. I may be slow paced DBA, but I never (want) use an Oracle option, feature or parameter without some expectation of what does...good *and* bad.
Nothing if free...

Thanks again,


-----Original Message-----
From: Wolfgang Breitling []=20 Sent: Thursday, March 10, 2005 10:02 AM
To: Marquez, Chris
Subject: Re: Mutating (SQL) execution plan!? that possible...strangest thing I have every seen

Marquez, Chris wrote:

>>>Do you per chance gather histograms ( "for=3D20
>>>all indexed columns size skewonly" or somethink like it?).

> Yes, Yes, Yes, we do!
> At the recommendation of Oracle *guru*? (web site article).

>>>(you also seem to run with cursor_sharing=3D3Dforce)=3D20


> Yes again.

Fix the application (obviously you can change the sql) to use bind=20 variables where appropriate and dump that cursor_sharing=3Dforce crutch.


>>>I have the feeling you are setting yourself up for the=3D20 bind=20
>>>variable peeking trap and appear to be falling into it.


> What the heck is "bind variable peeking trap"?
The Oracle 9 (and higher) optimizer uses the bind value of the variable=20
when it parses the plan as if the sql contained the value rather than a=20
bind variable. Bind variables (even faked ones through c_s=3Dforce) let=20
the optimizer reuse the sql without parsing (that's the idea of=20 binding). Therefore, the first bind value - the one that causes the=20 parse - determines the access path for all other executions. Now if you=20 have a skewed data distribution and a histogram and that first bind=20 value is such that a full scan is appropriate all the other executions=20 will use a full scan as well - until the sql is parsed again. Maybe=20 because it aged out, maybe because someone changed "AND" to "and", maybe

because one of the tables or indexes got somehow modified or analyzed,=20 maybe someone issued an "alter system flush shared_pool". Then things start over again.
Read up on bind variable peeking in the concepts or performance guide.

> Thanks...any suggestions?

  1. Stop collecting histograms on "all indexed columns". Not all indexed=20 columns should have a histogram and some non-indexed columns DO need=20 one. Collect histograms specifically and only for those columns that=20 need it.
  2. Don't listen to *guru*s


Wolfgang Breitling
Centrex Consulting Corporation

Received on Thu Mar 10 2005 - 10:44:10 CST

Original text of this message