Re: Histogram worthwhile?

From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Date: Tue, 24 Feb 2009 10:24:13 -0600
Message-Id: <5DDAE1F7-AB6A-4A91-8838-AB475901A47E_at_enkitec.com>

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. Here's a script that I find useful for 10g.

unstable_plan.sql - It uses an analytic function to calculate a standard deviation on the average elapsed time by plan. It then lists the statements which exceed a threshold (i.e. have multiple plans with a large difference in average elapsed time).

set lines 155

col execs for 999,999,999
col min_etime for 999,999.99
col max_etime for 999,999.99
col avg_etime for 999,999.999

col avg_lio for 999,999,999.9
col norm_stddev for 999,999.9999
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1 select * from (
select sql_id, sum(execs), min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev from (
select sql_id, plan_hash_value, execs, avg_etime, stddev(avg_etime) over (partition by sql_id) stddev_etime from (
select sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
(sum(elapsed_time_delta)/decode(sum(nvl(executions_delta,0)), 0,1,sum(executions_delta))/1000000) avg_etime -- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0), 0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number and executions_delta > 0
group by sql_id, plan_hash_value
)
)
group by sql_id, stddev_etime
)
where norm_stddev > nvl(to_number('&min_stddev'),2) and max_etime > nvl(to_number('&min_etime'),.1) order by norm_stddev
/

There's more info about this script on my blog if you're interested.

By the way, I have been lurking on this list for years, although I've never posted before. But this issue drives me crazy! Hopefully simply replying to the email list will work!

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com

On Feb 24, 2009, at 9:36 AM, Cary Millsap wrote:

> "Managing Statistics for Optimal Query Performance," by Karen Morton
> http://method-r.com/downloads/doc_details/11-managing-statistics-for-optimal-query-performance-karen-morton
>
> Cary Millsap
> http://method-r.com
> http://carymillsap.blogspot.com
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 24 2009 - 10:24:13 CST