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