Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

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

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 10 Mar 2005 08:01:55 -0700
Message-ID: <42306163.6010509@centrexcc.com>

Marquez, Chris wrote:

>>>Do you per chance gather histograms ( "for=20
>>>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=3Dforce)=20

>
> Yes again.

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

>
>

>>>I have the feeling you are setting yourself up for the=20
>>>bind 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 when it parses the plan as if the sql contained the value rather than a bind variable. Bind variables (even faked ones through c_s=force) let the optimizer reuse the sql without parsing (that's the idea of binding). Therefore, the first bind value - the one that causes the parse - determines the access path for all other executions. Now if you have a skewed data distribution and a histogram and that first bind value is such that a full scan is appropriate all the other executions will use a full scan as well - until the sql is parsed again. Maybe because it aged out, maybe because someone changed "AND" to "and", maybe because one of the tables or indexes got somehow modified or analyzed, 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 columns should have a histogram and some non-indexed columns DO need one. Collect histograms specifically and only for those columns that need it.
  2. Don't listen to *guru*s
-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 10 2005 - 10:05:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US