awr 中 SQL order by Elapsed Time脚本

如标题，本文介绍awr 中 SQL order by Elapsed Time脚本，只需要在plsqldeveloper中即可执行，方便快速收集top sql

==先收集快照的信息select snap_id,
       dbid,
       instance_number,
       to_char(END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss')
  from dba_hist_snapshot
 where instance_number = 1
 order by 1 desc;

--手动执行下面语句，并输入快照相关信息即可select 总耗时, cpu总耗时, 执行次数, 平均每次耗时, sql_id, 执行应用, sql文本
  from (select round(nvl((sqt.elap / 1000000), to_number(null)), 0) 总耗时,
               round(nvl((sqt.cput / 1000000), to_number(null)), 0) cpu总耗时,
               sqt.exec 执行次数,
               round(decode(sqt.exec,
                            0,
                            to_number(null),
                            (sqt.elap / sqt.exec / 1000000)),
                     4) 平均每次耗时,
               round((100 * (sqt.elap /
                     (SELECT sum(e.VALUE) - sum(b.value)
                                FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                               WHERE B.SNAP_ID = &beg_snap
                                 AND E.SNAP_ID = &end_snap
                                 AND B.DBID = &DBID
                                 AND E.DBID = &DBID
                                 AND B.INSTANCE_NUMBER = &INST_NUM
                                 AND E.INSTANCE_NUMBER = &INST_NUM
                                 and e.STAT_NAME = 'DB time'
                                 and b.stat_name = 'DB time'))),
                     0) norm_val,
               sqt.sql_id,
               to_char(to_clob(decode(sqt.module,
                                      null,
                                      null,
                                      'Module: ' || sqt.module))) 执行应用,
               dbms_lob.substr(nvl(st.sql_text,
                                   to_clob(' ** SQL Text Not Available ** ')),
                               2000,
                               1) sql文本
          from (select sql_id,
                       max(module) module,
                       sum(elapsed_time_delta) elap,
                       sum(cpu_time_delta) cput,
                       sum(executions_delta) exec
                  from dba_hist_sqlstat
                 where dbid = &dbid
                   and instance_number = &inst_num
                   and &beg_snap < snap_id
                   and snap_id <= &end_snap
                 group by sql_id) sqt,
               dba_hist_sqltext st
         where st.sql_id(+) = sqt.sql_id
           and st.dbid(+) = &dbid
         order by nvl(sqt.elap, -1) desc, sqt.sql_id)
 where rownum < 101;　　　　　　　　--设置top sql 100条，根据实际需求设置