Oracle 常見等待事件分析與優化指南（v$session_wait 實戰）

核心視圖

V$SESSION_WAIT 即時反映會話等待事件，P1/P2/P3 携帶事件具體上下文（文件號、塊號等）。結合 DBA_EXTENTS 可定位熱點段。

高頻事件與對策

1. db file scattered read（全表掃描/索引掃描過多）

定位：P1=file#, P2=block# → DBA_EXTENTS 查段。
對策：補充缺失索引、小表 KEEP POOL、優化 SQL 避免不必要全掃。

2. db file sequential read（索引讀/連接順序差）

定位：同 scattered read。
對策：調整連接順序、檢查索引選擇性、適當增大 DB_CACHE_SIZE、Hash Join 放入 PGA。

3. free buffer waits（DBWR 寫入跟不上）

對策：增大 DB_CACHE_SIZE、增加 DB_WRITER_PROCESSES、優化檢查點、增加物理磁盤。

4. buffer busy waits（熱塊競爭）

查 V$WAITSTAT 細分：

• 段頭塊 → 增加 FREELIST GROUPS
• Undo 頭塊 → 增加回滾段/Undo 表空間
• 數據塊 → 搬移熱數據、LMT 表空間、增加 FREELIST
• 索引塊 → 重建/分區/反向鍵索引

通用：增大 INITRANS/PCTFREE 預留 ITL 槽。

5. enqueue（排隊鎖）

• ST：空間管理 → 改 LMT、預分配區塊
• HW：高水位標記 → 手動分配區塊
• TX：
  1. 唯一鍵衝突 → 應用層捕獲
  2. 位圖索引段競爭 → 避免高並發更新
  3. 塊級鎖（ITL 不足） → 增大 INITRANS/MAXTRANS/PCTFREE
• TM：DDL/DML 衝突 → 外鍵必建索引

6. log buffer space / log file switch / log file sync（Redo 子系統）

• log buffer space：增大 LOG_BUFFER、Redo 放快盤/固態盤
• log file switch (archiving needed)：增大/增加 Redo 組、調整 LOG_ARCHIVE_MAX_PROCESSES
• log file switch (checkpoint incomplete)：同前、優化檢查點頻率
• log file sync：批量提交減少頻率、調整 _LOG_IO_SIZE、Redo 放高速盤

7. latch free（共享內存鎖）

常見 Latch 類型：

• Library Cache：未用綁定變量 → 強制綁定、調整 SHARED_POOL_SIZE
• Cache Buffers Chains：熱塊 → _DB_BLOCK_HASH_BUCKETS 設質數、消除熱點 SQL
• Cache Buffers LRU Chain：緩沖區過小/過大/排序過多 → 調整 DB_BLOCK_LRU_LATCHES、多緩沖池

8. library cache pin / lock（對象編譯衝突）

查 X$KGLOB/X$KGLPN/X$KGLLK 定位阻塞會話與 SQL，避免高峰期 DDL、熱門包體重編譯。

定位熱點對象通用 SQL

SELECT owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = :p1
  AND :p2 BETWEEN block_id AND block_id + blocks - 1;

Latch 熱塊定位

SELECT a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
FROM x$bh a, dba_objects b
WHERE (a.obj = b.object_id OR a.obj = b.data_object_id)
  AND a.hladdr = :p1raw
UNION
SELECT hladdr, file#, dbablk, tch, obj, null
FROM x$bh
WHERE obj IN (SELECT obj FROM x$bh WHERE hladdr = :p1raw
              MINUS SELECT object_id FROM dba_objects
              MINUS SELECT data_object_id FROM dba_objects)
  AND hladdr = :p1raw
ORDER BY 4;