DB2 基效能調校實戰：緩衝池、表空間與索引優化全指南

核心觀念：緩衝池是效能調校的基石

緩衝池作為記憶體中專供 DB2 使用的頁面快取區，直接決定磁碟 I/O 頻率。研究表明，合理配置緩衝池是資料庫效能調優的第一關鍵步驟。DB2 啟動時預設建立 IBMDEFAULTBP（Linux/Unix 1000 頁、Windows 250 頁，頁面 4KB），DBA 應依據實際負載透過 ALTER BUFFERPOOL 或控制中心動態調整大小與數量。

單一大池 vs 多小池：記憶體決策邏輯

• 可用記憶體 ≤ 10,000 頁（4KB）：單一大緩衝池優於多小池，避免頻繁頁面置換與目錄表 I/O 競爭。
• 記憶體充裕時：建議為不同特性表空間建立專用緩衝池：
  臨時表空間、高頻更新表空間、高頻查詢低更新表空間、隨機查詢表空間、低訪問歸檔表空間、核心熱點資料表空間。

特殊場景策略：超大表隨機存取用小池（用完即丟）；小表高頻訪問用大池（一次載入、反覆複用）。

表空間架構：SMS vs DMS 的效能取捨

關鍵參數：Extent Size 與 Prefetch Size 科學計算

預設皆為 32 頁，實際應依磁碟陣列調整：

Min Extent Size = (容器跨越的物理磁碟數 × 4096) / 表空間頁面大小
Max Extent Size = 524288 / 表空間頁面大小
最佳值介於兩者間，需實測驗證（建立後不可修改，須重建表空間）

Prefetch Size = Extent Size × 容器數 × 3（因子通常為 3）
Prefetch 越大通常越好，可突破 Extent 上限。

資料分離存放：打破預設共存模式

DMS 表空間允許將「常規資料、索引、長欄位」三類物件分置獨立表空間，預設同居一空間。實測分離存放常能顯著改善 I/O 平行度與維護彈性。

SMS 多頁分配開關 db2empfa

大量匯入時啟用 db2empfa（設 multipage_alloc=YES），讓 SMS 每次按 Extent 而非單頁擴展，減少檔案系統元資料操作開銷。

索引策略：設計顧問輔助決策

索引加速查詢與並發，但佔用空間、拖慢寫入。DB2 8.1 引入「設計顧問」，擷取典型負載後推薦建立/刪除索引，避免主觀臆測。

統計資訊維護：優化器的眼睛

RUNSTATS 定期更新系統目錄統計，支援優化器生成最優存取路徑。關鍵觸發時機：

• 大量增刪改、匯入/載入、新增欄位/索引、表重組後
• 統計更新後必須 REBIND 相關套件，否則靜態 SQL 可能沿用舊計劃導致效能退化

語法範例：RUNSTATS ON TABLE DEFAULT.EMPLOYEE WITH DISTRIBUTION AND INDEXES ALL SHRLEVEL CHANGE

調優路線圖總結

1. 實例/資料庫/註冊變數基礎配置
2. 緩衝池數量、大小、用途規劃
3. 表空間類型、Extent/Prefetch 精準計算
4. 統計資訊定期自動化維護
5. 應用層 SQL 與索引協同優化

基本調優非一蹴而就，需建立持續監控與迭代機制。