0 ADDM: use DBMS_ADDM package manage ADDM

Oracle 11g add new tables and package for management the ADDM.

Tables:

• DBA_ADDM_TASKS:  List  historical ADDM tasks
• DBA_ADDM_INSTANCES : List instance level information for ADDM tasks
• DBA_ADDM_FINDINGS: Provides additional information for advisor views
• DBA_ADVISOR_FINDING_NAMES: list all the fining names
• DBA_ADDM_FDG_BREAKDOWN   
• DBA_ADDM_SYSTEM_DIRECTIVES    
• DBA_ADDM_TASK_DIRECTIVES    

Package and function

• DBMS_ADDM.ANALYZE_DB -- Database  specific analysis base on 2 snapshot
• DBMS_ADDM.ANALYZE_INST -- Instance specific analysis base on 2 snapshot
• DBMS_ADDM.ANALYZE_PARTIAL -- Partial Database  specific analysis base on 2 snapshot
• DBMS_ADDM.GET_REPORT – Get the report
• DBMS_ADDM.DELETE – delete the ADDM task

List snapshot

select instance_number, snap_id,end_interval_time from  wrm$_snapshot

DBMS_ADDM.ANALYZE_DB

var tname VARCHAR2(60);

BEGIN

  :tname := 'PO TEST ADDM1';

  DBMS_ADDM.ANALYZE_DB(:tname, 290, 300);

END;

select task_name,advisor_name,begin_snap_id,end_snap_id, status,end_time  from DBA_ADDM_TASKS where task_name = 'PO TEST ADDM1';

Get ADDM report

Use SPOOL to output the file

set long 1000000

Spool /tmp/PO_TEST_ADDM1.rpt

SELECT DBMS_ADDM.GET_REPORT('PO TEST ADDM1') FROM dual;

spool off

DBA_ADVISOR_FINDINGS

 

COLUMN task_name FORMAT A15

COLUMN finding_name FORMAT A30

COLUMN type FORMAT A12

COLUMN impact_type FORMAT A20

select task_name, finding_name,type, impact_type from DBA_ADVISOR_FINDINGS

where task_name = 'PO TEST ADDM1';

Type name can be found in the  DBA_ADVISOR_FINDING_NAMES

Check to see how often the User IO wait class happen

 

select to_char(execution_end, 'hh24') hour, count(*)

from dba_advisor_findings a, dba_advisor_tasks b

where finding_name='"User I/O" wait Class' and b.task_name = 'PO TEST ADDM1'

and a.task_id=b.task_id

group by to_char(execution_end, 'hh24')

order by 1;