ADDM (Automatic Database Diagnostic Monitor)

As an Oracle DBA, you know how difficult it is sometimes to figure out why your database performance isn’t up. You may need to hunt down the causes, but too often, you are likely to mistake the symptoms for the causes. Correctly diagnosing performance problems is the key to efficient performance tuning. 

Traditionally, organizations have spent considerable amounts of effort on performance tuning, which usually tends to be quite laborious and not an exact science. Oracle Database 10g changes all that. It provides you with very powerful and accurate automatic Performance-tuning capabilities. The heart of the new automatic performance tuning is the new statistics collection facility, the Automatic Workload Repository (AWR), which automatically saves crucial performance information in the new mandatory SYSAUX tablespace. 

By default, the AWR collects new performance statistics in the form of a snapshot on an hourly basis and saves the snapshots for seven days before purging them. These snapshots of database activity include resource-intensive SQL statements. The Automatic Database Diagnostic Monitor (ADDM) runs automatically every hour, after the AWR takes a new snapshot. The ADDM uses the AWR performance snapshots to locate the root causes for poor performance and provides recommendations for improving performance. 

Oracle manages the ADDM with the help of a brand-new background process in Oracle Database 10g databases: the MMON. The MMON process schedules the automatic running of the ADDM. Each time the AWR takes a snapshot (every 60 minutes, by default), the MMON process asks the ADDM to analyze the interval between the last two snapshots it gathered. This is the default behavior of the ADDM performance analysis. Where does the ADDM store its analysis results? Not surprisingly, the ADDM stores the results in the AWR itself. 

The ADDM analyzes the AWR snapshots periodically and comes up with performance recommendations, usually quantified in terms of expected benefit of various actions. Following are some of the key problems that the ADDM diagnoses:

Configuration issues

Improper application usage

Expensive SQL statements

I/O performance issues

Locking issues

Excessive parsing

CPU bottlenecks

Undersized memory allocation

Connection management issues, such as excessive logon/logoff statistics


Benefits Provided by the ADDM

The ADDM bases its recommendations on a holistic approach, with time spent on database activities as its main focus. Here are some of the important benefits of using the ADDM:

It identifies the root causes of performance problems, instead of merely focusing on the symptoms. The ADDM will automatically capture highly resource-intensive SQL statements.

It produces automatic performance diagnostic reports at periodic intervals.

You’ll experience very little performance overhead when using the tool typical ADDM analysis takes only three or four seconds.

The ADDM points out nonproblem areas, so you don’t waste your efforts poking around in areas with little bang for the buck.


Taking  ADDM Report:

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\addmrpt.sql;


Current Instance


   DB Id    DB Name      Inst Num Instance

----------- ------------ -------- ------------

  115495086 DBA12               1 dba12


Instances in this Workload Repository schema


   DB Id     Inst Num DB Name      Instance     Host

------------ -------- ------------ ------------ ------------

* 115495086         1 DBA12        dba12        NAZMUL


Using  115495086 for database Id

Using          1 for instance number



Specify the number of days of snapshots to choose from


Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.


Listing the last 3 days of Completed Snapshots



Instance     DB Name        Snap Id    Snap Started    Level

------------ ------------ --------- ------------------ -----

dba12        DBA12              139 01 Jun 2010 16:55      1


                                140 01 Jun 2010 18:07      1


                                141 01 Jun 2010 20:34      1


                                142 01 Jun 2010 22:00      1


                                143 02 Jun 2010 10:05      1


                                144 03 Jun 2010 09:37      1

                                145 03 Jun 2010 11:01      1

                                146 03 Jun 2010 12:00      1

                                147 03 Jun 2010 13:00      1



Specify the Begin and End Snapshot Ids


Enter value for begin_snap: 140

Begin Snapshot Id specified: 140


Enter value for end_snap: 147

End   Snapshot Id specified: 147


Specify the Report Name


The default report file name is addmrpt_1_140_147.txt.  To use this name,

press <return> to continue, otherwise enter an alternative.


Enter value for report_name: check.txt


Using the report name check.txt


Running the ADDM analysis on the specified pair of snapshots ...


Generating the ADDM report for this analysis ...


Generate a ADDM Report:

  2. SQL>exec dbms_workload_repository. Create_snapshot;
  3. SQL> CREATE TABLE addmtest AS SELECT * FROM all_objects;
  4. Execute this script:

SQL> begin

           for i  in 1..15 Loop

           insert into addmtest

           select * from all_objects;

           delete from addmtest;

          end loop ;

          commit ;


  1. SQL>exec dbms_workload_repository. Create_snapshot;
  2. @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\addmrpt.sql;


You can check in the report some problem (Ex: I/O Wait) and recommendations.


Note: If you get no problem then force more activity in step 4.