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