oracle 语句性能批量比较分析

语句如下：

                   
                          
with cur_data_a as (
select s.con_id,s.snap_id,
       ss.instance_number node,
       sql_id,
       plan_hash_value,
       nvl(executions_delta, 0) execs, 
       trunc((elapsed_time_delta /decode(nvl(executions_delta, 0), 0, 1, executions_delta)) / 1000,3) avg_etime_ms,
       trunc((buffer_gets_delta / decode(nvl(buffer_gets_delta, 0), 0, 1, executions_delta)),9) avg_lio,
       trunc((disk_reads_delta / decode(nvl(buffer_gets_delta, 0), 0, 1, executions_delta)),9) avg_pio,
       row_number() over (partition by s.con_id ,sql_id order by (elapsed_time_delta /decode(nvl(executions_delta, 0), 0, 1, executions_delta)) desc) r
  from dba_HIST_SQLSTAT S, dba_HIST_SNAPSHOT SS
 where ss.instance_number = S.instance_number
   and s.con_id=ss.con_id
   and s.snap_id=ss.snap_id
   and executions_delta > 0
   and begin_interval_time > sysdate-24/24 and begin_interval_time <  sysdate-22/24),   --也可以不通数据库比较
    cur_data_b as (
select s.con_id,s.snap_id,
       ss.instance_number node,
       sql_id,
       plan_hash_value,
       nvl(executions_delta, 0) execs, 
       trunc((elapsed_time_delta /decode(nvl(executions_delta, 0), 0, 1, executions_delta)) / 1000,3) avg_etime_ms,
       trunc((buffer_gets_delta / decode(nvl(buffer_gets_delta, 0), 0, 1, executions_delta)),9) avg_lio,
       trunc((disk_reads_delta / decode(nvl(buffer_gets_delta, 0), 0, 1, executions_delta)),9) avg_pio,
       row_number() over (partition by s.con_id ,sql_id order by (elapsed_time_delta /decode(nvl(executions_delta, 0), 0, 1, executions_delta)) desc) r
  from dba_HIST_SQLSTAT S, dba_HIST_SNAPSHOT SS
 where ss.instance_number = S.instance_number
   and s.con_id=ss.con_id
   and s.snap_id=ss.snap_id
   and executions_delta > 0
   and begin_interval_time > sysdate-20/24 and begin_interval_time <  sysdate-18/24)   --同上
select a.sql_id,
       a.plan_hash_value,b.plan_hash_value,case when a.plan_hash_value=b.plan_hash_value then 'same_hash_plan' else  'diff_hash_plan' end sql_plans,
       a.execs,b.execs,trunc(a.execs/b.execs,3) a_mod_b_execs,
       a.avg_etime_ms,b.avg_etime_ms,trunc(a.avg_etime_ms/(case when b.avg_etime_ms=0 then 1 else b.avg_etime_ms end),3) a_mod_b_avg_etime_ms,
       a.avg_lio,b.avg_lio,trunc(a.avg_lio/(case when b.avg_lio=0 then 1 else b.avg_lio end),3) a_mod_b_avg_lio,
       a.avg_pio,b.avg_pio,trunc(a.avg_pio/(case when b.avg_pio=0 then 1 else b.avg_pio end),3) a_mod_b_avg_pio
  from cur_data_a a,cur_data_b B
 where a.sql_id=b.sql_id
   and a.con_id=b.con_id
   and a.node=b.node
   and a.r=1
   and b.r=1
order by 1;       

查询案例：红色不服，平均执行性能下降部分（B相较于A）