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
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
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