Fwd: Histogram worthwhile?
- From: gengmao <gengmao@xxxxxxxxx>
- To: ORACLE-L@xxxxxxxxxxxxx
- Date: Fri, 27 Feb 2009 11:55:35 +0800
---------- Forwarded message ----------
From: gengmao <gengmao@xxxxxxxxx>
Date: Fri, Feb 27, 2009 at 11:55 AM
Subject: Re: Histogram worthwhile?
To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
Jonathan,
Thanks for pointing it out. I find a blog post explained Adaptive Cursor
Sharing in great details,
http://optimizermagic.blogspot.com/2007/12/why-are-there-more-cursors-in-11g-for.html.
Karen Morton's white paper also explain it well.
As Karen said in the paper, "The whole bind peeking problem seems to be
solved with Oracle version 11." It is awesome. And it will be greater if
Oracle explains how histogram buckets and statistics affect bind_sensitive
and bind_aware.
Thanks,
Eric
They did not make big changes for cursor parsing, instead chose a steady
change strategy
On Thu, Feb 26, 2009 at 4:10 AM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx
> wrote:
>
> That describes part of what Oracle has done in 11g with adaptive cursor
> sharing.
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message ----- From: "gengmao" <gengmao@xxxxxxxxx>
> To: <greg@xxxxxxxxxxxxxxxxxx>
> Cc: <kerry.osborne@xxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
> Sent: Wednesday, February 25, 2009 9:45 AM
> Subject: Re: Histogram worthwhile?
>
>
>
> 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
>>
>>
>
Other related posts:
- » Fwd: Histogram worthwhile? - gengmao
