RE: Bind variable peeking and Dynamic sampling

From: fairlie rego <fairlie_r_at_yahoo.com>
Date: Tue, 8 May 2007 18:34:32 -0700 (PDT)
Message-ID: <380656.8892.qm@web31907.mail.mud.yahoo.com>

Wolfgang,

I am facing the same problem at this site as Brandon... We have deleted histograms and changed the stats collection job not to collect histograms but still see bind variable peeking.

Thanks
Fairlie

Wolfgang Breitling <breitliw_at_centrexcc.com> wrote: I'd be very interested in how you have different plans due to bind variable peeking without histograms on the predicate columns. The only scenario I can think of is that some queries use predicate values (far) outside the hi-lo range of the column statistic, or, more generally, the queries use ranges which are vastly different - i.e. some are only after a tiny sliver of the full range while others are after almost the entire range.

As for the hint idea, I am against it. There are already far too many hints and far too many people are (or seem to be) using far too many of them. It is my opinion that many, if not most, can be avoided by database design, sql coding, or proper statistics or any combination thereof.

At 10:57 AM 5/8/2007, Allen, Brandon wrote:
>Not that Wolfgang's suggestion isn't a good one, but just FYI - in my
>case, I got rid of histograms and am still having the problems of
>inappropriate plan sharing due to bind variable peeking.
>
>I've filed enhancement request 6030306 to add a hint or some other
>method of forcing Oracle to always hard-parse and bind-peek for certain
>queries. If any of you agree this would be a good enhancement, please
>submit an enhancement request of your own and reference the above ER
>number and if enough of us ask for it, hopefully they'll add it soon.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email

--
http://www.freelists.org/webpage/oracle-l





          Fairlie Rego
Senior Oracle Consultant
  http://el-caro.blogspot.com/
  M: +61 402 792 405
   






 
---------------------------------
Bored stiff? Loosen up...
Download and play hundreds of games for free on Yahoo! Games.
--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 08 2007 - 20:34:32 CDT