Automatic Workload Repository Compare Period report

Generating Automatic Workload Repository Compare Periods Reports
While an AWR report shows AWR data between two snapshots (or two points in time), the AWR Compare Periods report shows the difference between two periods (or two AWR reports, which equates to four snapshots). Using the AWR Compare Periods report helps you to identify detailed performance attributes and configuration settings that differ between two time periods. For example, if the application workload is known to be stable between 10:00 p.m. and midnight every night, but the performance on a particular Thursday was poor between 10:00 p.m. and 11:00 p.m., generating an AWR Compare Periods report for Thursday from 10:00 p.m. to 11:00 p.m. and Wednesday from 10:00 p.m. to 11:00 p.m. should identify configuration settings, workload profile, and statistics that were different in these two time periods. Based on the differences identified, the cause of the performance degradation can be more easily diagnosed. The two time periods selected for the AWR Compare Periods Report can be of different durations, because the report normalizes the statistics by the amount of time spent on the database for each time period, and presents statistical data ordered by the largest difference between the periods.
The AWR Compare Periods reports are divided into multiple sections. The HTML report includes links that can be used to navigate quickly between sections. The content of the report contains the workload profile of the system for the selected range of snapshots.
The primary interface for generating AWR Compare Periods reports is Oracle Enterprise Manager. Whenever possible, you should generate AWR Compare Periods reports using Oracle Enterprise Manager, as described in Oracle Database 2 Day + Performance Tuning Guide. If Oracle Enterprise Manager is unavailable, you can generate AWR Compare Periods reports by running SQL scripts, as described in the following sections:
Generating an AWR Compare Periods Report
Generating an AWR Compare Periods Report on a Specified Database Instance
To run these scripts, you must be granted the DBA role.
5.3.7.1 Generating an AWR Compare Periods Report
The awrddrpt.sql SQL script. generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.
To generate an AWR Compare Periods report:
At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrddrpt.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 in the first time period.
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 first time period:
Enter value for begin_snap: 102
Enter value for end_snap: 103
In this example, the snapshot with a snapshot ID of 102 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 103 is selected as the ending snapshot for the first time period.
Specify the number of days for which you want to list snapshot Ids in the second time period.
Enter value for num_days2: 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 second time period:
Enter value for begin_snap2: 126
Enter value for end_snap2: 127
In this example, the snapshot with a snapshot ID of 126 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 127 is selected as the ending snapshot for the second time period.
Enter a report name, or accept the default report name:
Enter value for report_name:
Using the report name awrdiff_1_102_1_126.txt
In this example, the default name is accepted and an AWR report named awrdiff_1_102_126 is generated.
5.3.7.2 Generating an AWR Compare Periods Report on a Specified Database Instance
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. This script. enables you to specify a database and instance before selecting time periods to compare.
To generate an AWR Compare Periods report on a specified database instance:
At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrddrpi.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) for the first time period:
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
Specify the number of days for which you want to list snapshot Ids in the first time period.
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 first time period:
Enter value for begin_snap: 102
Enter value for end_snap: 103
In this example, the snapshot with a snapshot ID of 102 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 103 is selected as the ending snapshot for the first time period.
Enter the values for the database identifier (dbid) and instance number (inst_num) for the second time period:
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 in the second time period.
Enter value for num_days2: 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 second time period:
Enter value for begin_snap2: 126
Enter value for end_snap2: 127
In this example, the snapshot with a snapshot ID of 126 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 127 is selected as the ending snapshot for the second time period.
Enter a report name, or accept the default report name:
Enter value for report_name:
Using the report name awrdiff_1_102_1_126.txt
In this example, the default name is accepted and an AWR report named awrdiff_1_102_126 is generated on the database instance with a database ID value of 3309173529.