簡介
在數據庫應用實際上線運營之後，數據庫管理員需要對應用的性能進行監控和維護。但是，由於應用的復雜性，一個應用中可能有成百上千的查詢語句，當應用真正發生性能問題之後，很難在短時間之內快速定位到引起性能問題的查詢語句。同時，當你對一個查詢語句進行局部優化之後，也許整個應用的性能並沒有得到改善，所以需要對應用進行整體的性能分析。對於一個復雜應用來說，這是一件非常困難的事情。
Optim Query Tuner，簡稱 OQT，是一個性能調優工具。OQT 為數據庫管理人員和應用開發人員提供了一系列工具對單個查詢語句或者一組查詢語句進行性能的分析和調優，包括性能監控、查詢語句的定位、圖形化的性能分析、智能的優化建議、生成報表等等，極大的提高企業對突發性能問題的處理能力和解決速度。
在本系列的前兩篇文章中，我們分別介紹了 Optim Query Tuner 的功能概況以及使用 OQT 進行單個查詢語句的性能調優。本文是針對 OQT 系列教程的最後一部分。本文分兩個章節，完整介紹了使用 OQT 對應用進行整體性能調優和監控的詳細過程。
應用性能調優
使用 OQT 獲取應用的詳細信息
使用 OQT 進行應用性能調優主要有兩種應用場合，一是當應用真正發生性能問題之後，使用 OQT 快速定位到應用中的性能瓶頸並使用 OQT 的優化專家進行修正，二是周期性地對應用的性能進行健康檢查，主動進行性能調優，提高應用的性能。不管是那種應用場合，在進行應用性能調優之前，首先需要獲取應用的詳細信息，包括應用中的相關查詢語句及其運行時的信息，比如執行次數和執行時間等。在 OQT 中，我們稱這樣的一組查詢及其相關信息為“Workload”（工作負載）。 OQT 提供了一個“向導”帶領用戶一步步獲取應用中的關鍵查詢及其相關信息，並把它們存儲到 OQT 自定義的一套表中以供接下來的分析處理。
啟動“向導”
OQT 提供了一個“Workload List”視圖用於管理工作負載。在這個視圖中，用戶可以點擊“New Workload”->“Wizard”菜單項啟動一個向導，從應用中獲取詳細信息並生成一個新的工作負載，見圖 1。
圖 1. 啟動“向導”
查看原圖（大圖）
指定查詢“源”
應用中的查詢語句一般可以分為兩類，一類是動態查詢，一類是靜態查詢。動態查詢執行之後會被緩存到 dynamic statement cache（動態語句高速緩存）中。靜態查詢經過預編譯、編譯和綁定之後會以 package（包）或者 plan（計劃）的形式存儲到 DB2 的 catalog（編目）表中。在“向導”的第二步中，用戶需要指定一個“源”類型以供 OQT 獲取應用中的查詢語句。如果應用中的查詢為動態查詢，則選擇“statement cache”作為“源”；如果應用中的查詢為靜態查詢，則選擇“catalog”作為源，見圖 2。
圖 2. 指定查詢“源”
指定過濾條件
數據庫中可能存在很多應用，每個應用中可能有成百上千的查詢，而真正引起性能問題的查詢又可能是其中的一小部分。為了准確定位到應用中的相關查詢，需要指定一些過濾條件。比如，對於“statement cache”，可以通過指定用戶的 ID 和執行次數來縮小獲取查詢的范圍。圖 3 展示了如何從“statement cache”中獲取由 ADMF001 執行了不少於 3 次的查詢語句。有關詳細信息，可以參考本系列文章的第二部分：使用 OQT 來對 SQL 語句進行優化。
圖 3. 指定過濾條件
指定抓取類型
如圖 4 所示，用戶可以指定不同的抓取類型，獲取應用中的查詢。
立即抓取：根據指定的過濾條件立即進行抓取。對於靜態查詢，只能選擇立即抓取。對於動態查詢，用於動態緩存中的查詢語句會不斷的變化，新的查詢不斷進入，舊的查詢經過一段時間之後可能被清除出去。所以除了“立即抓取”之外，用戶還可以使用如下方式獲取應用中的查詢。
一次抓取：指定一個將來的時間進行抓取，OQT 會在指定的時間到來之際，啟動後台線程進行抓取。
多次抓取：指定一個開始時間，結束時間和間隔時間進行多次抓取。比如，設置開始時間為上午 9 點，結束時間為上午 10 點，間隔時間為 15 分鐘。OQT 將每隔一刻鐘進行一次抓取，一共進行 5 次抓取。
連續抓取：指定一個開始時間和間隔時間進行無限次抓取，直到用戶退出 OQT 或者取消抓取任務。
圖 4. 指定抓取條件
查看原圖（大圖）
查看已抓取到的查詢語句
根據用戶指定的過濾條件和抓取類型，OQT 會啟動後台線程對應用中的查詢進行抓取，抓取到的查詢語句及其相關信息會被存儲到一個工作負載中。用戶可以從“Workload List”視圖中雙擊打開一個已有的工作負載，所有被抓取到的查詢語句及其相關信息將被顯示在一個叫做“Workload Tuning Editor”的界面中。這些信息包括查詢語句的文本信息，執行次數，累計執行時間和平均執行時間等，見圖 5。如果已抓取到的查詢數目特別多，OQT 還提供了翻頁功能，每頁缺省顯示 50 個查詢語句。同時，用戶還可以定制過濾條件，來減少顯示的查詢數目。
圖 5. 查看已抓取到的查詢語句
查看原圖（大圖）
合並查詢語句
這是一個可選的步驟。當工作負載中的查詢語句除了“文本值”（literal value）之外完全相同，可以通過“合並查詢語句”將這些查詢語句合並成一條，見圖 6。例如，下面的三條查詢語句除了“文本值”（粗體部分）之外，其他部分完全相同。
SELECT * FROM SYSIBM.SYSTABLES WHERE NAME= ’ T1 ’;
SELECT * FROM SYSIBM.SYSTABLES WHERE NAME= ’ T2 ’;
SELECT * FROM SYSIBM.SYSTABLES WHERE NAME= ’ T3 ’;
通過定義一個“合並文本值”的任務，這三條查詢將被合並成一條查詢。文本值部分將被替換成問號（question marker）
SELECT * FROM SYSIBM.SYSTABLES WHERE NAME=?;
圖 6. 合並查詢語句
查看原圖（大圖）
生成解釋信息（EXPLAIN）
在對工作負載進行性能分析之前，還需要為工作負載中的每一個查詢語句生成解釋信息。如果這些查詢語句是靜態查詢，並且在綁定的時候指定了 EXPLAIN YES 這個選項，那麼在抓取這些查詢語句的時候，對應的解釋信息也會同時被抓取出來存儲到工作負載中。否則，用戶必須通過“EXPLAIN”對話框定義一個 EXPLAIN 任務，如圖 7 所示。用戶可以選擇對所有的查詢語句都重新生成解釋信息，或者只對解釋信息缺失的查詢語句生成解釋信息。
圖 7. 生成解釋信息
使用存儲過程
如果應用中的查詢數目特別大，無論是抓取查詢語句、合並查詢語句還是生成 EXPLAIN 信息等任務都比較耗時，所以 OQT 提供了對這些功能的存儲過程實現。使用存儲過程可以減少網絡開銷，從而提高處理速度。為了使用存儲過程，需要進行如下配置：
在主機端安裝 Java 環境和 JCC 驅動。
從客戶端或者主機端配置存儲過程及其相關包。
從“首選項”中選擇“使用存儲過程”，見圖 8。
圖 8. 使用存儲過程
查看原圖（大圖）
使用 OQT 分析應用的統計信息並生成優化建議
基於 workload 的 Statistic Advisor 是 OQT 用來分析應用中統計信息的一個有力工具，簡稱 WSA。相比於用來分析單條查詢的 Statistic Advisor，WSA 會逐條分析每條查詢，並將所有的推薦的 RUNSTATS 命令進行合並。此外，WSA 還會根據每條查詢在 workload 之中的比重，推薦被引用次數最多的一些列和列的組合。
圖 9 是 WSA 分析完一個 workload 後獲得的 RUNSTATS 推薦方案：
圖 9. WSA 的推薦
查看原圖（大圖）
可以看到，推薦方案分為高低兩個優先級。可以注意到此處的優先級順序與 SA 正好相反，維護級別處於高優先級。之所以這樣是因為 WSA 更偏向於對系統的維護，鼓勵用戶定期收集 workload 所有相關的統計信息。
在 WSA 給出的推薦方案報告之中，最左面的是 RUNSTATS 推薦欄。在高優先級的推薦之中，WSA 給出了兩條 RUNSTATS 命令。點擊右面的“Run...”按鈕，就能執行這兩條 RUNSTATS 命令。在執行完畢之後，用戶還可以選擇去驗證新生成的統計信息。圖 10 是 RUNSTATS 後的結果。
圖 10. RUNSTATS 結果
查看原圖（大圖）
在推薦界面點擊“Details”按鈕後，就能看到關於整個 workload 的統計信息報告。這個報告中包含了在 workload 裡被引用過表及表上的列、列的組合和索引的統計信息。假如部分統計信息存在沖突，那麼將會在統計信息報告文本框下面的統計信息沖突文本框之中顯示出來。統計信息沖突的含義是指，在數據庫之中存在的幾個統計信息存在互相矛盾的情況。比如在系統編目中，表的記錄顯示某個表一共有 1000 條記錄；但是在有關列的記錄之中，這個表的某一個列卻包含了 1500 條不同的記錄。顯然這兩者至少有一個值是錯誤的，這就是統計信息沖突的一個典型的例子。圖 11 是 WSA 詳細的報告。
圖 11. WSA 詳細報告
查看原圖（大圖）
在推薦界面點擊“Save into Profile Table”按鈕後，OQT 會將當前在 RUNSTATS 命令推薦框中的 RUNSTATS 命令保存到數據庫的表中。而當用戶點擊“Retrive from Server”按鈕後，會從服務器得到以前保存過的相關表的 RUNSTATS 命令，並顯示在右邊的 Profile 框之中。
使用 OQT 分析應用的查詢並生成優化建議
基於 workload 的 Query Advisor 是 OQT 用來分析應用中查詢的一個有力的工具，簡稱 WQA。相比於單條查詢，WQA 能夠逐個分析應用中的每條查詢，並且根據優先級生成一個綜合報告，便於用戶能夠迅速定位到重要的問題查詢。圖 12 是一個綜合報告的例子：
圖 12. WQA 的報告
查看原圖（大圖）
在這個報告中，我們能看到所有分析的查詢條數共 22 條，有警告信息的查詢條數 4 條，較低嚴重程度的警告信息 7 個。另外，我們還能知道較低嚴重程度的查詢條數 4 條。因為一條查詢裡面可能包括了多個警告，所以這裡 4 條查詢裡面包括了 7 條較低嚴重程度的警告信息。一般來說，我們只需要顯示包括警告信息的查詢。圖 13 是包括警告信息的查詢條目：
圖 13. WQA 的警告信息
查看原圖（大圖）
我們可以很容易的看到這些查詢中包括的警告條數，以及它們相關的執行信息，例如執行次數，累計的執行時間，CPU 時間等等。選中任何一條查詢，點擊 Details 可以顯示實際的 Query Advisor 的推薦，如何去重寫查詢以便獲得更優性能等。
使用 OQT 分析應用的索引設計並生成優化建議
基於 workload 的 Index Advisor 是 OQT 用來分析應用中索引設計的一個有力工具，簡稱 WIA。相比於用來分析單條查詢的 Index Advisor，WIA 具有更加強大的功能，能針對不同類型用戶的需求，采用不同的策略，根據用戶所制定的條件，推薦出更優的索引設計方案。圖 14 是 WIA 分析完一個應用程序後獲得的索引推薦方案：
圖 14. WIA 的索引推薦方案
查看原圖（大圖）
在這個方案中，我們可以看到 WIA 給這個應用中所涉及的 5 個表推薦了 13 個新的索引，並且估計了這些索引所需要的磁盤空間共是 380.01MB，能夠獲得的性能提高是 69 ％。這個數據是在 WIA 按照缺省設置運行後給出的推薦。一般來說這個設置適用於幫助現有的應用獲得最優的性能，所以沒有限制新的索引能夠使用的磁盤空間。如果用戶只希望獲得可接受的性能提高，不希望占用過多的磁盤空間，可以通過 WIA 所提供的 What-If 分析功能來實現。只需要點擊 What-If Analysis 這個按鈕，然後修改磁盤空間的限制，WIA 就會推薦出一組修正的索引設計，如圖 15，我們限制磁盤空間為 200MB：
圖 15. WIA 通過 What-If 分析限制磁盤空間後的推薦
查看原圖（大圖）
在這個方案中，我們發現只有 4 個表推薦了索引，並且索引總數也下降為 8 個。當然因為限制了磁盤空間，所以最終的磁盤空間為 198.7MB，下降了 47.7%，性能提高為 61 ％，比原來 69 ％下降了 8%。所以通過這個調整，WIA 幫我們丟棄了那些消耗空間多，但性能提高不顯著的索引，在某些情況下，特別是系統磁盤空間緊張的時候，不失為一個折衷的方案。
應用性能監控
在實際的數據庫應用中，SQL 語句的執行效率很大程度上決定著整個應用的效率，因此對應用中 SQL 語句的運行進行監控對發現問題和優化性能都有重要意義。OQT 在 DB2 Profile 的基礎上提供了監控解決方案，能夠對數據庫系統中的靜態或者動態查詢並進行監控。本文將詳細介紹如何使用 OQT 監控器對數據庫中運行的 SQL 進行監控和調優。
使用 OQT 監控數據庫應用的日常運行以進行健康檢查
創建一個日常運行的監控器
１． OQT 提供了一個“Monitor List”視圖用於管理監控器。在這個視圖中，用戶可以點擊“Monitor Profile”->“New”菜單項啟動一個監控器向導，選擇監控器類型為正常（Normal）來創建一個正常的監控器，見圖 16。
圖 16. 啟動向導
查看原圖（大圖）
２．制定被監控 SQL 語句的類型。監控器向導中提供了 SQL 語句類型的選項，分別提供對動態 SQL 語句和靜態 SQL 語句的監控。在這裡，選擇監控動態 SQL 語句需要提供應用程序的 AUTHID 和 IP 地址；選擇監控靜態 SQL 語句需要提供已綁定的 Plan 的名稱，或者提供完整的 Plan、Collection ID 和 Package 的名稱，見圖 17。
圖 17. 選擇監控 SQL 語句的類型
查看原圖（大圖）
３．指定過濾條件。在這裡可以選擇是否抓取解釋信息，還可以選擇監控基本信息（執行時間和累計的 CPU 時間）或者監控全部運行時信息。監控基本信息對性能影響較小，如果監控全部信息則會增加 10%CPU 消耗，見圖 18。
圖 18 指定過濾條件
查看原圖（大圖）
４．選擇何時啟動監控器
立即啟動，監控器定義完成後立即啟動。
按計劃運行，手工指定監控器的啟動時間，停止時間。
不啟動，監控器定義完成後不啟動，需要時通過“Monitoring”->“Start Monitoring …”來啟動。
禁止，監控器定義完成後即被禁止，使用時需要先激活才能啟動。
見圖 19。
圖 19. 選擇何時啟動監控器
