8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Automatic Database Diagnostic Monitor (ADDM) Compare Period in Oracle Database 12c Release 1 (12.1)

Enterprise Manager Cloud Control has included functionality to compare ADDM periods for some time.

Cloud Control : Compare Period Menu

Oracle 12c has added this functionality into the DBMS_ADDM package, so it is now possible to compare ADDM periods without using Cloud Control.

This functionality requires Enterprise Edition and the Diagnostics and Tuning option. Make sure you have the correct licensing before using this functionality.

Snapshots
COMPARE_DATABASES
COMPARE_INSTANCES
Real Application Testing

Snapshots

As with previous releases, ADDM does analysis of the data in the AWR, so any references to snapshots relates to the AWR snapshots, described here. The article assumes you have identified snapshots from a specific time range using the DBA_HIST_SNAPSHOT view.

SET LINESIZE 120

COLUMN begin_interval_time FORMAT A30
COLUMN end_interval_time FORMAT A30
COLUMN startup_time FORMAT A30

SELECT snap_id, begin_interval_time, end_interval_time, startup_time
FROM   dba_hist_snapshot
WHERE  begin_interval_time > TRUNC(SYSTIMESTAMP)
ORDER BY snap_id;

   SNAP_ID BEGIN_INTERVAL_TIME            END_INTERVAL_TIME              STARTUP_TIME
---------- ------------------------------ ------------------------------ ------------------------------
      1962 14-JUL-2015 00:00:47.565       14-JUL-2015 01:00:51.255       09-JUL-2015 17:10:29.000
      1963 14-JUL-2015 01:00:51.255       14-JUL-2015 02:00:55.013       09-JUL-2015 17:10:29.000
      1964 14-JUL-2015 02:00:55.013       14-JUL-2015 03:00:58.649       09-JUL-2015 17:10:29.000
      1965 14-JUL-2015 03:00:58.649       14-JUL-2015 04:00:02.460       09-JUL-2015 17:10:29.000
      1966 14-JUL-2015 04:00:02.460       14-JUL-2015 05:00:06.256       09-JUL-2015 17:10:29.000
      1967 14-JUL-2015 05:00:06.256       14-JUL-2015 06:00:10.382       09-JUL-2015 17:10:29.000
      1968 14-JUL-2015 06:00:10.382       14-JUL-2015 07:00:14.812       09-JUL-2015 17:10:29.000
      1969 14-JUL-2015 07:00:14.812       14-JUL-2015 08:00:18.577       09-JUL-2015 17:10:29.000
      1970 14-JUL-2015 08:00:18.577       14-JUL-2015 09:00:22.491       09-JUL-2015 17:10:29.000
      1971 14-JUL-2015 09:00:22.491       14-JUL-2015 10:00:26.368       09-JUL-2015 17:10:29.000
      1972 14-JUL-2015 10:00:26.368       14-JUL-2015 11:00:30.457       09-JUL-2015 17:10:29.000

SQL>

COMPARE_DATABASES

The COMPARE_DATABASES function produced a HTML or XML report comparing two time periods of the same database or two different databases. When comparing two databases, all AWR data must reside locally. If the BASE_DBID and COMP_DBID parameters are omitted or set to NULL, the current database is assumed. The REPORT_TYPE parameter determines if the report is in HTML or XML format. The examples below show how to produce both types of report for the current database.

SET LONG 1000000 LONGCHUNKSIZE 1000000
SET LINESIZE 1000 PAGESIZE 0
SET TRIM ON TRIMSPOOL ON
SET ECHO OFF FEEDBACK OFF

SPOOL /tmp/addm_compare_db_report.html
SELECT DBMS_ADDM.compare_databases (
         base_begin_snap_id => 1962,
         base_end_snap_id   => 1964,
         comp_begin_snap_id => 1970,
         comp_end_snap_id   => 1972,
         report_type        => 'HTML') AS report
FROM   dual;
SPOOL OFF

SPOOL /tmp/addm_compare_db_report.xml
SELECT DBMS_ADDM.compare_databases (
         base_begin_snap_id => 1962,
         base_end_snap_id   => 1964,
         comp_begin_snap_id => 1970,
         comp_end_snap_id   => 1972,
         report_type        => 'XML') AS report
FROM   dual;
SPOOL OFF

You will have to edit the top and bottom of the spooled files to remove the additional SQL*Plus output.

COMPARE_INSTANCES

The COMPARE_INSTANCES function produced a HTML or XML report comparing two time periods of the same instance or two different instances. This is especially useful for comparing RAC instances. When comparing two instances from different databases, all AWR data must reside locally. If the BASE_DBID and COMP_DBID parameters are omitted or set to NULL, the current database is assumed. The BASE_INSTANCE_ID and COMP_INSTANCE_ID parameters specify the instances to be compared. The REPORT_TYPE parameter determines if the report is in HTML or XML format. The examples below show how to produce both types of report for the current database and instance.

SET LONG 1000000 LONGCHUNKSIZE 1000000
SET LINESIZE 1000 PAGESIZE 0
SET TRIM ON TRIMSPOOL ON
SET ECHO OFF FEEDBACK OFF

SPOOL /tmp/addm_compare_inst_report.html
SELECT DBMS_ADDM.compare_instances (
         base_instance_id   => 1,
         base_begin_snap_id => 1962,
         base_end_snap_id   => 1964,
         comp_instance_id   => 1,
         comp_begin_snap_id => 1970,
         comp_end_snap_id   => 1972,
         report_type        => 'HTML') AS report
FROM   dual;
SPOOL OFF

SPOOL /tmp/addm_compare_inst_report.xml
SELECT DBMS_ADDM.compare_instances (
         base_instance_id   => 1,
         base_begin_snap_id => 1962,
         base_end_snap_id   => 1964,
         comp_instance_id   => 1,
         comp_begin_snap_id => 1970,
         comp_end_snap_id   => 1972,
         report_type        => 'XML') AS report
FROM   dual;
SPOOL OFF

You will have to edit the top and bottom of the spooled files to remove the additional SQL*Plus output.

Real Application Testing

The DBMS_ADDM package also contains two Real Application Testing comparison functions.

COMPARE_CAPTURE_REPLAY_REPORT : Compares the capture to the replay and produces a Compare Period ADDM report, highlighting the differences between the two.
COMPARE_REPLAY_REPLAY_REPORT : Compares two replays to each other and produces Compare Period ADDM report, highlighting the differences between the two.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.