DB2 效能調優十大黃金法則：從監控開關到 SQL 成本分析的系統化實戰方法論

前言：為什麼需要系統化方法論？

電商 OLTP 系統效能劣化時，DBA 面臨巨大業務壓力。隨意調參數、加索引往往事倍功半。本文提煉經驗為 10 大優先級技巧，構成「由基礎設施到應用層」的完整診斷鏈，助你在最短時間定位根因、精準施策。

第 10 招：啟用監控開關——可見性的前提

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

• 未開啟 = 盲人騎瞎馬，無法獲取快照、事件監控器數據。
• 建議在實例啟動腳本中永久啟用，或資料庫激活時自動開啟（AUTOSTART）。

第 9 招：代理程式池充足度——連接風暴的緩衝

db2 "GET SNAPSHOT FOR DATABASE MANAGER"

關鍵指標：

第 8 招：最大開啟檔案數——避免反覆開關表空間檔案

db2 "GET SNAPSHOT FOR DATABASE ON <dbname>"

檢視 Database files closed：

• ≠ 0 表示 MAXFILOP 過小，DB2 強制關閉舊檔案開啟新檔案 → CPU 浪費、I/O 延遲。
• 調大：db2 "UPDATE DB CFG FOR <dbname> USING MAXFILOP N"（建議 500+，視表空間/容器數而定）。

第 7 招：鎖超時與鎖清單——防止雪崩效應

• LOCKTIMEOUT = -1（預設）= 永不超時，OLTP 災難：一個阻塞拖垮整個應用池。
• 建議設 10-15 秒：db2 "UPDATE DB CFG FOR <dbname> USING LOCKTIMEOUT 15"（需確認應用能處理 SQL0911N）。
• 同步監控：Lock list memory in use > 50% LOCKLIST → 擴大 LOCKLIST。
• 關鍵快照指標：Lock waits、Time database waited on locks、Deadlocks detected、Lock escalations、Lock Timeouts。

第 6 招：臨時表空間並行 I/O 設計——排序/雜湊連接的賽道

• 至少 3 個容器分佈在 3 個物理磁碟/RAID 群組。
• PREFETCHSIZE = EXTENTSIZE × 容器數（如 32 × 3 = 96）。
• 驗證：db2 "LIST TABLESPACES SHOW DETAIL" → 確認 Number of containers = 3、Prefetch size = 96。

第 5 招：記憶體排序堆守門員——拒絕大排序溢寫磁碟

db2 "GET SNAPSHOT FOR DATABASE ON <dbname>"

計算兩個關鍵比率：

• 排序溢出率 = Sort overflows * 100 / Total sorts < 3%（理想 < 1%）。超標 = 存在大排序，需優化 SQL/索引/叢集，而非單純加大 SORTHEAP。
• 每事務排序數 = Total sorts / (Commits + Rollbacks) < 5。超標 = CPU 密集型小排序過多，需改寫 SQL 避免排序。

SORTHEAP 預設 256 頁 (1MB) 通常足夠 OLTP；大幅調大只會掩蓋問題、增加記憶體壓力。

第 4 招：表存取強度分析——識別全表掃描

db2 "GET SNAPSHOT FOR DATABASE ON <dbname>"
db2 "GET SNAPSHOT FOR TABLES ON <dbname>"

步驟：

1. 從資料庫快照取得總事務數 = Commit + Rollback。
2. 從表快照取得各表 Rows Read。
3. 計算 RowsPerTX = Rows Read / 總事務數。

判讀：

• OLTP 表 RowsPerTX 應在 1-20 之間。
• 成百上千 → 全表掃描 → 檢查索引、RUNSTATS、叢集。
• Overflows 大 → 列遷移嚴重 → REORG。

第 3 招：表空間效能剖析——I/O 特徵的顯微鏡

db2 "GET SNAPSHOT FOR TABLESPACES ON <dbname>"

每個表空間必答五題：

1. 平均讀取時間 (ms) 多少？
2. 平均寫入時間 (ms) 多少？
3. 異步預取 / 同步隨機 I/O 佔比？
4. 緩衝池命中率？
5. 每事務物理/邏輯讀頁數？

跨表空間比較：找出讀寫最慢者 → 檢查容器磁碟速度、容器大小均衡、預取大小設定、存取模式與預期是否一致（隨機讀表應有高命中率、低物理 I/O）。

第 2 招：多緩衝池架構——記憶體分配的藝術

常見錯誤：機器 8GB 卻只有一個 16MB IBMDEFAULTBP。

標準四池起步：

進階：熱點表獨享緩衝池（BP_HOT）、索引獨享（BP_IX）。

調優科學法：逐步增大 → 觀察命中率、同步讀、物理 I/O 率 → 至邊際收益遞減點 → 定期重評（業務增長）。

第 1 招：SQL 成本分析——萬惡之源與終極戰場

一條爛 SQL 可摧毀完美調優的資料庫。DBA 常被綁手綁腳：不能改 SQL（廠商封裝）、只能加索引、改叢集、改統計。

真實成本 = 單次執行成本 × 執行頻率。高頻低成本 SQL 總成本常超過低頻高成本 SQL。

標準分析流程（SQL Event Monitor）

1. CREATE EVENT MONITOR SQLCOST FOR STATEMENTS WRITE TO FILE '...'
2. SET EVENT MONITOR SQLCOST STATE 1
3. 讓應用跑代表性負載（30-60 分鐘）。
4. SET EVENT MONITOR SQLCOST STATE 0
5. db2evmon -db <db> -evm SQLCOST > sqltrace.txt
6. 人工/工具分析 sqltrace.txt：聚合唯一 SQL、頻率、總 CPU/排序/IO 成本。

縮小搜尋範圍的三大捷徑

1. 結合 技巧 4：高 RowsPerTX 表 → 在 Event Monitor 輸出搜尋該表名。
2. 結合 技巧 3：高異步讀/物理 I/O 表空間 → 找屬於該表空間的表 → 搜尋 SQL。
3. 全量 EXPLAIN：對應用所有靜態/動態 SQL 跑 Explain，但耗時極長。

工具加速

人工分析 30 分鐘負載可能耗週。商業工具（如 SQL-GUY）可自動化識別高成本 SQL、聚合頻率與資源消耗，將分析時間壓縮至分鐘級。

持續調優：沒有終點的旅程

• 剔除高成本 SQL 後，必須重新評估所有配置與物理設計（緩衝池、排序堆、鎖清單、表空間容器、分區策略）。
• 電商業務持續變化，即使調優完美，也需定期微調。
• DBA 的工作永遠做不完——這正是價值所在。

快速回顧：十大技巧清單

1. 足夠代理程式，零等待、零偷取。
2. MAXFILOP 夠大，零檔案開關。
3. LOCKTIMEOUT 短（15s），鎖清單 50% 以下。
4. 臨時表空間 ≥3 容器，預取 = 區塊 × 容器數。
5. 排序溢出率 < 3%，每事務排序 < 5，優化 SQL 而非加 SORTHEAP。
6. RowsPerTX 1-20，超標即掃描 → 索引/RUNSTATS。
7. 表空間剖析：找最慢、最不命中、屬性不符者優化。
8. 多緩衝池，按存取模式分池，科學調大至邊際點。
9. SQL Event Monitor 識別真實成本最高 SQL → 索引/叢集/統計/改寫。
10. 調優不止，業務不停，持續微調。