How to use the Automatic Database Diagnostic Monitor (ADDM)

Applies to:

Oracle Server - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.

Purpose

The purpose of this article is to show an introduction on how to use the Automatic Database Diagnostic Monitor feature. The ADDM consists of functionality built into the Oracle kernel to assist in making tuning an Oracle instance less elaborate.

Please Note: The use of this feature requires a license to use the Oracle Diagnostic and the Oracle Tuning Packs

Scope

Audience: Oracle Developers and DBAs
Use: Using the Automatic Database Diagnostic Monitor feature as a first step in the creation of an autotunable database
Level of detail : Medium

Details

The Automatic Database Diagnostic Monitor (hereafter called ADDM) is an integral part of the Oracle RDBMS capable of gathering performance statistics and advising on changes to solve any exitsing performance issues measured.

For this it uses the Automatic Workload Repository (hereafter called AWR), a repository defined in the database to store database wide usage statistics at fixed size intervals (60 minutes).

To make use of ADDM, a PL/SQL interface called DBMS_ADVISOR has been implemented. This PL/SQL interface may be called directly through the supplied $ORACLE_HOME/rdbms/admin/addmrpt.sql script or used in combination with Oracle Enterprise Manager.

A number of views (with names starting with the DBA_ADVISOR_ prefix)  allow retrieval of the results of any actions performed with the DBMS_ADVISOR API. The preferred way of accessing ADDM is through the Enterprise Manager interface, as it shows a complete performance overview
including recommendations on how to solve bottlenecks on a single screen. When accessing ADDM manually, you should consider using the ADDMRPT.SQL script provided with your Oracle release, as it hides the complexities involved in accessing the DBMS_ADVISOR package.

To use ADDM for advising on how to tune the instance and SQL, you need to make sure that the AWR has been populated with at least 2 sets of performance data. When STATISTICS_LEVEL is set to TYPICAL or ALL the database will automatically schedule the AWR to be populated. Default for populating the AWR is 60 minute.
This default can be changed by executing:

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( interval => 30);

When you wish to create performance snapshots outside of the fixed intervals, then DBMS_WORKLOAD_REPOSITORY package can also be used:

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL');

For more information see:
Managing the Automatic Workload Repository


The snapshots need be created before and after the action you wish to examine For example, when examining a bad performing query, you need to have performance data snapshots from the timestamps before the query was started and after the query completed.

Example:
You can use ADDM through the PL/SQL API and query the various advisory views in SQL*Plus to examine how to solve performance issues.

The example is based on the SCOTT account executing the various tasks.
To allow SCOTT to both generate AWR snapshots and submit ADDM recommendation jobs, the user will need t be granted the following:

CONNECT / AS SYSDBA
GRANT ADVISOR TO scott;
GRANT SELECT_CATALOG_ROLE TO scott;
GRANT EXECUTE ON dbms_workload_repository TO scott;

The example presented makes use of a table called BIGEMP, residing in the SCOTT schema. The table (containing about 14 million rows) has been created as follows:

CONNECT scott/tiger
CREATE TABLE bigemp AS SELECT * FROM emp;
ALTER TABLE bigemp MODIFY (empno NUMBER);
DECLARE
n NUMBER;
BEGIN
   FOR n IN 1..18
   LOOP
     INSERT INTO bigemp SELECT * FROM bigemp;
   END LOOP;
   COMMIT;
END;
/
UPDATE bigemp SET empno = ROWNUM;
COMMIT;

The next step is to generate a performance data snapshot:

EXECUTE dbms_workload_repository.create_snapshot('TYPICAL');

Execute a query on the BIGEMP table to generate some load:

SELECT * FROM bigemp WHERE deptno = 10;

After this, generate a second performance snapshot:

EXECUTE dbms_workload_repository.create_snapshot('TYPICAL');

The simplest way to get the ADDM report is by executing:

@?/rdbms/admin/addmrpt

Running this script will show which snapshots have been generated, asks for the snapshot IDs to be used for generating the report, and will generate the report containing the ADDM findings.

DETAILED ADDM REPORT FOR TASK 'SCOTT_ADDM' WITH ID 5
----------------------------------------------------

Analysis Period: 17-NOV-2003 from 09:50:21 to 10:35:47
Database ID/Instance: 494687018/1
Snapshot Range: from 1 to 3
Database Time: 4215 seconds
Average Database Load: 1.5 active sessions

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


FINDING 1: 65% impact (2734 seconds)
------------------------------------
PL/SQL execution consumed significant database time.

RECOMMENDATION 1: SQL Tuning, 65% benefit (2734 seconds)
ACTION: Tune the PL/SQL block with SQL_ID fjxa1vp3yhtmr. Refer to
the "Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL
User's Guide and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID fjxa1vp3yhtmr
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;

FINDING 2: 35% impact (1456 seconds)
------------------------------------
SQL statements consuming significant database time were found.

RECOMMENDATION 1: SQL Tuning, 35% benefit (1456 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
gt9ahqgd5fmm2.
RELEVANT OBJECT: SQL statement with SQL_ID gt9ahqgd5fmm2 and
PLAN_HASH 547793521
UPDATE bigemp SET empno = ROWNUM

FINDING 3: 20% impact (836 seconds)
-----------------------------------
The throughput of the I/O subsystem was significantly lower than expected.

RECOMMENDATION 1: Host Configuration, 20% benefit (836 seconds)
ACTION: Consider increasing the throughput of the I/O subsystem.
Oracle's recommended solution is to stripe all data file using
the SAME methodology. You might also need to increase the
number of disks for better performance.

RECOMMENDATION 2: Host Configuration, 14% benefit (584 seconds)
ACTION: The performance of file
D:\ORACLE\ORADATA\V1010\UNDOTBS01.DBF was significantly worse
than other files. If striping all files using the SAME
methodology is not possible, consider striping this file over
multiple disks.
RELEVANT OBJECT: database file
"D:\ORACLE\ORADATA\V1010\UNDOTBS01.DBF"

SYMPTOMS THAT LED TO THE FINDING:
Wait class "User I/O" was consuming significant database time.
(34% impact [1450 seconds])

FINDING 4: 11% impact (447 seconds)
-----------------------------------
Undo I/O was a significant portion (33%) of the total database I/O.

NO RECOMMENDATIONS AVAILABLE

SYMPTOMS THAT LED TO THE FINDING:
The throughput of the I/O subsystem was significantly lower than
expected. (20% impact [836 seconds])
Wait class "User I/O" was consuming significant database time.
(34% impact [1450 seconds])

FINDING 5: 9.9% impact (416 seconds)
------------------------------------
Buffer cache writes due to small log files were consuming significant
database time.

RECOMMENDATION 1: DB Configuration, 9.9% benefit (416 seconds)
ACTION: Increase the size of the log files to 796 M to hold at
least 20 minutes of redo information.

SYMPTOMS THAT LED TO THE FINDING:
The throughput of the I/O subsystem was significantly lower than
expected. (20% impact [836 seconds])
Wait class "User I/O" was consuming significant database time.
(34% impact [1450 seconds])

FINDING 6: 9.2% impact (387 seconds)
------------------------------------
Individual database segments responsible for significant user I/O wait
were found.

RECOMMENDATION 1: Segment Tuning, 7.2% benefit (304 seconds)
ACTION: Run "Segment Advisor" on database object "SCOTT.BIGEMP"
with object id 49634.
RELEVANT OBJECT: database object with id 49634
ACTION: Investigate application logic involving I/O on database
object "SCOTT.BIGEMP" with object id 49634.
RELEVANT OBJECT: database object with id 49634

RECOMMENDATION 2: Segment Tuning, 2% benefit (83 seconds)
ACTION: Run "Segment Advisor" on database object
"SYSMAN.MGMT_METRICS_RAW_PK" with object id 47084.
RELEVANT OBJECT: database object with id 47084
ACTION: Investigate application logic involving I/O on database
object "SYSMAN.MGMT_METRICS_RAW_PK" with object id 47084.
RELEVANT OBJECT: database object with id 47084

SYMPTOMS THAT LED TO THE FINDING:
Wait class "User I/O" was consuming significant database time.
(34% impact [1450 seconds])

FINDING 7: 8.7% impact (365 seconds)
------------------------------------
Individual SQL statements responsible for significant physical I/O were
found.

RECOMMENDATION 1: SQL Tuning, 8.7% benefit (365 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
gt9ahqgd5fmm2.
RELEVANT OBJECT: SQL statement with SQL_ID gt9ahqgd5fmm2 and
PLAN_HASH 547793521
UPDATE bigemp SET empno = ROWNUM

RECOMMENDATION 2: SQL Tuning, 0% benefit (0 seconds)
ACTION: Tune the PL/SQL block with SQL_ID fjxa1vp3yhtmr. Refer to
the "Tuning PL/SQL Applications" chapter of Oracle's "PL/SQL
User's Guide and Reference"
RELEVANT OBJECT: SQL statement with SQL_ID fjxa1vp3yhtmr
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;

SYMPTOMS THAT LED TO THE FINDING:
The throughput of the I/O subsystem was significantly lower than
expected. (20% impact [836 seconds])
Wait class "User I/O" was consuming significant database time.
(34% impact [1450 seconds])

FINDING 8: 8.3% impact (348 seconds)
------------------------------------
Wait class "Configuration" was consuming significant database time.

NO RECOMMENDATIONS AVAILABLE

ADDITIONAL INFORMATION: Waits for free buffers were not consuming
significant database time.
Waits for archiver processes were not consuming significant
database time.
Log file switch operations were not consuming significant database
time while waiting for checkpoint completion.
Log buffer space waits were not consuming significant database
time.
High watermark (HW) enqueue waits were not consuming significant
database time.
Space Transaction (ST) enqueue waits were not consuming
significant database time.
ITL enqueue waits were not consuming significant database time.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ADDITIONAL INFORMATION
----------------------

An explanation of the terminology used in this report is available when
you run the report with the 'ALL' level of detail.

The analysis of I/O performance is based on the default assumption that
the average read time for one database block is 5000 micro-seconds.

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 "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class "Network" was not consuming significant database time.
Wait class "Scheduler" was not consuming significant database time.
Wait class "Other" was not consuming significant database time.

============================= END OF ADDM REPORT ======================

ADDM points out which events cause the performance problems to occur and suggests directions to follow to fix these bottlenecks. The ADDM recommendations show amongst others that the query on BIGEMP needs to be examined; in this case it suggests to run the Segment Advisor to check whether the data segment is fragmented or not; it also advices to check the application logic involved in accessing the BIGEMP table. Furthermore, it shows the system suffers from I/O problems (which is in this example caused by not using SAME and placing all database files on a single disk partition).

The findings are sorted descending by impact: the issues causing the greatest performance problems are listed at the top of the report. Solving these issues will result in the greatest performance benefits. Also, in the last section of the report ADDM indicates the areas that are not representing a problem for the performance of the instance

In this example the database is rather idle. As such the Enterprise Manager notification job (which runs frequently) is listed at the top. You need not worry about this job at all.

Please notice that the output of the last query may differ depending on what took place on your database at the time the ADDM recommendations were generated.