通過shell指令碼來得到不穩定的執行計劃

生產系統中總是可能碰到各種各樣的sql問題，其中大部分問題都和執行計劃有關，執行計劃出現問題有很多原因導致，比如統計資訊過舊，比如資料的分佈極不均勻等等都會導致執行計劃出現很大的偏差。可能有的sql語句在一週時間內，有一天執行只需要5秒，過了幾天之後卻需要幾個小時，這種執行計劃時需要格外重視的，這種問題目前oracle官方沒有提供很有效的工具，但是oracle內部卻已經提供了很多豐富的資料，完全可以基於這些資訊來甄別，oracle專家 kerry osborne就在他的部落格中分享了一個指令碼，就是專門來甄別這些不穩定的執行計劃，如果需要下載可以直接從以下連結得到。
http://kerryosborne.oracle-guy.com/scripts/unstable_plans.sql
我對這個指令碼進行了包裝，直接包裝在shell裡面，直接執行指令碼即可。
輸入引數可以靈活指定標準方差，我簡單對標準方差做一個解釋，
比如有兩組同學，每組三個同學，第一組同學的成績為69 70 71， 平均成績為70分。第二組為60,70,80，平均成績也為70分。
雖然平均成績相同，標準方差卻不同，標準方差就是來統計資料的離散程度。
比如第一組的同學，假設標準方差為a,則a^2=(69-70)^2+(70-70)^2+(71-70)^2)/3=0.67
第二組同學，標準方差為a^2=((60-70)^2+(70-70)^2+(80-70)^2)/3=67
得出的標準方差差別就是這麼大，個人感覺就是把資料的差別放大了，感覺還是挺實用的。
指令碼如下：
sqlplus -s $DB_CONN_STR@$SH_DB_SID < 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($1,2)
and max_etime > nvl($2,.1)
order by norm_stddev
/
EOF

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1430606/，如需轉載，請註明出處，否則將追究法律責任。