RE: Bind variable peeking and Dynamic sampling

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

Other related posts: