Re: Histogram worthwhile?
Date: Tue, 24 Feb 2009 18:17:41 -0800
Message-ID: <a9c093440902241817o39e84823l973ec83f4ff29087_at_mail.gmail.com>
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-lReceived on Tue Feb 24 2009 - 20:17:41 CST