DB2 for i5/OS SQL 優化目標 FIRSTIO 與 ALLIO 實測解析

優化目標概述

DB2 for i5/OS V5R4 引入新連接屬性，允許動態 SQL 接口（CLI、Native JDBC、Toolbox JDBC）程式化控制查詢優化目標。優化目標決定優化器側重「首行響應速度」（FIRSTIO）還是「全表掃描總耗時」（ALLIO）。不同接口預設目標不同，如 Toolbox JDBC 預設 FIRSTIO，Native JDBC 預設 ALLIO。

新介面與程式化控制

• CLI：SQLSetConnectAttr(hdbc, SQL_ATTR_QUERY_OPTIMIZE_GOAL, &attr, 0)，屬性值 SQL_FIRST_IO / SQL_ALL_IO
• Native JDBC：連接屬性 query optimize goal 或 DataSource.setQueryOptimizeGoal()，0=預設(ALLIO)、1=FIRSTIO、2=ALLIO
• Toolbox JDBC：同樣支援上述屬性，預設值略有不同

新介面作用於連接層級，利於多線程服務器環境針對不同業務使用不同目標，也便於動態調優策略。

FIRSTIO 與 ALLIO 形象比喻

FIRSTIO 如騎自行車，啟動快、適合短途（只需前幾行）；ALLIO 如坐飛機，啟動慢但巡航快、適合長途（需全量數據）。理解查詢輸出行為是選擇關鍵。

實測場景與結果分析

測試環境：約 1 GB 數據庫，使用 iSeries Navigator SQL Performance Monitor 對比。

查詢 1：僅返回 12 行

• ALLIO 總響應時間顯著優於 FIRSTIO
• FIRSTIO 啟動較快，但取數據階段較慢
• ALLIO 在啟動階段多花時間選擇全量最優計劃，整體獲勝

查詢 2：結果集數千行，應用僅初期需前 40 行（類 Web 搜尋）

• FIRSTIO 將前 40 行返回時間縮減一半
• ALLIO 傾向全表掃描或大排序，導致首屏延遲

工具支援：Visual Explain 與 Analyze

• SQL Performance Monitor：可視化比較啟動時間、取數據時間、總響應時間
• Analyze 功能：自動識別優化目標設置、高亮顯示
• Visual Explain：圖形化驗證計劃確實遵循 FIRSTIO/ALLIO 預期

最佳實踐建議

1. OLTP 高併發、分頁查詢、首屏優先 → 設定 FIRSTIO
2. 批量報表、全量導出、數據整合 → 設定 ALLIO
3. 連接池環境：按業務類型建立不同連接池，各自綁定對應目標
4. 利用 OPTIMIZE FOR N ROWS 子句在語句級覆蓋連接級設置