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;

0 comments:
Post a Comment