Oracle Automatic Workload Repository (AWR)
This RSA Knowledge Base Article is intended for those situations where an Oracle Automatic Workload Repository (AWR) report is needed to assist RSA Identity Governance & Lifecycle support, services and/or engineering with customer issues. It provides guidelines on how to utlitilize the Oracle AWR. This RSA Knowledge Base Article is not intended as a replacement for Oracle Support Documents on using AWR, but rather as a guideline to assist in the gathering of that information. The RSA Identity Governance and Lifecycle Oracle Development license allows engineering, services and technical support use of Oracle tools in customer environments.
RSA Identity Governance and Lifecycle customers should generate these AWR reports, when asked, as HTML-formatted reports (this is the default) and not as text. An HTML-formatted report is easier to review and interpret.
The Oracle Automatic Workload Repository (AWR) is a tool to gather performance and behavior-related information. The AWR installs along with the database and captures not only statistics, but the derived metrics as well. Data from the AWR can be gathered using both user interface based and command line based interfaces. From the user interface, the report is generated by selecting the AWR Report link from the Performance Hub of Oracle Enterprise Manager (OEM.) From the command line, specific SQL scripts are executed. Specific details on generating AWR reports are listed below.
NOTE: This RSA Knowledge Base Article is not intended as a replacement for Oracle Support Documents regarding the use of the Oracle AWR, but only as a quick reference. Please refer to the Oracle documentation for complete in-depth details of this utility, as well as the plethora of My Oracle Support articles related to AWR. (support.oracle.com (My Oracle Support aka Metalink) is the Oracle support website. A valid customer ID is required to login and access My Oracle Support.) Note that a valid Oracle license is required to make use of the AWR.
Use of the AWR
The RSA Identity Governance & Lifecycle database (as are all default Oracle-configured databases) is configured to capture workload snapshots every hour. These snapshots are retained for seven days. Each snapshot is given a unique ID number, and by default include one hour‟s worth of performance data. The start and stop snapshot is selected, based on the time period for which data is required. Typically a fairly narrow time span, during which performance problems are noticed, is identified as the time period to specify for the AWR report generation. It usually does not make sense to generate a report over a several day span for example. Even if a problem happened three or four days ago, an AWR report for the offending time period can still be created.
Using the Oracle Enterprise Manager (OEM) user interface to generate an AWR report
If Oracle Enterprise Manager (OEM) is accessible, it is possible to create an AWR directly from the OEM user interface. On an appliance, run acm statusoem which will tell you if OEM is running and the URL to use to access OEM. For a customer-supplied database, contact the DBA.
The following example is from an RSA Identity Governance & Lifecycle 7.1.1 system running Oracle Enterprise Manager Database Express 12c. This example illustrates how to capture AWR activity for July 28, 2019 between 10:00 am and 11:00 am.
- Login to OEM as the SYS user.
- From the home screen go to the Performance drop-down menu > Performance Hub > Select Time Period > Historical - Custom > choose your date and time > OK.
- Once you choose a timeframe, the AWR Report link in the far right corner will be highlighted. Click on the AWR Report link and select Yes to save the report.
- Open the report and note the snapshot is July 28 10:00 am to 11:00 am:
Using the command line interface to generate an AWR report
There are several different types of SQL scripts for generating AWR reports from the command line. For more information on the different AWR script files that are available, please see the Oracle documentation. The awrrpt.sql SQL script is the report typically requested from RSA Identity Governance & Lifecycle Support. An example of how to generate this report is shown below. The report is generated from snapshots or periods of time that activity has been logged to the Automatic Workload Repository.
- As the oracle user, set your default directory to the location of the AWR script files and login to SQL*Plus as the system user.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
- Execute the AWR script file
- Answer the following prompts based on the date/time required for the information requested.
- Enter value for report_type: This is where you define the output file as 'html' or 'text' format.
- Enter value for num_days: The number of days to look for snapshots (typically one to seven days.)
- Enter value for begin_snap: The snapshot ID that correlates to the beginning timeframe
- Enter value for end_snap: The snapshot ID that correlates to the date/time of the incident.
- Enter value for report_name: Take the default or supply your own report name.
- Exit SQL*Plus
This example illustrates how to capture AWR activity for July 30, 2019 between 8:00 am and 9:00 am.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
SQL*Plus: Release 184.108.40.206.0 Production on Tue Jul 30 14:50:01 2019
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Oracle Database 12c Enterprise Edition Release 220.127.116.11.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1752052327 AVDB 1 AVDB
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: html
Type Specified: html
Instances in this Workload Repository schema
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1752052327 1 AVDB AVDB acm-711
Using 1752052327 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: 1
Listing the last day's Completed Snapshots
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
AVDB AVDB 1895 30 Jul 2019 00:00 1
1896 30 Jul 2019 01:00 1
1897 30 Jul 2019 02:00 1
1898 30 Jul 2019 03:00 1
1899 30 Jul 2019 04:00 1
1900 30 Jul 2019 05:00 1
1901 30 Jul 2019 06:00 1
1902 30 Jul 2019 07:00 1
1903 30 Jul 2019 08:00 1
1904 30 Jul 2019 09:00 1
1905 30 Jul 2019 10:00 1
1906 30 Jul 2019 11:00 1
1907 30 Jul 2019 12:00 1
1908 30 Jul 2019 13:00 1
1909 30 Jul 2019 14:00 1
Specify the Begin and End Snapshot Ids
Enter value for begin_snap: 1903
Begin Snapshot Id specified: 1903
Enter value for end_snap: 1904
End Snapshot Id specified: 1904
Specify the Report Name
The default report file name is awrrpt_1_1903_1904.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: <cr>
Using the report name awrrpt_1_1903_1904.html
<REPORT CONTENTS SCROLL BY HERE>
End of Report
Report written to awrrpt_1_1903_1904.html
Open the report and note the snapshot is July 30 8:00 am to 9:00 am: