您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle I/O 問題深度解析：機制、等待事件與調優實戰

編輯：Oracle 資料庫基礎

1 Oracle I/O 產生機制

1.1 寫操作

Oracle 物理檔案：控制檔、重做日誌檔、資料檔（系統、暫存、回滾段、使用者資料）、歸檔日誌檔。

1.1.1 控制檔

記錄資料庫物理結構、檢查點 SCN，結構變更或資料檔 SCN 變更時更新。

1.1.2 使用者資料修改

資料優先在 Buffer Cache 修改，標記為「髒」區塊。
DBWn 進程依 LRU 演算法將「冷」且「髒」區塊寫入磁碟：

服務進程掃描閥值仍找不到乾淨區塊 → 通知 DBWn 寫入。
發生檢查點 → DBWn 寫入。

1.1.3 Redo Log

非直接寫模式下，交易寫操作產生 Redo 記錄，先寫入 Log Buffer。
LGWR 進程將 Log Buffer 寫入 Redo Log File（循環重用）。
觸發 LGWR 寫入的條件：交易提交、每 3 秒、Log Buffer 達 1/3 滿、DBWn 寫入髒區塊時。

1.1.4 歸檔日誌

啟用 Archive Log 模式後，日誌組切換時 ARCn 將 Redo Log 複製至歸檔目錄。

1.1.5 暫存表空間

三大使用場景：暫存表/索引操作、排序 (JOIN、建索引、ORDER BY、GROUP BY)、暫存 LOB 物件。

排序優先在記憶體 SORT_AREA_SIZE 進行；不足則使用磁碟暫存表空間。
範例：SORT_AREA_SIZE 10M 時為 memory sort；10K 時轉為 disk sort，產生物理讀寫。

1.1.6 回滾段

未提交交易的修改記錄於回滾段，支援回滾、一致性讀、資料庫恢復。提交後為維持一致性讀保留一段時間。

1.1.7 Direct-Path Insert

繞過 Buffer Cache 直接寫入資料檔（APPEND hint、CTAS、SQL*Loader Direct 模式）。可指定 NOLOGGING 減少 Redo 產生，提升大量載入效能。

1.2 讀操作

1.2.1 物理讀

首次讀取： 從磁碟讀入 Buffer Cache，置於 LRU 列表 MRU 端。
區塊被替換後重讀： Buffer Cache 空間不足時，LRU 端區塊被淘汰，再次存取需重新物理讀取。
全表掃描： 讀取區塊置於 LRU 端，避免汙染熱區塊；若需快取可指定 CACHE 屬性。

1.2.2 邏輯讀

從 Buffer Cache 讀取，分兩種模式：

即時讀： 讀取最新版本區塊（加行鎖、標記髒），發生於 DML 操作。
一致性讀： 依 SCN 比對，必要時從 Undo 重構舊版本，普通查詢皆為一致性讀。

1.2.3 資料尋找方式

方式	觸發條件	I/O 事件	特徵
Full Table Scan	無索引或索引代價過高	db file scattered read	多區塊讀 (`db_file_multiblock_read_count`)，讀至高水位線
INDEX UNIQUE SCAN	唯一索引全欄位等值比對	db file sequential read	從根節點單路徑走到葉子，最多 1 筆結果
INDEX RANGE SCAN	非唯一索引或唯一索引部分欄位	db file sequential read	閉包/非閉包範圍掃描，按索引順序回傳
TABLE ACCESS BY INDEX ROWID	索引無覆蓋所需欄位	db file sequential read	依 ROWID 回表取資料
INDEX FAST FULL SCAN	只需索引欄位、大範圍掃描	db file scattered read	直接掃描所有葉子區塊，多區塊讀
INDEX FULL SCAN	參數限制或強制索引	db file sequential read	從根節點遍歷整棵樹，單區塊讀

2 I/O 系統設計與配置

2.1 作業系統與存儲

2.1.1 檔案系統 vs 裸裝置

檔案系統雙重快取 (OS + Oracle)，裸裝置繞過 OS 快取，由 Oracle 直接控制，減少 CPU 開銷。

2.1.2 I/O 模式

Direct I/O / Concurrent I/O： 繞過檔案系統快取。各平台啟用參數 filesystemio_options (ASYNCH, DIRECTIO, SETALL, NONE)。AIX JFS2 支援 CIO，多進程並行讀寫同一檔案。
Asynchronous I/O： 發出請求不阻塞，完成時通知進程。DBWn、排序等大量 I/O 操作受益。需 OS 支援且設定 DISK_ASYNCH_IO=TRUE。

2.1.3 負載平衡與條帶化

條帶化將資料分散至多磁碟，避免熱點磁碟爭用。關鍵參數：

條帶深度： 單一條帶大小 (建議 256K~1M)。OLTP 採粗粒度 (> I/O 大小) 以單磁碟服務多請求；DSS 採細粒度 (< 多區塊讀大小) 讓單一請求跨多磁碟平衡負載。
條帶寬度： 條帶數量 (磁碟數)。需滿足 條帶寬度 >= I/O請求大小 / 條帶深度。規劃擴充時考慮邏輯卷管理 (LVM) 動態加入磁碟能力。

2.1.4 RAID 等級選擇

RAID 0：純條帶，高效能、無保護。
RAID 1：鏡像，最高保護、讀效能佳、寫效能降、空間利用率 50%。
RAID 0+1：兼顧效能與保護，成本高。
RAID 3/5：奇偶校驗，RAID 5 寫效能差 (每次寫需算校驗)，OLTP 不建議；RAID-S/7 引入快取緩解。

2.1.5 SAN / NAS

SAN：光纖通道、區塊層級存取、高速專用網路、支援異地備援。
NAS：檔案層級、透過網路介面直接掛載、適合檔案共享。

2.2 I/O 配置原則

2.2.1 條帶化設定

依平台 max_io_size、db_block_size、db_file_multiblock_read_count、sort_area_size 等決定條帶深度/寬度。OLTP 採粗粒度，DSS 採細粒度。

2.2.2 人工條帶 (無 LVM 時)

依各資料檔 I/O 吞吐量手工分散至不同磁碟。注意：索引與表同磁碟無衝突 (先讀索引再讀表)，切分依據為實際 I/O 負載而非物件類型。

2.2.3 檔案分離策略

表/索引/暫存表空間： 先 SQL 調優，再考慮分離熱點檔案。
Redo Log： 放無其他檔案磁碟，組員分離不同物理磁碟，避免 RAID 5，啟用 AIO。
歸檔日誌： 與 Redo Log 交錯放置 (如 A1,C1 / A2,C2 / B1,D1 / B2,D2) 避免 LGWR 與 ARCn 爭用。

2.3 三種簡單配置範例

所有檔案條帶化至單一鏡像邏輯卷 (RAID 1)，條帶深度 > 最大常態 I/O 大小。
歸檔日誌獨立磁碟，提升歸檔效率、減少干擾。
Redo Log 獨立磁碟 (2 顆鏡像 4 顆)，交錯放置避免 LGWR/ARC 衝突。

3 Oracle I/O 問題診斷與調優

3.1 判斷 I/O 瓶頸

Unix: iostat, sar -d；Windows: 效能監視器或 TopShow。
sar -u 中 %wio 高表示 CPU 等待 I/O，實際 CPU 閒置，瓶頸在磁碟。
Statspack/AWR Top 5 Timed Events 中 db file sequential read、db file scattered read 佔比高即為 I/O 瓶頸。

3.2 通用調優手段

SQL 調優： 少數 Top SQL 造成大量 I/O，優化它們可解決大部分問題 (10g ADDM + SQL Tuning Advisor)。
記憶體快取： 擴大 Buffer Cache、Log Buffer、Sort Area 減少物理 I/O。
多區塊讀大小： 調整 DB_FILE_MULTIBLOCK_READ_COUNT，平衡 I/O 建立時間與傳輸時間。
OS 層面： 啟用 Direct/Async I/O、提高 max_io_size。
ASM： 10g+ 自動負載平衡、防碎片。
存儲技術： 條帶化、RAID、SAN/NAS 實現負載平衡。
手工佈署： 無先進存儲時，依 I/O 負載手工分散檔案。
歸檔/硬體： 移除舊資料、採用更快硬體。

3.3 資料檔相關等待事件調優

3.3.1 db file sequential read (索引/單區塊讀)

SQL 調優： 建立/強制使用選擇性更好索引、重建碎片索引 (高度>3、使用率<75%、刪除率>15%)、降低聚簇因子 (重建表或建 IOT)、建立覆蓋索引避免回表、分區裁剪。
非 SQL 原因： 熱點檔案/磁碟 → 條帶化/遷移至高效能存儲；熱點段 → V$SEGMENT_STATISTICS 找出 → 重建/分區；Buffer Cache 不足 → 依 Statspack 建議調整、多尺寸快取池 KEEP 熱點段；Housekeep 歷史資料。

3.3.2 db file scattered read (全表/快速全索引掃描)

優化 Top SQL：建立缺失索引、修正統計資訊、避免 PL/SQL 集合/臨時表導致誤判、調整 optimizer_index_cost_adj (依 db file scattered/sequential read 平均等待時間比值迭代調整)、提高索引選擇性。
調大 DB_FILE_MULTIBLOCK_READ_COUNT (受限於 max_io_size)。
頻繁全掃描小表 → ALTER TABLE ... CACHE；索引 → KEEP 池。
分區表利用 Partition Pruning 減少掃描區塊；Housekeep 歷史資料。

3.3.3 db file parallel read

恢復操作或 Buffer Prefetching 並行讀取。優化參考 sequential read 非 SQL 手段。

3.3.4 direct path read / write (含 LOB)

磁碟排序 → 增加 SORT_AREA_SIZE 或 PGA_AGGREGATE_TARGET (WORKAREA_SIZE_POLICY=TRUE)。
並行查詢 → 謹慎設定平行度 (建議 2-4 x CPU)、配合 ASM、用 Hint 控制特定語句。
Hash Join → 增加 HASH_AREA_SIZE (1.5x Sort Area) 或 PGA。
LOB NOCACHE → 常讀 LOB 改 CACHE、或放檔案系統利用 OS 快取。
啟用 AIO (DISK_ASYNCH_IO=TRUE)、增加 PGA、升級存儲設備。

3.4 控制檔相關事件

control file parallel/sequential/single write：減少控制檔數量 (保底安全前提)、啟用 AIO、移至低負載磁碟。

3.5 Redo Log 相關事件

3.5.1 log file parallel write (LGWR 寫入)

避免長期熱備 (Redo 暴增)；Redo Log 放高速存儲/裸裝置、避免 RAID 5、隔離其他 I/O。
大量載入用 NOLOGGING/APPEND；減少 Redo Log 組成員數；降低補充日誌等級；適當增大 LOG_BUFFER (重試率>0.1% 或 redo log space requests>0)，上限 128K*CPU 或 512K 取大者。

3.5.2 log file sync (前台等待提交完成)

比較 log file sync 與 log file parallel write 平均等待時間：相近 → I/O 問題 (優化 Redo Log I/O)；前者遠大 → Latch 爭用 (log buffer latch、LGWR wait for copy)。
合併小交易為批次、減少提交次數。

3.5.3 日誌切換相關 (`log file switch completion`, `checkpoint incomplete`, `archiving needed`)

增大 Redo Log 檔案、降低切換頻率 (高峰期 20-30 分鐘一次)。
增大 LOG_CHECKPOINT_INTERVAL (受限於最小日誌 90%)。
歸檔慢 → 多 ARC 進程、增大歸檔緩衝、調整檢查點間隔、減少整體 I/O 衝突。

3.6 Buffer Cache 相關事件

db file parallel write：DBWR 並行寫髒區塊等待。減少磁碟 I/O 衝突、增加檢查點間隔、增大 Redo Log、啟用 AIO/多 DBWR/IO Slaves。
db file single write：修改資料檔頭等待。同樣處理磁碟衝突。
write complete waits：等待髒區塊寫入完成。啟用 AIO、增加 DBWR/IO Slaves、提升存儲效能。
free buffer waits：找不到乾淨區塊。調大 Buffer Cache (依 Statspack 建議)、提升髒區塊寫入效能。