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 should have been uploaded as Compare_ADDM_Period_sample.html, but was lost during the web site migration. 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.