資料庫突然變慢,第一步是找出「誰在等什麼」。本文提供從等待事件定位 Session,再反查完整 SQL 文本的標準診斷鏈。
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:行鎖競爭。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;
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);
-- 取得執行計畫 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');
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;
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% 的效能尖峰根因。