How to use the Automatic Database Diagnostic Monitor

The Automatic Database Diagnostic Monitor (ADDM) is an integral part of the Oracle RDBMS capable of gathering performance statistics and advising on changes to solve any existing performance issues measured. For this it uses the Automatic Workload Repository (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. 

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');


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.

DO IT YOURSELF
The example below 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 4 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');

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

@?/rdbms/admin/addmrpt

SQL> @C:\oracle\product\10.2.0\client_1\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.

         ADDM Report for Task 'TASK_18973'
          ---------------------------------

Analysis Period
---------------
AWR snapshot range from 3006 to 3007.
Time period starts at 18-MAR-12 11.39.54 AM
Time period ends at 18-MAR-12 11.43.48 AM

Analysis Target
---------------
Database 'HOMERAC' with DB ID 2202641643.
Database version 11.2.0.3.0.
ADDM performed an analysis of instance homerac1, numbered 1 and hosted at
OR-11.

Activity During the Analysis Period
-----------------------------------
Total database time was 249 seconds.
The average number of active sessions was 1.06.

Ref:250655.1