The Oracle Automatic Database Diagnostic Monitor (ADDM) is a utility which automatically detects and reports performance issues.  ADDM is based on snapshots take by AWR (Automatic Workload Repository). This data is analyzed and displayed as ADDM findings on Database Home Page of EM.   The DBMS_ADDM package can execute ADDM reports outside of EM but the user executing DBMS_ADDM must have ADVISOR privilege.  ADDM allows administrators to quickly identify performance problems which may not be readily noticeable.  Every ADDM finding will have one or more recommendations to reduce the impact.

 

AWR takes snapshots are taken once an hour and stored for 8 days by default.  It is recommended that snapshots are stored for 30 days.  A new snapshot will result in an ADDM analysis being performed with the following steps:

 

1. Locates the root cause of performance issue.
2. Provides recommendation to correct.
3. Quantifies the expected benefits.
4. Identifies areas where no action is needed.

 

ADDM analysis is done top down through DB time statistics this is an iterative process and once one problem is solved another will become the bottleneck.  ADDM recommendation may include:

 

1. Hardware changes – add CPU, memory, change I/O subsystem.
2. Database configuration – change init parameters.
3. Schema changes – could include hash partitioning table or index or using ASSM automatic segment space management.
4. Application changes – cache options or use of bind variables.
5. Using other advisors – Running SQL Tuning Advisor or Segment Advisor.

 

ADDM is enabled in database by default and controlled by init parameters CONTROL_MANAGEMENT_PACK_ACCESS.    ADDM is enabled when CONTROL_MANAGEMENT_PACK_ACCESS parameter is set to DIAGNOSTIC+TUNING (default) or DIAGNOSTIC.    ADDM is disabled when CONTROL_MANAGEMENT_PACK_ACCESS is setting to NONE.

Additionally the initialization parameter of STATISTICS_LEVEL must be set to TYPICAL (default) or ALL to enable ADDM, but if it is set to BASIC ADDM will be disable.

 

The parameter DBIO_EXPECTED of the DBMS_ADVISOR package is required to perform analysis of I/O performance. It is set to 7000 microseconds with the following package call:

 

EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(‘ ADDM’, ‘DBIO_EXPECTED’, 7000);

 

An ADDM analysis results come in one of three classes:

• PROBLEM – root cause of database performance issues.
• SYMPTOM – finding that’s info leads to one or more problem findings.
• INFORMATION – Findings that are used to report areas of system that do not have performance issues.

 

If one problem has multiple findings, ADDM may report them separately.   Problem will be shown largest impact first.

 

Larry Catt

OCP