/*
|| 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;
/