DB2 效能調優十大黃金法則：從監控開關到 SQL 成本分析

前言

針對 Unix/Windows/OS/2 環境下的 DB2 UDB 電商 OLTP 應用，彙整十大最關鍵效能改善技巧，按重要性遞增排序（第 1 為最關鍵）。

第 10 項：啟用監控開關

db2 "update monitor switches using 
  lock ON sort ON bufferpool ON uow ON 
  table ON statement ON"

未開啟無法獲取效能數據，為所有調優基礎。

第 9 項：確保代理程式充足

db2 "get snapshot for database manager"

檢查 Agents waiting for a token、Agents stolen 若非 0，需增加 MAXAGENTS/MAX_COORDAGENTS。

第 8 項：調大最大開啟檔案數 (MAXFILOP)

db2 "get snapshot for database on DBNAME"
# 檢查 Database files closed > 0 則需調大
db2 "update db cfg for DBNAME using MAXFILOP N"

頻繁開關檔案嚴重拖累 SQL 回應時間。

第 7 項：設定合理鎖逾時 (LOCKTIMEOUT)

預設 -1（無限等待）對 OLTP 災難性。建議設 10-15 秒，並監控鎖等待、鎖清單記憶體使用率（超 50% 需加大 LOCKLIST）。

第 6 項：優化暫存表空間 (TEMPSPACE) 平行 I/O

• 至少 3 個容器分佈於 3 顆不同實體磁碟
• PREFETCHSIZE = EXTENTSIZE × 容器數
• 驗證指令：db2 "list tablespaces show detail"

第 5 項：控制記憶體排序 (SORTHEAP)

• OLTP 應避免大量排序，預設 256 頁 (1MB) 通常足夠
• 監控 Sort overflows 比率：(Sort overflows × 100) / Total sorts > 3% 表示嚴重排序問題
• SortsPerTX > 5 可能導致 CPU 瓶頸，需優化 SQL/索引/叢集

第 4 項：分析表存取模式 (Rows Read Per Transaction)

# 取得交易總數
db2 "get snapshot for database on DBNAME"
# 取得各表讀取行數
db2 "get snapshot for tables on DBNAME"
# 計算 RowsPerTX = Rows Read / Transactions

OLTP 每事務讀取 1-20 行屬正常；成百上千行表示掃描，需建索引或 RUNSTATS。

第 3 項：表空間效能特徵分析

對每個表空間評估：平均讀/寫時間、非同步/同步 I/O 比例、緩衝池命中率、每分鐘物理頁讀取、每事務邏輯/物理讀取。識別最慢表空間並檢視容器分佈、存取屬性一致性。

第 2 項：建立多緩衝池策略

• IBMDEFAULTBP：系統目錄 (SYSCATSPACE)
• BP_TEMP：暫存表空間 (TEMPSPACE)
• BP_RAND：隨機存取表空間 (OLTP 核心表)
• BP_SEQ：循序存取表空間 (資料倉儲、索引掃描)
• 大表可建專屬緩衝池 (如 BP_HOT, BP_IX)
• 科學測試命中率、I/O 時間、物理讀取率，尋找最佳平衡點

第 1 項：SQL 成本分析 — 解決根本性能殺手

單一惡劣 SQL 可摧毀整個系統。DBA 面臨三條路：加/改索引、改叢集、更新統計資訊。實際成本 = 單次執行成本 × 執行頻率。

標準流程：

1. 建立 SQL Event Monitor 寫入檔案
2. 啟用監視器，執行代表性負載
3. 停用並用 db2evmon 格式化輸出
4. 分析高成本 SQL，結合技巧 3/4 縮小搜尋範圍
5. 考慮廠商工具 (如 SQL-GUY) 自動化識別資源密集型 SQL

持續調優原則

最佳效能需硬體、配置、SQL 三位一體。電商環境變動快，需定期微調。DBA 的工作永遠做不完！