/*
|| Oracle 11g ADDM Enhancements Listing
||
|| Demonstrates Oracle 11g enhancements to Automatic Database Diagnostic Monitor (ADDM)
|| features, including:
|| - Analyzing Real Application Cluster (RAC) clustered databases globally
|| - Examples of DBMS_ADDM functions and procedures
|| - Applying filters to ADDM report results
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11gR1
|| Automatic Database Diagnostic Monitor features, and they should be carefully proofread
|| before being executed against any existing Oracle database to avoid potential damage!
*/
/*
|| Listing 1:
|| Performing ADDM analyses via DBMS_ADDM
*/
-----
-- Perform ADDM analysis at the database level for a RAC database,
-- then print the corresponding text report
-----
VARIABLE taskname VARCHAR2(12);
BEGIN
:taskname := 'ADDM_100';
DBMS_ADDM.ANALYZE_DB(
task_name => :taskname
,begin_snapshot => 149
,end_snapshot => 150
,db_id => NULL
);
END;
/
-- Print the report
SPOOL ADDM_100.rpt
SET LONG 1000000
SET PAGESIZE 50000
TTITLE 'ADDM Analysis for Complete Cluster Database RACDB'
SELECT DBMS_ADDM.GET_REPORT('ADDM_100')
FROM DUAL
;
TTITLE OFF
SPOOL OFF
-----
-- Perform ADDM analysis for a single instance (RACDB1) of a RAC database,
-- then print the corresponding text report
-----
VARIABLE taskname VARCHAR2(60);
BEGIN
:taskname := 'ADDM_110';
DBMS_ADDM.ANALYZE_INST(
task_name => :taskname
,begin_snapshot => 149
,end_snapshot => 150
);
END;
/
SPOOL ADDM_100.rpt
SET LONG 1000000
SET PAGESIZE 50000
TTITLE 'ADDM Analysis for Cluster Database RACDB - Instance RACDB1 - PRE'
SELECT DBMS_ADDM.GET_REPORT('ADDM_110')
FROM DUAL
;
TTITLE OFF
SPOOL OFF
-----
-- Perform ADDM analyses for selected instances for a RAC database
-----
VARIABLE taskname VARCHAR2(12);
BEGIN
DBMS_ADDM.ANALYZE_PARTIAL(
task_name => :taskname
,instance_numbers => '1,2'
,begin_snapshot => 149
,end_snapshot => 150
,db_id => NULL
);
END;
/
/*
|| Listing 2:
|| Using ADDM directives to filter out specific findings from an ADDM report
*/
-----
-- Reset the original single-instance analysis task
-----
BEGIN
DBMS_ADVISOR.RESET_TASK(
task_name => 'ADDM_110'
);
END;
/
-----
-- Limit any findings for Interconnect Buffer Busy, but only
-- if it affected one or more sessions for a total impact of > 10%
-----
BEGIN
DBMS_ADDM.INSERT_FINDING_DIRECTIVE(
task_name => 'ADDM_110'
,dir_name => 'ADDM_110_FD_1'
,finding_name => 'Interconnect Buffer Busy'
,min_active_sessions => 1
,min_perc_impact => 10
);
END;
/
-----
-- Limit any findings for CURSOR_SHARING parameter
-----
BEGIN
DBMS_ADDM.INSERT_PARAMETER_DIRECTIVE(
task_name => 'ADDM_110'
,dir_name => 'ADDM_110_PD_1'
,parameter_name => 'CURSOR_SHARING'
);
END;
/
-----
-- Exclude specific segments from analysis
-----
BEGIN
DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE(
task_name => 'ADDM_110'
,dir_name => 'ADDM_110_SD_1'
,owner_name => 'AP'
,object_name => 'INVOICES'
,sub_object_name => NULL
);
DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE(
task_name => 'ADDM_110'
,dir_name => 'ADDM_110_SD_2'
,owner_name => 'AP'
,object_name => 'INVOICE_ITEMS'
,sub_object_name => NULL
);
END;
/
-----
-- Exclude specific SQL statements from analysis
-----
BEGIN
DBMS_ADDM.INSERT_SQL_DIRECTIVE(
task_name => 'ADDM_110'
,dir_name => 'ADDM_110_SQ_1'
,sql_id => 'cxubjzfd0hmyd'
,min_active_sessions => 3
,min_response_time => 5000000
);
DBMS_ADDM.INSERT_SQL_DIRECTIVE(
task_name => 'ADDM_110'
,dir_name => 'ADDM_110_SQ_2'
,sql_id => '6z4mq42v75q8s'
,min_active_sessions => 3
,min_response_time => 5000000
);
DBMS_ADDM.INSERT_SQL_DIRECTIVE(
task_name => 'ADDM_110'
,dir_name => 'ADDM_110_SQ_3'
,sql_id => '9qqv0czupcx22'
,min_active_sessions => 3
,min_response_time => 5000000
);
END;
/
-----
-- Execute the same task again, and then create a report for sake of
-- comparison
-----
BEGIN
DBMS_ADVISOR.EXECUTE_TASK('ADDM_110');
END;
/
SPOOL /home/oracle/ADDM_110_1.rpt
SET LONG 1000000
SET PAGESIZE 50000
TTITLE 'ADDM Analysis for Cluster Database RACDB - Instance RACDB1'
SELECT DBMS_ADDM.GET_REPORT('ADDM_110')
FROM DUAL
;
TTITLE OFF
SPOOL OFF
/*
|| Listing 3:
|| Queries against DBMS_ADDM Reporting Views
*/
-----
-- View: DBA_ADVISOR_FINDING_NAMES
-- Purpose: Lists standard ADDM advisory categories
-----
TTITLE 'ADDM Advisory Findings Categories|(from DBA_ADVISOR_FINDING_NAMES)'
COL id FORMAT 9999 HEADING 'Fdg|#'
COL advisor_name FORMAT A08 HEADING 'Advisor'
COL finding_name FORMAT A50 HEADING 'Finding|Name' WRAP
SELECT
id
,advisor_name
,finding_name
FROM dba_advisor_finding_names
ORDER BY 1,2,3
;
TTITLE OFF
-----
-- View: DBA_ADVISOR_FINDINGS
-- Purpose: Shows all current ADDM advice, including those that have been
-- filtered via DBMS_ADDM.INSERT_*_DIRECTIVE procedure calls
-----
TTITLE 'Current Advisor Findings|(from DBA_ADVISOR_FINDINGS)'
COL owner FORMAT A10 HEADING 'Task|Owner'
COL task_name FORMAT A10 HEADING 'Task|Name'
COL execution_name FORMAT A10 HEADING 'Exec|Name'
COL finding_name FORMAT A20 HEADING 'Finding|Name' WRAP
COL type FORMAT A11 HEADING 'Finding|Type'
COL filtered FORMAT A03 HEADING 'Fil|trd'
COL object_id FORMAT 9999999 HEADING 'Object|#'
COL impact number FORMAT 9999999 HEADING 'Impact|Value'
COL impact_type FORMAT A30 HEADING 'System Impact' WRAP
COL message FORMAT A30 HEADING 'Message' WRAP
COL more_info FORMAT A30 HEADING 'More Information' WRAP
SELECT
owner
,task_name
,execution_name
,finding_name
,type
,filtered
,object_id
,impact
,impact_type
,message
,more_info
FROM dba_advisor_findings
WHERE filtered <> 'NO'
ORDER BY owner, task_name
;
TTITLE OFF
/*
|| Listing 4:
|| Removing existing ADDM findings and ADDM tasks
*/
BEGIN
-- Remove all ADDM general finding filters
DBMS_ADDM.DELETE_FINDING_DIRECTIVE(
task_name => NULL
,dir_name => 'ADDM_110_FD_1'
);
-- Remove all initialization parameter finding filters
DBMS_ADDM.DELETE_PARAMETER_DIRECTIVE(
task_name => NULL
,dir_name => 'ADDM_110_PD_1'
);
-- Remove all segment finding filters
DBMS_ADDM.DELETE_SEGMENT_DIRECTIVE(
task_name => NULL
,dir_name => 'ADDM_110_SD_1'
);
DBMS_ADDM.DELETE_SEGMENT_DIRECTIVE(
task_name => NULL
,dir_name => 'ADDM_110_SD_2'
);
-- Remove all SQL statement finding filters
DBMS_ADDM.DELETE_SQL_DIRECTIVE(
task_name => NULL
,dir_name => 'ADDM_110_SQ_1'
);
DBMS_ADDM.DELETE_SQL_DIRECTIVE(
task_name => NULL
,dir_name => 'ADDM_110_SQ_2'
);
DBMS_ADDM.DELETE_SQL_DIRECTIVE(
task_name => NULL
,dir_name => 'ADDM_110_SQ_3'
);
END;
/
-----
-- Removing a completed ADDM task, including any
-- associated ADDM subtask(s) at the partial or
-- instance level
-----
BEGIN
DBMS_ADDM.DELETE(task_name => 'ADDM_100');
DBMS_ADDM.DELETE(task_name => 'ADDM_110');
DBMS_ADDM.DELETE(task_name => 'ADDM_120');
END;
/