Automatic Workload Repository (AWR) in Oracle Database 10g

Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).

• AWR Features

AWR Features

The AWR is used to collect performance statistics including:

• Wait events used to identify performance problems.


• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.


• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.


• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.


• Object usage statistics.


• Resource intensive SQL statements.

The repository is a source of information for several other Oracle 10g features including:

• Automatic Database Diagnostic Monitor


• SQL Tuning Advisor


• Undo Advisor


• Segment Advisor

Snapshots

By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
    interval  => 30);          -- Minutes. Current value retained if NULL.
END;
/

The changes to the settings are reflected in the DBA_HIST_WR_CONTROL view. Typically the retention period should capture at least one complete workload cycle. If you system has monthly archive and loads a 1 month retention time would be more beneficial that the default 7 days. An interval of "0" switches off snapshot collection, which in turn stops much of the self-tuning functionality, hence this is not recommended. Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics.

Extra snapshots can be taken and existing snapshots can be removed using:

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
    low_snap_id  => 22,
    high_snap_id => 32);
END;
/

Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.

Baselines

A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing like:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 210,
    end_snap_id   => 220,
    baseline_name => 'batch baseline');
END;
/

The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_baseline (
    baseline_name => 'batch baseline',
    cascade       => FALSE); -- Deletes associated snapshots if TRUE.
END;
/

Baseline information can be queried from the DBA_HIST_BASELINE view.

Workload Repository Views

The following workload repository views are available:

• V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.


• V$METRIC - Displays metric information.


• V$METRICNAME - Displays the metrics associated with each metric group.


• V$METRIC_HISTORY - Displays historical metrics.


• V$METRICGROUP - Displays all metrics groups.


• DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.


• DBA_HIST_BASELINE - Displays baseline information.


• DBA_HIST_DATABASE_INSTANCE - Displays database environment information.


• DBA_HIST_SNAPSHOT - Displays snapshot information.


• DBA_HIST_SQL_PLAN - Displays SQL execution plans.


• DBA_HIST_WR_CONTROL - Displays AWR settings.

Workload Repository Reports

Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql

The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.

AWR Report: To generate a 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 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: 100

Enter value for end_snap: 120

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_100_120

The workload repository report is thus generated.

Below is a sample output of the AWR Report.

WORKLOAD REPOSITORY report for

DB Name	DB Id	Instance	Inst num	Release	Cluster	Host
TEST	118468335	TEST	1	10.1.0.4.0	NO	test-db.ora.hp

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	834	11-Jul-06 01:27:31	1732	177.7
End Snap:	836	11-Jul-06 02:27:36	2038	196.3
Elapsed:		60.08 (mins)
DB Time:		381.35 (mins)

Report Summary

Cache Sizes (end)

Buffer Cache:	1,376M	Std Block Size:	8K
Shared Pool Size:	1,856M	Log Buffer:	10,240K

Load Profile

	Per Second	Per Transaction
Redo size:	2,389,415.27	27,164.76
Logical reads:	108,397.97	1,232.35
Block changes:	15,015.45	170.71
Physical reads:	158.28	1.80
Physical writes:	247.04	2.81
User calls:	3,714.26	42.23
Parses:	797.76	9.07
Hard parses:	1.97	0.02
Sorts:	1,014.79	11.54
Logons:	0.77	0.01
Executes:	7,300.46	83.00
Transactions:	87.96

% Blocks changed per Read:	13.85	Recursive Call %:	76.60
Rollback per transaction %:	22.64	Rows per Sort:	12.46

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	99.99	Redo NoWait %:	100.00
Buffer Hit %:	99.86	In-memory Sort %:	100.00
Library Hit %:	99.98	Soft Parse %:	99.75
Execute to Parse %:	89.07	Latch Hit %:	99.57
Parse CPU to Parse Elapsd %:	84.64	% Non-Parse CPU:	96.72

Shared Pool Statistics

	Begin	End
Memory Usage %:	51.28	71.99
% SQL with executions>1:	85.40	93.22
% Memory for SQL w/exec>1:	78.16	85.79

Top 5 Timed Events

Event	Waits	Time(s)	Percent Total DB Time	Wait Class
CPU time		17,107	74.76
db file sequential read	498,214	2,773	12.12	User I/O
log file sync	129,031	2,010	8.78	Commit
log file parallel write	445,391	517	2.26	System I/O
latch: library cache	26,609	141	.62