簡介
DB2 for i5/OS on V5R4 提供了一些新方法來通過一些流行的動態 SQL 接口提高數據庫查詢的性能。SQL Call Level Interface (CLI) 為用戶提供了一個新的連接屬性，用於調優數據庫查詢使用的優化目標。用於 IBM® Developer Kit for Java 的 Java Database Connectivity (JDBC) 接口（也稱 Native JDBC）和 Toolbox 也提供了一個新的可控制查詢優化目標的連接屬性。那些熟悉 System i 和數據庫查詢性能分析和調優的人就會理解控制優化目標是優化性能的關鍵步驟。從 V5R4 開始，這些動態接口也可利用 System i 其他地方出現的那種直接程序性控制。本文主要討論用於設置優化目標的新的 CLI 屬性。
優化目標接口
在過去，執行 SQL 語句的應用程序可以選擇通過幾種機制來調優優化目標：
Query Options File (QAQQINI) -- 可以通過將 OPTIMIZATION_GOAL 選項指定為以下幾個值之一，來控制整個系統或一個特定連接（任務）的優化目標。由於缺省的優化目標值取決於使用的 SQL 接口，因此該選項沒有缺省的設置。對於用於每種接口的缺省值，請參考表 1。
*ALLIO
*FIRSTIO
表 1. 每種 SQL 接口的缺省優化目標
接口 缺省優化目標 CLI ALLIO Native JDBC 驅動程序 ALLIO Toolbox JDBC 驅動程序 FIRSTIO - 如果使用擴展的動態包，則缺省值為 All I/O iSerIEs Access for Windows Open Database Connectivity (ODBC)、Object Linking and Embedding (OLE) DB 和 .Net 客戶機 FIRSTIO - 如果使用擴展的動態包，則缺省值為 All I/O 嵌入式靜態 SQL FIRSTIO 嵌入式動態 SQL ALLIO QSQPRCED API FIRSTIO STRSQL 實用程序 FIRSTIO RUNSQLSTM 實用程序 ALLIO
OPTIMIZE FOR N ROWS 子句 -- 通過使用 OPTIMIZE FOR N ROWS 子句，可以直接將優化構建到 SQL 請求中。若 N 值較小，則可能導致查詢優化器使用 FIRSTIO 目標，反之，如果該值較大（例如 ALL ROWS），則會導致查詢優化器使用 ALLIO 目標。
CLI SQLSetConnectAttr() API 和 SQL_ATTR_QUERY_OPTIMIZE_GOAL 屬性
SQL_ALL_IO （缺省）
SQL_FIRST_IO
Toolbox JDBC 連接屬性 "query optimize goal" 和 DataSource 方法 setQueryOptimizeGoal()
0 = 使用缺省目標 *FIRSTIO，但是如果使用擴展的動態包，則使用目標 *ALLIO
1 = *FIRSTIO
2 = *ALLIO
Native JDBC 連接屬性 "query optimize goal" 和 DataSource 方法 setQueryOptimizeGoal()
0 = 使用缺省目標 *ALLIO
1 = *FIRSTIO
2 = *ALLIO
新的 CLI 和 JDBC 接口
新的 CLI 和 JDBC 選項提供了一種更程序性、在某些情況下也是更細粒度的方法來調優優化目標。由於新的 CLI 連接屬性的作用范圍是整個連接，因此它將影響在設置該屬性之後執行的所有 SQL 查詢。這對於以服務器模式運行很多線程的環境很有利，因為這種環境必要時可以在多個連接上使用不同的優化目標。此外，這也使得設置優化目標更加有助於動態調優策略。注意，這些 JDBC 和 CLI 接口只對到 i5/OS V5R4 或更高版本上運行的 System i 服務器的連接有影響。
本文的目的不是對優化目標的設置進行深入的討論，但是對此作一個簡要的描述可能對某些讀者有用。通過將優化目標指定為 FIRSTIO，應用程序可以迫使查詢更快地返回第一頁結果的輸出。若優化目標為 ALLIO，則可以以最短的時間完成整個查詢。為了直觀地理解這些選項，可以將 FIRSTIO 選項比作自行車，而將 ALLIO 選項比作飛機。如果是去很近的地方，那麼自行車也許是最好的選擇，因為基本上沒有啟動成本。但是，如果要去比較遠的地方，雖然啟動成本較高，飛機仍然是更好的解決方案。對距離的理解，或者說對查詢的輸出行為的理解，對於為不同查詢選擇最有效的選項是至關重要的。
本文的目的之一是執行一些查詢並測試兩種優化設置（FIRSTIO 和 ALLIO）的性能，從而來證明 CLI 環境中新設置的實際實現。可以肯定的是，iSerIEs Navigator 的 SQL Performance Monitor 特性對於測量查詢的啟動時間、取數據時間和總響應時間以及優化器的實現是一個有用的工具。我編寫了一個 CLI 程序，用於以任意一種優化目標設置來運行 SQL 查詢。程序中為新的連接屬性提供了 CLI 常量、SQL_FIRST_IO 和 SQL_ALL_IO。
清單 1. 控制優化目標的 CLI 示例代碼
attr = SQL_FIRST_IO;
rtnc = SQLSetConnectAttr(hdbc,SQL_ATTR_QUERY_OPTIMIZE_GOAL,&attr,0);
iSerIEs Navigator 的 SQL 性能工具
可以通過在 圖 1 所示的下拉菜單中選擇 Monitor > Start SQL Performance Monitor 任務，從 iSerIEs Navigator Run SQL scripts 界面中啟動 SQL Performance Monitor。然後，使用 CL 前綴調用包含 CLI 調用的 C 程序，以發出一個 i5/OS CALL 命令。
圖 1. Run SQL Scripts 界面
對於一個包含大約 1 GB 數據的數據庫，第一個測試的查詢返回一個只包含 12 行的記錄。如下面的圖 2 所示，當使用 Performance Monitor 時，界面中將通過比較特性可視化地並列顯示 FIRSTIO 和 ALLIO 的性能數字。
圖 2. SQL 性能監視器比較
圖 3 顯示了測試這兩種優化目標時 SQL Monitor 比較的輸出，其中包含時間方面的數字。在這種情況下，對於第一個查詢，與使用 FIRSTIO 優化目標相比，使用 ALLIO 時返回全部 12 行所需的時間明顯更短。有趣的是，使用 FIRSTIO 時的啟動時間比 ALLIO 更短，但由於取數據的時間方面的差別，在整體響應時間方面還是 ALLIO 占優。這是因為在使用 ALLIO 的情況下，優化器在啟動階段額外耗費了一些時間，以選擇能快速取到所有數據的計劃，而不是像 FIRSTIO 那樣只急於獲取前幾行數據。
圖 3. 對於查詢 1 的 SQL Monitor 比較輸出
測試的第二個查詢使用了一條將返回包含數千行數據的結果集的 SQL 語句。但是，在這個例子中，CLI 應用程序在初始處理階段只關心前 40 行。應用程序最終也會處理剩下的行，但要稍後才會處理。現實中的一個例子就是 Web 頁面搜索引擎。Web 頁面希望盡快顯示前面數條結果，雖然剩下的結果也可能要顯示出來，但是通常情況下用戶並不急於第一眼或者在第一個頁面上看到它們。從圖 4 中的性能結果可以看出，對於第二個查詢，在將前 40 行返回到應用程序的過程中，使用 FIRSTIO 目標構建的訪問計劃比使用 ALLIO 目標的訪問計劃所花的時間要少一半。
圖 4. 對於查詢 2 的 SQL Monitor 比較輸出
還可以使用 SQL Performance Monitors 的 Analyze 功能來調查研究對查詢性能有影響的設置。有兩種方法可以開始這樣的分析，一種方法是在 Run SQL Scripts 界面中，從圖 1 所示的 Monitor 下拉菜單中選擇 Analyze 任務；另一種方法是在 SQL Performance Monitors 視圖中（見圖 2），在 iSerIEs Navigator 樹中右鍵單擊一個監視器並選擇 Analyze 任務。 通過運行 Analyze 功能，可以得到 圖 5 所示的監視器總結。注意，圖 5 中突出顯示了 FIRSTIO 優化目標設置。
圖 5. 對優化設置的分析的輸出
使用 Visual Explain 進行優化分析
理解在計劃創建期間 DB2 for i5/OS 查詢優化器使用的優化目標的另一種方法是使用 Visual Explain 工具，該工具是 iSerIEs Navigator 的一部分。可以在一個監視器集合中，通過右鍵單擊一個監視器並選擇 Show Statements 任務，在 SQL 語句上應用 Visual Explain 工具。通過分析 圖 6 中對於第二個查詢的 Visual Explain 輸出，可以證實優化器將達到 FIRSTIO 的預期優化目標。
圖 6. Visual Explain 分析
iSerIEs Navigator DB2 Performance 工具集中新的 V5R4 分析和比較特性為研究查詢性能提供了新的、令人激動的方法。此外，CLI 和 JDBC 提供的附加功能允許對優化目標進行程序性控制，從而為應用程序提供了更大的威力。
