用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;

[sql] view plain copy

1. select *  
2.   from (select round(nvl((sqt.elap / 1000000), to_number(null)),2) "Elapsed Time (s)",  
3.               round( nvl((sqt.cput / 1000000), to_number(null)),2) "CPU Time (s)",  
4.                sqt.exec,  
5.                round(decode(sqt.exec,  
6.                       0,  
7.                       to_number(null),  
8.                       (sqt.elap / sqt.exec / 1000000)),2) "Elap per Exec (s)",  
9.                round((100 *  
10.                (sqt.elap / (select sum(e.value) - sum(b.value)  
11.                                from dba_hist_sys_time_model b,  
12.                                     dba_hist_sys_time_model e  
13.                               where b.snap_id = &beg_snap and  
14.                                     e.snap_id = &end_snap and  
15.                                     b.dbid = &dbid and  
16.                                     e.dbid = &dbid and  
17.                                     b.instance_number = &inst_num and  
18.                                     e.instance_number = &inst_num and  
19.                                     e.stat_name = 'DB time' and  
20.                                     b.stat_name = 'DB time'))) ,2)norm_val,  
21.                sqt.sql_id,  
22.                decode(sqt.module, null, null, 'Module: ' || sqt.module) SqlModule,  
23.                nvl(to_nchar(SUBSTR(st.sql_text,1,2000)) , (' ** SQL Text Not Available ** ')) SqlText  
24.           from (<strong>select sql_id,  
25.                        max(module) module,  
26.                        sum(elapsed_time_delta) elap,  
27.                        sum(cpu_time_delta) cput,  
28.                        sum(executions_delta) exec  
29.                   from dba_hist_sqlstat  
30.                  where dbid = &dbid and  
31.                        instance_number = &inst_num and  
32.                        &beg_snap < snap_id and  
33.                        snap_id <= &end_snap  
34.                  group by sql_id</strong>) sqt,  
35.                dba_hist_sqltext st  
36.          where st.sql_id(+) = sqt.sql_id and  
37.                st.dbid(+) = &dbid  
38.          order by nvl(sqt.elap, -1) desc,  
39.                   sqt.sql_id)  
40.  where rownum < 65 and  
41.        (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