Search This Blog


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;

No comments: