監控 DB2 活動之使用解釋工具分析SQL

什麼是解釋工具?

　　將一條 SQL 語句提交給 DB2 數據庫引擎進行處理時，DB2 Optimizer 會對其加以分析，以生成所謂的訪問計劃。各訪問計劃包括將用於執行該語句的策略的詳細信息(例如是否使用索引;若有排序方法，需要怎樣的排序方法等)。如果該 SQL 語句是在一個應用程序中編寫的，則訪問計劃生成於預編譯時(若使用了延時綁定，則在綁定時生成)，另外還會生成一個可執行形式的訪問計劃，它作為稱為 “包” 的對象存儲在系統目錄中。但若語句是通過 Command Line Processor 提交的，或者語句是應用程序中的一條動態 SQL 語句(也就是說，這是一條在應用程序運行時構造的 SQL 語句)，則訪問計劃將在該語句發出時生成，而所生成的可執行形式則臨時地存儲在內存中(位於全局包緩沖區中)，而不是系統目錄。(若發出了一條 SQL 語句，而全局包緩沖區中已有其可執行形式的訪問計劃，則已有訪問計劃將被重用，不會再次調用 DB2 Optimizer。)

　　為什麼說這非常重要?原因在於，盡管可以使用數據庫系統監控器和健康監控器來獲取關於某些 SQL 操作執行的情況有多好(或多糟)的信息，但不能用這些監控器來分析單獨的 SQL 語句。要執行此類分析，您必須能夠捕獲並查看存儲於 SQL 語句的訪問計劃中的信息。而為了捕獲並查看訪問計劃信息，您必須使用 DB2 9 解釋工具。

　　使用解釋工具，您可以捕獲並查看為特定 SQL 語句選擇的訪問計劃的具體信息，還有可用於幫助確定編寫不良的語句或數據庫中弱點的性能信息。特別地，解釋數據將幫助您了解 DB2 Database Manager 如何為滿足查詢而訪問表和索引。解釋數據還可用於評估采取的任何性能調優行動。實際上，只要您更改了 DB2 Database Manager 的某些方面、SQL 語句或與語句交互的數據庫，都應收集並檢查解釋數據，弄清楚您的更改對性能產生了怎樣的效果(如果有效果的話)。

　　解釋表

　　必須首先創建一組特殊的表，即解釋表，之後才能捕獲解釋信息。表 4 列出了所用的各解釋表以及各表設計用於容納的信息。

表 4. 解釋表
表名 內容 EXPLAIN_ARGUMENT 包含所用各獨立操作符的獨特特征（如果存在的話）。 EXPLAIN_INSTANCE 包含所解釋的 SQL 語句的源的基本信息，還有關於進行解釋的環境的信息。（EXPLAIN_INSTANCE 表是所有解釋信息的主要控制表。其他解釋表中的各行數據顯式地鏈接到該表中的各行。） EXPLAIN_OBJECT 包含關於為 SQL 語句生成的訪問計劃所需的數據對象的信息。 EXPLAIN_OperaTOR 包含 SQL 編譯器為滿足 SQL 語句而需的所有操作符。 EXPLAIN_PREDICATE 包含確定特定操作符應用哪些謂詞的相關信息。 EXPLAIN_STATEMENT 包含在得到不同級別的解釋信息時存在的 SQL 語句文本。用戶輸入的原始 SQL 語句存儲在該表中，另外還有 DB2 Optimizer 用於選擇滿足 SQL 語句的訪問計劃的版本。（後一種版本可能與原始版本的語句略有差異，因為 SQL Precompiler 可能已通過額外的謂詞重寫和/或增強了該語句。） EXPLAIN_STREAM 包含關於各單獨操作符和數據對象之間存在的輸入輸出數據流的信息。（數據對象本身顯示於 EXPLAIN_OBJECT 表中，而數據流中涉及的操作符可在 EXPLAIN_OperaTOR 表中找到。）
　　典型情況下，解釋表用於數據庫開發之中，協助應用程序數據，但不會在應用程序代碼較為穩定的生產數據庫中。出於這方面的原因，它們不會隨系統目錄表一起作為數據庫創建過程的一部分而創建。相反，解釋表必須在要應用解釋工具的數據庫中手動創建，之後才能使用解釋工具。幸運的是，使用 Command Line Processor 創建解釋表的流程相當簡單，您只要建立一個到恰當數據庫的連接，並執行名為 EXPLAIN.DDL 的腳本即可，可在 DB2 9 軟件最初安裝的 “sqllib” 目錄下的 “misc” 子目錄中找到此腳本。(此文件頭部的注釋提供了執行方法信息。)

　　收集解釋數據

　　解釋工具由多個單獨的工具組成，而並非所有的工具需要的都是相同類型的解釋數據。因此，可收集兩種不同類型的解釋數據：

• 　　全面解釋數據(Comprehensive explain data)。包含關於一條 SQL 語句的訪問計劃的詳細信息。此信息跨多個不同的解釋表存儲。
• 　　解釋快照數據(Explain snapshot data)。包含一條 SQL 語句的當前內部表示以及所有相關信息。此信息存儲在 EXPLAIN_STATEMENT 解釋表的 SNAPSHOT 列中。

　　您或許已經想到，有多種收集這兩種解釋數據的方法。收集解釋數據的可行方法包括：

• 　　執行 EXPLAIN SQL 語句
• 　　設置 CURRENT EXPLAIN MODE 特殊寄存器
• 　　設置 CURRENT EXPLAIN SNAPSHOT 特殊寄存器
• 　　在 PRECOMPILE 或 BIND 命令中使用 EXPLAIN 綁定選項
• 　　在 PRECOMPILE 或 BIND 命令中使用 EXPLSNAP 綁定選項

　　EXPLAIN SQL 語句

　　為單獨一條動態 SQL 語句收集全面解釋信息和解釋快照數據的方法之一就是執行 EXPLAIN SQL 語句。該語句的基本語法是：

　　EXPLAIN [ALL | PLAN | PLAN SELECTION]
　　＜FOR SNAPSHOT | WITH SNAPSHOT＞
　　FOR [SQLStatement]

　　其中，SQLStatement 表示要為其收集解釋數據和/或解釋快照數據的 SQL 語句。(指定的語句必須是一條有效的 INSERT、UPDATE、DELETE、SELECT、SELECT INTO、VALUES 或 VALUES INTO SQL 語句。)

　　若在 EXPLAIN 語句中指定了 FOR SNAPSHOT 選項，則僅為指定動態 SQL 語句收集解釋快照信息。另一方面，若指定的是 WITH SNAPSHOT 選項，則指定動態 SQL 語句的全面解釋信息和解釋快照數據均會被收集。但若未使用任一選項，則僅收集全面解釋數據，而不會產生任何解釋快照數據。

　　要為 SQL 語句 SELECT * FROM DEPARTMENT 收集全面解釋數據和解釋快照信息，可像下面這樣執行 EXPLAIN 語句：

　　EXPLAIN ALL WITH SNAPSHOT FOR SELECT * FROM DEPARTMENT

　　另一方面，如果希望僅為這條 SQL 語句收集解釋快照數據，可像下面這樣執行 EXPLAIN 語句：

　　EXPLAIN ALL FOR SNAPSHOT FOR SELECT * FROM DEPARTMENT

　　最後，如果僅要為 SQL 語句 SELECT * FROM DEPARTMENT 收集全面解釋數據，像下面這樣執行 EXPLAIN 語句：

　　EXPLAIN ALL FOR SELECT * FROM DEPARTMENT

　　務必注意，EXPLAIN 語句未執行指定 SQL 語句，也未顯示所收集的解釋信息。要查看收集到的信息，必須使用其他解釋工具(下文中將介紹這樣的工具)。

　　CURRENT EXPLAIN MODE 和 CURRENT EXPLAIN SNAPSHOT 特殊寄存器

　　在您希望為單獨一條動態 SQL 語句收集解釋和/或解釋快照信息時，EXPLAIN SQL 語句非常有用，盡管如此，若有大量 SQL 語句需要分析，使用這種方法將極為耗時。要為多條動態 SQL 語句收集相同的信息，一種更好的方法就是在執行一組動態 SQL 語句之前，設置所提供的一種或兩種特殊解釋工具寄存器。隨後，語句准備好執行時，即可為所處理的各條語句收集解釋和/或解釋快照信息。(但在解釋和/或解釋快照信息收集完畢後，語句本身可能執行，也可能不會執行。)

　　以這種方式使用的兩個解釋工具特殊寄存器就是 CURRENT EXPLAIN MODE 特殊寄存器和 CURRENT EXPLAIN SNAPSHOT 特殊寄存器。CURRENT EXPLAIN MODE 特殊寄存器是使用 SET CURRENT EXPLAIN MODE SQL 語句設置的，CURRENT EXPLAIN SNAPSHOT 特殊寄存器是使用 SET CURRENT EXPLAIN SNAPSHOT SQL 語句設置的。SET CURRENT EXPLAIN MODE SQL 語句的基本語法是：

　　SET CURRENT EXPLAIN MODE <=>
　　[NO |
　　YES |
　　EXPLAIN |
　　REOPT |
　　RECOMMEND INDEXES |
　　EVALUATE INDEXES |
　　RECOMMEND PARTITIONINGS |
　　EVALUATE PARTITIONINGS]

　　SET CURRENT EXPLAIN SNAPSHOT SQL 語句的基本語法是：

　　SET CURRENT EXPLAIN SNAPSHOT <=> [YES | NO | EXPLAIN | REOPT]

　　可想而知，若 CURRENT EXPLAIN MODE 和 CURRENT EXPLAIN SNAPSHOT 特殊寄存器均設置為 NO，解釋工具即被禁用，不會捕獲任何解釋數據。另一方面，若任一特殊寄存器被設置為 EXPLAIN，則相應的解釋工具即被激活，每當一條動態 SQL 語句准備好執行時，就會為其收集全面解釋信息或解釋快照數據(若兩個特殊寄存器均這樣設置，則同時收集這兩種信息)。但語句本身並不執行。若任一特殊寄存器被設置為 YES，行為與任一寄存器設置為 EXPLAIN 時基本相同，但存在一個重要的差異：為其收集解釋信息的動態 SQL 語句會在適當的解釋/解釋快照數據收集完備後立即執行。

　　若 CURRENT EXPLAIN MODE 或 CURRENT EXPLAIN SNAPSHOT 特殊寄存器被設置為 REOPT，解釋工具將被激活，只要一條靜態或動態 SQL 語句在執行時的語句再優化過程中得到處理 —— 也就是說，在該語句中所用的主變量、特殊寄存器或參數標記的實際值可用時，解釋信息或解釋快照數據(若兩個特殊寄存器均按此設置，則同時收集這兩種信息)就會被捕獲。

　　EXPLAIN 和 EXPLSNAP 預編譯/綁定選項

　　至此，您已經看到了為動態 SQL 語句收集全面解釋信息和解釋快照數據的一些方法。但在很多時候，數據庫應用程序由靜態 SQL 語句構成，這些語句同樣需要分析。那麼如何使用解釋工具來分析在嵌入式 SQL 應用程序中編寫的靜態 SQL 語句呢?要為在嵌入式 SQL 應用程序中編寫的靜態和/或動態 SQL 語句收集全面解釋信息和/或解釋快照數據，可以依靠 EXPLAIN 和 EXPLSNAP 預編譯/綁定選項。

　　如您所料，EXPLAIN 預編譯/綁定選項用於控制是否為在嵌入式 SQL 應用程序中編寫的靜態和/或動態 SQL 語句收集全面解釋數據。類似地，EXPLSNAP 預編譯/綁定選項用於控制是否為在嵌入式 SQL 應用程序中編寫的靜態和/或動態 SQL 語句收集解釋快照數據。可在用於預編譯包含嵌入式 SQL 語句的源代碼文件的 PRECOMPILE 命令中指定這兩個選項或其中之一。若使用了延遲綁定，那麼可以在將應用程序的綁定文件綁定到數據庫的 BIND 命令中提供這些選項。

　　可為 EXPLAIN 選項和 EXPLSNAP 選項指派的值包括 NO、YES、ALL 或 REOPT。如果兩個選項均被指派以 NO 值(例如，EXPLAIN NO EXPLSNAP NO)，解釋工具將被禁用，不捕獲任何解釋數據。另一方面，若任一選項被指派以 YES 值，解釋工具將被激活，為應用程序中發現的每一條靜態 SQL 語句收集全面解釋信息或解釋快照數據(若兩個選項均按此設置，則同時收集這兩種信息)。若任一選項被指派以 ALL 值，解釋工具將被激活，並且為找到每一條靜態 SQL 語句和每一條動態 SQL 語句收集全面解釋信息或解釋快照數據(若兩個選項均按此設置，則同時收集這兩種信息)，即便是 CURRENT EXPLAIN MODE 和/或 CURRENT EXPLAIN SNAPSHOT 特殊寄存器已設置為 NO。

　　若 EXPLAIN 或 EXPLSNAP 選項被指派以 REOPT 值，則各可再優化的遞增綁定 SQL 語句的全面解釋信息或解釋快照數據(若兩個選項均按此設置，則同時收集這兩種信息)將在運行時放入解釋表，即便是 CURRENT EXPLAIN MODE 和/或 CURRENT EXPLAIN SNAPSHOT 特殊寄存器已設置為 NO。

　　評估解釋數據

　　至此為止，您一直將精力集中在收集全面解釋數據和解釋快照數據的不同方法上。但收集完數據後，如何查看這些數據?為了回答這個問題，您需要先看看專門設計用於以一種有意義的格式呈現解釋信息的解釋工具。其中包括：

• 　　db2expln
• 　　db2exfmt
• 　　Visual Explain
• 　　db2expln

　　之前您已經了解到，在包含嵌入式 SQL 語句的源代碼文件綁定到數據庫時(無論是作為預編譯流程的一部分還是在延遲綁定過程中)，DB2 Optimizer 將分析遇到的每一條靜態 SQL 語句，並生成一個相應的訪問計劃，此訪問計劃隨後以包的形式存儲在數據庫中。給定數據庫名稱、包名稱、包創建者 ID、部分號(若指定了部分號 0，則處理包的所有部分)，db2expln 工具即可為存儲在數據庫系統目錄中的任何包解釋並說明其訪問計劃。由於 db2expln 工具直接處理包而非全面解釋數據或解釋快照數據，因而通常用來獲取那些已選定用於未捕獲其解釋數據的包的訪問計劃的相關信息。但由於 db2expln 工具僅可訪問已存儲在包中的信息，因而只能說明所選的最終訪問計劃的實現，不能提供特定 SQL 語句優化方式的信息。

　　若使用額外的輸入參數，db2expln 工具還可用於解釋動態 SQL 語句(不包含參數標記的動態 SQL 語句)。

　　db2exfmt

　　與 db2expln 工具不同，db2exfmt 工具設計用於直接處理已收集並存儲在解釋表中的全面解釋數據或解釋快照數據。給定數據庫名和其他限定信息，db2exfmt 工具將在解釋表中查詢信息、格式化結果，並生成一份基於文本的報告，此報告可直接顯示在終端上或寫入 ASCII 文件。

　　Visual Explain

　　Visual Explain 是一種 GUI 工具，它為數據庫管理員和應用程序開發人員提供了查看為特定 SQL 語句選擇的訪問計劃的圖形化表示的能力。Visual Explain 允許您完成以下任務：

• 　　查看用於優化 SQL 語句的數據庫統計數據。
• 　　確定是否使用索引來訪問表數據。(若未使用索引，Visual Explain 可幫助您確定哪些列可受益於索引。)
• 　　允許您進行 “前” “後” 對比，從而查看性能調優的效果。
• 　　獲得訪問計劃執行的各操作的詳細信息，包括各操作的預計成本。

　　但 Visual Explain 只能用於查看解釋快照數據，要查看已收集並寫入了解釋表的全面解釋數據，則必須使用 db2exfmt 工具。

　　如您所見，可用於顯示全面解釋數據和解釋快照的不同工具有著很大的差異，無論是在復雜性方面還是在功能方面。表 5 總結了幾種可用工具，並強調了各工具的特征。要使解釋工具發揮出最好的效果，您應在選擇工具時考慮您的環境和需求。

表 5. 可用解釋工具的比較
所需特征 Visual Explain db2exfmt db2expln 用戶界面 圖形化 基於文本 基於文本 “快速但粗略的” 靜態 SQL 分析 否 否 是 靜態 SQL 支持 是 是 是 動態 SQL 支持 是 是 是 CLI 應用程序支持 是 是 否 詳細的 DB2 Optimizer 信息可用 是 是 否 適於分析多條 SQL 語句 否 是 是