SQL ordered by Elapsed Time 脚本

spell

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