AWR Report Generation

Caution

This script should be used carefully in production environments. AWR reports can consume significant resources during generation.

Script: awrrpt.sql

Rem $Header: awrrpt.sql 24-oct-2003.12:04:53 pbelknap Exp $
Rem
Rem awrrpt.sql
Rem
Rem Copyright (c) 1999, 2003, Oracle Corporation.  All rights reserved.
Rem
Rem    NAME
Rem      awrrpt.sql
Rem
Rem    DESCRIPTION
Rem      This script defaults the dbid and instance number to that of the
Rem      current instance connected-to, then calls awrrpti.sql to produce
Rem      the Workload Repository report.
Rem
Rem    NOTES
Rem      Run as select_catalog privileges.
Rem      This report is based on the Statspack report.
Rem
Rem      If you want to use this script in an non-interactive fashion,
Rem      see the 'customer-customizable report settings' section in
Rem      awrrpti.sql
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    pbelknap    10/24/03 - swrfrpt to awrrpt
Rem    pbelknap    10/14/03 - moving params to rpti
Rem    pbelknap    10/02/03 - adding non-interactive mode cmnts
Rem    mlfeng      09/10/03 - heading on
Rem    aime        04/25/03 - aime_going_to_main
Rem    mlfeng      01/27/03 - mlfeng_swrf_reporting
Rem    mlfeng      01/13/03 - Update comments
Rem    mlfeng      07/08/02 - swrf flushing
Rem    mlfeng      06/12/02 - Created
Rem

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

set echo off heading on underline on;
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;

@@awrrpti

undefine num_days;
undefine report_type;
undefine report_name;
undefine begin_snap;
undefine end_snap;
--
-- End of file

Purpose

The AWR Report (awrrpt.sql) script generates comprehensive performance reports from Oracle’s Automatic Workload Repository. It provides detailed insights into database performance over a specified time period.

Key Features

• Automatic Instance Detection: Defaults to current database instance
• Interactive Mode: Prompts for snapshot range selection
• Non-Interactive Support: Can be automated with predefined parameters
• Comprehensive Reporting: Based on Oracle’s Statspack functionality

Prerequisites

• SELECT_CATALOG_ROLE privileges
• AWR license (requires Diagnostic Pack)
• Oracle 10g or higher

Usage

Interactive Mode

SQL> @awrrpt.sql

The script will:

1. Display current instance information
2. Call awrrpti.sql for interactive snapshot selection
3. Generate the AWR report

Non-Interactive Mode

For automation, define parameters before calling the script:

define num_days = 3;
define report_type = 'html';
define report_name = '/tmp/awr_report.html';
define begin_snap = 100;
define end_snap = 110;
@awrrpt.sql

Output

The script generates either:

• HTML format report (recommended for readability)
• Text format report (suitable for automation)

Performance Considerations

• AWR report generation can be resource-intensive
• Run during low-activity periods for production systems
• Consider using AWR baselines for consistent comparisons

Related Scripts

• awrrpti.sql: Interactive AWR report generation
• ashrpt.sql: Active Session History reports
• addmrpt.sql: Automatic Database Diagnostic Monitor reports