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 Corporationwww.centrexcc.com
______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System.For more information please visit http://www.messagelabs.com/email ______________________________________________________________________
-- //www.freelists.org/webpage/oracle-l
