Re: Histogram worthwhile?

From: gengmao <gengmao_at_gmail.com>
Date: Wed, 25 Feb 2009 17:45:09 +0800
Message-ID: <afe23fd50902250145s7e3fd9bfq9a91d223e7b38f7c_at_mail.gmail.com>

Why bind peeking can only choose one plan? I think oracle could store multiple plans for a SQL (one sql_id could have multiple hash_value, right?), just based on histogram size. When executing query, oracle could peek the bind value, check what histogram bucket the value drops in, then generate or reuse a execution plan for that histogram bucket.

I think such an approach has following pros. 1) plans could be reused 2) its parsing cost is still less than hard parse (no bind variable) 3) plans are optimal for each histogram bucket. 4) Histogram buckets size determines the amount of plans. We can adjust buckets size to balance the cost of large amount stored plans and the optimal degree for a SQL. Easy for tuning.

Maybe it's a naive thought. While hope you could explain.

Thanks,
Eric Geng

On Wed, Feb 25, 2009 at 10:17 AM, Greg Rahn <greg_at_structureddata.org> 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

Received on Wed Feb 25 2009 - 03:45:09 CST