Sunday, March 3, 2013

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
image

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';

image

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

image

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';

image

Type name can be found in the  DBA_ADVISOR_FINDING_NAMES

image

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;
image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates