ADDM

Automatic Database Diagnostic Monitor (ADDM)

The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks.  For each of the identified issues, it locates the root cause and provides recommendations for correcting the problem.  An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the statistics_level parameter is set to TYPICAL or ALL. The ADDM analysis includes:

• CPU Load
• Memory Usage
• I/O Usage
• Resource Intensive SQL
• Resource Intensive PL/SQL and Java
• RAC Issues
• Application Issues
• Database Configuration Issues
• Concurrency Issues
• Object Contention

The ADDM reports are much simpler to read than those of AWR or STATSPACK, making them a useful method for identifying resource intensive SQL and PL/SQL.

There are several ways to produce reports from the ADDM analysis which will be explained.
1. How to Generate ADDM through Backend:-

First log in to database trough sqlplus command prompt. You would be using is like following:


Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing opti

SQL> @?/rdbms/admin/addmrpt.sql ------------ this is script to generate addm.

One you run this you would be getting following.



Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1299593730 ORCL 1 orcl


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------

addmrpt.sql Script

The addmrpt.sql script can be used to create an ADDM report from SQL*Plus. The script is called as follows.

-- UNIX

@/u01/app/oracle/product/10.1.0/db_1/rdbms/admin/addmrpt.sql

-- Windows

@d:\oracle\product\10.1.0\db_1\rdbms\admin\addmrpt.sql

It then lists all available snapshots and prompts you to enter the start and end snapshot along with the report name.


2. Login to OEM Tool.

Home Page go to below related link-
Click on Advisor Central.

Click on ADDM link-

Custom ADDM Reports in OEM

OEM supports generation of custom ADDM reports for a specific start and stop snapshot range. To generate a custom ADDM report from the OEM ADDM page, click on the Create ADDM Task button. This will take you to the Create ADDM Task page as seen in this screen shot:

Click OK

View and Save Report.