Generating Automatic Workload Repository Reports

An AWR report shows data captured between two snapshots (or two points in time). AWR reports are divided into multiple sections. The content of the report contains the workload profile of the system for the selected range of snapshots. The HTML report includes links that can be used to navigate quickly between sections.

Note:

If you run a report on a database that does not have any workload activity during the specified range of snapshots, then calculated percentages for some report statistics can be less than 0 or greater than 100. This result means that there is no meaningful value for the statistic.

This section describes how to generate AWR reports and contains the following topics:

User Interface for Generating an AWR Report

The primary interface for generating AWR reports is Oracle Enterprise Manager Cloud Control (Cloud Control). Whenever possible, generate AWR reports using Cloud Control.

If Cloud Control is unavailable, then generate AWR reports by running SQL scripts. The DBA role is required to run these scripts.

Generating an AWR Report Using the Command-Line Interface

This section describes how to generate AWR reports by running SQL scripts in the command-line interface. You can run an AWR report as any user as long as they have the following roles and privileges for the dbms_workload_repository.

ADVISOR
SELECT_CATALOG_ROLE
EXECUTE ON

Click on an appropriate task link in the following table for the detailed steps to generate the required AWR report.

Table 6-4 SQL Scripts for Generating AWR Reports

Task	SQL Script	Description
Generating an AWR Report for the Local Database	`awrrpt.sql`	Generates an AWR report in HTML or text format that displays statistics from a range of snapshot IDs in the local database instance.
Generating an AWR Report for a Specific Database	`awrrpti.sql`	Generates an AWR report in HTML or text format that displays statistics from a range of snapshot IDs in a specific database instance.
Generating an AWR Report for the Local Database in Oracle RAC	`awrgrpt.sql`	Generates an AWR report in HTML or text format that displays statistics from a range of snapshot IDs in the local database instance in an Oracle RAC environment.
Generating an AWR Report for a Specific Database in Oracle RAC	`awrgrpti.sql`	Generates an AWR report in HTML or text format that displays statistics from a range of snapshot IDs in a specific database instance in an Oracle RAC environment.
Generating an AWR Report for a SQL Statement on the Local Database	`awrsqrpt.sql`	Generates an AWR report in HTML or text format that displays statistics for a particular SQL statement from a range of snapshot IDs in the local database instance.
Generating an AWR Report for a SQL Statement on a Specific Database	`awrsqrpi.sql`	Generates an AWR report in HTML or text format that displays statistics for a particular SQL statement from a range of snapshot IDs in a specific database instance.

For more information, refer to How to Collect Standard Diagnostic Information Using AWR Reports for Performance Issues (Doc ID 1903158.1)

Generating an AWR Report for the Local Database

The awrrpt.sql SQL script generates an HTML or text report that displays statistics from a range of snapshot IDs.

To generate an AWR report on the local database instance using the command-line interface:

At the SQL prompt, enter:
```
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
```
Specify whether you want an HTML or a text report:
```
Enter value for report_type: text
```
In this example, a text report is chosen.
Specify the number of days for which you want to list snapshot IDs.
```
Enter value for num_days: 2
```
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.
Specify a beginning and ending snapshot ID for the workload repository report:
```
Enter value for begin_snap: 150
Enter value for end_snap: 160
```
In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.
Enter a report name, or accept the default report name:
```
Enter value for report_name: 
Using the report name awrrpt_1_150_160
```
In this example, the default name is accepted and an AWR report named awrrpt_1_150_160 is generated.

Generating an AWR Report for a Specific Database

The awrrpti.sql SQL script generates an HTML or text report that displays statistics from a range of snapshot IDs using a specific database instance. This script enables you to specify a database identifier and instance for which the AWR report will be generated.

To generate an AWR report on a specific database instance using the command-line interface:

At the SQL prompt, enter:
```
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
```

Specify whether you want an HTML or a text report:

Enter value for report_type: text

In this example, a text report is chosen.

A list of available database identifiers and instance numbers are displayed:

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 3309173529        1 MAIN         main         examp1690
 3309173529        1 TINT251      tint251      samp251

Enter the values for the database identifier (dbid) and instance number (inst_num):
```
Enter value for dbid: 3309173529
Using 3309173529 for database Id
Enter value for inst_num: 1
```
Note:

For an ADG standby database, the value for dbid can be determined as follows:
- For a Destination node, use the value of v$database.con_dbid .
- For a Source node, use the value of dbms_umf.get_node_id_local().
Specify the number of days for which you want to list snapshot IDs.
```
Enter value for num_days: 2
```
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.
Specify a beginning and ending snapshot ID for the workload repository report:
```
Enter value for begin_snap: 150
Enter value for end_snap: 160
```
In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.
Enter a report name, or accept the default report name:
```
Enter value for report_name: 
Using the report name awrrpt_1_150_160
```
In this example, the default name is accepted and an AWR report named awrrpt_1_150_160 is generated on the database instance with a database ID value of 3309173529.

Generating an AWR Report for the Local Database in Oracle RAC

The awrgrpt.sql SQL script generates an HTML or text report that displays statistics from a range of snapshot IDs using the current database instance in an Oracle Real Application Clusters (Oracle RAC) environment.

Note:

In an Oracle RAC environment, Oracle recommends generating an HTML report (instead of a text report) because it is much easier to read.

To generate an AWR report for Oracle RAC on the local database instance using the command-line interface:

At the SQL prompt, enter:
```
@$ORACLE_HOME/rdbms/admin/awrgrpt.sql
```
Specify whether you want an HTML or a text report:
```
Enter value for report_type: html
```
In this example, an HTML report is chosen.
Specify the number of days for which you want to list snapshot IDs.
```
Enter value for num_days: 2
```
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last day are displayed.
Specify a beginning and ending snapshot ID for the workload repository report:
```
Enter value for begin_snap: 150
Enter value for end_snap: 160
```
In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.
Enter a report name, or accept the default report name:
```
Enter value for report_name: 
Using the report name awrrpt_rac_150_160.html
```
In this example, the default name is accepted and an AWR report named awrrpt_rac_150_160.html is generated.

Generating an AWR Report for a Specific Database in Oracle RAC

The awrgrpti.sql SQL script generates an HTML or text report that displays statistics from a range of snapshot IDs using specific databases instances running in an Oracle RAC environment. This script enables you to specify database identifiers and a comma-delimited list of database instances for which the AWR report will be generated.

Note:

In an Oracle RAC environment, Oracle recommends generating an HTML report (instead of a text report) because it is much easier to read.

To generate an AWR report for Oracle RAC on a specific database instance using the command-line interface:

At the SQL prompt, enter:
```
@$ORACLE_HOME/rdbms/admin/awrgrpti.sql
```

Specify whether you want an HTML or a text report:

Enter value for report_type: html

In this example, an HTML report is chosen.

A list of available database identifiers and instance numbers are displayed:

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 3309173529        1 MAIN         main         examp1690
 3309173529        1 TINT251      tint251      samp251
 3309173529        2 TINT251      tint252      samp252

Enter the value for the database identifier (dbid):

Enter value for dbid: 3309173529
Using 3309173529 for database Id

Enter the value for the instance numbers (instance_numbers_or_all) of the Oracle RAC instances you want to include in the report:
```
Enter value for instance_numbers_or_all: 1,2
```
Specify the number of days for which you want to list snapshot IDs.
```
Enter value for num_days: 2
```
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.
Specify a beginning and ending snapshot ID for the workload repository report:
```
Enter value for begin_snap: 150
Enter value for end_snap: 160
```
In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.
Enter a report name, or accept the default report name:
```
Enter value for report_name: 
Using the report name awrrpt_rac_150_160.html
```
In this example, the default name is accepted and an AWR report named awrrpt_rac_150_160.html is generated on the database instance with a database ID value of 3309173529.

Generating an AWR Report for a SQL Statement on the Local Database

The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement from a range of snapshot IDs. Run this report to inspect or debug the performance of a SQL statement.

To generate an AWR report for a SQL statement on the local database instance using the command-line interface:

At the SQL prompt, enter:
```
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
```
Specify whether you want an HTML or a text report:
```
Enter value for report_type: html
```
In this example, an HTML report is chosen.
Specify the number of days for which you want to list snapshot IDs.
```
Enter value for num_days: 1
```
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.
Specify a beginning and ending snapshot ID for the workload repository report:
```
Enter value for begin_snap: 146
Enter value for end_snap: 147
```
In this example, the snapshot with a snapshot ID of 146 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 147 is selected as the ending snapshot.
Specify the SQL ID of a particular SQL statement to display statistics:
```
Enter value for sql_id: 2b064ybzkwf1y
```
In this example, the SQL statement with a SQL ID of 2b064ybzkwf1y is selected.
Enter a report name, or accept the default report name:
```
Enter value for report_name: 
Using the report name awrrpt_1_146_147.html
```
In this example, the default name is accepted and an AWR report named awrrpt_1_146_147 is generated.

Generating an AWR Report for a SQL Statement on a Specific Database

The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement from a range of snapshot IDs using a specific database instance.This script enables you to specify a database identifier and instance for which the AWR report will be generated. Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.

To generate an AWR report for a SQL statement on a specific database instance using the command-line interface:

At the SQL prompt, enter:
```
@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
```

Specify whether you want an HTML or a text report:

Enter value for report_type: html

In this example, an HTML report is chosen.

A list of available database identifiers and instance numbers are displayed:

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
 3309173529        1 MAIN         main         examp1690
 3309173529        1 TINT251      tint251      samp251

Enter the values for the database identifier (dbid) and instance number (inst_num):

Enter value for dbid: 3309173529
Using 3309173529 for database Id
Enter value for inst_num: 1
Using 1 for instance number

Specify the number of days for which you want to list snapshot IDs.
```
Enter value for num_days: 1
```
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.
Specify a beginning and ending snapshot ID for the workload repository report:
```
Enter value for begin_snap: 146
Enter value for end_snap: 147
```
In this example, the snapshot with a snapshot ID of 146 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 147 is selected as the ending snapshot.
Specify the SQL ID of a particular SQL statement to display statistics:
```
Enter value for sql_id: 2b064ybzkwf1y
```
In this example, the SQL statement with a SQL ID of 2b064ybzkwf1y is selected.
Enter a report name, or accept the default report name:
```
Enter value for report_name: 
Using the report name awrrpt_1_146_147.html
```
In this example, the default name is accepted and an AWR report named awrrpt_1_146_147 is generated on the database instance with a database ID value of 3309173529.