| 類型 | 特徵 | 根因 | 定位手段 |
|---|---|---|---|
| CPU 密集型 | 佔用大量 CPU、耗時長 | 演算法差、邏輯複雜、缺索引 | OS 層面(top、vmstat)追蹤到 Session → Module → SQL |
| I/O 密集型 | 大量物理讀、全表掃描、排序溢寫 | 執行計畫不佳、統計資訊過期、索引缺失 | 查詢 V$SQLAREA 共享池統計 |
SELECT executions,
disk_reads,
buffer_gets,
ROUND((buffer_gets - disk_reads) / buffer_gets, 2) AS hit_ratio,
sql_text
FROM v$sqlarea
WHERE executions > 0
AND buffer_gets > 0
AND (buffer_gets - disk_reads) / buffer_gets < 0.80 -- 命中率 < 80%
ORDER BY 4 DESC;
Executions=2, Disk_Reads=6, Buffer_Gets=19, Hit_Ratio=0.68
SQL: SELECT ... FROM emp WHERE empno = :b1 -- 唯一索引範圍掃描
唯一索引幾乎不產生邏輯讀,物理讀 ≈ 邏輯讀 → 命中率低,**但總 I/O 極少,極高效**。
Executions=2, Disk_Reads=3625, Buffer_Gets=178777, Hit_Ratio=0.98
SQL: 五表 JOIN,其中一表錯走全表掃描
緩衝區命中率 98% 看似完美,實則每次執行 3,600+ 物理讀。調整 SQL 後物理讀降至 <50,命中率反而降至 70%。
SELECT sql_text,
executions,
ROUND(disk_reads / executions, 2) AS reads_per_run,
disk_reads,
buffer_gets,
ROUND((buffer_gets - disk_reads) / buffer_gets, 2) AS hit_ratio
FROM v$sqlarea
WHERE executions > 0
AND buffer_gets > 0
ORDER BY reads_per_run DESC; -- 關鍵:按單次物理讀降序
輸出範例:
Executions Reads_Per_Run Disk_Reads Buffer_Gets Hit_Ratio SQL_Text
---------- ------------- ---------- ----------- --------- ------------
2 1812.5 3625 178777 0.98 五表 JOIN(需優化)
2 3 6 19 0.68 唯一索引查詢(無需動)
reads_per_run 查詢,取前 20 筆。EXPLAIN PLAN + DBMS_XPLAN.DISPLAY 看執行計畫。DBMS_STATS)、索引可用性、直方圖。reads_per_run 確認下降。SELECT s.sid, s.serial#, s.username, s.module,
ROUND(s.value/100, 2) AS cpu_sec
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'CPU used by this session'
AND s.value > 10000 -- 閾值視環境調整
ORDER BY s.value DESC;
配合 V$SQL 找出該 Session 正在跑的 SQL,再走優化流程。
別被「緩衝區命中率」迷惑。**單次執行物理讀**才是衡量 I/O 效率的黃金指標。建立「每日 Top N 物理讀 SQL」報表,持續優化,資料庫效能自然穩健。