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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/40239/viewspace-621702/