[Oracle10G新特性]_18.ADDM 和 SQL Tuning Advisor
?
???
Oracle的這個功能非常不錯，可以減輕很多的工作量。而且對于EM的操作，也需要多加以學習。
?
-----------------------------------------------------
?
ADDM 和 SQL Tuning Advisor
?
從最終權威那兒獲得關于 SQL 調整的幫助：該權威就是 Oracle 數據庫本身！使用 SQL 配置文件進行查詢，并了解如何使用 ADDM 來快速、輕松解決常見的性能問題。
?
??? 迄今為止這是平靜的一天：在數據庫中沒有重大問題發生，不需要去滅“火”。您幾乎可以放松一下了；接下來正好可以抓緊處理那些重要任務了，如對 RMAN 調整參數或多個塊大小進行調整等。
?
??? 突然，一位開發人員出現在您面前。他的 SQL 查詢要運行很長一段時間。他問您是否能盡快調整該查詢使其能夠“工作”。
?
??? 也許您放松得太早了。您最初的日程安排是花些時間制定戰略決策，以使您的數據庫運行得更好、更快、更安全。例如，確保數據庫是可恢復的，增強底層技術或研究安全性更新等等。相反，您將又花一天的時間集中處理 SQL 等戰術活動，而很少或沒有時間來考慮戰略問題。
?
??? 作為一名戰略 DBA，您想把自己從日常瑣事中解脫出來，更多地關注那些引人深思的領域。讓一名助理 DBA 幫助您做那些瑣事難道不好嗎？
?
??? 有了 Oracle 數據庫 10g，您就有了一位自動數據庫診斷監控程序 (ADDM) 形式的助理 DBA，這種機器人式的 DBA 會不知疲倦地反復搜索數據庫性能統計，以標識瓶頸、分析 SQL 語句、并據此提供多種改進性能的建議，它通常與其他“顧問”（如 SQL Tuning Advisor）一起使用。在這篇文章中，對該過程的工作方式進行了概述。
?
?
自動數據庫診斷監控程序 (ADDM)
?
??? 在第 6 周，您了解到被稱作快照的自動負載信息庫 (AWR)，它定期從數據庫中收集詳細的與性能相關的度量標準。每次快照后，調用 ADDM 來徹底分析源自快照間差異的數據和度量標準，然后就必要的操作提出建議。正如我早先所提及的，發現問題后，ADDM 可能會依次調用其他顧問（如 SQL Tuning Advisor），來提出改進建議。
?
??? 我將為您完全展示（而不是用文字來解釋）這種特性是如何運行的。假定您正設法診斷一個不可解釋的性能問題。在我們介紹的示例中，您知道了哪些 SQL 語句需要調整，或至少知道了哪些 SQL 語句存在問題。但是在現實生活中，您可能沒有這些有用信息。
?
??? 要在 10g 中執行診斷，您將在相關的時間間隔內選擇快照進行深入、透徹的分析。在 Enterprise Manager 10g 中，從 Database 主頁上，您將選擇 "Advisor Central"，然后單擊 "ADDM" 鏈接，它將出現一個類似于圖 1 的頁面。
?
?
圖 1：創建 ADDM 任務
?
??? 在該頁中，您可以創建 ADDM 要分析的任務。您知道性能問題發生在晚上 11 點左右，因此選擇那個時間間隔的快照，通過 "Period Start" 和 "Period End" 值進行指示。您也可以單擊照相機圖標，指示開始和終止快照的時間間隔，如此處的紅色橢圓形所示。選擇時間間隔后，按下 "OK" 按鈕，將出現一個類似于圖 2 所示的頁面。
?
?
圖 2：ADDM 查找結果
?
??? 這里 ADDM 在該時間間隔內標識了兩個關鍵的、相關的性能問題：某些 SQL 語句消耗著重要的 CPU 時間，從而使數據庫的速度顯著減慢。基于這些查找結果，ADDM 建議對圖中突出顯示的那些語句進行 SQL 調整。
?
??? 如果您單擊某條查找結果，ADDM 會顯示更多詳細信息。例如，單擊問題查找結果，將會出現一個類似于圖 3 所示的頁面。
?
?
圖 3：ADDM 查找結果的詳細信息
?
??? 在此您可以看到引發該問題的特定 SQL 語句。ADDM 建議您用 SQL Tuning Advisor 對該 SQL 語句進行徹底的分析，正如在 "Action" 部分中所提到的那樣。您可以通過單擊它旁邊的按鈕立即運行該任務，這將調用 SQL Tuning Advisor。
?
??? 在圖 2 中，您可能注意到了一個名稱為 "View Report" 的按鈕。除了在單獨的 Web 頁面中提供建議外，ADDM 還能夠創建純文本報表，以進行更快速的一步到位的分析。列表 1 顯示了在我們的示例純文本報表中提出的全面建議。注意報表是如何給出相關細節的，如所考慮的 SQL 語句、它的散列值等。可以在 Enterprise Manager 的 SQL Tuning Advisor 頁中或通過命令行將 SQL ID 用于獨立的分析。
?
??? 在收集了每一張 AWR 快照后就會調用 ADDM，因此可以查看基于相鄰快照的建議。因此，如果分析的范圍只是兩張相鄰的快照，就不必創建上面所示的 ADDM 任務。如果您想在兩張不相鄰的快照之間進行分析，就需要創建 ADDM 任務。
?
??? 記住 ADDM 所能做的遠不止這些；正如您在以前的文章中所看到的，它還提供內存管理、段管理、重作/撤消以及更多的分析和建議。由于在一篇簡短的文章中描述所有的 ADDM 功能是不可能的，在此我們只關注 SQL Tuning Advisor。現在讓我們看看它是如何工作的。
?
?
用 SQL Tuning Advisor 訪問分析
?
??? 在一個典型的運行時優化中，優化器生成一組可能的訪問路徑，并基于對象統計從中選擇出最“經濟”的路徑。但是，那時它沒有時間考慮是否能夠調整語句、統計是否陳舊、是否能夠創建索引等問題。相反，SQL Tuning Advisor 可以執行這種“專家系統”類型的思考。實質上，優化器能夠回答的問題是：“基于可用的資源,獲得結果的最佳方式是什么？”而 SQL Tuning Advisor 能夠回答的問題是：“基于用戶的需求，還需要做些什么來增強性能？”
?
??? 正如您可能預期的那樣，這種“思考”消耗了資源（如 CPU）；因此 SQL Tuning Advisor 在調整模式期間處理 SQL 語句，該模式可以在非高峰時間運行。在創建調整任務時，通過在函數中設置 SCOPE 和 TIME 參數來指示這種模式。在數據庫活動少的期間運行調整模式是一個好方法，以使常規用戶相對不受影響，稍后再進行分析。
?
??? 這個概念可以通過示例很好地解釋。就看看如下所示的開發人員引起您注意的那個查詢事例吧。
?
select account_no from accounts where old_account_no = 11
?
??? 該語句調整起來并不難，但是為了更容易說明問題，假定它很難調整。激發顧問的方式有兩種：使用 Enterprise Manager 或簡單明了的命令行。
?
??? 首先，讓我們看看如何在命令行中使用它。我們通過調用提供的包 dbms_sqltune 來調用顧問。
?
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;
/
?
??? 這個包創建并執行了一個名為 FOLIO_COUNT 的調整任務。接下來，您將需要查看任務執行的結果（也就是說，查看建議）。
?
set serveroutput on size 999999
set long 999999
select dbms_sqltune.report_tuning_task ('FOLIO_COUNT') from dual;
???
列表
2
顯示的是輸出結果。仔細查看這些建議；顧問說您可以通過在 OLD_ACCOUNT_NO 列上創建一個索引來改進性能。更佳的是，如果創建索引，顧問計算了查詢成本，從而使潛在的節省量變得更加可定義、更加具體。
?
??? 當然，考慮到本示例的簡單性，通過手動檢查也能得到這種結論。但是，可以想象出該工具對于那些更復雜的查詢十分有用，因為對這些查詢執行手動檢查也許是不可能的或不實際的。
?
中級調整：查詢重構
?
??? 假定查詢更復雜：
?
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);
?
??? 顧問建議如下：
?
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.
?
??? 這一次顧問不會建議任何結構上的更改（如索引），但會通過用 NOT EXISTS 取代 NOT IN的方式很聰明地猜測到調整查詢的正確方式。由于兩種構造相似但不相同，顧問給出了這種改變的基本原理，并把決定權留給 DBA 或應用程序開發人員，由他們決定該建議是否對環境有效。
?
?
高級調整：SQL 配置文件
??? 正如您可能知道的，優化器通過檢查查詢中引用的對象統計，來對查詢執行計劃作出決定，然后計算成本最低的方法。如果查詢涉及到一張表以上這種典型情況，優化器就會通過檢查所有引用對象的統計來計算成本最低的選項 — 但是它并不知道對象間的關系。
??? 例如，假定一個 DELINQUENT 狀態的帳戶擁有不到 1000 美元的余額。如果謂詞帶有一個只篩選 DELINQUENT 的子句，那么連接 ACCOUNTS 表和 BALANCES 表的查詢的報告行將較少。優化器不知道這種復雜關系 — 但是顧問知道；它從數據中“組合”了這種關系，并將其以 SQL 配置文件的形式存儲起來。通過訪問該 SQL 配置文件，優化器不但知道了表的數據分布，而且知道了這些表之間的數據相關系。這個附加信息可以讓優化器生成一個高級執行計劃，從而獲得一個優良的查詢。
??? SQL 配置文件通過手動將查詢提示添加到代碼中，而無需調整 SQL 語句。因此，SQL Tuning Advisor 使無需修改代碼即可調整打包的應用程序成為可能且極具優勢。
?
??? 要點在于，與對象統計不同的是，SQL 配置文件映射到一個查詢，而不是映射到一個或多個對象。涉及到同樣兩個表（ACCOUNTS 和 BALANCES）的另一個查詢可能具有不同的配置文件。使用查詢中的這種元數據信息，Oracle 就能夠改進性能。
?
??? 如果能夠創建一個配置文件，它是在 SQL Tuning Advisor 會話期間完成的，該顧問將在會話中生成配置文件并建議您“接受”。除非接受了配置文件，否則它不會被捆綁到語句上。您可以通過發出如下語句隨時接受配置文件：
?
begin
dbms_sqltune.accept_sql_profile (
task_name?? => 'FOLIO_COUNT',
name??????? => 'FOLIO_COUNT_PROFILE'
description => 'Folio Count Profile',
category??? => 'FOLIO_COUNT');
end;
?
??? 該命令將把顧問先前生成的名為 FOLIO_COUNT_PROFILE 的配置文件捆綁到一條語句上，該語句與前面示例中描述過的名為 FOLIO_COUNT 的調整任務相關聯。（注意：盡管 SQL 配置文件只能由顧問而不能由 DBA 創建，但只有您能決定何時使用它。）
?
??? 您可以在字典視圖 DBA_SQL_PROFILES 中查看創建的 SQL 配置文件。SQL_TEXT 列顯示分配給配置文件的 SQL 語句；STATUS 列指示是否啟用給該配置文件。（即使它已經捆綁到一條語句上，也必須啟用配置文件來影響執行計劃。）可以從視圖 DBMSHSXP_SQL_PROFILE_ATTR 查看配置文件的元數據信息，在該視圖中，ATTR_VALUE 列顯示了優化器用以確定計劃的多種專有信息。
?
?
使用 ADDM 和 SQL Tuning Advisor
??? 除了上面描述的三種情形外，SQL Tuning Advisor 還會標識出查詢中引用的丟失統計的任何對象。因此，顧問執行四種獨特類型的任務：
?
??? ● 檢查對象是否具有有效的、可用的統計，以進行適當優化
??? ● 試圖重新編寫性能更好的查詢并就重新編寫提出建議
??? ● 檢查訪問路徑，查看是否能夠通過添加額外的結構（如索引和物化視圖）來改進性能。
??? ● 創建 SQL 配置文件，并將它們附加到特定的查詢上。
?
??? 基于這些功能，我可以想到至少三種不同情況，將 ADDM 和 SQL Tuning Advisor 作為功能強大的工具來使用。
?
??? ●
反應式調整：您的應用程序突然運行得很糟糕。使用 ADDM，您可以把問題深入剖析到一條 SQL 語句或一組語句的層面上，如 ADDM 一節所示。根據 ADDM 的建議，您可以啟動 SQL Tuning Advisor 并矯正問題。
??? ● 積極式調整：應用程序的運行情況尚好；但是，您希望確保執行了所有必要的維護任務，并知道是否可以對查詢進行進一步調整。您將在獨立模式下激發 SQL Tuning Advisor，以標識出可能的調整選擇。
??? ● 開發式調整：在開發過程中對代碼進行測試時，相對于 QA 階段或生產階段，有許多機會可調整查詢。在開發最后確定 SQL 語句前，可以使用顧問的命令行版本對這些語句進行單獨測試。
?
?
使用 Enterprise Manager
?
??? 前一個示例被有意用來說明如何在命令行模式下使用 SQL Tuning Advisor，該模式對積極編寫這些任務的腳本非常有用。但是，在大多數情況下，您需要依據終端用戶所報告的問題執行調整。Enterprise Manager 10g 在這些情況下就可以大顯身手了。
?
??? 幾個星期以前（第 13 周），向您介紹了更新過的 Enterprise Manager 界面。這里將介紹您將如何用它來診斷和調整 SQL：從 Database 主頁單擊屏幕底部的 "Advisor Central" 鏈接，這將啟動一個包含所有顧問的頁面。接下來，單擊屏幕頂部的 "SQL Tuning Advisor"，如圖 4 所示。
?
?
圖 4：Enterprise Manager 中的 Advisor Central
?
??? 您剛才啟動了 SQL Tuning Advisor。從下一頁選擇 "Top SQL"，如圖 5 所示。
?
?
圖 5：SQL Tuning Advisors
?
??? 該操作啟動了一個類似于圖 6 所示的頁面，其中有一個包含多種等待級別的沿時間維進行跟蹤的圖表。
?
?
圖 6：頂部的 SQL 選擇器
?
??? 紅色橢圓內的灰色矩形區域把焦點置于該圖表上。用鼠標拖動該矩形，將其重新定位到 CPU 等待級別為高的位置上（如圖中所示）。該頁面的下面部分將顯示那個時間間隔內相關的 SQL 語句，如圖 7 所示。
?
?
圖 7：選擇基于活動性的 SQL 語句
?
??? 正如您可以看到的，顯示在頂部（被紅色橢圓形所包圍）的 SQL 語句活動性最高，其 CPU 消耗也最大。單擊語句 ID 查看該語句的詳細信息，這將引出一個如圖 8 所示的屏幕。
?
?
圖 8：SQL 詳細信息
?
??? 在該圖中，您可以看到在那個時間段引發 CPU 消耗的確切的 SQL 語句。您可以單擊 "Run SQL Tuning Advisor" 按鈕（在圖中作了標記）運行顧問。這將引出一個類似于圖 9 所示的屏幕。
?
?
圖 9：調度 SQL Tuning Advisor
?
??? 在顧問調度程序中，您可以確定任務的類型，以及將要做多少分析工作。例如，在上圖中，我選擇了“全面”分析和顧問將會立即運行。在顧問結束運行后，您可以查看其建議，如圖 10 所示。
?
?
圖 10：顧問建議
?
??? 我剛才描述的這個過程類似于您在命令行版本中所看到的過程；但是這個流程更反映了真實情況，在那種情況下，您對一個問題作出反應，深入剖析其原因，并接受關于如何修復它的建議。
?
?
結論
?
??? ADDM 是一個功能強大的“智能”工具，它能夠自動根據最佳實踐和經驗豐富的 Oracel 專業人員公認的方法，來分析性能度量標準和提供建議。這種功能不但能告訴 DBA 發生了什么以及為什么發生，而且最為重要的是，它還指明了下一步怎么做。
?
?
?
?
?
?
?
?