DB2 併發連線效能考量與實戰案例：查詢優化 43 倍、插入優化 11 倍提升

併發連線模型與效能拐點

• 連線數少時代理池充足：每請求即時獲協調代理，效能穩定
• 連線集中器啟用 (MAX_CONNECTIONS > MAX_COORDAGENTS) 且連線超過協調代理上限：請求排隊等待，部分連線效能顯著下降
• 對策：啟用 SMP (節點內平行) 或 MPP (多分區) 增加 I/O 與 CPU 平行度

案例一：查詢優化 — 從 653 秒降至 15 秒 (43 倍提升)

初始環境

• SMS 表空間、無索引、SORTHEAP 過小
• 三表關聯查詢含 SUBSTR 函數比對與 ORDER BY

優化步驟與效能貢獻

1. 改用 DMS 表空間：基礎效能改善
2. 建立關鍵索引：

   CREATE UNIQUE INDEX INDEX_C12 ON T1_DMS (C12 ASC);
   CREATE UNIQUE INDEX INDEX_C22 ON T2_DMS (C22 ASC);
   CREATE UNIQUE INDEX INDEX_C32 ON T3_DMS (C32 ASC);

   效能大幅提升
3. 調大 SORTHEAP：解決排序溢出
4. 執行 RUNSTATS：更新統計資訊
5. 調整優化等級：選擇適當 DFT_QUERYOPT
6. 增加冗余欄位 (空間換時間) — 最大貢獻：

   ALTER TABLE T1 ADD C12_Red VARCHAR(10);
   ALTER TABLE T2 ADD C21_Red VARCHAR(10);
   UPDATE T1 SET C12_Red = SUBSTR(C12,1,10);
   UPDATE T2 SET C21_Red = SUBSTR(C21,1,10);

   查詢改為 WHERE C12_Red = C21_Red AND C22 = C32，消除函數運算，效能峰值

案例二：插入優化 — 從 68 秒降至 6 秒 (11 倍提升)

初始環境

• 表含索引、CHECK 約束、APPEND OFF
• 插入 4 萬筆測試資料

優化步驟 (依貢獻排序)

1. 刪除索引
2. 刪除約束
3. 合併多列單一 INSERT (INSERT ... VALUES (...),(...),(...))
4. 啟用 APPEND ON 模式
5. 屏蔽表日誌 (ACTIVATE NOT LOGGED INITIALLY)
6. 採用平行寫入
7. 採用嚴格隔離等級 (降低鎖開銷)

調優避坑指南

• 緩衝池過大導致無法連線：透過 DB2_OVERRIDE_BPF 修正，建議啟用 STMM 自動管理
• 忽視 RUNSTATS/REORG：多數「I/O 瓶頸」實為統計過期導致優化器錯誤計劃，跑完即解
• 載入資料前未檢查索引/外鍵：實例中 1 PK + 4 FK 導入 1000 筆耗 5 分鐘，刪除後僅需秒級
• 單次只調一個參數：多參數同調無法歸因，下次仍需重來
• 索引雙面刃：查詢加速但拖慢寫入，需綜合評估
• XML 大頁面優勢：跨頁儲存特性下，大頁面 (16K/32K) 減少跨頁查詢
• 表分區實戰：每日 30 萬寫入 + 刪 5 天前資料 → 6 個分區對應 6 天，查詢/插入僅掃單分區，首次查詢無需重建索引
• CHNGPGS_THRESH：緩衝池髒頁閾值過大導致建立索引時 I/O 爆發，適度調小分攤寫入
• db2diag.log 過大：導致所有操作緩慢甚至掛起，定期清理，DIAGLEVEL=3 通常足夠