Skip to content

Automatic Database Diagnostic Monitor (ADDM)

    The Automatic Workload Repository (AWR) stores performance related statics for an Oracle database. The Automatic Database Diagnostic Monitor (ADDM) is a diagnostic tool that analyzes the AWR data on a regular basis, locates root causes of any performance problems, provides recommendations for correcting the problems, and identifies non-problem areas of the system. Because AWR is a repository of historical performance data, ADDM can analyze performance issues after the event, often saving time and resources in reproducing a problem.

    In most cases, ADDM output should be the first place that a DBA looks when notified of a performance problem. Automatic Database Diagnostic Monitor (ADDM) provides the following benefits:

    • Automatic performance diagnostic report every hour by default
    • Problem diagnosis based on decades of tuning expertise
    • Time-based quantification of problem impacts and recommendation benefits
    • Identification of root cause, not symptoms
    • Recommendations for treating the root causes of problems
    • Identification of non-problem areas of the system
    • Minimal overhead to the system during the diagnostic process

    Running ADDM in Database Mode

    For Oracle RAC configurations, you can run Automatic Database Diagnostic Monitor (ADDM) in Database mode to analyze all instances of the databases. For single-instance configurations, you can still run ADDM in Database mode; ADDM will behave as if running in Instance mode.

    To run ADDM in Database mode, use the DBMS_ADDM.ANALYZE_DB procedure:

    BEGIN
    DBMS_ADDM.ANALYZE_DB (
       task_name           IN OUT VARCHAR2,
       begin_snapshot      IN     NUMBER,
       end_snapshot        IN     NUMBER,
       db_id               IN     NUMBER := NULL);
    END;
    /
    

    The task_name parameter specifies the name of the analysis task that will be created. The begin_snapshot parameter specifies the snapshot number of the beginning snapshot in the analysis period. The end_snapshot parameter specifies the snapshot number of the ending snapshot in the analysis period. The db_id parameter specifies the database identifier of the database that will be analyzed. If unspecified, this parameter defaults to the database identifier of the database to which you are currently connected.

    The following example creates an ADDM task in database analysis mode, and executes it to diagnose the performance of the entire database during the time period defined by snapshots 137 and 145:

    VAR tname VARCHAR2(30);
    BEGIN
      :tname := 'ADDM for 7PM to 9PM';
      DBMS_ADDM.ANALYZE_DB(:tname, 137, 145);
    END;
    /
    

    Running ADDM in Instance Mode

    To analyze a particular instance of the database, you can run ADDM in Instance mode. To run ADDM in Instance mode, use the DBMS_ADDM.ANALYZE_INST procedure:

    BEGIN
    DBMS_ADDM.ANALYZE_INST (
       task_name           IN OUT VARCHAR2,
       begin_snapshot      IN     NUMBER,
       end_snapshot        IN     NUMBER,
       instance_number     IN     NUMBER := NULL,
       db_id               IN     NUMBER := NULL);
    END;
    /
    

    The task_name parameter specifies the name of the analysis task that will be created. The begin_snapshot parameter specifies the snapshot number of the beginning snapshot in the analysis period. The end_snapshot parameter specifies the snapshot number of the ending snapshot in the analysis period. The instance_number parameter specifies the instance number of the instance that will be analyzed. If unspecified, this parameter defaults to the instance number of the instance to which you are currently connected. The db_id parameter specifies the database identifier of the database that will be analyzed. If unspecified, this parameter defaults to the database identifier of the database to which you are currently connected.

    The following example creates an ADDM task in instance analysis mode, and executes it to diagnose the performance of instance number 1 during the time period defined by snapshots 137 and 145:

    VAR tname VARCHAR2(30);
    BEGIN
      :tname := 'my ADDM for 7PM to 9PM';
      DBMS_ADDM.ANALYZE_INST(:tname, 137, 145, 1);
    END;
    /
    

    Running ADDM in Partial Mode

    To analyze a subset of all database instances, you can run ADDM in Partial mode. To run ADDM in Partial mode, use the DBMS_ADDM.ANALYZE_PARTIAL procedure:

    BEGIN
    DBMS_ADDM.ANALYZE_PARTIAL (
       task_name           IN OUT VARCHAR2,
       instance_numbers    IN     VARCHAR2,
       begin_snapshot      IN     NUMBER,
       end_snapshot        IN     NUMBER,
       db_id               IN     NUMBER := NULL);
    END;
    /
    

    The task_name parameter specifies the name of the analysis task that will be created. The instance_numbers parameter specifies a comma-delimited list of instance numbers of instances that will be analyzed. The begin_snapshot parameter specifies the snapshot number of the beginning snapshot in the analysis period. The end_snapshot parameter specifies the snapshot number of the ending snapshot in the analysis period. The db_id parameter specifies the database identifier of the database that will be analyzed. If unspecified, this parameter defaults to the database identifier of the database to which you are currently connected.

    The following example creates an ADDM task in partial analysis mode, and executes it to diagnose the performance of instance numbers 1, 2, and 4, during the time period defined by snapshots 137 and 145:

    VAR tname VARCHAR2(30);
    BEGIN
      :tname := 'my ADDM for 7PM to 9PM';
      DBMS_ADDM.ANALYZE_PARTIAL(:tname, '1,2,4', 137, 145);
    END;
    /
    

    Displaying an ADDM Report

    To display a text report of an executed ADDM task, use the DBMS_ADDM.GET_REPORT function:

    DBMS_ADDM.GET_REPORT (
       task_name           IN VARCHAR2
      RETURN CLOB);
    

    The following example displays a text report of the ADDM task specified by its task name using the tname variable:

    CopySET LONG 1000000 PAGESIZE 0;
    SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;
    

    Note that the return type of a report is a CLOB, formatted to fit line size of 80. For information about reviewing the ADDM analysis results in an ADDM report, see “ADDM Analysis Results“.

    ADDM Views

    Typically, you should view ADDM analysis using Cloud Control or DBMS_ADDM package subprograms.

    However, you can also get ADDM information using the DBA_ADDM_* and DBA_ADVISOR_* views. This group of views includes:

    • DBA_ADVISOR_FINDINGS
      • This view displays all the findings discovered by all advisors. Each finding is displayed with an associated finding ID, name, and type. For tasks with multiple executions, the name of each task execution associated with each finding is also listed.
    • DBA_ADDM_FINDINGS
      • This view contains a subset of the findings displayed in the related DBA_ADVISOR_FINDINGS view. This view only displays the ADDM findings discovered by all advisors. Each ADDM finding is displayed with an associated finding ID, name, and type.
    • DBA_ADVISOR_FINDING_NAMES
      • This view lists all finding names registered with the advisor framework.
    • DBA_ADVISOR_RECOMMENDATIONS
      • This view displays the results of completed diagnostic tasks with recommendations for the problems identified in each execution. The recommendations should be reviewed in the order of the RANK column, as this relays the magnitude of the problem for the recommendation. The BENEFIT column displays the benefit to the system you can expect after the recommendation is performed. For tasks with multiple executions, the name of each task execution associated with each advisor task is also listed.
      • DBA_ADVISOR_TASKSThis view provides basic information about existing tasks, such as the task ID, task name, and when the task was created. For tasks with multiple executions, the name and type of the last or current execution associated with each advisor task is also listed.

    Also See:

    Real-Time ADDM Reports