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?
- 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.
- 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
