How To Generate ADDM Report

Automatic Database Diagnostic Monitor(ADDM) can analyze performance issues during a particular period and provide suggestion. An ADDM analysis is performed on a set of awr snapshots. addmrpt.sql script is used to generate addm report.

Generate ADDM report:

cd $ORACLE_HOME/rdbms/admin

SQL> @addmrpt.sql

It will ask for begin snap_id and end snap_id

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61

SQL> @addmrpt.sql   Current Instance ~~~~~~~~~~~~~~~~      DB Id    DB Name      Inst Num Instance ----------- ------------ -------- ------------   936252554 POCD                1 POCD           Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   DB Id      Inst Num   DB Name      Instance     Host ------------ ---------- ---------    ----------   ------ * 936252554      1      POCD         POCD         mac60-1   Using  936252554 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 Instance     DB Name      Snap Id       Snap Started    Snap Level ------------ ------------ ---------- ------------------ ---------- POCD         POCD              1052  26 Feb 2018 08:00    1                                1053  26 Feb 2018 08:30    1                                1054  26 Feb 2018 09:00    1                                1055  26 Feb 2018 09:30    1                                1056  26 Feb 2018 10:00    1                                1057  26 Feb 2018 10:30    1                                1058  26 Feb 2018 11:00    1                                1059  26 Feb 2018 11:30    1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 1058 Begin Snapshot Id specified: 1058   Enter value for end_snap: 1059 End   Snapshot Id specified: 1059       Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is addmrpt_1_1058_1059.txt.  To use this name, press  to continue, otherwise enter an alternative.   Enter value for report_name: addm_recent.txt

Privileges required for non-sys users to generate addm report:

1 2 3 4 5 6 7

sqlplus / as sysdba GRANT ADVISOR TO REPORT_USER; GRANT SELECT_CATALOG_ROLE TO REPORT_USER; GRANT EXECUTE ON dbms_workload_repository TO REPORT_USER;