查找Oracle高消耗语句的方法

　　<span style="font-size:18px;">set line 1000

　　set linesize 200

　　set pagesize 2000

　　set long 999999

　　set echo on

　　set markup html on

　　select res.*

　　from (select to_char(d.end_interval_time,'yyyy-mm-dd'),

　　a.PARSING_SCHEMA_NAME,

　　c.MODULE,

　　a.sql_id,

　　a.execs as 执行次数,

　　ROUND(a.cpu_times / a.execs, 2) as 单次执行时间,

　　a.cpu_times as cpu消耗时间,

　　ROUND(a.cpu_times / b.sum_time * 100, 2) as 消耗cpu百分比,

　　a.buffer_gets as 逻辑读,

　　ROUND(a.buffer_gets / b.sum_buffer * 100, 2) as 逻辑读百分比,

　　a.disk_read as 物理读,

　　ROUND(a.disk_read / b.sum_disk * 100, 2) as 物理读百分比,

　　c.sql_fulltext

　　from (select PARSING_SCHEMA_NAME,

　　sql_id,

　　sum(EXECUTIONS_DELTA) AS execs,

　　round(sum(CPU_TIME_DELTA) / 1000000, 2) AS cpu_times,

　　round(sum(ELAPSED_TIME_DELTA) / 1000000, 2) AS elapsed_time,

　　sum(BUFFER_GETS_DELTA) AS buffer_gets,

　　sum(DISK_READS_DELTA) AS disk_read

　　from sys.WRH$_SQLSTAT wr, gv$instance i

　　where SNAP_ID <= &end_snap

　　and snap_id >= &begin_snap

　　and wr.INSTANCE_NUMBER = i.INSTANCE_NUMBER

　　and i.instance_number = &instance_number

　　group by PARSING_SCHEMA_NAME, wr.INSTANCE_NUMBER, sql_id) a,

　　(SELECT round(SUM(CPU_TIME_DELTA) / 1000000, 2) sum_time,

　　SUM(BUFFER_GETS_DELTA) sum_buffer,

　　sum(DISK_READS_DELTA) sum_disk

　　FROM sys.WRH$_SQLSTAT wr, gv$instance i

　　where SNAP_ID <= &end_snap

　　and snap_id >= &begin_snap

　　and wr.INSTANCE_NUMBER = i.INSTANCE_NUMBER

　　and i.instance_number = &instance_number) b,

　　v$sqlarea c,

　　dba_hist_snapshot d

　　where a.execs > 0

　　and a.sql_id = c.sql_id

　　and a.PARSING_SCHEMA_NAME <> 'SYS'

　　and d.snap_id = &end_snap

　　order by cpu消耗时间 desc) res

　　where rownum < 41;

　　exit</span>