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

dbhelper發表於2015-02-24
生產系統中總是可能碰到各種各樣的sql問題,其中大部分問題都和執行計劃有關,執行計劃出現問題有很多原因導致,比如統計資訊過舊,比如資料的分佈極不均勻等等都會導致執行計劃出現很大的偏差。可能有的sql語句在一週時間內,有一天執行只需要5秒,過了幾天之後卻需要幾個小時,這種執行計劃時需要格外重視的,這種問題目前oracle官方沒有提供很有效的工具,但是oracle內部卻已經提供了很多豐富的資料,完全可以基於這些資訊來甄別,oracle專家 kerry osborne就在他的部落格中分享了一個指令碼,就是專門來甄別這些不穩定的執行計劃,如果需要下載可以直接從以下連結得到。

我對這個指令碼進行了包裝,直接包裝在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

執行指令碼的命令為:
ksh get_unstable_plan.sh 
如果想去預設值,則標準方差為2,最小執行時間會為0.1秒
得到的結果如下:
SQL_ID        SUM(EXECS)   MIN_ETIME   MAX_ETIME   NORM_STDDEV
------------- ---------- ----------- ----------- -------------
3z4j311583sk6          3       29.02    1,289.51       30.7138
0m3s751sxzva5         13        3.21      405.64       71.6833
fkptmvqbtv85k          7         .78      374.25      340.5359
6nm4yy7pgdzad          2         .12      347.92    1,996.4307

可以看到有些語句的差別還是很大的,本來幾十秒,但是有時候執行又是半個小時,本來執行在毫秒,但是有時候執行時間在幾分鐘,這些都是需要注意的問題。

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

相關文章