DB2 9 查詢優化新特性深度解析：優化剖面與統計視圖

優化挑戰與新特性概覽

DB2 基於成本的優化器決策受系統配置、模式、註冊表變量、優化級別、統計信息等多重因素影響。數據動態性使最佳計劃選擇極其複雜。DB2 9 引入優化剖面和統計視圖兩大增強，分別從「直接干預計劃」和「提升基數估算精度」兩個維度解決疑難優化問題。

優化剖面：向優化器發送具體指導

適用場景

大多數查詢已調優良好，但少數 SQL 盡力調整索引、統計、參數後仍不達標，且不願/不能修改應用代碼。優化剖面提供非侵入式干預手段。

工作機制

將目標查詢及指導規則封裝為符合 XML Schema 的優化剖面文檔，包含全局規則（OPTPROFILE 區段）和語句級規則（STATEMENT PROFILE 區段，按 STMTKEY 匹配）。激活剖面後，所有 SQL 嘗試匹配活動剖面，匹配高效低開銷。指導規則覆蓋默認成本模型決策。

激活方式（需設置 DB2_OPTPROFILE=YES）

1. CLP/應用嵌入：SET CURRENT OPTIMIZATION PROFILE = KCHEN.PROF1（會話級）。
2. CLI/舊版 JDBC：db2cli.ini 中 CURRENTOPTIMIZATIONPROFILE=KCHEN.PROF1。
3. JCC Universal Driver：應用代碼嵌入 SET CURRENT OPTIMIZATION PROFILE。
4. SQL PL 過程：CALL SYSPROC.SET_ROUTINE_OPTS('OPTPROFILE KCHEN.PROF1')（V8 FP13+/V9 FP1+）。
5. C/C++ 嵌入式 SQL：PREP 生成綁定文件，BIND OPTPROFILE KCHEN.PROF。
6. SQLJ 靜態 SQL：定制階段 -bindoptions "OPTPROFILE KCHEN.PROF1"，注意需包含隱式 DECLARE CURSOR 子句。

驗證：Explain 輸出會顯示剖面名稱與生效指南。無效指南返回 SQL0437W (rc=13)。修改 SYSTOOLS.OPT_PROFILE 表後需 FLUSH OPTIMIZATION PROFILE CACHE。

指導範例分類

統計視圖：解決基數估算偏差的根本之策

優化器的兩大假設與局限

1. 均勻分布假設：無分布統計時，假設列值均勻。數據傾斜（如少數值佔多數行）會導致嚴重誤估。即使有分布統計，複雜謂詞組合仍難精準。
2. 域值包含假設：連接 T1.C1 = T2.C1 時，假設一方值域包含另一方。實際數據常不滿足（如 T2 有 T1 無的值），導致連接基數高估，錯誤隨連接鏈遞增放大。

統計視圖原理

創建視圖物化關鍵連接/謂詞結果，收集其完整統計（含分布），優化器利用這些「真實」統計替代假設推算，實現精準基數估算。步驟：

CREATE VIEW SCHEMA.V1 AS SELECT * FROM T1, T2 WHERE T1.C1 = T2.C1;
ALTER VIEW SCHEMA.V1 ENABLE QUERY OPTIMIZATION;
RUNSTATS ON TABLE SCHEMA.V1 WITH DISTRIBUTION;

• ENABLE QUERY OPTIMIZATION：標記視圖統計參與優化。
• RUNSTATS 必須含分布信息，必要時用列組統計。
• 視圖統計包含結果列 COLCARD、基數、值計數等完整信息，無推斷成分。
• 優化器自動匹配符合條件的查詢（無需直接引用視圖），用視圖統計修正選擇率/基數估算，產生更優計劃。

實戰效果演示

連接 T1.C1=T2.C1 加本地謂詞 T1.C2='B'，無統計視圖時基數估算為 1，實際 4 行；啟用統計視圖後優化器獲知真實分布，估算修正為 4，避免錯誤計劃（如錯選 NLJ 而非 HJ）。DPF 與非 DPF 環境均有效。

總結

優化剖面提供「外科手術式」計劃干預，統計視圖從根源修正成本模型輸入。兩者互補：剖面解決個別頑固語句，統計視圖系統性提升整類查詢優化質量。實施前務必透過 Explain 定位問題根因，避免盲目應用。