Home

Introduction

Oracle’s Automatic Database Diagnostic Monitor (ADDM) provides performance tuning advice by analyzing the database’s performance metrics. While Oracle provides a built-in method to run and retrieve these reports, automating the process of generating and emailing ADDM reports can save time, especially for DBAs who need to regularly monitor the database’s health. In this article, we’ll walk through how to automate this process using PL/SQL and send the results via email.

Prerequisites

• Oracle Database version 11g or higher.
• Configured SMTP server for sending emails from the database.
• Proper ACL (Access Control List) settings for allowing the database to send emails.

PL/SQL Script to Send ADDM Report via Email

Below is a PL/SQL script that generates an ADDM report for a specified time window and emails it to a recipient. This script uses Oracle’s DBMS_ADVISOR package to create the ADDM task, retrieve the report, and send it via email using UTL_SMTP.

DECLARE
    dbid           NUMBER;
    bid            NUMBER;
    eid            NUMBER;
    db_unique_name VARCHAR2(30);
    host_name      VARCHAR2(64);
    status         VARCHAR2(11);
    starttime      CHAR(5);
    endtime        CHAR(5);
    output         VARCHAR2(32000);
    v_from         VARCHAR2(80);
    v_recipient    VARCHAR2(80) := 'arvind@domain.com';
    v_mail_host    VARCHAR2(30) := 'YOUR_SMTP_SERVER';
    v_mail_conn    UTL_SMTP.connection;
    tname          VARCHAR2(50);
    tid            NUMBER;
BEGIN
    starttime := '01:00';
    endtime := '12:00';

    SELECT MIN(snap_id), MAX(snap_id)
        INTO bid, eid
        FROM dba_hist_snapshot
        WHERE TO_CHAR(begin_interval_time, 'hh24:mi') >= starttime
        AND TO_CHAR(end_interval_time, 'hh24:mi') <= endtime
        AND TRUNC(begin_interval_time) = TRUNC(SYSDATE)
        AND TRUNC(end_interval_time) = TRUNC(SYSDATE);

    SELECT dbid, db_unique_name
        INTO dbid, db_unique_name
        FROM v$database;

    SELECT host_name INTO host_name
        FROM v$instance;

    DBMS_ADVISOR.CREATE_TASK('ADDM', tid, tname, 'ADDM Report(' || bid || ',' || eid || ')');
    DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', bid);
    DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', eid);
    DBMS_ADVISOR.EXECUTE_TASK(tname);  

    status := 0;

    WHILE status != 'COMPLETED' LOOP
        SELECT status INTO status FROM dba_advisor_tasks WHERE task_id = tid;
        dbms_lock.sleep(5);  
    END LOOP;

    v_from := db_unique_name || '@' || host_name;

    v_mail_conn := UTL_SMTP.OPEN_CONNECTION(v_mail_host, 25);
    UTL_SMTP.HELO(v_mail_conn, v_mail_host);
    UTL_SMTP.MAIL(v_mail_conn, v_from);
    UTL_SMTP.RCPT(v_mail_conn, v_recipient);
    UTL_SMTP.OPEN_DATA(v_mail_conn);
    UTL_SMTP.WRITE_DATA(v_mail_conn, 'From:' || v_from || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(v_mail_conn, 'Subject: ADDM Report of ' || v_from || ' ' || SYSDATE || ' ' || starttime || '-' || endtime || UTL_TCP.CRLF || UTL_TCP.CRLF);

    SELECT DBMS_ADVISOR.GET_TASK_REPORT(tname) INTO output FROM DUAL;
    UTL_SMTP.WRITE_DATA(v_mail_conn, output);

    UTL_SMTP.CLOSE_DATA(v_mail_conn);
    UTL_SMTP.QUIT(v_mail_conn);

EXCEPTION
    WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
        RAISE_APPLICATION_ERROR(-20000, 'Unable to send mail: ' || SQLERRM);
END;
/
        

How This Script Works

This script generates the ADDM report for a specific time window (in this case, between 01:00 and 12:00). It queries the dba_hist_snapshot view to get the snapshot IDs, and then uses the DBMS_ADVISOR package to generate the ADDM report. Once the task is completed, the script retrieves the report using DBMS_ADVISOR.GET_TASK_REPORT and sends it via email using the UTL_SMTP package.

Configuring SMTP and ACL for Oracle

To successfully send emails from Oracle using the above script, you need to configure an SMTP server and set the appropriate ACL for the database. Ensure that the SMTP server is reachable from the database server and that the ACL settings allow outgoing connections to the mail server. You may also need to update the v_mail_host variable in the script to point to your SMTP server.

Conclusion

Automating ADDM report generation and email distribution helps Oracle DBAs keep an eye on database performance without manually running reports each time. This PL/SQL script, when properly configured, can save a significant amount of time and ensure that performance insights are delivered regularly via email.