55 lines
2.0 KiB
SQL
55 lines
2.0 KiB
SQL
----------------------------------------------------------------------------------------
|
|
--
|
|
-- File name: unstable_plans.sql
|
|
--
|
|
-- Purpose: Attempts to find SQL statements with plan instability.
|
|
--
|
|
-- Author: Kerry Osborne
|
|
--
|
|
-- Usage: This scripts prompts for two values, both of which can be left blank.
|
|
--
|
|
-- min_stddev: the minimum "normalized" standard deviation between plans
|
|
-- (the default is 2)
|
|
--
|
|
-- min_etime: only include statements that have an avg. etime > this value
|
|
-- (the default is .1 second)
|
|
--
|
|
-- See http://kerryosborne.oracle-guy.com/2008/10/unstable-plans/ for more info.
|
|
---------------------------------------------------------------------------------------
|
|
|
|
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
|
|
and elapsed_time_delta > 0
|
|
and s.snap_id > nvl('&earliest_snap_id',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
|
|
/
|