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.