Oracle ADDM : Automatic Database Diagnostic Monitor

SHOW PARAMETER statistics_level;

ALTER SYSTEM SET statistics_level = TYPICAL;

SET LONG 100000
SET PAGESIZE 50000
SET LINESIZE 200

SELECT DBMS_ADDM.GET_REPORT(task_name => (SELECT task_name FROM dba_advisor_tasks WHERE advisor_name = 'ADDM' AND status = 'COMPLETED'
ORDER BY execution_end DESC FETCH FIRST 1 ROW ONLY)) AS addm_report FROM dual;

Using DBA_ADDM_FINDINGS View :

If you prefer a tabular output instead of a full report:

SELECT task_name, finding_name, impact_percent, type, message FROM dba_addm_findings WHERE task_name = (SELECT task_name FROM dba_advisor_tasks 
WHERE advisor_name = 'ADDM' AND status = 'COMPLETED' ORDER BY execution_end DESC FETCH FIRST 1 ROW ONLY) ORDER BY impact_percent DESC;

What the Output Looks Like

How to Read This

The IMPACT_PERCENT column is the most important number here. It tells you what percentage of your total DB Time is being wasted because of that specific problem.

My advice: Always start with the highest impact finding. Do not get distracted by small issues when a big one is staring at you.

Step 2 – Understand the ADDM Report

Here is a sample ADDM report section:

FINDING 1: 45.2% impact (1200 seconds)
---------------------------------------
SQL statements consuming significant database time were found.

RECOMMENDATION 1: SQL Tuning, 45.2% benefit (1200 seconds)
  ACTION: Run SQL Tuning Advisor on the SELECT statement with SQL_ID "abc123xyz".
  REASON: SQL statement was responsible for significant database time.

Let me break down what each part means:

Common ADDM Findings and What They Mean :

Over the years, I have seen certain findings come up again and again. Here are the most common ones and what you should do about them.

Step 3 – Run ADDM Manually for a Specific Time Period :

Automatic ADDM runs every hour. But sometimes you need to analyze a specific window — maybe there was an incident last night between 2 AM and 3 AM, and you want to know what happened.

This is where manual ADDM comes in.

SQL Command :

VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(30);

BEGIN
    :task_name := 'ADDM_MANUAL_01';
    :task_id := DBMS_ADDM.ANALYZE_DB(
        task_name => :task_name,
        begin_snapshot => 100,
        end_snapshot => 101,
        db_id => NULL
    );
END;
/

PRINT task_id
PRINT task_name

Replace 100 and 101 with the actual AWR snapshot IDs for your time window.

Get the Report :

SELECT DBMS_ADDM.GET_REPORT('ADDM_MANUAL_01') FROM dual;

When to Use Manual ADDM

• You know exactly when a problem occurred

• Automatic ADDM missed something

• You want to analyze a specific SQL or session

Step 4 – Handle Failed ADDM Tasks :

Sometimes ADDM tasks fail. This can happen for a few reasons:

• AWR snapshots expired before ADDM could analyze them

• The database was idle (not enough workload data)

• Resource constraints (not enough memory or temp space)

Check for Failed Tasks :

SELECT task_id, task_name, status, error_message FROM dba_addm_pending_autotasks WHERE status = 'FAILED';

Re-execute Failed Tasks :

BEGIN
    DBMS_ADDM.REEXECUTE_FAILED_AUTO_TASKS(
        instance_number => 1,
        begin_snapshot => 100,
        end_snapshot => 110,
        time_budget_in_sec => 300,
        max_attempts => 3
    );
END;
/

Generate a Failure Report

SELECT DBMS_ADDM.FAILED_AUTO_TASKS_REPORT(instance_number => 1, begin_snapshot => 100, end_snapshot => 110) FROM dual;

Step 5 – ADDM in Oracle Cloud (OCI) :

If you are running Oracle Database on Oracle Cloud Infrastructure, you can access ADDM findings through the Performance Hub graphical interface.

How to Access

1. Go to your database details page

2. Click on Performance Hub

3. Select the ADDM tab

4. Choose your time range

5. Click on the gray AWR snapshot icons

What You Will See

You can also run a manual ADDM task directly from Performance Hub by clicking the Run ADDM Task button.

Best Practices for Using ADDM :

Here is what I recommend based on real-world experience:

1. Review ADDM daily – Make it part of your morning routine.

2. Prioritize by impact – Always tackle the highest impact finding first.

3. Do not ignore warnings – They often indicate data quality issues that can mislead you.

4. Cross-reference with ASH – Use ASH data to validate ADDM findings.

5. Set up alerts – Automate monitoring for critical ADDM findings.

6. Keep AWR retention adequate – At least 30 days is recommended.

7. Use manual ADDM for incidents – When something goes wrong at a specific time, analyze that window.

How ADDM Connects to Previous Topics

Summary

Final Thoughts :

ADDM is one of the most powerful tools Oracle gives you for free. It is like having an expert DBA looking over your shoulder, constantly analyzing your database and telling you what needs attention.

The key is to actually use it. Do not wait for users to complain. Check ADDM regularly. Understand what it is telling you. Act on the recommendations.

If you do this consistently, you will catch problems before they become emergencies.