Run ADDM Statistic

The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. The ADDM analysis includes

Run ADDM report user can with Oracle Enterprise manager, but there i want tell you about script which provided same information. After running this script in PL/SQL Developer you can get ADDM report result.

1. Report Interval 

 you should decide on what period do you want see ADDM report, thats why you run this select on DB :

select * from dba_hist_snapshot
order by snap_id

and know you know begin and end snap_id(snap_id_1, snap_id_2) 

2. Create ADDM Task

BEGIN
-- Create an ADDM task.
DBMS_ADVISOR.create_task (
advisor_name => 'ADDM',
  task_name => 'Test Report',
task_desc => 'Test Report');

-- Set the start and end snapshots.
DBMS_ADVISOR.set_task_parameter (
  task_name =>'Test Report',
parameter => 'START_SNAPSHOT',
  value => snap_id_1);

DBMS_ADVISOR.set_task_parameter (
  task_name => 'Test Report',
parameter => 'END_SNAPSHOT',
  value => snap_id_2);

-- Execute the task.
DBMS_ADVISOR.execute_task(task_name =>'Test Report');

END;

3. Get Report

SELECT DBMS_ADVISOR.get_task_report('Test Report ','TEXT', 'TYPICAL', 'ALL') AS report
FROM dual;

In BLOB you can see result of report

4. Delete Report

for deleting report you can run this statement:

BEGIN
  DBMS_ADVISOR.delete_task ( task_name => 'Test Report');
END;