資料庫物理設計實戰經驗談：從作業系統優化到控制檔案管理全景指南

核心原則

資料庫物理設計的核心目標：**資料安全**與**效能高效**。良好的物理分布是基石，如蓋樓打地基，必須在建立資料庫前規劃，而非事後補救。

十一大物理設計面向

1. 作業系統優化

• 確保足夠 I/O 頻寬、CPU 能力、交換空間，降低系統時間
• Oracle 進程優先級一致，避免高優先級等待低優先級 (優先級反轉)
• 勿將背景進程綁定特定 CPU (恐餓死)
• 善用 OS Resource Manager 劃分資源優先級、限制消耗

2. 磁碟佈局與 RAID 選型

• 多小碟優於單大碟 (並行 I/O)
• 資料檔、重做日誌、臨時表空間、索引**分離不同物理磁碟**
• I/O 統計：V$FILESTAT (phyrds/phyblkrd/phywrts) 監控熱點

3. 初始化參數選擇 (不可變/難變參數)

• DB_BLOCK_SIZE (2K~32K)：較大區塊提升全表掃描效能，但增加區塊競爭，需配合 FREELISTS/INITRANS/MAXTRANS
• DB_NAME, DB_DOMAIN, COMPATIBLE, NLS_*：建庫前必定

4. 記憶體管理 (SGA/PGA)

• SGA ≈ 物理記憶體 40~60%
• 關鍵參數：DB_CACHE_SIZE (9i+)、SHARED_POOL_SIZE、LOG_BUFFER、LARGE_POOL、JAVA_POOL
• 命中率監控：V$SYSSTAT (db block gets + consistent gets vs physical reads) 目標 > 90%
• PGA：PGA_AGGREGATE_TARGET (OLTP 20% / DSS 50% 可用記憶體)

5. CPU 監控與管理

• Unix: sar -u；Windows: 效能監視器
• 空閒 + I/O wait < 5% 即為瓶頸
• 解決：SQL 優化、加硬體、DB Resource Manager 分配 CPU

6. 表空間規劃原則

• 應用表/索引分散多表空間，降低單一檔案 I/O 競爭
• 系統表空間僅放資料字典與系統回滾段
• 索引與表**分離不同表空間** (並發 I/O 隔離)
• 臨時表空間專用於排序，勿放其他物件

7. 回滾段管理

• OLTP：多小回滾段 (10~20KB, 2~4 extent)
• 長查詢：大回滾段 (最大表 10%)
• OPTIMAL 設定目標縮小尺寸，監控 V$ROLLSTAT (extends/shrinks 高則調大)
• 等待統計 V$WAITSTAT (undo header/block) > 10% 請求總數則增加回滾段

8. 聯機重做日誌

• 大小：數百 MB ~ 數 GB，目標 ~20 分鐘切換一次
• 重做日誌緩衝區爭用：V$SYSSTAT 'redo log space requests' 接近 0，否則增 LOG_BUFFER
• 擴大日誌：建新群組 → 切換 → 刪舊 → 重建 → 切換 → 刪過渡 → 備份控制檔

9. 歸檔重做日誌

• 與聯機日誌、資料檔、控制檔**分離不同裝置**
• 備份後可刪除/移走，避免佔滿磁碟

10. 控制檔案管理

• 多路徑多工 (multiplex) 至少 2 份
• 移動流程：關閉 → 移動檔案 → 修改 init.ora control_files → 啟動 mount

11. 資料檔遷移實戰 (兩種方法)

-- 方法 1: ALTER DATABASE
SHUTDOWN;  -- OS 層移動檔案
STARTUP MOUNT;
ALTER DATABASE RENAME FILE 'old' TO 'new';
ALTER DATABASE OPEN;

-- 方法 2: ALTER TABLESPACE (9i+ 可線上)
ALTER TABLESPACE users OFFLINE;
-- OS 移動
ALTER TABLESPACE users RENAME DATAFILE 'old' TO 'new';
ALTER TABLESPACE users ONLINE;

結語

物理設計雖以 Oracle 為例，但**核心思想通用**：I/O 隔離、資源預留、單點失效排除、監控驅動調整。各資料庫語法不同，原則一致。