DB2 效能調校簡易指南 - 監控、緩衝池與 SQL 優化

前置作業：啟用監控開關

DB2 "UPDATE MONITOR SWITCHES USING LOCK ON SORT ON BUFFERPOOL ON UOW ON TABLE ON STATEMENT ON"

開啟鎖、排序、緩衝池、工作單元、表、陳述式等監控開關，以獲取效能分析所需資料。

最直接見效：緩衝池調校

核心概念

• 緩衝池 = 記憶體中快取資料庫頁面 (含表列、索引項) 的區域。
• 記憶體存取速度遠快於磁碟，提高緩衝池命中率 = 減少實體 I/O = 提升效能。
• 預設緩衝池 IBMDEFAULTBP 隨資料庫建立，大小由 BUFFPAGE (當 NPAGES=-1) 或 NPAGES 控制。

建議設定

• 將 NPAGES = -1，改由 BUFFPAGE 統一管理，方便調整。
• 依資料庫大小與應用特性，將 BUFFPAGE 設為合理大值 (預設極小)。

命中率計算與目標

DB2 "GET SNAPSHOT FOR ALL BUFFERPOOLS"

從快照輸出取得：

• Logical Reads (邏輯讀取) = Buffer Pool Data Logical Reads + Index Logical Reads
• Physical Reads (實體讀取) = Buffer Pool Data Physical Reads + Index Physical Reads

命中率公式：

(1 - (Physical Reads / Logical Reads)) * 100%

• 目標 > 95%，愈接近 100% 愈好。

提升命中率策略

1. 增加緩衝池大小。
2. 建立多個緩衝池：熱門大表專用表空間 + 專用緩衝池；小表群組共用緩衝池；嘗試不同大小組合找最佳解。
3. 避免過度配置：記憶體不足導致 OS 分頁反而降低效能。依測試環境快照決定適當大小。

原則：太小 → 過多實體 I/O；太大 → OS 分頁風險 + CPU 管理開銷。最佳點在效能曲線拐點處。

關鍵：SQL 語句優化

• 一條爛 SQL 可摧毀整個系統效能；無配置參數能補救錯誤 SQL 造成的高成本。
• DBA 常無法修改應用程式 SQL (廠商提供)，僅能透過三條路徑優化：
  1. 新增/修改索引
  2. 調整叢集/分區
  3. 更新目錄統計資訊 (RUNSTATS)

SQL 實際成本 = 單次執行成本 × 執行頻率

• 單次成本可由 DB2 EXPLAIN、第三方工具、SQL EVENT MONITOR 獲得。
• 執行頻率需分析 SQL EVENT MONITOR 歷史資料 (耗時)。
• 核心任務：識別最高「實際成本」的 SQL，並降低其成本。

最佳效能 = 合適的物理架構 + 無高成本 SQL

• 所有調整旋鈕設定得宜、記憶體有效分配、I/O 均勻分佈於各磁碟。

不可遺漏：鎖與並發

(原文截斷，補充核心觀念)

• 監控 LOCK LIST 使用率、鎖升級、死結/逾時頻率。
• 優化索引減少掃描範圍 → 減少鎖持有數量/時間。
• 適當隔離等級 (CS/UR) 平衡一致性與並發。
• 長交易拆解、避免使用者互動式交易。

總結：調校優先順序

1. 緩衝池 (最快見效、風險最低)
2. SQL/索引 (根本解決、需開發配合)
3. 排序堆/鎖清單/套件快取 等記憶體參數
4. 磁碟佈局/容器/平行度 等物理架構
5. 應用程式邏輯/交易設計 (源頭治理)

持續監控、量化分析、針對性調整，方能維持 DB2 系統長期高效運行。