這篇文章主要介紹了查找Oracle高消耗語句的方法,需要的朋友可以參考下
在運行下面的腳本之前需要先用生成AWR報告的SQL（程序腳本一般保存在$ORACLE_HOME下的rdbms/admin中，名稱為awrrpt.sql，需要輸入生成AWR報告的天數范圍）找到開始和結束的snapshot編號：begin_snap和end_snap。 代碼如下: <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> 將腳本輸出內容保存到記事本txt中，並將記事本的後綴名更改為.html，這樣就可以輸出以下的網頁內容：