db2look 重現優化器存取計劃實戰 (四)：統計資訊、優化級別與配置參數的三重對齊法

前言：為什麼測試環境跑不出生產環境的計劃？

SQL 語句相同、表結構相同、索引相同，甚至資料量相似，但 EXPLAIN 出來的存取計劃卻截然不同。根因通常隱藏在三大維度的不一致：統計資訊、優化級別、配置參數。本文實戰演示如何用 db2look 精準複製這三重屬性，實現測試環境「像素級」重現生產計劃。

核心工具鏈

• db2look -m：提取統計資訊 (UPDATE 陳述式)
• db2look -e -a -t <table>：提取 DDL (含索引、約束)
• db2look -l：提取表空間/容器/緩衝池佈局
• db2exfmt：格式化 EXPLAIN 輸出
• UPDATE DB CFG：同步優化級別等關鍵參數

場景一：優化級別不一致導致連接算子差異

生產環境計劃 (Hash Join)

Access Plan:
Total Cost: 25.8843
  HSJOIN (Hash Join)
   /    \
TBSCAN  TBSCAN

測試環境計劃 (Merge Join) —— 成本微異

Access Plan:
Total Cost: 25.8843
  MSJOIN (Merge Scan Join)
   /    \
TBSCAN  TBSCAN

根因排查：db2exfmt 輸出配置段對比

修復動作

UPDATE DB CFG FOR SAMPLE USING DFT_QUERYOPT 5
db2 terminate
db2 connect to sample
# 重新 EXPLAIN → 獲得相同 Hash Join 計劃

場景二：統計資訊未同步導致存取路徑偏移 (db2look -m 關鍵性)

生產環境建立索引並收集完整統計

db2 "connect to sample"
db2 "create index name_ind on staff (name,id)"
db2 "runstats on table skapoor.staff with distribution and indexes all"
db2 "set current explain mode explain"
db2 "select name from staff where id=10 order by name"
db2 "set current explain mode no"

生產計劃：IXSCAN (索引掃描)，成本 0.11

Access Plan:
Total Cost: 0.111065
  IXSCAN
   |
INDEX: SKAPOOR.NAME_IND

統計資訊提取與部署

# 1. 提取儲存佈局 (表空間、容器、緩衝池)
db2look -d sample -l -o storage.out

# 2. 提取 DDL + 統計資訊 (關鍵 -m 參數)
db2look -d sample -e -a -m -t STAFF -o db2look.out
# -m 產生 RUNSTATS 等價的 UPDATE 陳述式，直接套用至測試庫即可複製統計分佈

測試環境部署順序

1. 執行 storage.out 建立同構儲存佈局
2. 執行 db2look.out 建立表、索引、套用統計資訊
3. 驗證 SYSCAT.INDEXES、SYSCAT.COLUMNS、SYSCAT.COLDIST 統計欄位一致
4. 重新 EXPLAIN → 驗證計劃一致

場景三：綜合參數對齊檢查清單 (生產→測試標準化遷移單)

db2exfmt 專業輸出指令

db2exfmt -d DUMMYDB -g TIC -w -1 -n % -s % -# 0 -o test_dummydb_exfmt.txt
# -g TIC: 圖形化計劃
# -w -1: 寬度自適應
# -n % -s %: 所有節點/所有節
# -# 0: 所有查詢

進階技巧：統計資訊「無損遷移」三板斧

1. db2look -m 生成的 UPDATE 陳述式直接執行：繞過 RUNSTATS 掃描，秒級完成，統計值位元級一致
2. 手動同步 COLGROUPCOLS / COLGROUPDIST：多欄位相關性統計 (V9.7+)，db2look -m 同步包含
3. 驗證查詢：

   
   SELECT TABNAME, COLNAME, COLCARD, NUMNULLS, AVGCOLLEN, HIGH2KEY, LOW2KEY
   FROM SYSCAT.COLUMNS WHERE TABSCHEMA='SKAPOOR' AND TABNAME='STAFF';
   SELECT * FROM SYSCAT.COLDIST WHERE TABSCHEMA='SKAPOOR' AND TABNAME='STAFF';
   

常見陷阱與避坑

• ❌ 僅同步 DDL、忽略 -m 統計 → 計劃偏移 90%+ 機率
• ❌ 忽略 DFT_QUERYOPT、DB2_WORKLOAD、DB2_SQLROUTINE_PREPOPTS 等註冊表變數
• ❌ 測試機 CPU/記憶體遠超生產 → 優化器選擇更激進並行計劃 (需用 CPUSPEED 校準或限制資源)
• ✅ 建立「計劃重現檢查清單」納入交付驗收標準

結語：重現計劃 = 重現決策上下文

優化器是確定性函數：Plan = f(SQL, Schema, Statistics, Config, Hardware)。db2look 提供了前四項的完整序列化能力。掌握 -m 統計遷移、-l 佈局遷移、配置參數逐項對齊 三板斧，配合 db2exfmt 可視化比對，即可在測試環境精準重現生產計劃，實現「上線零驚喜」的 SQL 質量保障體系。