Oracle 效能調校：資料庫配置與 I/O 優化策略

核心原則

• 降低磁碟 I/O 總量
• 分散 I/O 載跨多裝置
• 表空間採用本地管理 (LMT)

一、檔案佈局分散策略

1. 資料檔與 Redo Log 分離：不同磁碟/控制器/RAID 群組。
2. 移除非資料庫 I/O：OS、Swap、應用 Log 另置。
3. 評估 Raw Device：繞過檔案系統減少開銷（現代檔案系統已優化，視測試決定）。
4. 分割表資料：Partitioning 將熱/冷資料分散至不同表空間/磁碟。

二、表空間規劃準則

• SYSTEM 僅存放字典物件。
• 建立 LMT 表空間（EXTENT MANAGEMENT LOCAL）避免空間管理爭用。
• 表與索引分離至獨立表空間。
• 獨立 Rollback/Undo 表空間。
• 大型物件（LOB、Partition）各自獨立表空間。
• 建立一個以上 Temporary 表空間（TEMPORARY TABLESPACE）。
• 必須獨立表空間的物件：字典、Undo、索引、臨時段、表、大物件。

三、I/O 統計監控

SELECT phyrds, phywrts, d.name
  FROM v$datafile d, v$filestat f
 WHERE f.file# = d.file#
 ORDER BY d.name;

識別高 PHYRDS/PHYWRTS 檔案，針對性分散或調整存取路徑。

四、檔案分割實作

• RAID 0/1/5/10 硬體分條。
• 手工指定 Extent 位置：
  ALTER TABLE t ALLOCATE EXTENT (DATAFILE 'file_path' SIZE 10M);
  ⚠️ 維護負擔大，建議配合 Partitioning 自動管理。

五、全表掃描優化

監控掃描類型

SELECT name, value FROM v$sysstat WHERE name LIKE '%table scan%';

• table scans (long tables) 偏高 → 缺乏有效索引或統計過期，檢查應用/建立索引。
• DB_FILE_MULTIBLOCK_READ_COUNT 依 OS 最佳化（通常 8~128），減少多區塊讀取呼叫次數。

長時間操作追蹤

SELECT sid, serial#, opname, target,
       TO_CHAR(start_time,'HH24:MI:SS') start_time,
       (sofar/totalwork)*100 pct_done
  FROM v$session_longops
 WHERE totalwork > 0 AND sofar < totalwork;

關聯 SQL_HASH_VALUE → V$SQLTEXT 定位具體 SQL。

六、Checkpoint 調校

• 動作：DBWn 寫髒塊、CKPT 更新檔頭/控制檔。
• 頻繁 Checkpoint：縮短恢復時間、降低運行效能（I/O 尖峰）。
• 觸發：Log Group 循環寫滿 → 強制 Checkpoint。
• OLTP 大 SGA 少 Checkpoint 易造成 I/O 風暴 → **適度增加頻率**平滑 I/O。
• 調整手段：增大 Redo Log 檔案、增加 Log Group 數量** 延長切換間隔。

七、Redo Log 檔案最佳實踐

• 大小適中（切換間隔 15~30 分鐘為宜）。
• 至少 3~4 Group 消除等待。
• 放置於獨立、高速儲存（可用 Raw Device）。
• Group 內成員多路徑鏡像。

八、Archive Log 參數範本 (10g+ 多目的地語法)

LOG_ARCHIVE_MAX_PROCESSES = 2
LOG_ARCHIVE_DEST_1 = 'LOCATION=/u05/prodarch MANDATORY'
LOG_ARCHIVE_DEST_STATE_1 = ENABLE
LOG_ARCHIVE_DEST_2 = 'LOCATION=/u05/prodarch2 OPTIONAL REOPEN=10'
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
LOG_ARCHIVE_MIN_SUCCEED_DEST = 1
LOG_ARCHIVE_DEST_STATE_3 = DEFER
LOG_ARCHIVE_DEST_STATE_4 = DEFER
LOG_ARCHIVE_DEST_STATE_5 = DEFER

關鍵：雙目的地（一主一備）、REOPEN 容錯、MIN_SUCCEED_DEST=1 避免單點失效阻塞主庫。