Behind the scene of Oracle ADDM

Automatic Database Diagnostic Monitoring (ADDM) advisors relies on following system views:

1. dba_hist_waitclassmet_history - historical wait events, segment header waits, latch serialization, network wait, user I/O wait
2. dba_hist_filestatxs - historical disk I/O statistics by each file
3. dba_hist_filemetric_history - historical disk I/O statistics, commonly used with above view

Following procedure show how to manually create ADDM task and extract the report. Example shown to take snapshot between 1 and 2 (assumption):

1. exec dbms_workload_repository.create_snapshot();
2. exec dbms_advisor.set_task_parameter('ADHOC_ADDM_TASK_1', 'START_SNAPSHOT', 1 );
3. exec dbms_advisor.set_task_parameter('ADHOC_ADDM_TASK_1', 'END_SNAPSHOT', 2 );
4. exec exec dbms_advisor.execute_task ('ADHOC_ADDM_TASK_1');
5. select dbms_advisor.get_task_report ('ADHOC_ADDM_TASK_1', 'TEXT',  'ALL') FROM dual;