Re: Histogram worthwhile?
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: kerry.osborne@xxxxxxxxxxx
- Date: Tue, 24 Feb 2009 18:17:41 -0800
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
Other related posts:
