Timeron 和 SQL 翻譯
Timeron
為了分析解釋信息，您需要了解的最重要的事情就是 timeron 的概念。Timeron 是 DB2 Optimizer 使用的一種度量單位，用于計量查詢完全執行所需的時間和資源數量。timeon 是時間、CPU 占用率、磁盤 I/O 和其他一些因素的綜合。由于這些參數的值不斷變化，執行一個查詢所需的 timeon 數量也是動態的，每次執行都有所不同。
Timeron 也是一種創造出來的度量單位，因此，沒有什么公式可以將執行一個查詢所需的 timeron 數轉換成秒數。除此之外，timeron 可以幫助您確定一種查詢執行途徑是否比另一種更快。(若執行一個查詢所需的 timeron 數在兩次編譯之間相差 10 或 20 timeron，不必擔心，這可能僅僅是由于 CPU 活動、磁盤活動或數據庫使用情況發生了變化。)
SQL 翻譯
對一個數據庫執行任何 SQL 語句之前，必須首先準備 SQL 語句。在此過程中，SQL 語句會簡化為代數語句，DB2 Optimizer 隨后可對此代數語句進行分析。這條代數語句就是所謂的查詢圖解模型，在整個優化過程中發揮作用。圖 6 展示了在執行 SQL 查詢之間，必要的優化及解析步驟。
圖 6. SQL 翻譯流程
優化流程的最終輸出是一個訪問計劃。訪問計劃是 DB2 用以執行 SQL 語句的途徑和步驟。這是由所有可用解釋工具顯示的信息。乍看上去，訪問計劃似乎非常復雜。但在具備了一定經驗之后，您會很快發現它們實際上非常容易閱讀和分析。
激活 Visual Explain
只要收集到了全面解釋和/或解釋快照數據，關于數據收集實現和方法的信息就會記錄在 EXPLAIN_INSTANCE 解釋表中。您可隨時通過 Explained Statement History 窗口查看此信息。要激活 Explained Statement History 窗口，可在 Control Center 中高亮顯示適當的數據庫，并在 Control Center 菜單中選擇 Selected > Show Explained Statement History 即可。圖 7 展示了在為一條 SQL 語句收集了解釋快照數據之后，Explained Statement History 窗口的外觀。
圖 7. Explained Statement History 窗口
一旦打開了 Explained Statement History 窗口，就可以使用 Visual Explain 來分析為任何記錄收集的解釋快照數據，這些數據的顯示方法是，高亮顯示一條記錄，并在 Explained Statement History 窗口的主菜單中選擇 Statement > Show Access Plan。圖 8 展示了以這種方法為以下查詢創建的 Access Plan 窗口(此查詢可在隨 DB2 提供的 SAMPLE 數據庫上執行)：
SELECT * FROM EMPLOYEE, DEPARTMENT WHERE WORKDEPT=DEPTNO
圖 8. Access Plan Graph 窗口
另一方面，還可為新查詢收集解釋快照數據，相應的訪問計劃可通過在 Explained Statement History 窗口的主菜單中選擇 Statement > Explain Query... 顯示出來。在選中這些菜單項時，Explain Query Statement 窗口將打開，并提示您為查詢輸入文本。圖 9 展示了以一個簡單的查詢填充后的 Explain Query Statement 窗口。
圖 9. Explain Query Statement 窗口
Access Plan Graph 窗口中展示的訪問計劃的每個組件都可供單擊，單擊后即可看到關于該組件的詳細信息。例如，若選中圖 8 所示的訪問計劃中的操作符，則 Operator details 窗口中將顯示如圖 10 所示的詳細信息。
圖 10. Operator details 窗口
在分析一個訪問計劃以定位性能瓶頸時，最好嘗試單擊所有不同的對象類型，以便充分了解已有的查詢信息。
Visual Explain 組件
您可能已經注意到，Access Plan 窗口中提供的輸出(參見圖 9)由層次化圖形構
成，表示處理為指定查詢選定的訪問計劃時所必需的不同組件。計劃中的各組件都顯示為一種稱為節點 的圖形對象。可存在兩種類型的節點：
操作符(Operator)。操作符節點用于確定是否必須在數據上執行一項活動，或者通過表或索引生成的輸出。
操作對象(Operand)。操作對象節點用于確定對其進行操作的實體(例如，表可以是一個表掃描操作符的操作對象)。
操作對象
典型情況下，操作對象節點用于確定表、索引和表隊列(表隊列用于使用了內部分區并行操作的情況下)，它們在層次圖中的符號分別是矩形(表)、菱形(索引)和平行四邊形(表隊列)。圖 11 給出了表和索引操作對象的示例。
圖 11. 表和索引操作對象
操作符
另一方面，操作符節點用于確定從插入操作到索引或表掃描的一切活動。操作符節點在層次圖中的符號為橢圓形，表示數據的訪問方法、表的連接方法，以及其他一些因素，例如是否要執行排序操作等。表 6 列出了訪問計劃層次圖中較為常見的操作符。
表 6. 常見 Visual Explain 操作符
| 操作符 |
所執行的操作 |
| CMPEXP |
計算表達式。（僅用于調試模式。） |
| DELETE |
從表中刪除行。 |
| EISCAN |
掃描用戶定義的索引，產生一系列簡化的行。 |
| FETCH |
使用指定的記錄標識符從表中獲取列。 |
| FILTER |
通過應用一個或多個謂詞過濾數據。 |
| GENROW |
生成一個行表。 |
| GRPBY |
按指定列或函數的公共值組織行，并對集合函數求值。 |
| HSJOIN |
顯示一個散列連接，其中一個或多個表在連接列上是混編的。 |
| INSERT |
向表中插入行。 |
| IXAND |
對兩個或多個索引掃描所得到的行標識符（RID）進行 AND 運算。 |
| IXSCAN |
使用可選的啟動/停止條件掃描表索引，產生有序的行流。 |
| MSJOIN |
顯示合并連接，其中外部和內部表必須按連接謂詞的順序排列。 |
| NLJOIN |
顯示嵌套循環連接，為外部表中的各行訪問內部表一次。 |
| PIPE |
翻譯行。（僅用于調試模式。） |
| RETURN |
將查詢返回的數據顯示給用戶。 |
| RIDSCN |
掃描一個行標識符（RID）列表，該列表是從一個或多個索引中獲得的。 |
| RPD |
遠程計劃的操作符。與 V8 中的 SHIP 操作符極為類似（之前版本中的 RQUERY 操作符），惟一的不同在于它不包含 SQL 或 XQuery 語句。 |
| SHIP |
從遠程數據庫源中檢索數據。在聯合系統中使用。 |
| SORT |
按特定類的順序排序行，可以選擇消除重復條目。 |
| TBSCAN |
通過直接從數據頁中讀取所有數據而檢索行。 |
| TEMP |
將數據存儲在臨時表中以便讀回（很可能要讀回多次）。 |
| TQUEUE |
在數據庫代理之間傳輸表數據。 |
| UNION |
串聯來自多個表的行流。 |
| UNIQUE |
消除特定列值重復的行。 |
| UPDATE |
更新表中的行。 |
| XISCAN |
掃描 XML 表的索引。 |
| XSCAN |
在一個 XML 文檔節點子樹中導航。 |
| XANDOR |
允許為多個 XML 索引應用 ANDed 和 ORed 謂詞。 |
圖 12 中展示
了一些更為常見的操作對象的示例。在這個示例中，執行了三種不同的行動：兩個表執行了表掃描，兩個數據集使用散列連接算法連接。
圖 12. 一些常見操作符
連接符和 RETRUN 操作符
箭頭說明數據從一個節點流向另一個節點的方式，它將層次圖中的所有節點連接在一起，RETURN 操作符通常用于終止這一過程。RETURN 操作符表示最終結果集產生，并包含關于查詢的匯總信息以及所完成的 SQL 語句返回的內容。使用 RETURN 顯示的 timeron 值表示按 timeron 度量的時間總長度，是完成查詢所必需的時間。圖 13 展示了 RETURN 操作符的一個示例。
圖 13. RETURN 操作符
影響查詢性能的因素
數據庫環境的配置方式和用于準備查詢的查詢優化級別對于查詢的準備方式、執行方式有著重大的影響。
配置參數值
Visual Explain 可迅速匯總影響查詢編譯的所有參數，并在一個匯總窗口中顯示出來。這個窗口就稱為 Optimization Parameters 窗口，通過在 Access Plan Graph 窗口的主菜單中選擇 Statement > Show Optimization Parameters 可調用此窗口。圖 14 展示了 Optimization Parameters 窗口在激活時的外觀。
圖 14. Optimization Parameters 窗口
Optimization Parameters 窗口中包含的部分配置參數包括：
AVG_APPLS(平均應用程序)：此參數表示為數據庫并發運行的應用程序平均數量。DB2 使用此信息來確定排序空間和緩沖池使用得有多么頻繁，并確定查詢能夠使用的空間有多少。
SORTHEAP(排序堆)：排序堆是執行排序時可用的內存空間數量。若排序需要的內存多于排序堆中可用內存，則部分排序數據將不得不分頁到磁盤上(這會對性能造成嚴重的負面影響)。
LOCKLIST(鎖列表)：該參數表示 DB2 可用于存儲各應用程序的鎖定信息的內存數量。若鎖列表空間過小，則 DB2 可能必須逐步提交(escalate)部分鎖，以便為應用程序具有的所有鎖騰出空間。
MAXLOCKS(最大鎖列表百分比)：該參數控制整個鎖列表空間中有百分之多少的空間可為一個應用程序所有。若一個應用程序具有過多的開放鎖，從而試圖占用過多的內存，DB2 將提交部分鎖，以釋放鎖列表中的空間。
NUM_FREQVALUES(頻繁值數)：DB2 Runstats 實用工具使用頻繁值數來控制 DB2 將在內存中保留多少使用頻率最高的值。優化器使用該信息來確定 WHERE 子句中的一個謂詞將消耗結果集的多少百分比。
NUM_QUANTILES(數據分位數)：DB2 Runstats 實用工具使用分位數來控制為列數據捕獲多少分位。增加分位數將給予 DB2 關于數據庫中數據分布情況的更多信息。
DBHEAP(數據庫堆)：數據庫堆控制數據庫對象信息的可用內存量。對象包括索引、緩沖池和表空間。事件監控器和日志緩沖區信息也存儲在這里。
CPUSPEED (CPU 速度)：計算機的 CPU 速度。若此值設置為 -1，則 DB2 使用 CPU 速度度量程序來確定恰當的設置。
BUFFPAGE 和緩沖池大小：優化器可在優化數據中使用的緩沖池大小。增加或減少緩沖池大小會對訪問計劃產生顯著影響。
所用優化級別
影響為查詢生成訪問計劃的方式的最重要因素就是優化級別，優化級別用于為此任務做準備。該信息告訴 DB2 Optimizer 要付出多少努力、使用什么技術來確定解決查詢的最佳訪問計劃。較高的級別將使優化器使用更為復雜的算法和代數分析 —— 因而也需要花費更多的時間 —— 來生成最終計劃。
有 7 個可用的優化級別，各級別使用所有可用規則和統計信息的不同子集。可用的優化級別包括：
0 —— 使用最少的優化
1 —— 所用的優化程度大致與 DB2/6000 V1 相同，此外還增加了一些 V1 中沒有的低成本特性
2 —— 使用優化級別 5 的特性，但簡化了連接算法
3 —— 執行中等數量的優化，與 DB2 for MVS/ESA 的查詢優化特征相似
5 —— 使用大量優化，利用 Heuristic Rules(若未另行指定，這將是默認優化級別)
7 —— 使用無 Heuristic Rules 的大量優化
9 —— 使用所有可用優化技術
在確定要使用的最佳優化級別時，以下指導原則可給予您很大的幫助：
- 為需要很少的優化、極其依賴主鍵索引搜索或極簡單的連接(例如，非常簡單的 OLTP)的查詢使用優化級別 0 或 1。
- 若查詢比較簡單，僅包含少量表和設計表的索引的連接(例如，OLTP)，則為查詢使用優化級別 1。
- 對包含復雜 OLTP 的工作負載或包含多個表上許多復雜連接的報告(例如，混合的 OLTP 和報告)，應使用優化級別 5。
對于需要大量數據統計數據分析、且運行時間較長(超過 1 分鐘)的查詢(例如，非常復雜的數據研究或決策支持)，使用優化級別 9。DB2 Optimizer 需要花費較長的時間來生成訪問計劃，但訪問計劃中改進的價值往往超出產生計劃所花費的額外時間。
對 SQL 進行故障排除的最終思考
已經有許多書籍專門探討如何改進 SQL 性能這個話題，本文不可能面面俱到地介紹查詢性能調優。但在您開始使用 Visual Explain 對執行狀況不佳的查詢進行故障排除時，應牢記一些要點：
未充分地利用索引。查詢是否按您的希望使用了索引?應確保未在您理所當然地認為應該具有索引的表上進行表掃描。此問題可通過查看查詢的訪問計劃圖表輕松應對。若確實存在索引，則檢查基數或索引鍵的順序。情況未必與您預計的相同。
表基數和 'SELECT *' 的使用。有時，由于您要返回的列數，DB2 Optimizer 會判定掃描整個表的速度更快。有可能表非常小，也有可能掃描索引并返回大量行(即返回表中的所有列)的效率很低。嘗試僅返回那些您確實需要的列。查看查詢各部分返回的列，觀察您是否確實需要這些列，并觀察這是否是表掃描發生的原因。同樣，考慮使用索引中包含的列。
優化級別設置過低。許多 DBA 將優化級別降低到 1，以縮短查詢準備所需時間。有的時候，將優化級別增加到 5 將允許優化器找到更佳的訪問計劃，而您不必創建一個新索引來改進性能。您在 Explain Query Statement 窗口中選擇為查詢生成解釋信息時(參見圖 9)，可輕松在 Visual Explain 工具中調整該值。還可在 Command Line Processor 中設置該值，執行以下命令即可：
SET CURRENT QUERY OPTIMIZATION [0|1|2|3|5|7|9]