針對 pureXML 應用套用 DB2 優化準則：從基礎架構到 XML 專用指導方針實戰

優化準則：影響執行計劃的精準槓桿

DB2 成本基礎優化器雖強大，但在特定環境、查詢特徵與資料分布交互下，仍可能選擇次優計劃。優化準則讓使用者無需修改應用程式或資料庫配置，即可指導關鍵計劃屬性：表存取方法、索引選擇、聯結方法與順序。DB2 9.7 增強此基礎設施，新增 XML 專用準則 並擴展既有準則涵蓋 XML 運算子。

優化設定檔架構

優化設定檔 為 XML 文件，遵循 COPS (Current Optimization Profile Schema) 規範，包含兩類準則：

• 全域準則：對所有陳述式生效（如 `` 強制每次執行重編譯）。
• 陳述式層級準則：針對特定 SQL/XQuery，含 STMTKEY（精確匹配查詢文本，允許空白差異）與 OPTGUIDELINES（指導計劃片段）。

範例設定檔片段：

<OPTPROFILE VERSION="9.7.0.0">
  <OPTGUIDELINES><REOPT VALUE="ALWAYS"/></OPTGUIDELINES>
  <STMTPROFILE ID="Sample">
    <STMTKEY SCHEMA="TPOX"><![CDATA[SELECT * FROM security WHERE XMLEXISTS(...)]]></STMTKEY>
    <OPTGUIDELINES><XANDOR TABLE="SECURITY"/></OPTGUIDELINES>
  </STMTPROFILE>
</OPTPROFILE>

CDATA 區塊處理 XML 特殊字元（<、>）。

部署與啟用全流程

1. 建立 SYSTOOLS.OPT_PROFILE 表：CALL SYSINSTALLOBJECTS('opt_profiles', 'C', '', '') 或手動 DDL。
2. 載入設定檔：INSERT、IMPORT（含 LOB 檔案）或 LOAD。
3. 啟用設定檔：
   • 會話級：SET CURRENT OPTIMIZATION PROFILE = 'TPOX.PROFILE1'
   • 套裝級：`OPTPROFILE` BIND 選項（靜態 SQL 專用）
   • CLI：`CURRENTOPTIMIZATIONPROFILE` 客戶端配置
4. 執行工作負載，優化器自動匹配並套用準則。
5. 驗證：`db2exfmt` 輸出 Profile Information 區塊確認套用設定檔與陳述式設定檔名稱；若現警告 SQL0437W (Reason code 13) 則表示準則套用失敗，需檢視診斷資訊（如 EXP0009W 表引用無效）。

XML 專用準則深度解析

1. 表識別策略：公開名稱 vs 內部 TABID

• 公開名稱：查詢中顯式的表名/別名。``
• 內部 TABID：優化後 SQL 中的內部量化編號（如 Q1、Q2），從 `db2exfmt` 的 Optimized Statement 區塊取得。支援針對衍生表達式（如 XMLExists 謂詞轉換的內部表達式）指定存取路徑。準則中可混用 `TABLE` 與 `TABID` 屬性。

2. 核心 XML 準則

3. 現有準則的 XML 擴展

• 聯結準則（HSJOIN、NLJOIN、MSJOIN）：`FIRST`、`OUTER`/`INNER` 等屬性現支援 XML 運算子作為聯結輸入。
• 存取準則（INDEX、TABLE）：`INDEX` 可指定 XML 索引名。
• 全域準則：`REOPT`、`OPTIMIZATION LEVEL` 等同樣作用於 XML 查詢。

故障排除與最佳實踐

• STMTKEY 匹配失敗：確認大小寫、空白、結構完全一致；使用 `db2exfmt` 擷取實際優化後 SQL 對照。
• 表引用無效 (EXP0009W)：檢查 `TABLE`/`TABID` 是否與優化後計劃一致；優先使用 TABID 針對複雜 XML 查詢。
• 多設定檔衝突：同一查詢匹配多個 STMTPROFILE 時，套用首個；建議一查詢一設定檔，或以 Schema 隔離。
• 效能驗證：對比套用準則前後的 `db2exfmt` 計劃與實際執行時間，納入回歸測試。

結語

DB2 9.7 的 XML 優化準則基礎設施，賦予開發人員在不動應用碼的前提下，精準干預 XML 查詢執行計劃的能力。掌握設定檔結構、表識別技巧、XML 專用準則與驗證診斷流程，是釋放 pureXML 應用效能潛力的關鍵鑰匙。