DIFF AWR TOPSQL

有时候应用版本更新后想用SQL获取最近几天的AWR 报告中比较某天某个时段TOP SQL 的情况，从中比较出来每个时间段的TOPSQL 是否有异动，如果
出现新的比较异常的SQL ，可以从此结果集中发现加以分析处理

 
select DT, 
       HH, 
       SNAP_ID, 
       schema, 
       SQL_ID, 
       EXECUTIONS, 
       BUFFGET_PER, 
       BUFFGET_PER - lag(BUFFGET_PER, 1, 0) over(partition by sql_id order by snap_id) as buff_diff, 
       ELAP_TIME_PER, 
       ELAP_TIME_PER - lag(ELAP_TIME_PER, 1, 0) over(partition by sql_id order by snap_id) as elap_time_diff, 
       FETCHES_PER, 
       DISKR_PER, 
       OPTIMIZER_COST, 
       ROWS_PROCESSED_PER, 
       sql, 
       SUM_TIME, 
       sum_gets 
  from (SELECT substr(to_char(snp.begin_interval_time, 'YYYYMMDDHH24DDMISS'), 
                      1, 
                      8) DT, 
               substr(to_char(snp.begin_interval_time, 'YYYYMMDDHH24DDMISS'), 
                      9, 
                      2) HH, 
               stat.SNAP_ID, 
               stat.parsing_schema_name schema, 
               stat.SQL_ID, 
               stat.OPTIMIZER_COST, 
               stat.EXECUTIONS_DELTA EXECUTIONS, 
               round(stat.DISK_READS_DELTA / stat.EXECUTIONS_DELTA, 3) DISKR_PER, 
               round(stat.ELAPSED_TIME_DELTA / 1000/1000 / stat.EXECUTIONS_DELTA, 3) ELAP_TIME_PER, 
               round(stat.FETCHES_DELTA / stat.EXECUTIONS_DELTA, 3) FETCHES_PER,       
               round(stat.BUFFER_GETS_DELTA / stat.EXECUTIONS_DELTA, 3) BUFFGET_PER, 
               round(stat.ROWS_PROCESSED_DELTA / stat.EXECUTIONS_DELTA, 3) ROWS_PROCESSED_PER, 
               txt.sql_text sql, 
               sum(stat.ELAPSED_TIME_DELTA / stat.EXECUTIONS_DELTA) over(partition by stat.sql_id, stat.parsing_schema_name) sum_time, 
               --  stat.FETCHES_DELTA / stat.EXECUTIONS_DELTA 
               sum(stat.BUFFER_GETS_DELTA) over(partition by stat.sql_id, stat.parsing_schema_name) sum_gets 
          from dba_hist_sqlstat  stat, 
               dba_hist_snapshot snp, 
               dba_hist_sqltext  txt 
         WHERE stat.snap_id = snp.snap_id   
           and stat.sql_id =  nvl (&1,stat.sql_id ) 
           and stat.parsing_schema_name in ('&schema_name') 
           and stat.dbid = snp.dbid 
           and txt.dbid = stat.dbid 
           and txt.sql_id = stat.sql_id 
           and SUBSTR(to_char(begin_interval_time, 'YYYYMMDDHH24DDMISS'), 
                      9, 
                      2) IN 
               ('08', '09', '10', '11', '12') 
           and begin_interval_time >= trunc(sysdate - 3, 'DD') 
           and begin_interval_time <= trunc(sysdate, 'DD') 
           and stat.EXECUTIONS_DELTA <> 0) 
 order by sum_time desc, snap_id;