Using Automatic Database Diagnostic Monitor (ADDM).

The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance problem. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem.

--- Finding All Snapshot List

SQL> SELECT SNAP_ID,
  SNAP_LEVEL,BEGIN_INTERVAL_TIME,
  TO_CHAR(BEGIN_INTERVAL_TIME, 'dd/mm/yy hh24:mi:ss') BEGIN
FROM
   DBA_HIST_SNAPSHOT
ORDER BY SNAP_ID desc;  2    3    4    5    6
   SNAP_ID SNAP_LEVEL BEGIN_INTERVAL_TIME                                                         BEGIN
---------- ---------- --------------------------------------------------------------------------- -----------------
        19          1 03-APR-15 03.11.48.885 PM                                                   03/04/15 15:11:48
        18          1 03-APR-15 03.00.55.000 PM                                                   03/04/15 15:00:55
        17          1 03-APR-15 02.06.34.519 AM                                                   03/04/15 02:06:34
        16          1 03-APR-15 02.00.19.724 AM                                                   03/04/15 02:00:19
        15          1 03-APR-15 01.00.16.811 AM                                                   03/04/15 01:00:16
        14          1 03-APR-15 12.00.13.144 AM                                                   03/04/15 00:00:13
        13          1 02-APR-15 11.10.11.140 PM                                                   02/04/15 23:10:11
        12          1 02-APR-15 10.59.19.000 PM                                                   02/04/15 22:59:19
8 rows selected.

--- Executing Oracle Package DBMS_ADVISOR to Generate advisory task.

SQL> BEGIN
  -- Create an ADDM task.
  DBMS_ADVISOR.create_task (
    advisor_name      => 'ADDM',
  2    3    4    5      task_name         => '12_15_AWR_SNAP',
  6      task_desc         => 'Advisor for snapshots 12 to 15.');
  7
  8    -- Set the start and end snapshots.
  9    DBMS_ADVISOR.set_task_parameter (
 10      task_name => '12_15_AWR_SNAP',
 11      parameter => 'START_SNAPSHOT',
 12      value     => 12);
 13
 14    DBMS_ADVISOR.set_task_parameter (
 15      task_name => '12_15_AWR_SNAP',
 16      parameter => 'END_SNAPSHOT',
 17      value     => 15);
 18
 19    -- Execute the task.
 20    DBMS_ADVISOR.execute_task(task_name => '12_15_AWR_SNAP');
 21  END;
 22  /
PL/SQL procedure successfully completed.

------ Showing ADDM Report data.

SQL> SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('12_15_AWR_SNAP') AS report
FROM   dual;
SET PAGESIZE 24SQL> SQL>   2
REPORT
--------------------------------------------------------------------------------
          ADDM Report for Task '12_15_AWR_SNAP'
          -------------------------------------
Analysis Period
---------------
AWR snapshot range from 12 to 15.
Time period starts at 02-APR-15 11.10.11 PM
Time period ends at 03-APR-15 02.00.20 AM
Analysis Target
---------------
Database 'ORCLN1' with DB ID 1237928617.
Database version 11.2.0.3.0.
Analysis was requested for all instances, but ADDM analyzed instance orcln1,
numbered 1 and hosted at OEL64BN1.
See the "Additional Information" section for more information on the requested
instances.
Activity During the Analysis Period
-----------------------------------
Total database time was 128 seconds.
The average number of active sessions was .01.
ADDM analyzed 1 of the requested 1 instances.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
There are no findings to report.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
          Additional Information
          ----------------------
Miscellaneous Information
-------------------------
There was no significant database activity to run the ADDM.
The database's maintenance windows were active during 99% of the analysis
period.
SQL>