萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle SQL 效能問題診斷四大法:V$SQLAREA、Session 監控、SQL Trace/TKPROF 與 AWR/ASH 深度分析

識別高耗資源 SQL 是調優起點。本文系統介紹四大互補診斷法,從輕量級即時查詢到深度事後分析,構建完整 SQL 效能可視化體系。

方法一:V$SQLAREA 即時掃描 (零負擔、秒級)

共享池中已解析 SQL 的聚合統計,適合快速定位高頻/高耗 SQL。

SELECT
  sql_id,
  executions,
  disk_reads,
  buffer_gets,
  rows_processed,
  elapsed_time/1e6 elapsed_sec,
  cpu_time/1e6 cpu_sec,
  sql_text
FROM v$sqlarea
WHERE parsing_schema_name NOT IN ('SYS','SYSTEM')
  AND executions > 0
ORDER BY buffer_gets DESC  -- 或 disk_reads, elapsed_time
FETCH FIRST 20 ROWS ONLY;

關鍵欄位解讀

欄位含義異常徵兆
EXECUTIONS執行次數極高且未重用 → 硬解析風暴
DISK_READS物理讀塊數/EXECUTIONS > 1000 → 缺索引或統計過期
BUFFER_GETS邏輯讀塊數/EXECUTIONS > 10000 → 全掃描或計畫劣化
SHARABLE_MEM佔用 Shared Pool 位元組單條 > 10MB → 可能為大 SQL 或計畫膨脹
OPTIMIZER_MODE優化器模式CHOOSE/RULE → 應遷移至 ALL_ROWS

方法二:Session 即時監控 (進行中長跑 SQL)

SELECT s.sid, s.serial#, s.username, s.status, s.sql_id,
       sq.sql_text, sq.elapsed_time/1e6 elapsed_sec
FROM v$session s
JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE s.type = 'USER'
  AND s.status = 'ACTIVE'
  AND s.last_call_et > 60  -- 執行超過 60 秒
ORDER BY s.last_call_et DESC;

工具:Enterprise Manager、SQL Developer Monitor、oratop、MOATS。

方法三:SQL Trace + TKPROF (單一 SQL 深度剖析)

  1. 確認參數:TIMED_STATISTICS=TRUE、USER_DUMP_DEST、MAX_DUMP_FILE_SIZE=UNLIMITED。
  2. 開啟 Trace:ALTER SESSION SET SQL_TRACE=TRUE; (或 DBMS_MONITOR.SESSION_TRACE_ENABLE)
  3. 執行目標 SQL。
  4. 關閉 Trace:ALTER SESSION SET SQL_TRACE=FALSE;
  5. 轉換 Trace 檔:tkprof trace.trc output.txt EXPLAIN=user/pass SYS=NO SORT=EXEELA,FCHELLA

TKPROF 報告關鍵指標

指標含義調優方向
Parse Count (Hard)硬解析次數大 → 增加 Shared Pool、使用繫結變數
Query/Current Gets邏輯讀 (一致性/當前模式)大 → 缺索引、計畫錯誤、統計過期
Disk Reads物理讀大 → 索引缺失、緩衝區太小、資料未在記憶體
Library Cache MissesShared Pool 未命中>1 → Shared Pool 太小或 Cursor Sharing 問題

方法四:AWR/ASH 歷史趨勢分析 (企業版 + Diagnostic Pack)

  • AWR 報告:@$ORACLE_HOME/rdbms/admin/awrrpt.sql,對比兩個快照間 Top SQL、Wait Events、Instance Efficiency。
  • ASH 報告:@$ORACLE_HOME/rdbms/admin/ashrpt.sql,秒級採樣,適合間歇性尖峰分析。
  • SQL Monitoring (11g+):DBMS_SQLTUNE.REPORT_SQL_MONITOR,即時顯示長跑 SQL 的並行執行細節、I/O、等待分布。

整合診斷流程

  1. **V$SQLAREA** 快速掃描 Top 20 高耗 SQL。
  2. **Session 監控** 確認是否有進行中長跑 SQL 需立即干預。
  3. 對候選 SQL 執行 **SQL Trace/TKPROF** 逐行剖析。
  4. **AWR/ASH** 驗證歷史趨勢、關聯系統級資源爭用。
  5. 制定優化方案 → 測試環境驗證 → 生產變更 → 持續監控。

四大法互補:V$SQLAREA 廣度、Session 監控即時性、Trace 深度、AWR/ASH 歷史趨勢。建立「日巡 V$SQLAREA、週讀 AWR、事後 Trace」機制,讓 SQL 效能問題無所遁形。

copyright © 萬盛學電腦網 all rights reserved