Antwort: Re: Histogram worthwhile?
- From: Martin Klier <Martin.Klier@xxxxxxxxxx>
- To: kerry.osborne@xxxxxxxxxxx
- Date: Wed, 25 Feb 2009 08:58:26 +0100
Hi,
maybe you want to have a look at Karen Morton's recent paper on managing
statistics, IMHO she explains the whole issue in a nice way, 10g vs. 11g as
well.
http://method-r.com/downloads/doc_download/11-managing-statistics-for-optimal-query-performance-karen-morton
Regards
Martin
--
Mit freundlichem Gruß
Martin Klier
------------------------------------------------------------------------------
Klug GmbH integrierte Systeme
Lindenweg 13, D-92552 Teunz
Tel.: +49 9671/9216-245
Fax.: +49 9671/9216-112
mailto: martin.klier@xxxxxxxxxx
www.klug-is.de
------------------------------------------------------------------------------
Geschäftsführer: Johann Klug, Roman Sorgenfrei
Sitz der Gesellschaft: Teunz, USt-ID-Nr. DE175481608,
HRB Nr. 2037, Amtsgericht Amberg
oracle-l-bounce@xxxxxxxxxxxxx schrieb am 25.02.2009 07:07:21:
> Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
> Gesendet von: oracle-l-bounce@xxxxxxxxxxxxx
>
> 25.02.2009 07:09
>
> Bitte antworten an
> kerry.osborne@xxxxxxxxxxx
>
> An
>
> greg@xxxxxxxxxxxxxxxxxx
>
> Kopie
>
> oracle-l@xxxxxxxxxxxxx
>
> Thema
>
> Re: Histogram worthwhile?
>
> Greg,
>
> 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@xxxxxxxxxxx> 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
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Antwort: Re: Histogram worthwhile? - Martin Klier
