Oracle 效能調校：快速定位高負載 SQL 的實戰方法論

兩類效能問題分類

CPU 瓶頸通常源於應用邏輯，解決方向為「推算下推到資料庫」(高效 SQL、PL/SQL、批次綁定、結果快取)；I/O 瓶頸則直接可從資料庫視圖定位具體 SQL。

核心視圖：V$SQLAREA

記錄共享池中所有已解析 SQL 的累積統計 (自首次解析以來)。

關鍵欄位

• EXECUTIONS：執行次數
• DISK_READS：物理讀取區塊數 (最關鍵指標)
• BUFFER_GETS：邏輯讀取區塊數
• SQL_TEXT：SQL 文本 (前 1000 字元)
• HIT_RATIO = (BUFFER_GETS - DISK_READS) / BUFFER_GETS：緩衝區命中率

基礎查詢：命中率 < 80% 的 SQL

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
ORDER BY 4 DESC;

進階查詢：每次執行物理讀取量 (更直觀)

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
  AND (buffer_gets - disk_reads) / buffer_gets < 0.80
ORDER BY reads_per_run DESC;

• reads_per_run 直接反映單次執行 I/O 成本，排序後最耗資源的 SQL 置頂。
• 命中率有時誤導：唯一索引查詢邏輯/物理讀接近 → 命中率低但實際高效；全表掃描大表命中率高但總 I/O 龐大。

實戰案例分析

案例一：低命中率但高效的 SQL

Executions=2, Disk_Reads=6, Buffer_Gets=19, Hit_Ratio=0.68
SQL: SELECT ... FROM ... WHERE pk = :b1  -- 唯一索引查詢

• 唯一索引存取，邏輯/物理讀極少 (19/6)，命中率低僅因分母小。
• 結論：勿單憑命中率判斷，需結合絕對 I/O 量與存取路徑。

案例二：高命中率但低效的 SQL

Executions=2, Disk_Reads=3625, Buffer_Gets=178777, Hit_Ratio=0.98
SQL: 五表連接，其中一表錯誤全表掃描

• 命中率 98% 看似優秀，但單次執行 3,625 物理讀、17 萬邏輯讀。
• 重寫 SQL (修正連接順序/加索引) 後：物理讀 < 50，邏輯讀大幅下降，命中率反而降至 70%。
• 結論：總物理 I/O 才是核心成本，命中率僅供參考。

定位策略總結

1. 首選指標：DISK_READS / EXECUTIONS (單次執行物理讀) — 直接反映 I/O 成本。
2. 輔助指標：BUFFER_GETS (邏輯讀) — 反映 CPU/記憶體壓力；EXECUTIONS — 識別高頻 SQL。
3. 過濾條件：EXECUTIONS > 0 AND BUFFER_GETS > 0 避免除零。
4. 後續動作：對高物理讀 SQL 執行 EXPLAIN PLAN、檢查索引、統計資訊、考慮 SQL Profile / Baseline / 重寫。

延伸工具

• AWR/ASH 報告：企業版授權下，自動識別 Top SQL (按 Elapsed Time、DB Time、Buffer Gets、Disk Reads 等維度)。
• SQL Trace / TKPROF：單條 SQL 深度追蹤。
• SQL Monitoring (11g+)：即時監控長時間執行 SQL。

定位高負載 SQL 只是第一步；真正調校在於理解執行計畫、統計資訊鮮度、索引設計、資料分佈與業務邏輯的匹配度。