使用 db2look 重現優化器訪問計劃（下）：實戰演示從生產到測試的完整復現流程

實戰場景設定

生產環境基準計劃獲取

1. 收集關鍵配置

db2 get db cfg for sample > dbcfg_sample.out
# 關注：DBHEAP=600, STMTHEAP=2048, NUM_FREQVALUES=10, NUM_QUANTILES=20

2. 執行 runstats（含分佈統計）

db2 connect to sample
db2 runstats on table <schema>.org with distribution and indexes all
db2 runstats on table <schema>.sales with distribution and indexes all
db2 terminate

3. 建立 EXPLAIN 表並採集計劃

db2 connect to sample
db2 -tvf <install_path>/sqllib/misc/EXPLAIN.DDL
db2 terminate

# query.sql 內容
connect to sample
set current explain mode explain
select * from org a, staff b where a.deptnumb=b.dept and b.dept=15
set current explain mode no
terminate

db2 -tvf query.sql
db2exfmt -d SAMPLE -g TIC -w -1 -n % -s % -# 0 -o prod_sample_exfmt.txt

4. 基準計劃特徵

Access Plan:
Total Cost: 25.8823
Query Degree: 1
RETURN -> HSJOIN -> TBSCAN(ORG) + TBSCAN(STAFF)

測試環境復現步驟

1. 從生產收集三類資訊

db2look -d SAMPLE -l -o storage.out
db2look -d SAMPLE -f -fd -o config.out
db2look -d SAMPLE -e -a -m -t ORG SALES -o table.ddl

2. 修改腳本適配測試庫

• 將所有 CONNECT TO SAMPLE; 改為 CONNECT TO DUMMYDB;
• config.out 中將 dft_queryopt 5 改為 3（演示優化級別影響）

3. 執行腳本建立環境

db2 -tvf storage.out > storage_output.out
db2 -tvf config.out > config_output.out
db2 -tvf table.ddl > table.out

檢查輸出確認無誤，並同步修改 DBHEAP、STMTHEAP、NUM_FREQVALUES、NUM_QUANTILES 等配置。

4. 重啟實例並重建 EXPLAIN 表

db2stop && db2start
db2 connect to dummydb
db2 -tvf <install_path>/sqllib/misc/EXPLAIN.DDL
db2 terminate

5. 驗證計劃復現

修改 query.sql 連線目標為 DUMMYDB，執行並產出 db2exfmt 報表，對比 Total Cost、Access Plan 結構是否與生產一致。

關鍵成功因子總結

1. 統計信息完全一致：-m 選項導出的 UPDATE 統計語句是核心
2. 優化器環境參數對齊：含 db2fopt 虛擬緩衝池/排序堆、註冊表變量、優化級別
3. 表空間物理屬性對齊：PREFETCHSIZE、EXTENTSIZE、OVERHEAD、TRANSFERRATE
4. 分區拓撲邏輯一致：MPP 環境邏輯分區數必須相同

避坑提醒

• 若需測試計劃「執行效能」，仍需盡可能導入真實數據量
• 測試與生產硬體差異仍可能導致實際執行特性不一致，性能調優終究是「科學與藝術的結合」
• 同樣方法適用於 SQL0901N、實例崩潰等優化器層面問題的復現與測試