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