通过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 <<EOF
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

可以看到有些语句的差别还是很大的,本来几十秒,但是有时候执行又是半个小时,本来执行在毫秒,但是有时候执行时间在几分钟,这些都是需要注意的问题。

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。