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

Automatic Database Diagnostic Monitor (ADDM) Enhancements in Oracle Database 11g Release 1

The Automatic Database Diagnostic Monitor (ADDM) was introduced in Oracle 10g as part of the Diagnostics and Tuning pack for the Enterprise Edition of the Oracle database. In that release the only API available for ADDM was the DBMS_ADVISOR package. From Oracle 11g onward the DBMS_ADDM package is now the simplest API to use for ADDM. It is essentially a helper-wrapper over the DBMS_ADVISOR package.

The DBMS_ADDM package is mentioned in one of the manuals in Oracle 10g Release 2, but there is no formal documentation for it until Oracle 11g.

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

Snapshots
ANALYZE_DB
ANALYZE_INST
GET_REPORT
DELETE
Miscellaneous

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
---------- ------------------------------ ------------------------------ ------------------------------
      1770 06-JUL-2015 00:00:54.031       06-JUL-2015 01:00:58.011       03-JUL-2015 11:13:51.000
      1771 06-JUL-2015 01:00:58.011       06-JUL-2015 02:00:02.167       03-JUL-2015 11:13:51.000
      1772 06-JUL-2015 02:00:02.167       06-JUL-2015 03:00:06.332       03-JUL-2015 11:13:51.000
      1773 06-JUL-2015 03:00:06.332       06-JUL-2015 04:00:10.573       03-JUL-2015 11:13:51.000
      1774 06-JUL-2015 04:00:10.573       06-JUL-2015 05:00:14.648       03-JUL-2015 11:13:51.000
      1775 06-JUL-2015 05:00:14.648       06-JUL-2015 06:00:18.690       03-JUL-2015 11:13:51.000
      1776 06-JUL-2015 06:00:18.690       06-JUL-2015 07:00:22.717       03-JUL-2015 11:13:51.000
      1777 06-JUL-2015 07:00:22.717       06-JUL-2015 08:00:27.209       03-JUL-2015 11:13:51.000
      1778 06-JUL-2015 08:00:27.209       06-JUL-2015 09:00:31.694       03-JUL-2015 11:13:51.000
      1779 06-JUL-2015 09:00:31.694       06-JUL-2015 10:00:36.238       03-JUL-2015 11:13:51.000
      1780 06-JUL-2015 10:00:36.238       06-JUL-2015 11:00:40.915       03-JUL-2015 11:13:51.000
      1781 06-JUL-2015 11:00:40.915       06-JUL-2015 12:00:45.594       03-JUL-2015 11:13:51.000
      1782 06-JUL-2015 12:00:45.594       06-JUL-2015 13:00:49.954       03-JUL-2015 11:13:51.000
      1783 06-JUL-2015 13:00:49.954       06-JUL-2015 14:00:54.322       03-JUL-2015 11:13:51.000
      1784 06-JUL-2015 14:00:54.322       06-JUL-2015 15:00:58.984       03-JUL-2015 11:13:51.000
      1785 06-JUL-2015 15:00:58.984       06-JUL-2015 16:00:03.464       03-JUL-2015 11:13:51.000

SQL>

ANALYZE_DB

The ANALYZE_DB procedure does analysis of the whole database for the period between the two specified snapshots. It can also accept the DB_ID parameter, but this defaults to the current database, so it's not really necessary in most cases.

CONN sys@cdb1 AS SYSDBA

DECLARE
  l_task_name VARCHAR2(30) := '1783_1785_addm_db';
BEGIN
  DBMS_ADDM.analyze_db (
    task_name      => l_task_name,
    begin_snapshot => 1783,
    end_snapshot   => 1785);
END;
/

Once the analysis is complete, you can get the report, as described below.

ANALYZE_INST

The ANALYZE_INST procedure does analysis of a specific instance for the period between the two specified snapshots. If the INSTANCE_NUMBER parameter is NULL, it defaults to the current instance. It can also accept the DB_ID parameter, but this defaults to the current database, so it's not really necessary in most cases.

CONN sys@cdb1 AS SYSDBA

DECLARE
  l_task_name VARCHAR2(30) := '1783_1785_addm_inst';
BEGIN
  DBMS_ADDM.analyze_inst (
    task_name       => l_task_name,
    begin_snapshot  => 1783,
    end_snapshot    => 1785,
    instance_number => 1);
END;
/

Once the analysis is complete, you can get the report, as described below.

GET_REPORT

The GET_REPORT function returns the findings for the specified analysis task.

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

SELECT DBMS_ADDM.get_report('1783_1785_addm_db') FROM dual;

DELETE

You can remove any ADDM tasks using the DELETE procedure.

BEGIN
  DBMS_ADDM.delete('1783_1785_addm_db');
END;
/

Miscellaneous

The DBMS_ADDM package contains routines to affect the ADDM analysis and reporting.

INSERT_FINDING_DIRECTIVE : Limit reporting for a specific finding.
INSERT_PARAMETER_DIRECTIVE : Prevent ADDM from creating actions to alter a specific parameter.
INSERT_SEGMENT_DIRECTIVE : Prevent ADDM from creating actions to run the Segment Advisor for specific segments.
INSERT_SQL_DIRECTIVE : Limit reporting of actions on specific SQL.
DELETE_FINDING_DIRECTIVE : Delete a finding directive.
DELETE_PARAMETER_DIRECTIVE : Delete a parameter directive.
DELETE_SEGMENT_DIRECTIVE : Delete a segment directive.
DELETE_SQL_DIRECTIVE : Delete an SQL directive.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.