Oracle 架構層效能優化：I/O 分散、檢查點、重做日誌與歸檔配置

降低與分散磁碟 I/O

• 資料檔與重做日誌分離不同磁碟/控制器。
• 移除非資料庫佔用的磁碟 I/O 負載。
• 評估裸裝置 提升寫入效能。
• 資料分區：將熱點表/索引分散至不同表空間/磁碟。

表空間規劃原則

• SYSTEM 僅存放字典物件。
• 採用本地管理 (LMT) 避免空間管理爭用。
• 表與索引分離至獨立表空間。
• 獨立回滾/UNDO 表空間。
• 大型物件 (LOB) 獨立表空間。
• 建立一個或多個專用臨時表空間。

I/O 統計監控

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

識別讀寫最頻繁檔案，針對性調整佈局。

全表掃描優化

• 監控 V$SYSSTAT 中 table scans (long tables)，數值高表示缺索引或統計過期。
• DB_FILE_MULTIBLOCK_READ_COUNT 依 OS 調整，減少多區塊讀取呼叫次數。
• V$SESSION_LONGOPS 結合 V$SQLTEXT 定位長時間全掃描 SQL。

檢查點策略

• 頻繁 Checkpoint：縮短恢復時間，但增加運行期 I/O 壓力 (DBWn 寫髒塊、CKPT 更新檔頭)。
• 觸發條件：重做日誌組循環切換 (Log Switch)。
• 調整手段：增大日誌檔、增加日誌組數量 → 延長切換間隔。
• OLTP 大 SGA 環境：適度增加頻率避免單次 Checkpoint 衝擊。

重做日誌配置最佳實踐

• 日誌組成員多路徑鏡像 (Multiplexing)。
• 日誌檔大小適中，避免過頻切換或單次切換耗時過長。
• 放置於專用高速儲存裝置 (可用裸裝置)。
• 至少 3 組日誌，確保 LGWR 不等待 ARCn。

歸檔日誌穩健配置 (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

• 雙歸檔目的地，一主一備，備用可選、自動重試。
• 最少 1 個成功即可，避免單點故障掛起資料庫。
• 預留額外目的地狀態為 DEFER，緊急時啟用。