ADDM Report Generation

Caution

This script requires Diagnostic Pack licensing. ADDM analysis can be resource-intensive during execution.

Script: addmrpt.sql

Rem
Rem $Header: addmrpt.sql 13-oct-2003.14:01:18 pbelknap Exp $
Rem
Rem addmrpt.sql
Rem
Rem Copyright (c) 2003, Oracle Corporation.  All rights reserved.
Rem
Rem    NAME
Rem      addmrpt.sql - SQL*Plus script to run ADDM analysis on a pair on AWR
Rem                    snapshots and to display the textual ADDM report
Rem                    of the analysis.
Rem
Rem    DESCRIPTION
Rem      This SQL*Plus script can be used only to run ADDM on snapshots
Rem      taken by the current instance. If you want to run ADDM on snapshots from
Rem      other instances in a RAC environment or snapshots imported from
Rem      other databases, please use the addmrpti.sql script.
Rem
Rem    NOTES
Rem      Assumes the current database's dbid and instance_number,
Rem      Assumes num_days to be 3
Rem      Displays the snapshots taken in the past &num_days,
Rem      Prompts for a pair of AWR snapshots,
Rem      Runs ADDM across those snapshots and
Rem      Displays and spools the textual ADDM report of the analysis.
Rem
Rem      If you want to use this script in an non-interactive fashion,
Rem      do something similar to the following:
Rem
Rem      define  dbid         = 1234567890;
Rem      define  inst_num     = 1;
Rem      define  num_days     = 3;
Rem      define  report_name  = /tmp/addm_report_10_11.txt
Rem      define  begin_snap   = 10;
Rem      define  end_snap     = 11;
Rem      @@?/rdbms/admin/addmrpti

--
-- Customer configurable variables

--
-- Specify the number of days of snapshots to choose from:
define num_days = 3;

--
-- Optionally specify a report_name
-- define report_name = /tmp/addm_report.txt

--
-- End of customer configurable variables


--
-- Get the current database/instance information - this will be used
-- later in the report along with bid, eid to lookup snapshots

set heading on echo off feedback off verify off underline on timing off;

column inst_num  heading "Inst Num"  new_value inst_num  format 99999;
column inst_name heading "Instance"  new_value inst_name format a12;
column db_name   heading "DB Name"   new_value db_name   format a12;
column dbid      heading "DB Id"     new_value dbid      format 9999999999 just c;

prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~

select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
  from v$database d,
       v$instance i;

@@addmrpti

--
-- Reset SQL*Plus settings to defaults
set heading on echo off feedback 6 verify on underline on timing off;

--
-- Undefine SQL*Plus variables defined in this file
undefine   num_days
undefine   report_name
undefine   inst_num
undefine   inst_name
undefine   db_name
undefine   dbid

--
-- End of file

Purpose

The ADDM Report (addmrpt.sql) script runs Oracle’s Automatic Database Diagnostic Monitor to analyze performance between two AWR snapshots and provide actionable recommendations for performance improvement.

Key Features

• Automated Analysis: Provides performance findings and recommendations
• Problem Identification: Automatically identifies performance bottlenecks
• Impact Quantification: Shows potential benefit of implementing recommendations
• Root Cause Analysis: Identifies underlying causes of performance issues

Prerequisites

• SELECT_CATALOG_ROLE privileges
• Diagnostic Pack license required
• Oracle 10g or higher
• AWR snapshots must exist for the analysis period

Usage

Interactive Mode

SQL> @addmrpt.sql

The script will:

1. Display current instance information
2. Show available snapshots from the last 3 days
3. Prompt for beginning and ending snapshot IDs
4. Run ADDM analysis
5. Display the diagnostic report

Non-Interactive Mode

define dbid = 1234567890;
define inst_num = 1;
define num_days = 3;
define report_name = /tmp/addm_report_10_11.txt;
define begin_snap = 10;
define end_snap = 11;
@@addmrpti

Configuration Options

• num_days: Number of days of snapshots to display (default: 3)
• report_name: Output file path for the report
• begin_snap: Starting snapshot ID
• end_snap: Ending snapshot ID

ADDM Analysis Features

Performance Findings

• CPU bottlenecks
• I/O performance issues
• Memory allocation problems
• Concurrency and locking issues
• Application design problems

Recommendations Include

• SQL tuning advice
• Index recommendations
• Memory sizing suggestions
• I/O configuration changes
• Application design improvements

Output Format

The report includes:

1. Analysis Period: Time range and database information
2. Summary: Top findings and their impact
3. Findings: Detailed problem descriptions
4. Recommendations: Specific actions to improve performance
5. Benefit: Estimated performance improvement

Best Practices

1. Snapshot Selection: Choose snapshots that represent the problem period
2. Regular Analysis: Run ADDM after significant workload changes
3. Action Tracking: Document which recommendations were implemented
4. Baseline Comparison: Compare with known good performance periods

Performance Considerations

• ADDM analysis requires CPU and memory resources
• Analysis time depends on snapshot interval and activity level
• Consider running during off-peak hours for large analyses

Related Scripts

• awrrpt.sql: AWR performance reports
• ashrpt.sql: Active Session History reports
• addmrpti.sql: ADDM report for RAC or imported snapshots