10G新特性系列： ADDM 的報告（一）

kewin發表於2009-12-04

ADDM 的報告
2009-12-2
ADDM 是的Automatic Database Diagnostic Monitor 的簡稱.是在內嵌到10G核心的工具。使用這個工具需要購買單獨的license，具體可以參考：
http://download.oracle.com/docs/cd/B28359_01/license.111/b28287/toc.htm
ADDM負責收集效能統計資料，並提供解決任何效能問題的建議。收集的統計資料會存放在AWR 倉庫中，其實我認為ADDM是使用了AWR收集的快照資訊，然後通過一推策略來探測系統是否存在瓶頸，並根據ORACLE多年的優化經驗來提供建議。
為了使用ADDM,一個新的名叫DBMS_ADVISOR  程式包被開發出來。這個PL/SQL  介面可以被指令碼或者EM節目來呼叫。一組以DBA_ADVISOR開頭的檢視開始服務於10G：

SQL> select table_name from dict where table_name like '%DBA_ADVISOR%';
TABLE_NAME
------------------------------
DBA_ADVISOR_ACTIONS
DBA_ADVISOR_COMMANDS
DBA_ADVISOR_DEFINITIONS
DBA_ADVISOR_DEF_PARAMETERS
DBA_ADVISOR_DIRECTIVES
DBA_ADVISOR_FINDINGS
DBA_ADVISOR_JOURNAL
DBA_ADVISOR_LOG
DBA_ADVISOR_OBJECTS
DBA_ADVISOR_OBJECT_TYPES
DBA_ADVISOR_PARAMETERS
DBA_ADVISOR_PARAMETERS_PROJ
DBA_ADVISOR_RATIONALE
DBA_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_SQLA_REC_SUM
DBA_ADVISOR_SQLA_WK_MAP
DBA_ADVISOR_SQLA_WK_STMTS
DBA_ADVISOR_SQLW_COLVOL
DBA_ADVISOR_SQLW_JOURNAL
DBA_ADVISOR_SQLW_PARAMETERS
DBA_ADVISOR_SQLW_STMTS
DBA_ADVISOR_SQLW_SUM
DBA_ADVISOR_SQLW_TABLES
DBA_ADVISOR_SQLW_TABVOL
DBA_ADVISOR_SQLW_TEMPLATES
DBA_ADVISOR_TASKS
DBA_ADVISOR_TEMPLATES
DBA_ADVISOR_USAGE
28 rows selected.
設定引數statistics_level 為BASIC,會禁止ADDM REPORT 自動執行.為了讓能夠執行ADDM,一般都是設定 statistics_level 為TYPICALL或者ALL，
還有AWR中至少有兩組效能的資料。沒有資料，怎麼比較、怎麼去分析呢？
預設情況是AWR收集後，ADDM會自動執行。可以通過隱含引數來禁止自動執行。
通過下面的指令碼來檢視最近一次的ADDM執行情況： 
SELECT dbms_advisor.GET_TASK_REPORT(task_name)
FROM dba_advisor_tasks
WHERE task_id = (
SELECT max(t.task_id)
FROM dba_advisor_tasks t,
dba_advisor_log l
WHERE t.task_id = l.task_id AND
t.advisor_name = 'ADDM' AND
l.status = 'COMPLETED');
檢視所有ADDM指令碼執行的情況： 
SELECT dbms_advisor.GET_TASK_REPORT(task_name)
FROM dba_advisor_tasks
WHERE task_id in  (
SELECT t.task_id
FROM dba_advisor_tasks t,
dba_advisor_log l
WHERE t.task_id = l.task_id AND
t.advisor_name = 'ADDM' AND
l.status = 'COMPLETED');
如果要手工觸發跑ADDM，可以執行指令碼： SQL> @?/rdbms/admin/addmrpt ADDM 報表比AWR報表多的模組： 
Excessive logon/logoff
PL/SQL, Java time
Checkpointing causes
Locks and ITL contention
RAC service issues
Hot blocks and objects w/SQL
Memory undersizing
部分ADDM的截圖： 
 FINDING 1: 28% impact (97 seconds)
----------------------------------
Individual database segments responsible for significant user I/O wait were found.
   RECOMMENDATION 1: Segment Tuning, 28% benefit (97 seconds)
      ACTION: Run "Segment Advisor" on TABLE "SCOTT.T" with object id 51924.
         RELEVANT OBJECT: database object with id 51924
      ACTION: Investigate application logic involving I/O on TABLE "SCOTT.T" with object id 51924.
         RELEVANT OBJECT: database object with id 51924
      RATIONALE: The SQL statement with SQL_ID "5328shb1qxs6u" spent
         significant time waiting for User I/O on the hot object.
         RELEVANT OBJECT: SQL statement with SQL_ID 5328shb1qxs6u
         insert into a select * from t
   SYMPTOMS THAT LED TO THE FINDING:
      Wait class "User I/O" was consuming significant database time. (30%  impact [105 seconds])

ADDITIONAL INFORMATION
----------------------
Wait class "Administrative" was not consuming significant database time.
Wait class "Application" was not consuming significant database time.
Wait class "Cluster" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
檢視 addmrpt.sql 指令碼來檢視報表到底是如何執行的？
ADDM 指令碼是執行 dbms_advisor 包來完成報表的收集。這就驗證dbms_advisor 是為ADDM服務的。而AWR的報表是通過dbms_workload_repository包來實現。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/40239/viewspace-621702/，如需轉載，請註明出處，否則將追究法律責任。

相關文章