【Oracle指令碼】尋找Oracle高消耗語句

標籤：oracle   admin   cccccc   style   color   

在運行下面的指令碼之前需要先用產生AWR報告的SQL（程式指令碼一般儲存在$ORACLE_HOME下的rdbms/admin中，名稱為awrrpt.sql，需要輸入產生AWR報告的天數範圍）找到開始和結束的snapshot編號：begin_snap和end_snap。

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;

將指令碼輸出內容儲存到記事本txt中，並將記事本的尾碼名更改為.html，這樣就可以輸出以下的網頁內容：

650) this.width=650;" src="http://img.blog.csdn.net/20140321225947406?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbGVleGlkZQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast" "918" height="76" />

【Oracle指令碼】尋找Oracle高消耗語句