Overview
This document is a quick guide to gathering Oracle AWR reports for an Oracle database (Version
10gR1 or higher).
The procedures are non-intrusive and place minimal overhead on the database system.
Generating AWR reports involves querying the DBA_HIST tables, and as such requires the
Diagnostics Pack to comply with Oracle License Requirements.
Report Requirement Guidelines
The following guidelines should be followed for generating AWR reports:
- The reports should ideally cover a one-week period (168 hours), or any other specific period of interest.
- Ideally reports should cover timeframes that include peak database activity.
- Ideally include prime heavy usage business hours timeframes.
- Ideally include batch processing timeframes.
- A database user with SELECT privileges to the DBA_HIST is required to execute the script. SELECT ANY DICTIONARY and execute privileges on DBMS_WORKLOAD_REPOSITORY are the required privileges for the script to execute.
- If the environment is RAC, the script will generate reports for all nodes from a single connection.
- When the reports have been generated combine them into a single folder to be uploaded to the Live Optics collector. Reports from multiple databases and multiple RAC nodes can be combined into a single folder.
Generating AWR Reports
The Live Optics provided SQL script (genawrshtml.sql) can be used to generate multiple
AWR reports in HTML format. Multiple single reports allow us to identify peaks in the workload
as well as trends.
The script can be used in batch or interactive modes. If using it in batch mode, specify the
number of hours to include in the AWR snapshot range on the command line. If you do not
specify a range, the script will prompt you. The range covers the last n hours from the current
database time.
A script in the current directory called awr_collect_[DBNAME].sql will be generated for
each execution of the genawrshtml.sql script. This second script contains all the commands to
generate a sequence of AWR reports from each node in the database.
The list of steps required to generate AWR reports is detailed below:-
- Transfer the script genawrshtml.sql and the associated capacity.sql scripts to any suitable directory on the database servers or a machine with an Oracle SQL*Plus client and network access to the target databases. Note that there are two variations (capacity and capacity_splunk) of the capacity script and there are specific versions for Oracle 10g, 11g, and 12c and later.
- Execute the script genawrshtml.sql from SQL*Plus.
- Execute the awr_collect_[DBNAME].sql script that was generated in Step 2.
- Repeat steps 1 – 3 for each database to be analyzed
- Add all the AWR reports along with the file capacity review output to a single folder to be uploaded to the Live Optics collector.
AWR Report Generation Example in Interactive Mode
The following section shows an example of executing the genawrshtml.sql script in interactive
mode.
- SQL> @genawrshtml.sql
Dell Snapshot Oracle Collection Tool
WARNING: only use this script if you are licensed for the Tuning and Diagnostics pack.
- Enter the AWR snapshot range to collect in hours
- Enter value for 1: 4
- now execute the script awr_collect_DBNAME.sql
When the “AWR report generation complete” message appears and the SQL prompt returns the process is complete. Note that for a wide range of reports, generation may take several hours, but the load on the system should be negligible.
AWR Report Generation Example in Batch Mode
The following section shows an example of executing the genawrshtml.sql script in batch mode.
SQL> @genawrshtml.sql 168
In this example the AWR reports for the last 168 hours will be generated. Assuming one AWR
snapshot is performed per hour, approximately 167 reports will be generated per instance in
the database.