RE: Bind variable peeking and Dynamic sampling
- From: fairlie rego <fairlie_r@xxxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx, Brandon.Allen@xxxxxxxxxxx
- Date: Tue, 8 May 2007 18:34:32 -0700 (PDT)
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@xxxxxxxxxxxxx> 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
______________________________________________________________________
--
//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.
Other related posts:
