DB2 優化剖面文件實戰：以 XML 指導執行計劃，突破成本優化器極限

為什麼需要優化剖面？

• 成本優化器依賴統計資訊，**統計缺失/過時/誤導**會導致錯誤計劃
• 特定業務模式（如資料傾斜、相關列）優化器模型難以捕捉
• Oracle Hint 機制需修改 SQL 文本，侵入性強、維護困難
• DB2 **優化剖面** 以外部 XML 文件指導優化器，**零 SQL 修改、靈活針對表/語句、支援多維度規則**

優化剖面 XML 結構速覽

<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.7.0.0">
  <STMTPROFILE ID="Sample">
    <STMTKEY SCHEMA="XQPENG">
      <![CDATA[select a.c3,b.c3 from t2 a,t1 b where a.c1=b.c1 and a.c1<100 and b.c2 >60]]>
    </STMTKEY>
    <OPTGUIDELINES>
      <HSJN>                      <!-- 指定 Hash Join -->
        <Access TABLEID='b'/>   <!-- t1 為小表建 Hash Table -->
        <Access TABLEID='a'/>   <!-- t2 為大表探查 -->
      </HSJN>
    </OPTGUIDELINES>
  </STMTPROFILE>
</OPTPROFILE>

四大類優化規則完整清單

部署五步驟

1. 建立工具表：

   CREATE TABLE SYSTOOLS.OPT_PROFILE (
     SCHEMA VARCHAR(128) NOT NULL,
     NAME VARCHAR(128) NOT NULL,
     PROFILE BLOB(2M) NOT NULL,
     PRIMARY KEY (SCHEMA, NAME)
   );

2. 準備資料來源檔 (OptProfile.del)：

   XQPENG,HSJOIN,HSJOIN.xml

3. 匯入工具表：

   IMPORT FROM OptProfile.del OF DEL MODIFIED BY LOBSINFILE
   INSERT INTO SYSTOOLS.OPT_PROFILE

4. 啟用資料庫配置：

   db2set DB2_OPTPROFILE=YES
   db2stop; db2start  # 生效

5. 會話級啟用剖面：

   SET CURRENT OPTIMIZATION PROFILE = XQPENG.HSJOIN

實戰案例：糾正 Hash Join Build/Probe 端反向

原始查詢：select t1.c3,t2.c3 from t2,t1 where t1.c1=t2.c1 and t1.c1<100 and t2.c2>60

優化器預設計劃：t2 (Build) → t1 (Probe)，總成本 804.126

業務實況：t1 符合列數遠少於 t2，應以 t1 Build Hash Table

剖面指導：<HSJN><Access TABLEID='b'/><Access TABLEID='a'/></HSJN> (b=t1, a=t2)

套用後計劃：t1 (Build) → t2 (Probe)，總成本 804.126 (成本相同但實際執行時間大幅下降)

快取管理與失效

# 清除特定剖面快取
FLUSH OPTIMIZATION PROFILE CACHE XQPENG.HSJOIN

# 清除所有剖面快取
FLUSH OPTIMIZATION PROFILE CACHE ALL

謹慎使用原則

• **最後手段**：索引、統計資訊、重寫 SQL、配置參數調優全部嘗試無效後再考慮
• **定期審查**：資料分佈變化可能導致固定計劃變差，建議季度審查
• **版本升級風險**：優化器內部邏輯變更可能導致剖面規則失效或產生副作用
• **匹配開銷**：每次編譯需匹配剖面，高並發 OLTP 應謹慎大規模部署

總結

優化剖面是 DB2 給資深 DBA 的「手術刀」：**外部化、非侵入式、多維度指導執行計劃**。掌握 XML 結構、四大類規則、部署流程與快取管理，配合「審慎使用、定期審查」原則，即可在成本優化器失效的邊界場景中精準接管控制權，實現確定性效能保障。