用SQL生成awr报表中的“SQL ordered by Elapsed Time” 部分

以下SQL用于生成awr的以下部分。

--提取&beg_snap 、&end_snap
select * from dba_hist_snapshot x ;
--提取&dbid 
select * from v$database;
--提取$inst_num
select * from v$instance;

select *
  from (select round(nvl((sqt.elap / 1000000), to_number(null)),2) "Elapsed Time (s)",
              round( nvl((sqt.cput / 1000000), to_number(null)),2) "CPU Time (s)",
               sqt.exec,
               round(decode(sqt.exec,
                      0,
                      to_number(null),
                      (sqt.elap / sqt.exec / 1000000)),2) "Elap per Exec (s)",
               round((100 *
               (sqt.elap / (select sum(e.value) - sum(b.value)
                               from dba_hist_sys_time_model b,
                                    dba_hist_sys_time_model 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'))) ,2)norm_val,
               sqt.sql_id,
               decode(sqt.module, null, null, 'Module: ' || sqt.module) SqlModule,
               nvl(to_nchar(SUBSTR(st.sql_text,1,2000)) , (' ** SQL Text Not Available ** ')) SqlText
          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 < 65 and
       (rownum <= 10 or norm_val > 1);

从dba_hist_sqlstat提取更多的指标，调整加粗部分，照葫芦画瓢就可以写出
SQL ordered by CPU Time
SQL ordered by Gets
SQL ordered by Reads
SQL ordered by Executions
SQL ordered by Parse Calls
SQL ordered by Sharable Memory