本文介紹如何撰寫查詢,找出 Oracle 資料庫中資源消耗最高的 SQL 語句,協助 DBA 進行效能調優。
執行下列腳本前,需先用產生 AWR 報告的 SQL(一般保存在 $ORACLE_HOME 下的 rdbms/admin 中,名稱為 awrrpt.sql,執行時需輸入範圍天數)找出開始與結束的 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;
exit
將腳本輸出的內容貼到記事本並存檔,再把副檔名改為 .html,即可用瀏覽器開啟,輕鬆檢視各 SQL 的執行次數、CPU 消耗、邏輯讀與物理讀等指標,快速鎖定需要優化的高消耗語句。