Generate ADDM Compare Period

Overview

The easiest option to use the OEM facility under AWR in Performance. The alternative is to use the COMPARE_INSTANCES Function in SQL*Plus.

References

• Database PL/SQL Packages and Types Reference
  • 17 DBMS_ADDM
    • COMPARE_INSTANCES Function

Procedure

Access Compare ADDM via Performance Menu in OEM

From the database home page in OEM, access the Compare Period ADDM from the AWR sub-menu of the Performance pull-down menu as shown below:

Fill in Compare ADDM Period Parameter

In the screenshot below, I have chosen the most recent two (2) hour AWR snapshot, and compare it to the immediately preceding one:

When I hit the run button, I get the following message:

Generate ADDM Compare Report in SQL*Plus

Run the following commands in SQL*Plus to generate a HTML report through the COMPARE_INSTANCES Function. A sample is uploaded as Compare_ADDM_Period_sample.html. Adobe Flashplayer is required to display it.

REM ============================================================================ REM Get Current DBID REM ============================================================================ VARIABLE dbid NUMBER BEGIN SELECT dbid INTO :dbid FROM v$database; END; / REM ============================================================================ REM Produce ADDM Compare Period Report as HTML REM ============================================================================ SET LINESIZE 32767 PAGESIZE 1000 LONG 9999 HEADING OFF FEEDBACK OFF COLUMN report FORMAT A32767 SELECT dbms_addm.compare_instances( base_dbid => :dbid, base_instance_id => 1, base_begin_snap_id => 299, base_end_snap_id => 300, comp_dbid => :dbid, comp_instance_id => 1, comp_begin_snap_id => 298, comp_end_snap_id => 299, report_type => 'HTML') AS report FROM dual;

The alternative formats are 'TEXT' and 'XML' which both produce XML documents.