Oracle 10g R2 非同步提交：COMMIT_WRITE 參數消除 log file sync 等待實戰

前言

高併發 OLTP 系統中，頻繁 COMMIT 導致 log file sync 等待事件飆升，成為寫入瓶頸。Oracle 10g R2 引入 Asynchronous Commit（非同步提交），允許 COMMIT 立即返回，由 LGWR 批次異步刷盤。本文解析 COMMIT_WRITE 參數機制、系統/會話級配置、風險權衡與適用場景。

一、傳統提交 vs 非同步提交

二、配置方式

1. 系統級（需重啟或動態修改 spfile）

ALTER SYSTEM SET COMMIT_WRITE = 'BATCH,NOWAIT' SCOPE=SPFILE;
-- 或分開設定
ALTER SYSTEM SET COMMIT_WRITE = 'BATCH' SCOPE=SPFILE;
ALTER SYSTEM SET COMMIT_WRITE = 'NOWAIT' SCOPE=SPFILE;

SCOPE=SPFILE 需重啟生效；SCOPE=MEMORY 即時生效但重啟失效。

2. 會話/語句級（無需權限，靈活控制）

-- 單次提交
COMMIT WRITE BATCH NOWAIT;

-- 會話級持久
ALTER SESSION SET COMMIT_WRITE = 'BATCH,NOWAIT';

三、參數組合語義

• WAIT（預設）：等待 LGWR 寫入完成
• NOWAIT：不等待，立即返回
• IMMEDIATE（預設）：每次 COMMIT 觸發 LGWR 寫入
• BATCH：累積 redo 批次寫入，減少 I/O 次數

推薦組合：BATCH, NOWAIT 效能增益最大，但風險也最高。

四、風險與數據損失場景

1. 實例崩潰：記憶體中尚未刷盤的 redo 丟失，對應事務雖已向用戶返回「提交成功」，但重啟後實際回滾
2. 備份/恢復不一致：若用該模式跑批次匯入，崩潰後無法從 Redo 恢復該批次
3. Data Guard 影響：Standby 適用 Redo Apply，若 Primary 丟失 redo，Standby 也會缺失

五、適用與禁用場景

六、監控驗證

-- 觀察 log file sync 等待時間下降
SELECT event, total_waits, time_waited_micro
FROM v$system_event
WHERE event = 'log file sync';

-- 確認參數生效
SHOW PARAMETER commit_write;

七、總結

COMMIT_WRITE = BATCH,NOWAIT 是用「有限數據風險」換取「極致寫入吞吐」的利器。**嚴禁用於核心業務**；若使用，必須：

1. 業務方簽署風險確認書
2. 配合 RMAN 高頻備份（如每 15 分鐘）
3. 建立「崩潰後數據對賬/補錄」應急預案

對多數系統，**優化提交頻率（批次提交）、調大 Redo Log、使用 SSD 存放 Redo** 才是正統調優路徑。