Re: Histogram worthwhile?

You're right, it's an overstatement. And don't get me wrong, I think the optimizers ability to recognize skewed data via a histogram and pick the right plan based on the input to the query is a great feature. I just think it's silly to lock in a plan based on a first look at bind variables, and then ignore their values from there on out. I think it would have been better to stick with the standard costing (ignoring the histogram) when bind variables are in use. At least we'd get stability, if not the absolute best performance. I guess that's why they added the "_optim_peek_user_binds" parameter. At any rate, I think they have it right now in 11g. Although I haven't really had a chance to put it through it's paces in a production setting. We have several clients with 11g production systems, but none that I've really had to look closely at (so maybe that's a good initial indication). We're in the process of building out a new 11g RAC system that will be replacing a system that has had plan stability issues due to bind variable peeking, so it will be interesting to see how well 11g handles that out of the box. I'll try to post some info here after we get some testing done on that system. I'd be interested to hear of anyone else's experiences in that regard as well.

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com

On Feb 24, 2009, at 8:17 PM, Greg Rahn wrote:

> On Tue, Feb 24, 2009 at 8:24 AM, Kerry Osborne
> <kerry.osborne_at_enkitec.com> wrote:
>> I have to say that I think "Bind Variable Peeking" is a bug that
>> Oracle has
>> dressed up as a feature for the last several years. It just makes
>> no sense
>> at all to purposely introduce instability the way bind variable
>> peeking
>> does. In my opinion, histograms cause more problems than they solve
>> if you
>> don't take the time to apply literals appropriately in your
>> statements (i.e.
>> where you have skewed data and have built a histogram). Karen's
>> paper is a
>> great reference on the subject, by the way. Unfortunately, 10g's
>> default
>> stats gathering approach creates histograms all over the place.
>> Anyway, 11g
>> finally addresses the issue.
>
> I think it may be a bit of an overstatement that bind peeking is a
> bug. I have never heard anyone complain when they get better plans
> with bind peeking on. But then again, I really would not expect it.
> Better stated perhaps, don't mix binds on columns that have histograms
> and skew as it is a bit of a conflict of interest. On one hand you
> want to reuse the plan, on the other you don't. A bit schizophrenic
> at times maybe...
>
> Some of my notes on the topics:
> http://structureddata.org/2008/03/26/choosing-an-optimal-stats-gathering-strategy/
> http://structureddata.org/2007/11/21/troubleshooting-bad-execution-plans/
>
> I'd be interested in hearing from people on 11g using histograms and
> binds to get a feel what the experience is with the new feature
> designed to address the known issues.
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
> --
> http://www.freelists.org/webpage/oracle-l
>
>

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