Re: Histogram worthwhile?
- From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
- To: greg@xxxxxxxxxxxxxxxxxx
- Date: Wed, 25 Feb 2009 00:07:21 -0600
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
Other related posts:
