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
To get snap id Execute below query :
select DBID, INSTANCE_NUMBER, SNAP_ID, BEGIN_INTERVAL_TIME from DBA_HIST_SNAPSHOT;
SQL> @addmrpt.sql
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;
Note : From 12c onward, awr report contains addm output also