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;
