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.
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.
Related articles.
- Automatic Database Diagnostic Monitor (ADDM) in Oracle Database 10g
- Automatic Database Diagnostic Monitor (ADDM) Enhancements in Oracle Database 11g Release 1
- Real-Time Automatic Database Diagnostics Monitor (ADDM) in Oracle Database 12c Release 1 (12.1)
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:
- Automatic Database Diagnostic Monitor (ADDM) in Oracle Database 10g
- Automatic Database Diagnostic Monitor (ADDM) Enhancements in Oracle Database 11g Release 1
- Real-Time Automatic Database Diagnostics Monitor (ADDM) in Oracle Database 12c Release 1 (12.1)
- DBMS_ADDM
- Automatic Performance Diagnostics
Hope this helps. Regards Tim...