Oracle DELETE 與 COMMIT 執行流程深度解析：從 Buffer Cache 到 Redo Log 的完整鏈路

DELETE 階段：標記刪除、產生 Redo、寫入 Undo

1. 讀取數據塊：Server Process 從數據文件讀取目標塊到 **Buffer Cache** (若不在缓存)。
2. 記錄 Redo：在 **Redo Log Buffer** 記錄刪除操作細節 (為了崩潰恢復重做)。
3. 分配 Undo 槽位：在相應 **Undo Segment Header** 的事務表中創建事務槽位 (Transaction Slot)。
4. 寫入前鏡像：將被刪除行的 **前鏡像 (Before Image)** 寫入 **Undo Block** (用於回滾/一致性讀)。
5. 修改緩存塊：在 Buffer Cache 中標記行刪除 (設定刪除標誌)、將塊標記為 **Dirty (髒塊)**。

此階段 **未持久化**，僅在內存完成。若實例崩潰，PMON 回滾未提交事務，Redo/Undo 保證可恢復。

COMMIT 階段：SCN 賦予、LGWR 刷盤、快速提交/延遲塊清理

1. 生成 SCN：Oracle 分配一個 **全局單調遞增 SCN** 標記此提交點。
2. 標記事務提交：在 Undo Segment Header 事務表中將該事務槽位狀態置為 **COMMITTED**。
3. LGWR 刷盤：LGWR 將 **Redo Log Buffer** 同步寫入 **Online Redo Log File** (含該事務所有 Redo 記錄及 SCN) —— **提交唯一持久化動作**。
4. 快速提交：若髒塊仍在 Buffer Cache，**直接在塊頭記錄提交 SCN** (ITL 入口)，無需立即寫盤，**極快**。
5. 延遲塊清理：若髒塊已被 DBWn 寫回數據文件 (提交前)，下次訪問該塊的進程會：
   1. 從 Undo Segment 讀取事務狀態確認已提交。
   2. 獲取提交 SCN 寫回塊頭 (ITL)。
   3. 清理鎖標記。
   此為 **Delayed Block Cleanout**，分攤清理開銷到後續讀取者。

關鍵圖解 (文字版)

DELETE:
User Process → Buffer Cache (Dirty Block) → Redo Log Buffer → Undo Segment (Before Image)
                                    ↓
COMMIT:
Generate SCN → Undo Header (Commit Flag) → LGWR → Online Redo Log (Disk) [持久化點]
                                    ↓
Fast Commit: Block Header (ITL) <- SCN (if block in cache)
Delayed Cleanout: Next Visitor -> Undo Header -> Block Header (ITL) <- SCN

實務啟示

結語

DELETE+COMMIT 是 **「內存標記 → Redo 持久化 → SCN 全局有序 → 快速/延遲清理」** 的精密協作。理解 **LGWR 刷盤是提交的唯一持久化點**、**SCN 是一致性與恢復的時鍾**、**快速提交避免寫盤延遲**、**延遲清理分攤開銷**，能指導 DBA 從存儲、參數、應用模式三個維度系統性優化寫入效能。