26 | 06 | 2017
Latest Articles
Popular Articles

Performance Tuning

Automatic Database Diagnostic Monitoring (ADDM) reports

User Rating:  / 0
PoorBest 

ADDM Reports

 

The Automatic Database Diagnostic Monitor - ADDM - is an advisor which detects problem area' s in the database and which gives recommendations. ADDM uses the statistical data from the AWR - Automatic Workload Repository - stored in the sysaux tablespace. By default the statistical data is gathered / written to disk by snapshots every hour and kept in the sysaux tablespace for 7 days, assuming you haven' t set the instance parameter statistics_level to basic. ( default = typical ). Please note you can also use

1. AWR Reports which are an equivalent of the statspack reports from previous releases.  ( AWR reports give even more detailled information ).

2. ASH Reports. The active session history is usefull for analysis of recent database performance events.

Note that you need to purchase an additional licence - the Database Diagnostics Pack - in order to use this AWR repository.

Here is an example of how one can take a ADDM report.

cd $ORACLE_HOME/rdbms/admin

SQL> start addmrpt.sql;

Current Instance

DB Id DB Name Inst Num Instance


2144058311 MY_DB 1 DB1

Instances in this Workload Repository schema

DB Id Inst Num DB Name Instance Host


  • 2144058311 1 MY_DB DB1 Oracle


Using 2144058311 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 without
specifying a number lists all completed snapshots.

Listing the last 3 days of Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level


MYDB1 MY_DB 6332 08 Oct 2005 00:01 1
6333 08 Oct 2005 01:00 1
6334 08 Oct 2005 02:00 1
6335 08 Oct 2005 03:00 1
6336 08 Oct 2005 04:00 1
6337 08 Oct 2005 05:00 1
...
6394 10 Oct 2005 14:00 1
6395 10 Oct 2005 15:00 1
6396 10 Oct 2005 16:00 1
6397 10 Oct 2005 17:00 1
6398 10 Oct 2005 17:46 1
6399 10 Oct 2005 17:48 1

Specify the Begin and End Snapshot Ids

Enter value for begin_snap: 6398
Begin Snapshot Id specified: 6398

Enter value for end_snap: 6399
End Snapshot Id specified: 6399

Specify the Report Name

The default report file name is addmrpt_1_6398_6399.txt. To use this name,
press to continue, otherwise enter an alternative.