識別高資源消耗 SQL 的 Oracle 監控方法論

識別高資源消耗 SQL 的 Oracle 監控方法論

SQL 調優始於發現問題語句。本文建構系統性識別流程：從使用者回報、V$SQLAREA 統計分析、Session 即時監控到 SQL Trace 深度診斷，提供 DBA 完整工具鏈。

1. 來源：使用者與測試團隊回報

收集具體症狀：特定功能回應緩慢、批次作業超時、特定時段效能下降。記錄：時間點、執行參數、預期與實際耗時。

2. 核心視圖：V$SQLAREA 統計分析

V$SQLAREA 記錄共用池中游標的累積執行統計，是發現高消耗 SQL 的首選切入點。

關鍵欄位

常用分析查詢

-- Top 10 邏輯讀 SQL
SELECT * FROM (
   SELECT sql_id, executions, buffer_gets, disk_reads,
          buffer_gets/executions "Gets/Exec",
          sql_text
   FROM   v$sqlarea
   WHERE  executions > 0
   ORDER  BY buffer_gets DESC
) WHERE rownum <= 10;

-- Top 10 物理讀 SQL
SELECT * FROM (
   SELECT sql_id, executions, disk_reads,
          disk_reads/executions "Reads/Exec",
          sql_text
   FROM   v$sqlarea
   WHERE  executions > 0
   ORDER  BY disk_reads DESC
) WHERE rownum <= 10;

-- 高頻執行 SQL（可能為熱點語句）
SELECT * FROM (
   SELECT sql_id, executions, sql_text
   FROM   v$sqlarea
   ORDER  BY executions DESC
) WHERE rownum <= 10;

3. 即時 Session 監控

Enterprise Manager / SQL Developer / 自訂查詢 V$SESSION、V$SESSION_WAIT、V$SQL_MONITOR（11g+）。

時鐘圖示：長時間運行 SQL 的視覺指標，需立即介入分析。

關鍵等待事件對應表

• db file sequential read：索引讀取，檢查索引選擇性
• db file scattered read：全表掃描，評估是否需索引
• log file sync：Commit 等待，檢查重做日誌 I/O
• latch: cache buffers chains：熱塊競爭，考慮分區/反向索引

4. SQL Trace + TKPROF 深度診斷

啟用追蹤步驟

-- 1. 確認參數
SHOW PARAMETER timed_statistics;
SHOW PARAMETER user_dump_dest;
SHOW PARAMETER max_dump_file_size;

-- 2. Session 級啟用
ALTER SESSION SET SQL_TRACE = TRUE;
-- 或 DBMS_MONITOR.SESSION_TRACE_ENABLE

-- 3. 執行目標 SQL

-- 4. 關閉追蹤
ALTER SESSION SET SQL_TRACE = FALSE;

-- 5. 使用 TKPROF 格式化
TKPROF tracefile.trc output.txt EXPLAIN=user/pass SYS=NO SORT=PRSDS,EXEDS,FCHDS

TKPROF 關鍵指標解讀

5. 現代化監控工具鏈

• AWR/ASH 報表：企業版授權功能，歷史效能分析金標準
• SQL Monitoring (V$SQL_MONITOR)：11g+ 即時長時間 SQL 監控
• Real-Time SQL Monitoring：EM Cloud Control 視覺化介面
• Automatic SQL Tuning Advisor：自動調優建議與設定檔實作

持續改善流程

1. 建立基線：記錄核心業務 SQL 正常執行指標
2. 設定告警：Buffer Gets/Exec、Disk Reads/Exec、Elapsed Time 閾值
3. 定期審視：每週 Top 20 SQL 調優回顧會議
4. 知識沉澱：建立 SQL 誓優案例庫，避免重複踩坑