Skip to content

How to Generate AWR Report in Oracle

  • by

In this post, I will introduce some ways to generate AWR report for different purposes in Oracle. DBA should know there're two types of AWR reports can be generated. One is single-instance report, the other is cluster database report.

  1. Single-Instance Database
  2. Cluster Database

What is Automatic Workload Repository (AWR)? AWR is a place where to store performance related statics for an Oracle database. Whereas Automatic Database Diagnostic Monitor (ADDM) is a diagnostic tool that analyzes the AWR data and then provides recommendations for any findings of the database system. This set of feature is called Automatic Performance Diagnostics.

Generate AWR Report for Single-Instance Database

For single-instance database report, you have to run awrrpt.sql to generate AWR report.

SQL> @?/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
  933515296 COMPDB              1 primdb


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 933515296         1 COMPDB       primdb       primary01.ex
                                                ample.com

Using  933515296 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days:

Listing all Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
primdb       COMPDB               5 04 Feb 2016 09:10      1
                                  6 04 Feb 2016 10:00      1
                                  7 04 Feb 2016 11:00      1
                                  8 04 Feb 2016 12:00      1
                                  9 04 Feb 2016 13:00      1
                                 10 04 Feb 2016 14:00      1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 7
Begin Snapshot Id specified: 7

Enter value for end_snap: 8
End   Snapshot Id specified: 8



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_7_8.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_7_8.html

<html lang="en"><head><title>AWR Report for DB: COMPDB, Inst: primdb, Snaps: 7-8</title>

...
Report written to awrrpt_1_7_8.html
SQL> !
[oracle@primary01 ~]$ ll
...
-rw-r--r--. 1 oracle oinstall 533219 Feb  4 14:12 awrrpt_1_7_8.html
[oracle@primary01 ~]$ exit
...

In which, @ (at sign) means that it executes starting with the following string. ? (question mark) means $ORACLE_HOME. For more explanation, you may refer to this post: What Symbols are Used in Oracle Database?

Generate AWR Report for Cluster Database

There're two basic types to generate AWR reports, one is specific for instance-level, the other is specific for database-level.

Instance-Level

For instance-level report, you have to run awrrpti.sql to generate AWR report for instance, one at a time. In this case, we choose instance #2 as our main source of the report.

SQL> @?/rdbms/admin/awrrpti.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt.  Default value is 'html'.

'html'          HTML format (default)
'text'          Text format
'active-html'   Includes Performance Hub active report

Enter value for report_type:

Type Specified:                                  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 3240481230        1 ERPFINDB     erpfindb1    rac1
  3240481230        2 ERPFINDB     erpfindb2    rac2

Enter value for dbid: 3240481230
Using 3240481230 for database Id
Enter value for inst_num: 2
Using 2 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 8

Listing the last 8 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
erpfindb1    ERPFINDB         83611 16 Apr 2019 00:00      1
...
...
                              83679 23 Apr 2019 00:00      1
...


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 83611
Begin Snapshot Id specified: 83611

Enter value for end_snap: 83679
End   Snapshot Id specified: 83679



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_2_83611_83679.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: awr_instance_2_report_20190423.html
...

Global-Level

For global-level report, you have to run awrgrpt.sql to generate AWR report for cluster database globally.

SQL> @?/rdbms/admin/awrgrpt.sql

Current Database
~~~~~~~~~~~~~~~~

   DB Id    DB Name
----------- ------------
 3240481230 ERPFINDB


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt.  Default value is 'html'.

'html'          HTML format (default)
'text'          Text format
'active-html'   Includes Performance Hub active report

Enter value for report_type:

Type Specified:                                  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id      INSTT_NUM DB Name
------------ ---------- ------------
INSTT_NAME                                       Host
------------------------------------------------ ------------
* 3240481230          1 ERPFINDB
erpfindb1                                        rac1
* 3240481230          2 ERPFINDB
erpfindb2                                        rac2

Using 3240481230 for database Id
Using instances ALL (default 'ALL')


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 8

Listing the last 8 days of Completed Snapshots

                                           Snap
DB Name        Snap Id    Snap Started    Level
------------ --------- ------------------ -----
ERPFINDB         83611 16 Apr 2019 00:00      1
...
...
                 83679 23 Apr 2019 00:00      1
...


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 83611
Begin Snapshot Id specified: 83611

Enter value for end_snap: 83679
End   Snapshot Id specified: 83679



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_rac_83611_83679.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: awr_global_database_report_20190423.html
...

Differences in Scope of Reports

Let's summarize the differences in scope of statistics among SQL scripts as the following chart.

No.ScriptScope
1awrrpt.sqlCurrent instance
2awrrpti.sqlSelected instance
3awrgrpt.sqlEntire cluster database
including all instances

Although we use awrgrpt.sql to generate AWR report for the entire cluster database, we can have more detailed instance AWR report generated by awrrpti.sql.

Leave a Reply

Your email address will not be published.