萬盛學電腦網

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

Oracle 捕獲問題 SQL 實戰:從 V$SESSION_WAIT 定位等待事件到 V$SQLTEXT 還原完整 SQL 文本

資料庫突然變慢,第一步是找出「誰在等什麼」。本文提供從等待事件定位 Session,再反查完整 SQL 文本的標準診斷鏈。

步驟 1:檢視實例級等待概況

SELECT event, COUNT(*) sessions, AVG(seconds_in_wait) avg_sec
FROM v$session_wait
WHERE wait_time = 0  -- 僅看正在等待
GROUP BY event
ORDER BY sessions DESC;

重點關注:

  • db file scattered read:全表掃描多塊讀。
  • db file sequential read:索引單塊讀。
  • log file sync:Commit 等待 LGWR 寫入 Redo。
  • enq: TX - row lock contention:行鎖競爭。

步驟 2:鎖定高頻等待的 SID

SELECT sid, event, p1, p1text, p2, p2text, p3, p3text, seconds_in_wait
FROM v$session_wait
WHERE event = 'db file scattered read'
ORDER BY seconds_in_wait DESC;

步驟 3:由 SID 反查完整 SQL 文本

SELECT sql_text
FROM v$sqltext
WHERE hash_value = (
  SELECT sql_hash_value FROM v$session WHERE sid = :target_sid
)
ORDER BY piece ASC;

或使用 V$SQL 直接取 SQL_FULLTEXT (10g+):

SELECT sql_fulltext
FROM v$sql
WHERE sql_id = (SELECT sql_id FROM v$session WHERE sid = :target_sid);

步驟 4:關聯執行計畫與物件

-- 取得執行計畫
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  (SELECT sql_id FROM v$session WHERE sid = :sid), NULL, 'ALL'));

-- 取得等待事件關聯物件 (p1/p2 解碼)
SELECT o.object_name, o.object_type
FROM dba_objects o
WHERE o.object_id = (SELECT p1 FROM v$session_wait WHERE sid = :sid AND event='db file scattered read');

進階:ASH 歷史分析 (需 Diagnostic Pack)

SELECT sql_id, event, COUNT(*) samples
FROM v$active_session_history
WHERE sample_time > SYSDATE - 1/24  -- 最近 1 小時
GROUP BY sql_id, event
ORDER BY samples DESC;

自動化巡檢腳本 (生成 Top 10 問題 SQL 報告)

SET PAGES 1000 LINES 200
COL sql_text FORMAT A100
SELECT * FROM (
  SELECT s.sql_id, s.sql_text, s.executions, s.elapsed_time/1e6 elapsed_sec,
         s.buffer_gets, s.disk_reads, s.rows_processed
  FROM v$sql s
  WHERE s.parsing_schema_name NOT IN ('SYS','SYSTEM')
  ORDER BY s.elapsed_time DESC
) WHERE ROWNUM <= 10;

掌握「等待事件 → SID → SQL_TEXT → 執行計畫 → 物件」完整鏈路,能在 5 分鐘內定位 80% 的效能尖峰根因。

copyright © 萬盛學電腦網 all rights reserved