Automatic Workload Repository (AWR) Basics

The Automatic Workload Repository (AWR) automatically persists the cumulative and delta values for most of the statistics at all levels except the session level.

This process is repeated on a regular time period and the result is called an AWR snapshot. The delta values captured by the snapshot represent the changes for each statistic over the time period.

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database.

Concept of STATISTICS_LEVEL Parameter:

Gathering database statistics using the AWR is enabled by default and is controlled by the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVEL parameter should be set to the TYPICAL or ALL to enable statistics gathering by the AWR. The default setting is TYPICAL. Setting STATISTICS_LEVEL to BASIC disables many Oracle Database features, including the AWR, and is not recommended.

SQL> show parameter STATISTICS_LEVEL
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

Concept of Snapshots

Snapshots are sets of historical data for specific time periods that are used for performance comparisons by ADDM.

By default, Oracle Database automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 8 days.

Managing Snapshots

OEM is easy to use tool for managing snapshots but if you don’t use OEM then go for below manual ways.

A) Creating Snapshots:

You can manually create snapshots with the CREATE_SNAPSHOT procedure to capture statistics at times different than those of the automatically generated snapshots. For example:

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

B) Dropping Snapshots

You can drop a range of snapshots using the DROP_SNAPSHOT_RANGE procedure. To view a list of the snapshot IDs along with database IDs, check the DBA_HIST_SNAPSHOT view. For example, you can drop the following range of snapshots:

BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 22, high_snap_id => 32, dbid => 3310949047);
END;
/

Active Session History data (ASH) that belongs to the time period specified by the snapshot range is also purged when the DROP_SNAPSHOT_RANGE procedure is called.

C) Modifying Snapshot Settings

You can adjust the interval, retention, and captured Top SQL of snapshot generation for a specified database ID, but note that this can affect the precision of the Oracle Database diagnostic tools.

The INTERVAL setting affects how often the database automatically generates snapshots. The RETENTION setting affects how long the database stores snapshots in the workload repository. The TOPNSQL setting affects the number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, sharable Memory, and Version Count). The value for this setting is not affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. It is possible to set the value for this setting to MAXIMUM to capture the complete set of SQL in the shared SQL area, though by doing so (or by setting the value to a very high number) may lead to possible space and performance issues because there will more data to collect and store. To adjust the settings, use the MODIFY_SNAPSHOT_SETTINGS procedure. For example:

BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200, interval => 30, topnsql => 100, dbid => 3310949047);
END;
/

Concept of Baselines

A baseline contains performance data from a specific time period that is preserved for comparison with other similar workload periods when performance problems occur. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely.

There are several types of available baselines in Oracle Database:

■ Fixed Baselines

A fixed baseline corresponds to a fixed, contiguous time period in the past

■ Moving Window Baseline

A moving window baseline corresponds to all AWR data that exists within the AWR retention period. Oracle Database automatically maintains a system-defined moving window baseline. The default window size for the system-defined moving window baseline is the current AWR retention period, which by default is 8 days

■ Baseline Templates

You can also create baselines for a contiguous time period in the future using baseline templates. There are two types of baseline templates: single and repeating.

You can use a single baseline template to create a baseline for a single contiguous time period in the future.

You can use a repeating baseline template to create and drop baselines based on a repeating time schedule. This is useful if you want Oracle Database to automatically capture a contiguous time period on an ongoing basis.

Managing the Baselines

A) Creating a Baseline

1.Review the existing snapshots in the DBA_HIST_SNAPSHOT view to determine the range of snapshots to use.

2.Use the CREATE_BASELINE procedure to create a baseline using the desired range of snapshots:

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 270,
end_snap_id => 280, baseline_name => 'peak baseline',
dbid => 3310949047, expiration => 30);
END;
/

B) Dropping a Baseline

Periodically, you may want to drop a baseline that is no longer used to conserve disk space. The snapshots associated with a baseline are retained indefinitely until you explicitly drop the baseline or the baseline has expired.

To drop a baseline:

1.Review the existing baselines in the DBA_HIST_BASELINE view to determine the baseline to drop.

2.Use the DROP_BASELINE procedure to drop the desired baseline:

BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline',
cascade => FALSE, dbid => 3310949047);
END;
/

In the example, the name of baseline is peak baseline. The cascade parameter is set to FALSE, which specifies that only the baseline is dropped. Setting this parameter to TRUE specifies that the drop operation will also remove the snapshots associated with the baseline. The optional dbid parameter specifies the database. identifier, which in this example is 3310949047. If you do not specify a value for dbid, then the local database identifier is used as the default value.

C) Renaming a Baseline

To rename a baseline:

1.Review the existing baselines in the DBA_HIST_BASELINE view to determine the baseline to rename.

2.Use the RENAME_BASELINE procedure to rename the desired baseline:

BEGIN
DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (
old_baseline_name => 'peak baseline',
new_baseline_name => 'peak mondays',
dbid => 3310949047);
END;
/

In this example, the name of the baseline is renamed from peak baseline, as specified by the old_baseline_name parameter, to peak mondays, as specified by the new_baseline_name parameter.

Generating Automatic Workload Repository Reports

1) Generating an AWR Report

At the SQL prompt, enter:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Enter value for report_type: text or HTML
Enter value for num_days: 2
Enter value for begin_snap: 150
Enter value for end_snap: 160
Enter value for report_name:
Using the report name awrrpt_1_150_160

2) Generating an Oracle RAC AWR Report

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

At the SQL prompt, enter:

@$ORACLE_HOME/rdbms/admin/awrgrpt.sql
Enter value for report_type: text or HTML
Enter value for num_days: 2
Enter value for begin_snap: 150
Enter value for end_snap: 160
Enter value for report_name:
Using the report name awrrpt_rac_150_160.html

3) Generating an AWR Report on a Specific Database Instance

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

@$ORACLE_HOME/rdbms/admin/awrrpti.sql
Enter value for report_type: text
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 value for dbid: 3309173529
Enter value for inst_num: 1
Enter value for num_days: 2
Enter value for num_days: 2
Enter value for begin_snap: 150
Enter value for end_snap: 160
Enter value for report_name:
Using the report name awrrpt_1_150_160

4) Generating an Oracle RAC AWR Report on Specific Database Instances

The awrgrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot IDs using specific databases and instances running in an Oracle RAC environment.

@$ORACLE_HOME/rdbms/admin/awrgrpti.sql
Enter value for report_type: html
Enter value for dbid: 3309173529
Enter value for instance_numbers_or_all: 1,2
Enter value for num_days: 2
Enter value for begin_snap: 150
Enter value for end_snap: 160
Enter value for report_name:
Using the report name awrrpt_rac_150_160.html

5) Generating an AWR Report for a SQL Statement

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.

@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
Enter value for report_type: html
Enter value for num_days: 1
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
Enter value for report_name:
Using the report name awrrpt_1_146_147.html

6) Generating an AWR Report for a SQL Statement on a Specific Database Instance

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 using a specific database and instance.

@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
Enter value for report_type: html
Enter value for dbid: 3309173529
Using 3309173529 for database Id
Enter value for inst_num: 1
Using 1 for instance number
Enter value for num_days: 1
Enter value for begin_snap: 146
Enter value for end_snap: 147
Enter value for sql_id: 2b064ybzkwf1y
Enter value for report_name:
Using the report name awrrpt_1_146_147.html

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 time periods. Based on the differences, you can more easily diagnose the cause of the performance degradation. 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.

1) Generating an AWR Compare Periods Report

@$ORACLE_HOME/rdbms/admin/awrddrpt.sql
Enter value for report_type: html
Enter value for num_days: 2
Specify a beginning and ending snapshot ID for the first time period:
Enter value for begin_snap: 102
Enter value for end_snap: 103
Specify the number of days for which you want to list snapshot IDs in the second time period.
Enter value for num_days2: 1
Specify a beginning and ending snapshot ID for the second time period:
Enter value for begin_snap2: 126
Enter value for end_snap2: 127
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

2) Generating an Oracle RAC AWR Compare Periods Report

@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql
Enter value for report_type: html
Enter value for num_days: 2
Specify a beginning and ending snapshot ID for the first time period:
Enter value for begin_snap: 102
Enter value for end_snap: 103
Specify the number of days for which you want to list snapshot IDs in the second time period.
Enter value for num_days2: 1
Specify a beginning and ending snapshot ID for the second time period:
Enter value for begin_snap2: 126
Enter value for end_snap2: 127
Enter a report name, or accept the default report name:
Enter value for report_name:
Using the report name awrracdiff_1st_1_2nd_1.html

3) Generating an AWR Compare Periods Report on a Specific Database Instance

@$ORACLE_HOME/rdbms/admin/awrddrpi.sql
Enter value for report_type: text

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
3309173529 1 ORA ORA1 examp1690
3309173529 1 ORA2 ORA251 samp251

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
Enter value for num_days: 2
Enter value for begin_snap: 102
Enter value for end_snap: 103
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
Enter value for num_days2: 1
Enter value for begin_snap2: 126
Enter value for end_snap2: 127
Enter value for report_name:
Using the report name awrdiff_1_102_1_126.txt

4) Generating an Oracle RAC AWR Compare Periods Report on Specific Database Instances

The awrgdrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods using specific databases and instances in an Oracle RAC environment.

@$ORACLE_HOME/rdbms/admin/awrgdrpi.sql
Enter value for report_type: html

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

Enter value for dbid: 3309173529
Using 3309173529 for Database Id for the first pair of snapshots
Enter value for inst_num: 1,2
Using instances 1 for the first pair of snapshots
Enter value for num_days: 2
Enter value for begin_snap: 102
Enter value for end_snap: 103

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
3309173529 1 MAIN main examp1690
3309173529 1 ORA251 ORA251 samp251
3309173529 2 ORA251 ORA252 samp252
Enter value for dbid2: 3309173529
Using 3309173529 for Database Id for the second pair of snapshots
Enter value for instance_numbers_or_all2: 3,4
Enter value for num_days2: 1
Specify a beginning and ending snapshot ID for the second time period:
Enter value for begin_snap2: 126
Enter value for end_snap2: 127
Enter value for report_name:
Using the report name awrracdiff_1st_1_2nd_1.html