Overview ADDM Enhancements
The Automatic Database Diagnostic Monitor, which Oracle introduced in Oracle Database 10g, analyzes the AWR data, diagnoses the root causes for performance problems, and makes recommendations for fixing those problems. The database performs an ADDM analysis on a pair of AWR snapshots, which determine the time period for the ADDM analysis. In Oracle Database 11g, the ADDM has the following new features:
- ADDM for Real Application Clusters
- New DBMS_ADDM package
- Naming Advisor Findings and Directives
- New ADDM views
ADDM for Real Application Clusters
In Oracle Database 11g, you can deploy the ADDM to perform a cluster-wide performance analysis. In addition to analyzing a single instance, you can now use the ADDM to analyze an entire Oracle Real Application Cluster (RAC). The traditional single instance–wide analysis you’re familiar with from Oracle Database 10g is called Instance ADDM and the cluster-wide mode is called Database ADDM. The cluster-wide mode is a special mode of the ADDM, in which the tool reports on the performance of the entire cluster in addition to the individual instances in the cluster. When operating in an Oracle RAC environment, you can deploy ADDM in the following three analysis modes.
- Database ADDM Analyze all instances of the RAC
- Instance ADDM Analyzes a particular instance (equivalent to the Oracle Database 10g ADDM analysis)
- Partial ADDM Analyzes a subset of the instances in the RAC
Run the ADDM in the Database analysis mode if you’re using an Oracle RAC system, to analyze performance of all instances in the database. The Database ADDM accesses the AWR data of all instances in the system and identifies critical performance problems for an entire RAC cluster. As with the single-instance ADDM that you’re familiar with from Oracle Database 10g, Database ADDM runs automatically by default when a new AWR snapshot is taken by the database. In this mode, the ADDM will add the DB time for all instances in the RAC to come up with the DB time for the database. The Database analysis mode presents the problems and recommendations for each instance in a single report, instead of your having to peruse multiple reports for the same information. In the Database mode (Database ADDM), the ADDM accesses the AWR data generated by all the instances in a RAC system to analyze the throughput performance of the entire cluster instead of any single instance in the cluster. Database ADDM performs an analysis of the following entries:
- Global resources such as global locks and global I/O usage
- High-load SQL
- Contention across the instances
- Global cache interconnect traffic
- Network latency issues
- Skew in instance response times
The ADDM will aggregate any findings across instances if they affect the entire database. If a finding pertains to a global resource such as I/O, that finding will be deemed as a global finding affecting multiple resources. On the other hand, if a finding pertains to a local resource such as a CPU-bound instance, it results in just a local finding for a single instance.
By default, Database ADDM analysis is performed automatically after each AWR snapshot. If you want, you can run the ADDM in the partial analysis mode by having the ADDM analyze only a subset of the instances in the cluster. Database ADDM is mainly targeted for use by DBAs so they can test the cluster performance as a whole, whereas Instance ADDM is more useful for application development to test application or system changes.
Automatic database diagnostic monitoring is enabled by default. You can control automatic database diagnostic monitoring by setting the control_management_ pack_access parameter, which has the default value diagnostic+tuning. You must specify either the value diagnostic or the value diagnostic+tuning (default value) to enable the ADDM. If you set the value to none, you disable the ADDM. Of course, you must also ensure that the initialization parameter statistics_level is set to either typical or all (but not basic) to enable automatic database diagnostic monitoring.
New DBMS_ADDM Package
Oracle Database 11g introduces the DBMS_ADDM package to facilitate the managing of the ADDM. You can use the DBMS_ADDM package to create an ADDM task and view the results. The following list offers a brief description of the important procedures and functions of the DBMS_ADDM package:
- ANALYZE_DB Creates a global ADDM task
- ANALYZE_INST Creates an instance ADDM task
- ANALYZE_PARTIAL Creates an ADDM task to analyze a set of instances
- DELETE Deletes an ADDM task
- GET_REPORT Gets a text report of an ADDM task
The following example shows how to create and execute a database ADDM task for an Oracle RAC configuration:
SQL> begin
2 :tname := 'Test ADDM Run1';
3 dbms_addm.analyze_db(:tname,1664,1665);
4* end;
In the example, I use the ANALYZE_DB procedure to create a global ADDM task that pertains to all instances in an Oracle RAC configuration. The numbers 1664 and 1665 are specified as values for the begin_snapshot and end_snapshot parameters for the ADDM analysis.
To get the ADDM report, use the DBMS_ADDM.GET_REPORT function, as shown here:
SET LONG 100000
SET PAGESIZE 50000
1* select dbms_addm.get_report(:tname) from dual;
DBMS_ADDM.GET_REPORT(:TNAME)
---------------------------------------------------------------
ADDM Report for Task 'Test ADDM Run3'
-------------------------------------
AWR snapshot range from 1664 to 1665.
Time period starts at 10-NOV-07 03.00.04 PM
Time period ends at 10-NOV-07 04.00.12 PM
Analysis Target
---------------
Database 'ORCL2' with DB ID 611115374.
Database version 11.1.0.6.0.
ADDM performed an analysis of instance orcl2,
numbered 1 and hosted at localhost.localdomain.
...
Naming Advisor Findings and Directives
Oracle Database 11g classifies and names all ADDM advisor findings. The database stores the ADDM findings in the DBA_ADVISOR_FINDINGS and the USER_ ADVISOR_FINDINGS views. The classification of ADDM findings enables you to query the DBA_ADVISOR_FINDINGS view to find which findings occur most frequently in the database. You can query the new DBA_ADVISOR_FINDING_ NAMES view to see all the finding names, as shown here:
SQL> select finding_name from dba_advisor_finding_names;
FINDING_NAME
------------------------------
normal, successful completion
"Administrative" Wait Class
"Application" Wait Class
"Cluster" Wait Class
"Concurrency" Wait Class
"
...80 rows selected.
In Oracle Database 11g, you can create an ADDM task by inserting a finding directive to limit or filter the findings. The DBMS_ADDM package contains several “directive” procedures to add specific directives to create directives of various kinds,such as INSERT_FINDING_ DIRECTIVE procedure.This procedure creates a directive to limit reporting of a specific finding type. The directive can be created for a specific task , or for all subsequently created ADDM tasks (such as a system directive).
For example, the following code shows how to use the INSERT_FINDING_ DIRECTIVE procedure to stipulate that the ADDM report show an “Undersized SGA” finding only if it meets two specific conditions: The first condition specifies that the finding must be responsible for at least two average active sessions during the analysis period (MIN_ACTIVE_SESSIONS), and the second condition specifies that the finding must cover at least 10 percent of the total database time during the same period (MIN_PERC_IMPACT).
For min_active_sessions and min_perc_impact ,please refer to following explanation.
- min_active_sessions: Minimal number of active sessions for the finding. If a finding has less than this number, it is filtered from the ADDM result.
- min_perc_impact: Minimal number for the "percent impact" of the finding relative to total database time in the analysis period. If the finding's impact is less than this number, it is filtered from the ADDM result.
SQL> var tname varch2(60);
SQL> begin
dbms_addm.insert_finding_directive(NULL,
'SGA Directive',
Undersized SGA',
2,
10);
:tname := 'Test ADDM Task';
dbms_addm.analyze_inst(:tname,1634,1635);
end;
/
In addition to the INSERT_FINDING_DIRECTIVE illustrated here, which helps you create a directive to limit the reporting of a specific finding type, you can also use the following ADDM directives:
INSERT_SQL_DIRECTIVE: Creates a directive to limit reporting of actions on specific SQL statements
INSERT_SEGMENT_DIRECTIVE: Creates a directive to prevent the ADDM from creating actions to run the Segment Advisor on certain segments
INSERT_PARAMETER_DIRECTIVE: Creates a directive to prevent the ADDM from creating actions that alter the value of a specific system parameter
You can delete any of the four INSERT_* procedures by replacing the INSERT with DELETE at the beginning of the procedure name. For example, you can execute the DELETE_FINDING_DIRECTIVE procedure to delete a finding directive you created through the INSERT_FINDING_DIRECTIVE procedure.
Note:the above procedure can only be invoked when the task is in INITIAL status
New ADDM Views
Oracle Database 11g introduces the following new ADDM views:
- DBA_ADDM_TASKS Shows all executed ADDM tasks
- DBA_ADDM_INSTANCES Shows instance-level information for all completed ADDM tasks
- DBA_ADDM_FINDINGS An extension of the corresponding advisor view
- DBA_ADVISOR_FINDING_NAMES Provides a list of all registered finding names
Each of the four views shown here also has a corresponding USER_* view associated with it. In addition, the DBA_ADVISOR_FINDINGS, DBA_ADVISOR_ RECOMMENDATIONS, and DBA_ADVISOR_ACTIONS views have a new column named FILTERED, which shows if a row in the view was filtered out by a directive. If the FILTERED column shows a value of Y, it means that row was filtered out by a directive or directives. A value of N means the row wasn’t filtered.
参考至:《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》
《Oracle® Database PL/SQL Packages and Types Reference 11g Release 2 (11.2)》
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_addm.htm#ARPLS65061
本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:czmcj@163.com
相关推荐
ORACLE数据库(11g或10g)AWR与ADDM报告收集方法ORACLE数据库(11g或10g)AWR与ADDM报告收集方法
oracle ADDM 自动诊断 监视 工具
Linux下生成oracle awr和addm的数据库脚本。通过生产环境测试。
ADDM:automatic database diannose management ,自动数据库诊断管理。
oracle ADDM自动诊断监视工具 oracle ADDM自动诊断监视工具
Oracle11gR2数据库新特性: 自动内存管理 ADDM for RAC 数据卫士( Dataguard )技术革新 闪回技术 闪回存档 RAC和ASM RAC One Node ASM 数据安全 Database Vault Audit Vault 高级压缩 OLAP表压缩 LOB字段压缩 ...
Oracle数据库10g提供了一组集成的自我管理功能,可以在不受工作负载影响的情况下,简化管理、提高效率以及降低与系统管理相关的成本。本白皮书论述了Oracle新性能诊断和监控技术的基础架构和部件,该技术内置于...
Oracle+10G+最佳20位新特性:SQL+Advisor+和+ADDM
Oracle Database 10 g :“g”代表网格 1-6 Oracle 数据库体系结构 1-8 数据库结构 1-9 Oracle 内存结构 1-10 进程结构 1-12 Oracle 实例管理 1-13 服务器进程和数据库缓冲区高速缓存 1-14 物理数据库结构 1-...
AWR 实质上是一个 Oracle 的内置工具,它采集与性能相关的统计数据,并从那些统计数据中导出性能量度,以跟踪潜在的问题。与 Statspack 不同,快照由一个称为 MMON 的新的后台进程及其从进程自动地每小时采集一次。...
9.5.7 Oracle 10g/11g Latch机制的变化 467 第10章 性能诊断与SQL优化 471 10.1 使用AUTOTRACE功能辅助SQL优化 471 10.1.1 AUTOTRACE功能的启用 471 10.1.2 Oracle 10g AUTOTRACE功能的增强 473 10.1.3 ...
Advisor 来确定其大小,自动数据库诊断监视器 (ADDM):执行自上而下的实例分析,确定问 题和潜在的原因,并提供修复问题的建议案。ADDM 可潜在地调用其他指导。SGA 指导(Memory Advisor):根据系统全局区(SGA) 中...
在 Oracle 数据库 10g 中,等待界面经过了彻底的重新设计,从而只需更少的 DBA 干预即可提供更多的信息。在本文中,我们将浏览这些新的特性,并了解它们如何帮助我们诊断性能问题。对于大多数性能问题,您可以从自动...
在 Oracle 数据库 10g 中,等待界面经过了彻底的重新设计,从而只需更少的 DBA 干预即可提供更多的信息。在本文中,我们将浏览这些新的特性,并了解它们如何帮助我们诊断性能问题。对于大多数性能问题,您可以从自动...
本书以Oracle 10g为基础,由浅入深、从易到难,详细介绍了DBA职位所要求的知识结构和实战技能。第1章至第4章是基础篇,包括数据库建模、Oracle体系结构、网络结构、备份恢复和使用OEM,这些对于刚刚从事DBA或者试图...
对oracle有需求的,准备考OCP的朋友们,可以讲视频下载看看。
$ORACLE_HOME/rdbms/admin目录下awr,addm,ash相关脚本功能分析
关于Oracle性能优化的相关工具的介绍,如AWR,ASH,ADDM等的详细原理介绍及实践使用,非常有用