在通常的数据库运维中，经常担心数据库一些重要的SQL的性能突然变差，导致整个数据库性能糟糕响应缓慢，甚至让数据库瘫痪的情况时有出现，往往这个时候要在事后通过awrsqrpt.sql脚本从top sql中去抓取分析执行变化的SQL语句，今天脑袋一闪想有没有办法有脚本可以自己去对比呢？回家就开始敲命令测试，测试的平台为11gr1版本，通过dba_hist_sqlstats数据字典中抓去sql id数据进行对比，通过对比获取相关的SQL_ID，以及此SQL的最优执行计划是什么。通过该sql可以设定一个固定的执行时间，通过分别查出来的数据进行监控，对执行计划产生误差的语句及时的通过SQL_PROFILE或者SPM（11g)调整成最优执行计划。
具体语句如下：
spool sql_have_logs_plan.txt
set lines 220 pages 9999 trimspool on
set numformat 999,999,999
column plan_hash_value format 99999999999999
column min_snap format 999999
column max_snap format 999999
column min_avg_ela format 999,999,999,999,999
column avg_ela format 999,999,999,999,999
column ela_gain format 999,999,999,999,999
select sql_id,
min(min_snap_id) min_snap,
max(max_snap_id) max_snap,
max(decode(rw_num,1,plan_hash_value)) plan_hash_value,
max(decode(rw_num,1,avg_ela)) min_avg_ela,
avg(avg_ela) avg_ela,
avg(avg_ela) - max(decode(rw_num,1,avg_ela)) ela_gain,
-- max(decode(rw_num,1,avg_buffer_gets)) min_avg_buf_gets,
-- avg(avg_buffer_gets) avg_buf_gets,
max(decode(rw_num,1,sum_exec))-1 min_exec,
avg(sum_exec)-1 avg_exec
from (
select sql_id, plan_hash_value, avg_buffer_gets, avg_ela, sum_exec,
row_number() over (partition by sql_id order by avg_ela) rw_num , min_snap_id, max_snap_id
from
(
select sql_id, plan_hash_value , sum(BUFFER_GETS_DELTA)/(sum(executions_delta)+1) avg_buffer_gets,
sum(elapsed_time_delta)/(sum(executions_delta)+1) avg_ela, sum(executions_delta)+1 sum_exec,
min(snap_id) min_snap_id, max(snap_id) max_snap_id
from dba_hist_sqlstat a
where exists (
select sql_id from dba_hist_sqlstat b where a.sql_id = b.sql_id
and a.plan_hash_value != b.plan_hash_value
and b.plan_hash_value > 0)
and plan_hash_value > 0
group by sql_id, plan_hash_value
order by sql_id, avg_ela
)
order by sql_id, avg_ela
)
group by sql_id
having max(decode(rw_num,1,sum_exec)) > 1
order by 7 desc
/
spool off
clear columns
set numformat 9999999999
具体的执行结果参考如下：
SQL> spool sql_with_more_than_1plan.txt set lines 220 pages 9999 trimspool on SQL> SQL> set numformat 999,999,999 SQL> column plan_hash_value format 99999999999999 SQL> column min_snap format 999999 SQL> column max_snap format 999999 SQL> column min_avg_ela format 999,999,999,999,999 SQL> column avg_ela format 999,999,999,999,999 SQL> column ela_gain format 999,999,999,999,999 SQL> select sql_id, 2 min(min_snap_id) min_snap, 3 max(max_snap_id) max_snap, 4 max(decode(rw_num,1,plan_hash_value)) plan_hash_value, 5 max(decode(rw_num,1,avg_ela)) min_avg_ela, 6 avg(avg_ela) avg_ela, 7 avg(avg_ela) - max(decode(rw_num,1,avg_ela)) ela_gain, 8 -- max(decode(rw_num,1,avg_buffer_gets)) min_avg_buf_gets, 9 -- avg(avg_buffer_gets) avg_buf_gets, 10 max(decode(rw_num,1,sum_exec))-1 min_exec, 11 avg(sum_exec)-1 avg_exec 12 from ( 13 select sql_id, plan_hash_value, avg_buffer_gets, avg_ela, sum_exec, 14 row_number() over (partition by sql_id order by avg_ela) rw_num , min_snap_id, max_snap_id 15 from 16 ( 17 select sql_id, plan_hash_value , sum(BUFFER_GETS_DELTA)/(sum(executions_delta)+1) avg_buffer_gets, 18 sum(elapsed_time_delta)/(sum(executions_delta)+1) avg_ela, sum(executions_delta)+1 sum_exec, 19 min(snap_id) min_snap_id, max(snap_id) max_snap_id 20 from dba_hist_sqlstat a 21 where exists ( 22 select sql_id from dba_hist_sqlstat b where a.sql_id = b.sql_id 23 and a.plan_hash_value != b.plan_hash_value 24 and b.plan_hash_value > 0) 25 and plan_hash_value > 0 26 group by sql_id, plan_hash_value 27 order by sql_id, avg_ela 28 ) 29 order by sql_id, avg_ela 30 ) 31 group by sql_id 32 having max(decode(rw_num,1,sum_exec)) > 1 33 order by 7 desc 34 / spool off clear columns set numformat 9999999999 SQL_ID MIN_SNAP MAX_SNAP PLAN_HASH_VALUE MIN_AVG_ELA AVG_ELA ELA_GAIN MIN_EXEC AVG_EXEC ------------- -------- -------- --------------- -------------------- -------------------- -------------------- ------------ ------------ gfjvxb25b773h 192 204 3801470479 6,368 171,152 164,784 1 1 7asddw0r2wykx 192 211 3660107835 10,311 16,448 6,137 57 30 7ng34ruy5awxq 192 209 2606284882 117 320 204 426 1,371 g278wv0zzpn5j 195 211 3142739690 0 189 189 72 63 1gu8t96d0bdmu 192 209 2035254952 67 154 87 826 1,134 bsa0wjtftg3uw 193 211 2020579421 0 18 18 224 191 0kugqg48477gf 201 202 3321035584 0 0 0 19 27 7 rows selected.
通过以上查询我们发现了7个结果，其中MIN_SNAP和MAX_SNAP代表该sql_id所代表最早出现时候的快照和最后一次出现的快照；PLAN_HASH_VALUE代表着最佳的执行计划；ELA_GAIN字段代表如果使用最佳的执行计划，与平均执行时间能够提速多少时间。
在查出结果后，我们可以通过sql profile（参考老刘的《验证sql_profile的执行计划》），也可以通过11g的dbms_spm.load_plans_from_cursor_cache更改相关Sql_id的sql的执行计划，这里以11g为例子做如下变更,对上面查询到的7asddw0r2wykx语句执行计划改为最优：
SQL> variable v_sqlid number SQL> begin 2 :v_sqlid := 3 dbms_spm.load_plans_from_cursor_cache( 4 sql_id=>'&sql_id', 5 plan_hash_value=>&plan_hash, 6 fixed=>'YES'); 7 end; 8 / Enter value for sql_id: 7asddw0r2wykx old 4: sql_id=>'&sql_id', new 4: sql_id=>'7asddw0r2wykx', Enter value for plan_hash: 3660107835 old 5: plan_hash_value=>&plan_hash, new 5: plan_hash_value=>3660107835, PL/SQL procedure successfully completed.
