Oracle 資料庫效能調優：磁碟 I/O 優化完整指南

概述

影響資料庫效能的關鍵因素主要包含系統層面、資料庫本身以及網路傳輸三大維度。而在資料庫內部優化中，磁碟 I/O 優化佔據核心地位，其他還包括回滾段優化、Redo 日誌優化、系統全域區 (SGA) 優化以及資料庫物件優化。

資料庫效能監控基礎

若要掌握系統運行狀態，必須先開啟統計收集功能。在 init.ora 參數檔中設定 TIMED_STATISTICS=TRUE，並在 Session 層級執行 ALTER SESSION SET TIMED_STATISTICS=TRUE。接著以 connect internal 方式登入 svrmgrl，於業務正常運行期間執行 utlbstat.sql 開始採樣，經過一段時間後執行 utlestat.sql 停止採樣。分析報告將自動生成於 report.txt 中（這兩個腳本通常位於 $ORACLE_HOME/RDBMS/ADMIN 目錄下）。

磁碟 I/O 優化核心策略

1. 診斷系統級 I/O 瓶頸

在 UNIX 系統可使用 sar -d 檢視整體 I/O 統計；Windows 環境則透過「效能監視器」觀察磁碟佇列長度、平均秒數/傳輸等關鍵計數器。

2. 識別 Oracle 檔案 I/O 進程

Oracle 透過多個背景進程處理 I/O：DBWn (資料庫寫入進程) 負責將髒緩衝區寫入資料檔；LGWR (日誌寫入進程) 循序寫入 Redo Log；ARCH (封存進程) 處理封存日誌；CKPT (檢查點進程) 更新控制檔與資料檔標頭。理解這些進程職責有助於定位爭用熱點。

3. 利用 V$FILESTAT 精準定位資料檔熱點

執行以下查詢即可獲得每個資料檔的物理讀寫次數，進而判斷哪些檔案承受最大 I/O 壓力：

SELECT NAME, PHYRDS, PHYWRTS 
FROM V$DATAFILE DF, V$FILESTAT FS 
WHERE DF.FILE# = FS.FILE#;

4. 實施 I/O 分散策略消除磁碟爭用

• 分離資料檔與 Redo Log： 將高頻循序寫入的 Redo Log 放置於獨立磁碟，避免干擾資料檔的隨機讀寫。
• 表資料條帶化： 利用 RAID 0+1 或 ASM 將大表資料分散至多磁碟，提升平行 I/O 能力。
• 分離表與索引： 將表與其索引置於不同表空間/磁碟，減少查詢時的磁頭跳動。
• 排除非 Oracle I/O 干擾： 確保資料庫專用磁碟不運行備份、防毒掃描等無關負載。

5. 避免動態空間擴展帶來的效能抖動

當段 (Segment) 空間耗盡觸發動態擴展時，會產生遞迴呼叫與鎖競爭。可透過 V$SYSSTAT 中的 recursive calls 指標監控頻率。解決方案為：預估實體最大尺寸，建立時一次配置足夠的 Extent (區間)；或使用區域管理表空間 (LMT) 搭配自動區間分配。

6. 回滾段空間管理最佳實踐

• 回滾段大小由儲存參數決定，必須容納最大交易的回滾項目。
• 長查詢修改資料需指派大型回滾段 (SET TRANSACTION USE ROLLBACK SEGMENT ...)。
• OLTP 高頻小額交易適合使用多個小型回滾段分散競爭。

7. 消除遷移與鏈結列

遷移列因 UPDATE 導致行長度超過區塊剩餘空間而移動；鏈結列因單行過大跨越多個區塊。處理步驟：

1. ANALYZE TABLE ... LIST CHAINED ROWS 收集資訊至 CHAINED_ROWS 表。
2. 建立中間表暫存問題列 → 刪除原表問題列 → 從中間表插回 → 刪除中間表。
3. 重新分析驗證；若仍有列出現於 CHAINED_ROWS，屬鏈結列，唯有增大區塊大小 (DB_BLOCK_SIZE) 解決。

排序作業調優

記憶體排序 vs 磁碟排序

查詢 V$SYSSTAT 中 SORTS (MEMORY) 與 SORTS (DISK) 比值。若磁碟排序佔比過高，應調大 SORT_AREA_SIZE (專用伺服器) 或 PGA_AGGREGATE_TARGET (共用伺服器/自動 PGA 管理)。

利用 NOSORT 建立索引跳過排序

若資料已依索引鍵排序，可用 CREATE INDEX ... NOSORT 避免排序開銷。

關鍵背景進程 I/O 調優

Checkpoint 頻率平衡

Checkpoint 會觸發 DBWn 將所有髒緩衝區刷盤並更新控制檔。過於頻繁增加 I/O 壓力，過於稀疏延長復原時間。透過 LOG_CHECKPOINT_INTERVAL、LOG_CHECKPOINT_TIMEOUT、FAST_START_MTTR_TARGET (10g+) 精細控制。

LGWR I/O 優化

單次 I/O 大小取決於 LOG_BUFFER。過大延遲寫入，過小導致頻繁小 I/O。若平均 I/O 尺寸過大致 Redo Log 成為瓶頸，可實施 Redo Log 條帶化 (多成員分布於不同磁碟)。

DBWn 多進程並行寫入

設定 DB_WRITER_PROCESSES (10g 預設為 CPU 核心數/8 取整) 啟用多寫入進程，分攤刷盤負載。

鎖與閂爭用診斷與解決

資源限制檢視

V$RESOURCE_LIMIT 顯示各資源當前使用量與上限，快速判斷是否觸及上限。

等待事件分析三步走

1. V$SYSTEM_EVENT 找出平均等待時間最長的事件。
2. V$WAITSTAT 定位等待最多的區塊類別 (Data Block, Undo Header 等)。
3. V$SESSION_WAIT 精確到具體 Session、File#、Block#。

回滾段爭用專項查詢

SELECT CLASS, COUNT 
FROM V$WAITSTAT 
WHERE CLASS IN ('SYSTEM UNDO HEADER','SYSTEM UNDO BLOCK','UNDO HEADER','UNDO BLOCK');

調度器與共用伺服器爭用

• 檢視 V$DISPATCHER 忙碌率 > 50% 需增加 MTS_DISPATCHERS。
• 檢視 V$QUEUE 平均等待時間過長需擴充調度器或共用伺服器 (MTS_MAX_SERVERS)。

Redo Log Buffer Latch 爭用深度解析

V$SYSSTAT 中 redo buffer allocation retries 應趨近 0，持續增長代表空間不足，需增大 LOG_BUFFER。Latch 細分為 Allocation Latch (空間分配) 與 Copy Latch (內容複製，數量由 LOG_SIMULTANEOUS_COPIES 控制，最大至 2×CPU)。

查詢 V$LATCH / V$LATCHNAME 計算 Miss Rate 判斷嚴重度：

SELECT ln.name, gets, misses, immediate_gets, immediate_misses
FROM v$latch l, v$latchname ln
WHERE ln.name IN ('redo allocation','redo copy') AND ln.latch# = l.latch#;

• 降低 Allocation Latch 爭用：減小 LOG_SMALL_ENTRY_MAX_SIZE 縮短持有時間。
• 降低 Copy Latch 爭用：增大 LOG_SIMULTANEOUS_COPIES 增加 Latch 數量。

Free List 爭用處理

1. V$WAITSTAT 確認 Data Block 爭用。
2. V$SYSTEM_EVENT 確認 Buffer Busy Waits 高企。
3. V$SESSION_WAIT 取得具體 File/Block/ID。
4. 查 DBA_EXTENTS 定位段名與類型 → 查 DBA_SEGMENTS 取得 FREELISTS 數值。
5. 重建表時指定 FREELISTS / FREELIST GROUPS (RAC) 參數，或採用 ASSM (自動段空間管理) 徹底解決。

資料庫檔案佈局黃金法則

• 表與索引建立於不同表空間並置於不同磁碟。
• Redo Log 與回滾段分離於不同磁碟。
• Oracle 執行檔與資料庫檔案物理隔離。
• 核心熱點表/索引/表空間獨享高效能磁碟 (SSD/NVMe)。
• 嚴禁在資料庫磁碟安裝第三方軟體。

磁碟 I/O 監控實戰查詢

定期執行以下查詢，依 PHYRDS + PHYWRTS 總量評估各表空間 I/O 壓力，指導檔案遷移：

SELECT NAME, PHYRDS, PHYWRTS
FROM V$DATAFILE, V$FILESTAT
WHERE V$DATAFILE.FILE# = V$FILESTAT.FILE#;

範例輸出顯示 SYSTEM 表空間讀寫顯著高於其他，若為核心業務表空間則正常；若為審計/臨時表空間異常高則需調查。