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

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:

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

From 12c onward, awr report contains addm output also