You can view AWR reports by running the following SQL scripts:
■ The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.
■ The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.
■ The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.
■ The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance. Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.
■ The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.
Automatic Workload Repository
■ The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.
Running the awrrpt.sql Report To generate an HTML or text report for a range of snapshot Ids, run the awrrpt.sql script at the SQL prompt:
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
First, you need to specify whether you want an HTML or a text report.
Enter value for report_type: text
Specify the number of days for which you want to list snapshot Ids.
Enter value for num_days: 2
After the list displays, you are prompted for the beginning and ending snapshot Id for the workload repository report.
Enter value for begin_snap: 150 Enter value for end_snap: 160
Next, accept the default report name or enter a report name. The default name is accepted in the following example:
Enter value for report_name:
Using the report name awrrpt_1_150_160 The workload repository report is generated.
Running the awrrpti.sql Report To specify a database and instance before entering a range of snapshot Ids, run the awrrpti.sql script at the SQL prompt to generate an HTML or text report:
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
First, specify whether you want an HTML or a text report. After that, a list of the database identifiers and instance numbers displays, similar to the following:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
--- --- 3309173529 1 MAIN main dlsun1690 3309173529 1 TINT251 tint251 stint251
Enter the values for the database identifier (dbid) and instance number (inst_num) at the prompts.
Enter value for dbid: 3309173529 Using 3309173529 for database Id Enter value for inst_num: 1
Next you are prompted for the number of days and snapshot Ids, similar to the awrrpt.sql script, before the text report is generated. See "Running the awrrpt.sql Report" on page 5-18.
Running the awrsqrpt.sql Report To generate an HTML or text report for a particular SQL statement, run the awrsqrpt.sql script at the SQL prompt:
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
Automatic Workload Repository
First, you need to specify whether you want an HTML or a text report.
Enter value for report_type: text
Specify the number of days for which you want to list snapshot Ids.
Enter value for num_days: 1
After the list displays, you are prompted for the beginning and ending snapshot Id for the workload repository report.
Enter value for begin_snap: 146 Enter value for end_snap: 147
Specify the SQL Id of a particular SQL statement to display statistics.
Enter value for sql_id: 2b064ybzkwf1y
Next, accept the default report name or enter a report name. The default name is accepted in the following example:
Enter value for report_name:
Using the report name awrsqlrpt_1_146_147.txt The workload repository report is generated.
Running the awrsqrpi.sql Report To specify a database and instance before entering a particular SQL statement Id, run the awrsqrpi.sql script at the SQL prompt to generate an HTML or text report:
@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
First, you need to specify whether you want an HTML or a text report.
Enter value for report_type: text
Next, a list of the database identifiers and instance numbers displays, similar to the following:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
--- --- 3309173529 1 MAIN main dlsun1690 3309173529 1 TINT251 tint251 stint251
Enter the values for the database identifier (dbid) and instance number (inst_num) at the prompts.
Enter value for dbid: 3309173529 Using 3309173529 for database Id Enter value for inst_num: 1 Using 1 for instance number
Next you are prompted for the number of days, snapshot Ids, SQL Id and report name, similar to the awrsqrpt.sql script, before the text report is generated. See "Running the awrsqrpt.sql Report" on page 5-18.
Running the awrddrpt.sql Report To compare detailed performance attributes and
configuration settings between two time periods, run the awrddrpt.sql script at the SQL prompt to generate an HTML or text report:
Automatic Workload Repository
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql
First, you need to specify whether you want an HTML or a text report.
Enter value for report_type: text
Specify the number of days for which you want to list snapshot Ids for the first time period.
Enter value for num_days: 2
After the list displays, you are prompted for the beginning and ending snapshot Id for the first time period.
Enter value for begin_snap: 102 Enter value for end_snap: 103
Next, specify the number of days for which you want to list snapshot Ids for the second time period.
Enter value for num_days2: 1
After the list displays, you are prompted for the beginning and ending snapshot Id for the second time period.
Enter value for begin_snap2: 126 Enter value for end_snap2: 127
Next, accept the default report name or enter a report name. The default name is accepted in the following example:
Enter value for report_name:
Using the report name awrdiff_1_102_1_126.txt The workload repository report is generated.
Running the awrddrpi.sql Report To specify a database and instance before selecting time periods to compare, run the awrddrpi.sql script at the SQL prompt to generate an HTML or text report:
@$ORACLE_HOME/rdbms/admin/awrddrpi.sql
First, you need to specify whether you want an HTML or a text report.
Enter value for report_type: text
Next, a list of the database identifiers and instance numbers displays, similar to the following:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
--- --- 3309173529 1 MAIN main dlsun1690 3309173529 1 TINT251 tint251 stint251
Enter the values for the database identifier (dbid) and instance number (inst_num) for the first time period at the prompts.
Enter value for dbid: 3309173529
Using 3309173529 for Database Id for the first pair of snapshots Enter value for inst_num: 1
Using 1 for Instance Number for the first pair of snapshots
Automatic Workload Repository
Specify the number of days for which you want to list snapshot Ids for the first time period.
Enter value for num_days: 2
After the list displays, you are prompted for the beginning and ending snapshot Id for the first time period.
Enter value for begin_snap: 102 Enter value for end_snap: 103
Next, enter the values for the database identifier (dbid) and instance number (inst_
num) for the second time period at the prompts.
Enter value for dbid2: 3309173529
Using 3309173529 for Database Id for the second pair of snapshots Enter value for inst_num2: 1
Using 1 for Instance Number for the second pair of snapshots
Specify the number of days for which you want to list snapshot Ids for the second time period.
Enter value for num_days2: 1
After the list displays, you are prompted for the beginning and ending snapshot Id for the second time period.
Enter value for begin_snap2: 126 Enter value for end_snap2: 127
Next, accept the default report name or enter a report name. The default name is accepted in the following example:
Enter value for report_name:
Using the report name awrdiff_1_102_1_126.txt The workload repository report is generated.