AWR Report

The automatic workload repository is a new performance statistics gathering and reporting utility introduced in Oracle 10g. AWR is similar to Statspack with improved functionalities and features. It can be called as the next generation of Statspack. Oracle documentation recommends the use of AWR over Statspack even though statspack is still functional on 10g databases.

Features of Automatic Workload Repository

By default, AWR generates the system snapshots every 60 minutes and stores the information in the workload repository. Oracle utilizes the new background process MMON for this purpose. The data is retained in the repository for 7 days. It is possible to change the retention period and the snapshot interval. The space consumed by the repository mainly depends on these two factors. The space consumption can be reduced by increasing the snapshot interval and decreasing the retention period.

AWR data acts as a source of information for several Oracle 10g features like

v    Automatic Database Diagnostic monitor

v    SQL Tuning Advisor

v    Segment Advisor

v    Undo Advisor

Changing the default settings of AWR is not advisable, as it will affect the accuracy and validity of the features which depends on AWR.

The initialization parameter STATISTICS_LEVEL must be set to TYPICAL or ALL to enable Automatic Workload Repository. Typical is the default and oracle recommended value.
By setting the value to BASIC, the collection of AWR statistics is deactivated. However, the statistics can be captured manually using the procedures in DBMS_WORKLOAD_REPOSITORY package. But the statistics will not contain many system statistics like segment statistics, memory advisor data and will be incomplete.

Performance Statistics gathered by AWR

v    Wait events used to identify performance issues

v    Object statistics which helps to determine the usage/access of dba_segments

v   Time model statistics  from the V$SESS_TIME_MODEL and   V$SYS_TIME_MODEL views

v    SQL statements causing highest load on system ,based on CPU time and elapsed time

v    Some system and session statistics collected from V$SYSSTAT and V$SESSTAT views

v    Active Session History (ASH) statistics from V$_ACTIVE_SESSION_HISTORY view

AWR Snapshots and Baselines

A snapshot is a collection of database performance statistics captured at a specific point of time. Each snapshot is identified with the unique SNAP_ID.

A baseline is the performance statistics preserved for a set of snapshots, which is used to compare with similar workload period when performance issue occurs.

Creating Snapshots

Snapshots can be created manually using CREATE_SNAPSHOT procedure to capture the statistics at any time other than the automatically generated snapshots.

BEGIN
     DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ( );
END;
/

This snapshot can be viewed from DBA_HIST_SNAPSHOT view.

Dropping Snapshots

We can drop a range of snapshots using DROP_SNAPSHOT_RANGE procedure.

BEGIN
     DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (
     low_snap_id  = > 28202, high_snap_id  = > 28798 , dbid  = > 3777558519 );
END ;
/

In this case, the snapshots with snap_ids from 28202 to 28798 are deleted.
The Active Session History (ASH) for the time period within this snapshot range is purged when drop_snapshot_range procedure is used.

Note: Active Session History consists of the session data sampled every second and stored in a rolling buffer in SGA. The data can be used for the analysis of system performance at the current time as well as in recent history. The information is overwritten when required.

Altering Snapshot Settings

The snapshot interval and retention can be altered using MODIFY_SNAPSHOT_SETTINGS procedure.

BEGIN
     DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (
     retention = > 21600 , interval = > 30 , dbid  = > 3777558519 );
END ;
/

Retention specifies the time period in minutes the snapshots are stored in the workload repository.
Interval specifies the time interval in minute between two consecutive snapshots.

In this example, the retention period is 21600 minutes (15 days) and the interval between each snapshot is 30 minutes. The existing values can be preserved by specifying these parameters as ‘NULL’. An interval of “0” disables the snapshot creation. The current settings can be viewed from DBA_HIST_WR_CONTROL view.

Creating Baselines

Baseline is created using CREATE_BASELINE procedure

BEGIN
     DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (
     start_snap_id  = >  28202, end_snap_id  = > 28210 , baseline_name = > ‘        test_baseline’, dbid  = > 3777558519 );
END ;
/

Here, test baseline is the name of the baseline. Oracle automatically assigns a unique baseline Id to each baseline when it is created. The pair of snapshots associated with the baseline is retained until the baseline is explicitly dropped. The details of the baselines is reflected in DBA_HIST_BASELINE view

Dropping Baselines

Baselines are dropped using DROP_BASELINE procedure

BEGIN
     DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (
      baseline_name = > ‘ test_baseline’, cascade = > FALSE, dbid  = > 3777558519 );
END ;
/

FALSE specifies that only the baseline should be dropped. TRUE will drop the pair of snapshots associated with the baseline.

AWR Reports

Oracle has provided two scripts, awrrpt.sql and awrrpti.sql to generate AWR report.
The report can be generated either in HTML or in text format. The user must have the DBA role to generate the report.

Running awrrpt.sql

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

The script will prompt to enter the format of the report, number of days of snapshots to be displayed, begin and end snapshots and the report name.

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days: 2

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 25550
Begin Snapshot Id specified: 25550

Enter value for end_snap: 25552
End   Snapshot Id specified: 25552

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_25550_25552.txt.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_25550_25552.txt

Running awrrpti.sql

If we want to specify a database or an instance, we can use awrrpti.sql

SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

The script will list a set of database Ids, Instance number and prompt us to enter those values.

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id            Inst Num     DB Name      Instance       Host
------------             --------     ------------      ------------   ------------
  1702649309        1            EUCAND1      eucand1      vulture
* 1400894599        1              EATD1            eatd1        sanpgpdbo08

Enter value for dbid: 1400894599
Using 1400894599 for database Id

Enter value for inst_num: 1
Using 1 for instance number

ME_MODEL views

Next, the script prompts for the number of days and the snapshot Ids similar to awrrpt.sql

Note: If the report is taken for a time frame when no workload is there, the percentage for some statistics can be less than zero or greater than 100.

Accessing the Automatic Workload Repository through OEM

AWR reports can be generated and the snapshots settings can be edited through Oracle Enterprise Manger.

 On Administration page, select Automatic Workload Repository link under Statistics Management. Click on the Edit tab under General section to modify AWR settings.

Click on the Snapshots under Manage Snapshots and Preserved Snapshot Sets to generate AWR reports or to create baselines.

 AWR vs Statspack

v    AWR reports provides all the statistics available in Statspack as well as some additional statistics

v    Statspack must be installed manually whereas AWR installs along with the database

v    The AWR also contains views like dba_hist_service_wait_class, dba_hist_service_name and dba_hist_service_stat  which stores history for performance cumulative statistics .

v    Statspack report must be run by an external scheduler like dbms_jobs, crontab etc. By default, AWR snapshots are generated every one hour.

v    Statspack snapshots must be purged manually. Statspack will fail if the statspack tablespace is full. AWR snapshots are purged automatically by MMON.

v    Statspack reports may increase the load on the server, whereas AWR utilizes special process hence less affecting the performance

v    AWR supports the usage of PL/SQL packages and OEM to generate the reports, hence making it more flexible compared to statspack.

Workload Repository Views

v    DBA_HIST_WR_CONTROL -  displays the settings for AWR

v    DBA_HIST_SQL_PLAN   -       displays SQL execution plans

v    DBA_HIST_SNAPSHOTS  -    displays the snapshots information

v    DBA_HIST_BASELINE  -        displays the baseline information

v    DBA_HIST_ACTIVE_SESS_HISTORY - displays the information about the Active Session History