Oracle 11g新特性：SQL Performance Analyzer
數據庫重放是一款很棒的工具，可用于捕獲數據庫中的實際負載并隨意重放。它是 Oracle 真正應用測試選件的一部分，它的重點是“實際”一詞，即重放的負載就是數據庫中實際發生的負載。
在數據庫重放中，捕獲的全部負載都針對數據庫進行重放。但是，如果您不想那樣做那該怎么辦？例如，您可能很想了解 SQL 執行計劃以及 SQL 性能是如何受更改影響的，因為它們會嚴重影響應用程序的性能和可用性。此外，數據庫重放只重放捕獲的負載。在生產版本中執行之前，您可能很想知道參數更改對某些 SQL 的影響。
這一領域正是真正應用測試系列的另一個重要組件 SQL Performance Analyzer （SPA） 的用武之地。通過 SPA，您可以根據各種更改類型（如初始化參數更改、優化程序統計刷新和數據庫升級）播放特定的 SQL 或整個的 SQL 負載，然后生成比較報告，幫助您評估它們的影響。在本文中，您將學習如何使用 SQL Performance Analyzer 工具解決這個重要的問題。
示例問題
我們來進行一個測試。首先，我們定義要解決的問題。
這是一個典型的問題：Oracle 不使用索引，而您想了解其中的原因。為了回答這個問題，我拜讀了 Oracle 精英 Tim Gorman 的經典論文“Searching for Intelligent Life in Oracle's CBO”。（在 Web 上隨處都能找到這篇論文多種形式的各個版本。）
Tim 的一個建議是，將 optimizer_index_cost_adj 參數的值由默認的 100 更改為一個較小的值。這篇論文還給出了計算這個值的公式。根據公式，在我的案例中，我計算得出這個值為 10.但是，這帶來一個比較棘手的問題：這樣的更改會對每條 SQL 語句都有裨益嗎？
在 Oracle 數據庫 11g 之前的版本中，我需要捕獲所有 SQL 語句，通過跟蹤運行這些語句，然后得到執行計劃 — 這是一項極其耗時又極易出錯的任務。有了新版本之后，我不需要再那樣做了，我改用非常簡單而有效的 SQL Performance Analyzer.
首先，為了舉例說明，我們在數據庫中運行以下查詢：
select /* CONTROL_QUERY11 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 33; select /* CONTROL_QUERY12 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 350; select /* CONTROL_QUERY13 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 351; select /* CONTROL_QUERY14 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 534; select /* CONTROL_QUERY15 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 999; select /* CONTROL_QUERY21 */ sum(QUANTITY_SOLD) from sales where channel_id = 2; select /* CONTROL_QUERY22 */ sum(QUANTITY_SOLD) from sales where channel_id = 3; select /* CONTROL_QUERY23 */ sum(QUANTITY_SOLD) from sales where channel_id = 4; select /* CONTROL_QUERY24 */ sum(QUANTITY_SOLD) from sales where channel_id = 5; select /* CONTROL_QUERY25 */ sum(QUANTITY_SOLD) from sales where channel_id = 9; |
假設這些查詢由您的應用程序發出。通過調整 SQL 中的注釋，您可以在以后對它們進行搜索。語句執行完成后，您可以對其使用SPA.
用法
通常，最好通過 Oracle 企業管理器使用 SPA 功能。（當然，您也可以通過系統提供的程序包 dbms_sqlpa 使用命令行選項，但是不能超過企業管理器添加的可管理性值。）
執行以下步驟：
1、打開 Enterprise Manager Database Control 并單擊 Performance 選項卡。然后滾動到頁面底部，您將看到如下超鏈接。
2、單擊 Search Sessions，出現如下屏幕：
3、從游標緩存中搜索已經執行的 SQL 中的模式。注意，這些 SQL 都有一個注釋 CONTROL_QUERYn，其中 n 為 11、12 等等。輸入該字符串作為搜索函數。將給出先前執行過的所有 SQL.在上面顯示的屏幕中，您將看到標題為 Save to a new SQL Tuning Set 的單選按鈕。選擇該按鈕，然后輸入一個名為 CONTROL1 的 SQL 調整工具集。（注：為了舉例說明，您要選擇一些語句。）無需在 SQL 中加入任何注釋，您只要創建一個“SQL 調整工具集”并填入所有相關語句即可。
4、單擊 SQL Tuning Sets，將出現 SQL Tuning Sets 頁面。現在選擇名為 CONTROL1 的 STS.在此頁面中，您可以檢查該 STS 并為其添加和刪除 SQL.下面是該 STS 頁面的屏幕截圖：
#p#
5、現在，從 Performance 頁面中單擊 SQL Performance Analyzer.出現如下所示的 SPA 主頁面。
6、如您所見，還沒有定義任何 SPA 任務。現在定義一個 SPA 任務。在本例中，您將對參數 optimizer_index_cost_adj 的更改所帶來的影響進行評估，因此請單擊 Parameter Change.出現如下所示的 SPA Task Definition 頁面。
7、在此頁面中，您需要輸入 SPA 任務的必要信息。為任務命名：例如 STS1.
8、接下來，您需要輸入 SQL 調整工具集的名稱。單擊旁邊的手電筒圖標，然后選擇名為 CONTROL1 的 SIS.
9、在 Parameter Change 部分中，輸入要更改的參數。單擊手電筒圖標，然后選擇參數 optimizer_index_cost_adj.當前值已經填入，在 Changed Value 框中輸入目標值。
10、接下來，決定這些更改的比較方式：按所用時間、按 CPU 時間等等。為了舉例說明，選擇 Buffer Gets.
11、最后，安排該 SPA 任務的執行時間。選擇單選按鈕 Immediate，表明您要立即運行這個任務。
12、所有詳細信息都填好后，單擊 Submit.這樣就創建了一個您能夠獨立監控的作業，但您也可以停留在這個頁面中并監控該 SPA 任務的狀態，如下所示。
13、單擊 Refresh 查看該任務當前的狀態。注意 Last Run Status 欄下的圖標。下面是對這些圖標的說明：
14、圖中的圖標表示該任務現在正在運行。不斷單擊 Refresh.當圖標變為 Completed，顯示為一個核對符號時，該 SPA 任務就完成了。
15、單擊該 SPA 任務的名稱 （STS1），查看比較標準。出現如下所示的屏幕。
16、注意 Comparison Report 欄下的眼鏡圖標。在這里，您可以比較參數更改前后執行 SQL 的結果。單擊該圖標。
17、這是最令人興奮的部分。出現如下所示的屏幕：
這就是您一直等待的結果。在左上角，您會看到更改前后兩次運行 SQL 集的結果比較。總體而言，性能提升了 60%！這個結果就顯示在這，非常明確。在屏幕的右手邊，您會看到有多少 SQL 語句改變了執行計劃。似乎大部分都改變了計劃，只有少數沒有。
屏幕底端顯示了該任務分析的 SQL 語句的 SQL ID.SQL ID 前面的小箭頭顯示了這些 SQL 語句是改進了還是退化了，SQL ID 后面的數字顯示了改進或退化的百分比。這些數據告訴您更改對每條 SQL 語句的確切影響。如果您愿意，可以通過單擊 SQL ID 查看相應的 SQL.注意第一條 SQL，它受到的影響最大，如果單擊該 SQL，您會看到與下面類似的屏幕：
#p#
這個屏幕顯示了有關執行該 SQL 的大量統計信息。 屏幕底部顯示了執行計劃的比較：
現在您可以看到，使用索引是如何強制減少緩沖區的。但是，情況總是那么樂觀嗎？看看另一條 SQL：
與上一例的 31.95% 相比，此例改進甚微，只有 0.48%.原因是什么？為了找到答案，單擊 SQL ID，出現如下屏幕：
在這里，您可以看到究竟是什么改變了。所用時間實際上從 0.504 秒延長為 1.022 秒，而且都是因為 CPU 時間。為什么？如果您檢查一下數據分布模式，您就會看到 promo_id 是這樣分布的：
SQL> select promo_id, count(1) cnt from sales group by promo_id; PROMO_ID CNT |
promo_id 999 在表中出現了 887，837 次，將近 97%.當將計劃改為包含索引掃描時，這個查詢就比較困難了。如果對全表進行掃描，情況應該會好一些。因此，即使整體影響是積極的，也會有個別組件拖后腿。當您決定是否要更改參數時，您應該考慮到這些 SQL 語句的重要性，這些語句既可能改進也可能退化。
正如您所見，您希望評估對數據庫參數進行重要更改而帶來的影響。使用 SPA，您不必估計潛在的性能影響，連“猜測估計”也不必。您可以使用應用程序針對數據庫執行的 SQL 語句客觀地衡量。
現在看另一個案例：更改參數后，性能退化了，而不是改進了。下面是一個屏幕截圖：
這里，SQL 語句的運行情況都比更改之前要差。您可以利用（本文中討論的）SQL 計劃管理解決這個問題。SPM 允許您選擇優良的執行計劃作為您的基準，從而保證執行計劃的穩定性。隨后，優化程序會將這個基準用于相應 SQL 的所有執行過程。這個基準計劃會一直使用，直到被禁用或者您創建了新的基準計劃。另一個解決 SQL 退化問題的方法是使用 SQL Tuning Advisor，它能提出 SQL 調整建議或建議進行外部修改，如通過創建索引提高性能。
應用案例
SPA 在很多情形中都是極有用的，包括數據庫版本升級、部署數據庫補丁集、數據庫參數更改和優化程序參數更改等等。
例如，當您決定是否要提高優化程序參數時，比如從 10.2 更改為 11.1，您肯定想了解這個更改會對您的 SQL 語句產生怎樣的影響。執行該任務最好的工具就是 SPA.唯一不同的是，在上面的步驟 5 中，不是選擇 Database Parameter Changes，而是選擇 Optimizer Changes，將出現如下所示屏幕。
在此屏幕中，選擇合適的源優化程序版本和目標優化程序版本，然后完成剩余的步驟。
結論
使用這個新工具的最佳時間是什么時候？簡單的回答就是：在您進行任何更改的時候。與數據庫重放不同，在數據庫重放中您看不到實際的 SQL，而使用 SPA，您能夠得到特定 SQL 或整個應用程序 SQL 負載的結果。您可以評估正反兩方面的影響，并達到最佳的可能更改狀態而并不危害您的應用程序的性能。沒有哪種選擇是永遠絕對正確或錯誤的，是對與錯的程度使人們難于進行決策。SPA 將對錯程度推向某一個極端，從而使您更容易作出決策。
【編輯推薦】
