Oracle Redo Log Buffer 爭用消除與索引建立最佳實務

Redo Log Buffer 爭用會完全扼殺資料庫效能。核心指標：redo log space requests 應趨近 0。若偏高，需系統性消除 Latch 爭用並輔以索引優化減少 Redo 產生量。

一、Redo Log Buffer 空間爭用監控

SELECT name, value FROM V$SYSSTAT WHERE name = 'redo log space requests';

值 > 0 → 增加 LOG_BUFFER (init.ora/SPFILE)，重啟生效。

二、Latch 爭用深度解析

查詢 V$LATCH + V$LATCHNAME：

SELECT n.name, l.gets, l.misses, l.immediate_gets, l.immediate_misses
FROM V$LATCH l, V$LATCHNAME n
WHERE n.name IN ('redo allocation','redo copy') AND n.latch# = l.latch#;

• Willing-to-wait 模式：GETS 成功、MISSES 失敗排隊。
• Immediate 模式：IMMEDIATE_GETS 立即成功、IMMEDIATE_MISSES 立即失敗自旋。

分配 Latch 爭用對策

• 縮短持有時間 → 減少 Redo Copy 次數 → 降低 LOG_SMALL_ENTRY_MAX_SIZE。

複製 Latch 爭用對策

1. 增加 Latch 數 → 增加 LOG_SIMULTANEOUS_COPIES (建議 2×CPU)。
2. 縮短持有時間 → 先建構 Redo Entry 再取 Latch → 增加 LOG_ENTRY_PREBUILD_THRESHOLD。

三、排序區與磁碟排序優化

• sorts (memory) vs sorts (disk)。
• 磁碟排序比例高 → 增加 SORT_AREA_SIZE 或 PGA_AGGREGATE_TARGET。

四、索引建立與重建最佳實務

1. 缺索引是新系統效能問題首因：檢視 WHERE 子句欄位決定索引欄位。
2. 謹慎評估：索引佔空間、拖慢 DML；若索引大小接近表大小則效益低。
3. 快速重建：ALTER INDEX idx REBUILD 遠快於 Drop+Create。
4. 資料已排序：CREATE INDEX idx ON t(col) NOSORT 跳過排序階段。
5. Bitmap/Function-based Index：低基數欄位用 Bitmap；函數運算欄位用 FBI。

五、關鍵參數速查