[Oracle10G新特性]_18.ADDM 和 SQL Tuning Advisor
?
???
Oracle的這個(gè)功能非常不錯(cuò)，可以減輕很多的工作量。而且對(duì)于EM的操作，也需要多加以學(xué)習(xí)。
?
-----------------------------------------------------
?
ADDM 和 SQL Tuning Advisor
?
從最終權(quán)威那兒獲得關(guān)于 SQL 調(diào)整的幫助：該權(quán)威就是 Oracle 數(shù)據(jù)庫(kù)本身！使用 SQL 配置文件進(jìn)行查詢，并了解如何使用 ADDM 來(lái)快速、輕松解決常見的性能問(wèn)題。
?
??? 迄今為止這是平靜的一天：在數(shù)據(jù)庫(kù)中沒有重大問(wèn)題發(fā)生，不需要去滅“火”。您幾乎可以放松一下了；接下來(lái)正好可以抓緊處理那些重要任務(wù)了，如對(duì) RMAN 調(diào)整參數(shù)或多個(gè)塊大小進(jìn)行調(diào)整等。
?
??? 突然，一位開發(fā)人員出現(xiàn)在您面前。他的 SQL 查詢要運(yùn)行很長(zhǎng)一段時(shí)間。他問(wèn)您是否能盡快調(diào)整該查詢使其能夠“工作”。
?
??? 也許您放松得太早了。您最初的日程安排是花些時(shí)間制定戰(zhàn)略決策，以使您的數(shù)據(jù)庫(kù)運(yùn)行得更好、更快、更安全。例如，確保數(shù)據(jù)庫(kù)是可恢復(fù)的，增強(qiáng)底層技術(shù)或研究安全性更新等等。相反，您將又花一天的時(shí)間集中處理 SQL 等戰(zhàn)術(shù)活動(dòng)，而很少或沒有時(shí)間來(lái)考慮戰(zhàn)略問(wèn)題。
?
??? 作為一名戰(zhàn)略 DBA，您想把自己從日?，嵤轮薪饷摮鰜?lái)，更多地關(guān)注那些引人深思的領(lǐng)域。讓一名助理 DBA 幫助您做那些瑣事難道不好嗎？
?
??? 有了 Oracle 數(shù)據(jù)庫(kù) 10g，您就有了一位自動(dòng)數(shù)據(jù)庫(kù)診斷監(jiān)控程序 (ADDM) 形式的助理 DBA，這種機(jī)器人式的 DBA 會(huì)不知疲倦地反復(fù)搜索數(shù)據(jù)庫(kù)性能統(tǒng)計(jì)，以標(biāo)識(shí)瓶頸、分析 SQL 語(yǔ)句、并據(jù)此提供多種改進(jìn)性能的建議，它通常與其他“顧問(wèn)”（如 SQL Tuning Advisor）一起使用。在這篇文章中，對(duì)該過(guò)程的工作方式進(jìn)行了概述。
?
?
自動(dòng)數(shù)據(jù)庫(kù)診斷監(jiān)控程序 (ADDM)
?
??? 在第 6 周，您了解到被稱作快照的自動(dòng)負(fù)載信息庫(kù) (AWR)，它定期從數(shù)據(jù)庫(kù)中收集詳細(xì)的與性能相關(guān)的度量標(biāo)準(zhǔn)。每次快照后，調(diào)用 ADDM 來(lái)徹底分析源自快照間差異的數(shù)據(jù)和度量標(biāo)準(zhǔn)，然后就必要的操作提出建議。正如我早先所提及的，發(fā)現(xiàn)問(wèn)題后，ADDM 可能會(huì)依次調(diào)用其他顧問(wèn)（如 SQL Tuning Advisor），來(lái)提出改進(jìn)建議。
?
??? 我將為您完全展示（而不是用文字來(lái)解釋）這種特性是如何運(yùn)行的。假定您正設(shè)法診斷一個(gè)不可解釋的性能問(wèn)題。在我們介紹的示例中，您知道了哪些 SQL 語(yǔ)句需要調(diào)整，或至少知道了哪些 SQL 語(yǔ)句存在問(wèn)題。但是在現(xiàn)實(shí)生活中，您可能沒有這些有用信息。
?
??? 要在 10g 中執(zhí)行診斷，您將在相關(guān)的時(shí)間間隔內(nèi)選擇快照進(jìn)行深入、透徹的分析。在 Enterprise Manager 10g 中，從 Database 主頁(yè)上，您將選擇 "Advisor Central"，然后單擊 "ADDM" 鏈接，它將出現(xiàn)一個(gè)類似于圖 1 的頁(yè)面。
?
?
圖 1：創(chuàng)建 ADDM 任務(wù)
?
??? 在該頁(yè)中，您可以創(chuàng)建 ADDM 要分析的任務(wù)。您知道性能問(wèn)題發(fā)生在晚上 11 點(diǎn)左右，因此選擇那個(gè)時(shí)間間隔的快照，通過(guò) "Period Start" 和 "Period End" 值進(jìn)行指示。您也可以單擊照相機(jī)圖標(biāo)，指示開始和終止快照的時(shí)間間隔，如此處的紅色橢圓形所示。選擇時(shí)間間隔后，按下 "OK" 按鈕，將出現(xiàn)一個(gè)類似于圖 2 所示的頁(yè)面。
?
?
圖 2：ADDM 查找結(jié)果
?
??? 這里 ADDM 在該時(shí)間間隔內(nèi)標(biāo)識(shí)了兩個(gè)關(guān)鍵的、相關(guān)的性能問(wèn)題：某些 SQL 語(yǔ)句消耗著重要的 CPU 時(shí)間，從而使數(shù)據(jù)庫(kù)的速度顯著減慢?；谶@些查找結(jié)果，ADDM 建議對(duì)圖中突出顯示的那些語(yǔ)句進(jìn)行 SQL 調(diào)整。
?
??? 如果您單擊某條查找結(jié)果，ADDM 會(huì)顯示更多詳細(xì)信息。例如，單擊問(wèn)題查找結(jié)果，將會(huì)出現(xiàn)一個(gè)類似于圖 3 所示的頁(yè)面。
?
?
圖 3：ADDM 查找結(jié)果的詳細(xì)信息
?
??? 在此您可以看到引發(fā)該問(wèn)題的特定 SQL 語(yǔ)句。ADDM 建議您用 SQL Tuning Advisor 對(duì)該 SQL 語(yǔ)句進(jìn)行徹底的分析，正如在 "Action" 部分中所提到的那樣。您可以通過(guò)單擊它旁邊的按鈕立即運(yùn)行該任務(wù)，這將調(diào)用 SQL Tuning Advisor。
?
??? 在圖 2 中，您可能注意到了一個(gè)名稱為 "View Report" 的按鈕。除了在單獨(dú)的 Web 頁(yè)面中提供建議外，ADDM 還能夠創(chuàng)建純文本報(bào)表，以進(jìn)行更快速的一步到位的分析。列表 1 顯示了在我們的示例純文本報(bào)表中提出的全面建議。注意報(bào)表是如何給出相關(guān)細(xì)節(jié)的，如所考慮的 SQL 語(yǔ)句、它的散列值等?？梢栽?Enterprise Manager 的 SQL Tuning Advisor 頁(yè)中或通過(guò)命令行將 SQL ID 用于獨(dú)立的分析。
?
??? 在收集了每一張 AWR 快照后就會(huì)調(diào)用 ADDM，因此可以查看基于相鄰快照的建議。因此，如果分析的范圍只是兩張相鄰的快照，就不必創(chuàng)建上面所示的 ADDM 任務(wù)。如果您想在兩張不相鄰的快照之間進(jìn)行分析，就需要?jiǎng)?chuàng)建 ADDM 任務(wù)。
?
??? 記住 ADDM 所能做的遠(yuǎn)不止這些；正如您在以前的文章中所看到的，它還提供內(nèi)存管理、段管理、重作/撤消以及更多的分析和建議。由于在一篇簡(jiǎn)短的文章中描述所有的 ADDM 功能是不可能的，在此我們只關(guān)注 SQL Tuning Advisor?，F(xiàn)在讓我們看看它是如何工作的。
?
?
用 SQL Tuning Advisor 訪問(wèn)分析
?
??? 在一個(gè)典型的運(yùn)行時(shí)優(yōu)化中，優(yōu)化器生成一組可能的訪問(wèn)路徑，并基于對(duì)象統(tǒng)計(jì)從中選擇出最“經(jīng)濟(jì)”的路徑。但是，那時(shí)它沒有時(shí)間考慮是否能夠調(diào)整語(yǔ)句、統(tǒng)計(jì)是否陳舊、是否能夠創(chuàng)建索引等問(wèn)題。相反，SQL Tuning Advisor 可以執(zhí)行這種“專家系統(tǒng)”類型的思考。實(shí)質(zhì)上，優(yōu)化器能夠回答的問(wèn)題是：“基于可用的資源,獲得結(jié)果的最佳方式是什么？”而 SQL Tuning Advisor 能夠回答的問(wèn)題是：“基于用戶的需求，還需要做些什么來(lái)增強(qiáng)性能？”
?
??? 正如您可能預(yù)期的那樣，這種“思考”消耗了資源（如 CPU）；因此 SQL Tuning Advisor 在調(diào)整模式期間處理 SQL 語(yǔ)句，該模式可以在非高峰時(shí)間運(yùn)行。在創(chuàng)建調(diào)整任務(wù)時(shí)，通過(guò)在函數(shù)中設(shè)置 SCOPE 和 TIME 參數(shù)來(lái)指示這種模式。在數(shù)據(jù)庫(kù)活動(dòng)少的期間運(yùn)行調(diào)整模式是一個(gè)好方法，以使常規(guī)用戶相對(duì)不受影響，稍后再進(jìn)行分析。
?
??? 這個(gè)概念可以通過(guò)示例很好地解釋。就看看如下所示的開發(fā)人員引起您注意的那個(gè)查詢事例吧。
?
select account_no from accounts where old_account_no = 11
?
??? 該語(yǔ)句調(diào)整起來(lái)并不難，但是為了更容易說(shuō)明問(wèn)題，假定它很難調(diào)整。激發(fā)顧問(wèn)的方式有兩種：使用 Enterprise Manager 或簡(jiǎn)單明了的命令行。
?
??? 首先，讓我們看看如何在命令行中使用它。我們通過(guò)調(diào)用提供的包 dbms_sqltune 來(lái)調(diào)用顧問(wèn)。
?
declare
?
l_task_id???? varchar2(20);
l_sql???????? varchar2(2000);
begin
l_sql := 'select account_no from accounts where old_account_no = 11';
dbms_sqltune.drop_tuning_task ('FOLIO_COUNT');
l_task_id := dbms_sqltune.create_tuning_task (
sql_text? => l_sql,
user_name? => 'ARUP',
scope????? => 'COMPREHENSIVE',
time_limit => 120,
task_name? => 'FOLIO_COUNT'
?? );
dbms_sqltune.execute_tuning_task ('FOLIO_COUNT');
end;
/
?
??? 這個(gè)包創(chuàng)建并執(zhí)行了一個(gè)名為 FOLIO_COUNT 的調(diào)整任務(wù)。接下來(lái)，您將需要查看任務(wù)執(zhí)行的結(jié)果（也就是說(shuō)，查看建議）。
?
set serveroutput on size 999999
set long 999999
select dbms_sqltune.report_tuning_task ('FOLIO_COUNT') from dual;
???
列表
2
顯示的是輸出結(jié)果。仔細(xì)查看這些建議；顧問(wèn)說(shuō)您可以通過(guò)在 OLD_ACCOUNT_NO 列上創(chuàng)建一個(gè)索引來(lái)改進(jìn)性能。更佳的是，如果創(chuàng)建索引，顧問(wèn)計(jì)算了查詢成本，從而使?jié)撛诘墓?jié)省量變得更加可定義、更加具體。
?
??? 當(dāng)然，考慮到本示例的簡(jiǎn)單性，通過(guò)手動(dòng)檢查也能得到這種結(jié)論。但是，可以想象出該工具對(duì)于那些更復(fù)雜的查詢十分有用，因?yàn)閷?duì)這些查詢執(zhí)行手動(dòng)檢查也許是不可能的或不實(shí)際的。
?
中級(jí)調(diào)整：查詢重構(gòu)
?
??? 假定查詢更復(fù)雜：
?
select account_no from accounts a
where account_name = 'HARRY'
and sub_account_name not in
( select account_name from accounts
where account_no = a.old_account_no and status is not null);
?
??? 顧問(wèn)建議如下：
?
1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The optimizer could not unnest the subquery at line ID 1 of the execution
plan.
?
Recommendation
? --------------
Consider replacing "NOT IN" with "NOT EXISTS" or ensure that columns used
on both sides of the "NOT IN" operator are declared "NOT NULL" by adding
either "NOT NULL" constraints or "IS NOT NULL" predicates.
?
Rationale
? ---------
A "FILTER" operation can be very expensive because it evaluates the
subquery for each row in the parent query.The subquery, when unnested can
drastically improve the execution time because the "FILTER" operation is
converted into a join.Be aware that "NOT IN" and "NOT EXISTS" might
produce different results for "NULL" values.
?
??? 這一次顧問(wèn)不會(huì)建議任何結(jié)構(gòu)上的更改（如索引），但會(huì)通過(guò)用 NOT EXISTS 取代 NOT IN的方式很聰明地猜測(cè)到調(diào)整查詢的正確方式。由于兩種構(gòu)造相似但不相同，顧問(wèn)給出了這種改變的基本原理，并把決定權(quán)留給 DBA 或應(yīng)用程序開發(fā)人員，由他們決定該建議是否對(duì)環(huán)境有效。
?
?
高級(jí)調(diào)整：SQL 配置文件
??? 正如您可能知道的，優(yōu)化器通過(guò)檢查查詢中引用的對(duì)象統(tǒng)計(jì)，來(lái)對(duì)查詢執(zhí)行計(jì)劃作出決定，然后計(jì)算成本最低的方法。如果查詢涉及到一張表以上這種典型情況，優(yōu)化器就會(huì)通過(guò)檢查所有引用對(duì)象的統(tǒng)計(jì)來(lái)計(jì)算成本最低的選項(xiàng) — 但是它并不知道對(duì)象間的關(guān)系。
??? 例如，假定一個(gè) DELINQUENT 狀態(tài)的帳戶擁有不到 1000 美元的余額。如果謂詞帶有一個(gè)只篩選 DELINQUENT 的子句，那么連接 ACCOUNTS 表和 BALANCES 表的查詢的報(bào)告行將較少。優(yōu)化器不知道這種復(fù)雜關(guān)系 — 但是顧問(wèn)知道；它從數(shù)據(jù)中“組合”了這種關(guān)系，并將其以 SQL 配置文件的形式存儲(chǔ)起來(lái)。通過(guò)訪問(wèn)該 SQL 配置文件，優(yōu)化器不但知道了表的數(shù)據(jù)分布，而且知道了這些表之間的數(shù)據(jù)相關(guān)系。這個(gè)附加信息可以讓優(yōu)化器生成一個(gè)高級(jí)執(zhí)行計(jì)劃，從而獲得一個(gè)優(yōu)良的查詢。
??? SQL 配置文件通過(guò)手動(dòng)將查詢提示添加到代碼中，而無(wú)需調(diào)整 SQL 語(yǔ)句。因此，SQL Tuning Advisor 使無(wú)需修改代碼即可調(diào)整打包的應(yīng)用程序成為可能且極具優(yōu)勢(shì)。
?
??? 要點(diǎn)在于，與對(duì)象統(tǒng)計(jì)不同的是，SQL 配置文件映射到一個(gè)查詢，而不是映射到一個(gè)或多個(gè)對(duì)象。涉及到同樣兩個(gè)表（ACCOUNTS 和 BALANCES）的另一個(gè)查詢可能具有不同的配置文件。使用查詢中的這種元數(shù)據(jù)信息，Oracle 就能夠改進(jìn)性能。
?
??? 如果能夠創(chuàng)建一個(gè)配置文件，它是在 SQL Tuning Advisor 會(huì)話期間完成的，該顧問(wèn)將在會(huì)話中生成配置文件并建議您“接受”。除非接受了配置文件，否則它不會(huì)被捆綁到語(yǔ)句上。您可以通過(guò)發(fā)出如下語(yǔ)句隨時(shí)接受配置文件：
?
begin
dbms_sqltune.accept_sql_profile (
task_name?? => 'FOLIO_COUNT',
name??????? => 'FOLIO_COUNT_PROFILE'
description => 'Folio Count Profile',
category??? => 'FOLIO_COUNT');
end;
?
??? 該命令將把顧問(wèn)先前生成的名為 FOLIO_COUNT_PROFILE 的配置文件捆綁到一條語(yǔ)句上，該語(yǔ)句與前面示例中描述過(guò)的名為 FOLIO_COUNT 的調(diào)整任務(wù)相關(guān)聯(lián)。（注意：盡管 SQL 配置文件只能由顧問(wèn)而不能由 DBA 創(chuàng)建，但只有您能決定何時(shí)使用它。）
?
??? 您可以在字典視圖 DBA_SQL_PROFILES 中查看創(chuàng)建的 SQL 配置文件。SQL_TEXT 列顯示分配給配置文件的 SQL 語(yǔ)句；STATUS 列指示是否啟用給該配置文件。（即使它已經(jīng)捆綁到一條語(yǔ)句上，也必須啟用配置文件來(lái)影響執(zhí)行計(jì)劃。）可以從視圖 DBMSHSXP_SQL_PROFILE_ATTR 查看配置文件的元數(shù)據(jù)信息，在該視圖中，ATTR_VALUE 列顯示了優(yōu)化器用以確定計(jì)劃的多種專有信息。
?
?
使用 ADDM 和 SQL Tuning Advisor
??? 除了上面描述的三種情形外，SQL Tuning Advisor 還會(huì)標(biāo)識(shí)出查詢中引用的丟失統(tǒng)計(jì)的任何對(duì)象。因此，顧問(wèn)執(zhí)行四種獨(dú)特類型的任務(wù)：
?
??? ● 檢查對(duì)象是否具有有效的、可用的統(tǒng)計(jì)，以進(jìn)行適當(dāng)優(yōu)化
??? ● 試圖重新編寫性能更好的查詢并就重新編寫提出建議
??? ● 檢查訪問(wèn)路徑，查看是否能夠通過(guò)添加額外的結(jié)構(gòu)（如索引和物化視圖）來(lái)改進(jìn)性能。
??? ● 創(chuàng)建 SQL 配置文件，并將它們附加到特定的查詢上。
?
??? 基于這些功能，我可以想到至少三種不同情況，將 ADDM 和 SQL Tuning Advisor 作為功能強(qiáng)大的工具來(lái)使用。
?
??? ●
反應(yīng)式調(diào)整：您的應(yīng)用程序突然運(yùn)行得很糟糕。使用 ADDM，您可以把問(wèn)題深入剖析到一條 SQL 語(yǔ)句或一組語(yǔ)句的層面上，如 ADDM 一節(jié)所示。根據(jù) ADDM 的建議，您可以啟動(dòng) SQL Tuning Advisor 并矯正問(wèn)題。
??? ● 積極式調(diào)整：應(yīng)用程序的運(yùn)行情況尚好；但是，您希望確保執(zhí)行了所有必要的維護(hù)任務(wù)，并知道是否可以對(duì)查詢進(jìn)行進(jìn)一步調(diào)整。您將在獨(dú)立模式下激發(fā) SQL Tuning Advisor，以標(biāo)識(shí)出可能的調(diào)整選擇。
??? ● 開發(fā)式調(diào)整：在開發(fā)過(guò)程中對(duì)代碼進(jìn)行測(cè)試時(shí)，相對(duì)于 QA 階段或生產(chǎn)階段，有許多機(jī)會(huì)可調(diào)整查詢。在開發(fā)最后確定 SQL 語(yǔ)句前，可以使用顧問(wèn)的命令行版本對(duì)這些語(yǔ)句進(jìn)行單獨(dú)測(cè)試。
?
?
使用 Enterprise Manager
?
??? 前一個(gè)示例被有意用來(lái)說(shuō)明如何在命令行模式下使用 SQL Tuning Advisor，該模式對(duì)積極編寫這些任務(wù)的腳本非常有用。但是，在大多數(shù)情況下，您需要依據(jù)終端用戶所報(bào)告的問(wèn)題執(zhí)行調(diào)整。Enterprise Manager 10g 在這些情況下就可以大顯身手了。
?
??? 幾個(gè)星期以前（第 13 周），向您介紹了更新過(guò)的 Enterprise Manager 界面。這里將介紹您將如何用它來(lái)診斷和調(diào)整 SQL：從 Database 主頁(yè)單擊屏幕底部的 "Advisor Central" 鏈接，這將啟動(dòng)一個(gè)包含所有顧問(wèn)的頁(yè)面。接下來(lái)，單擊屏幕頂部的 "SQL Tuning Advisor"，如圖 4 所示。
?
?
圖 4：Enterprise Manager 中的 Advisor Central
?
??? 您剛才啟動(dòng)了 SQL Tuning Advisor。從下一頁(yè)選擇 "Top SQL"，如圖 5 所示。
?
?
圖 5：SQL Tuning Advisors
?
??? 該操作啟動(dòng)了一個(gè)類似于圖 6 所示的頁(yè)面，其中有一個(gè)包含多種等待級(jí)別的沿時(shí)間維進(jìn)行跟蹤的圖表。
?
?
圖 6：頂部的 SQL 選擇器
?
??? 紅色橢圓內(nèi)的灰色矩形區(qū)域把焦點(diǎn)置于該圖表上。用鼠標(biāo)拖動(dòng)該矩形，將其重新定位到 CPU 等待級(jí)別為高的位置上（如圖中所示）。該頁(yè)面的下面部分將顯示那個(gè)時(shí)間間隔內(nèi)相關(guān)的 SQL 語(yǔ)句，如圖 7 所示。
?
?
圖 7：選擇基于活動(dòng)性的 SQL 語(yǔ)句
?
??? 正如您可以看到的，顯示在頂部（被紅色橢圓形所包圍）的 SQL 語(yǔ)句活動(dòng)性最高，其 CPU 消耗也最大。單擊語(yǔ)句 ID 查看該語(yǔ)句的詳細(xì)信息，這將引出一個(gè)如圖 8 所示的屏幕。
?
?
圖 8：SQL 詳細(xì)信息
?
??? 在該圖中，您可以看到在那個(gè)時(shí)間段引發(fā) CPU 消耗的確切的 SQL 語(yǔ)句。您可以單擊 "Run SQL Tuning Advisor" 按鈕（在圖中作了標(biāo)記）運(yùn)行顧問(wèn)。這將引出一個(gè)類似于圖 9 所示的屏幕。
?
?
圖 9：調(diào)度 SQL Tuning Advisor
?
??? 在顧問(wèn)調(diào)度程序中，您可以確定任務(wù)的類型，以及將要做多少分析工作。例如，在上圖中，我選擇了“全面”分析和顧問(wèn)將會(huì)立即運(yùn)行。在顧問(wèn)結(jié)束運(yùn)行后，您可以查看其建議，如圖 10 所示。
?
?
圖 10：顧問(wèn)建議
?
??? 我剛才描述的這個(gè)過(guò)程類似于您在命令行版本中所看到的過(guò)程；但是這個(gè)流程更反映了真實(shí)情況，在那種情況下，您對(duì)一個(gè)問(wèn)題作出反應(yīng)，深入剖析其原因，并接受關(guān)于如何修復(fù)它的建議。
?
?
結(jié)論
?
??? ADDM 是一個(gè)功能強(qiáng)大的“智能”工具，它能夠自動(dòng)根據(jù)最佳實(shí)踐和經(jīng)驗(yàn)豐富的 Oracel 專業(yè)人員公認(rèn)的方法，來(lái)分析性能度量標(biāo)準(zhǔn)和提供建議。這種功能不但能告訴 DBA 發(fā)生了什么以及為什么發(fā)生，而且最為重要的是，它還指明了下一步怎么做。
?
?
?
?
?
?
?
?