--提取&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);
