jump to navigation

Automatic Workload Repository (AWR) Performance Monitoring Tool Basics May 5, 2012

Posted by Mich Talebzadeh in Oracle.

What is AWR Starting from Oracle 10g statspack utility has evolved into the Automatic Workload Repository (AWR). AWR is a comprehensive and powerful tool for monitoring Oracle performance. Unlike tracing, AWR is not enabled for the session. It is for the complete database. It also requires enterprise licence. The AWR collects and stores database statistics relating to problem detection and tuning. AWR is an evolvement for the statspack utility which helps gather database performance statistics. AWR generates snapshots of key matrices such as system and session statistics, cpu, waits, redo, writes and other time-model statistics. AWR stores data in SYSAUX tablespace. The background process MMON is responsible for collecting the statistics in the SGA and saving them to the SYSAUX tablespaces. AWR provides statistics in two formats

  • temporary – in memory collection of statistics in the SGA, accessible via the V$ views
  • persistent – type of performance data in the form of regular AWR snapshots which are accessible via the DBA_ views

It will collect data on the following matrices

  • Base statistics that are also part of the v$SYSSTAT and V$SESSTAT views
  • SQL statistics
  • Database object-usage statistics
  • Time-model statistics
  • Wait statistics
  • ASH (active session history) statistics
  • Operating system statistics

AWR uses the following V$ views to collect statistics

  • v$sys_time_model time model stats (db time, java execution time, pl/sql execution time and others)
  • v$osstat Operating system stats (avg_busy_ticks, avg_idle_ticks, etc)
  • v$service_stats wait statistics ( db cpu, app wait time, user commits, etc)
  • v$sysstat system stats
  • v$sesstat session stats

Database performance stats fall into one of three categories:

  • Cumulative values – collect stats over a period of time from the v$sysstat, etc
  • Metrics – use the collected stats to make some sort of sense.
  • Sampled data – the ASH sampler is used to collect these stats.

Setting up AWR Statistics in Oracle depends on the system parameter statistics_level. By default this parameter is set to typical which collects standard level of statistics. There is more comprehensive level called all that collects execution plans and timing info. Beware that setting this value in production can result in a typical four times overhead. It will show all roll over timings as well (say in the output of autotrace or tkprof) but at a cost of running the query much slower! AWR can be disabled by setting statistics_level to basic. Beware that this disables a lot more than just AWR. To display the current setting for statistics_level do

show parameter statistics_level;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL
-- To set up statistics level to typical do
alter system set statistics_level = typical;
-- To set up statistics level to full do
alter system set statistics_level = all;
-- to diable AWR and many other things
alter system set statistics_level = basic;

Snapshotting Most monitor tools work with intervals and snapshots and AWR is no exception. Oracle provides dbms_workload_repository package to define AWR snapshots. Alternatively you can use Enterprise Manager To display a list of snapshots do

select * from (select snap_id, snap_level, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') AS begin from dba_hist_snapshot order by 1) where rownum <=4;
SNAP_ID SNAP_LEVEL BEGIN ---------- ---------- -----------------       3908          1 28/04/12 08:26:10       3909          1 28/04/12 08:36:28       3910          1 28/04/12 10:00:00       3911          1 28/04/12 11:00:49

To see snapshot interval and retention period do

select  snap_interval
,       retention
from    dba_hist_wr_control;
------------------------------ ------------------------------
+00000 01:00:00.0              +00008 00:00:00.0

You can use modify_snapshot_settings procedurte in dbms_workload_repository package to change the snapshot settings. For example to change the interval to 30minutes you can do

exec dbms_workload_repository.modify_snapshot_settings (interval => 30) ;

To change the retention period to two weeks you can do

exec dbms_workload_repository.modify_snapshot_settings (retention => 14*24*60)

To manually create a snalpshot you can do:

exec dbms_workload_repository.create_snapshot();

To delete snapshots

exec dbms_workload_repository.drop_snapshot_range (low_snap_id => 1077, high_snap_id => 1078);

To create a baseline

exec dbms_workload_repository.create_baseline (start_snap_id => 1110, end_snap_id => 1120, baseline_name => 'Batch baseline');

To delete a basline

exec dbms_workload_repository.drop_baseline (baseline_name => 'Batch baseline', cascade => FALSE);

Generating AWR Reports DBAs can run AWR reports. The most common method of generating AWR report is to run the following piece of sql


For example as user system I run this report


Current Instance ~~~~~~~~~~~~~~~~

DB Id    DB Name      Inst Num Instance ----------- ------------ -------- ------------  2678249386 MYDB                1 mydb

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

Type Specified:  text

Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id     Inst Num DB Name      Instance     Host ------------ -------- ------------ ------------ ------------ * 2678249386        1 MYDB         mydb         rhes564

Using 2678249386 for database Id Using          1 for instance number

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  without specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day's Completed Snapshots

Snap Instance     DB Name        Snap Id    Snap Started    Level ------------ ------------ --------- ------------------ ----- mydb         MYDB              4121 06 May 2012 00:00      1                                4122 06 May 2012 01:00      1                                4123 06 May 2012 02:00      1                                4124 06 May 2012 03:00      1                                4125 06 May 2012 04:00      1                                4126 06 May 2012 05:00      1                                4127 06 May 2012 06:00      1                                4128 06 May 2012 07:00      1                                4129 06 May 2012 08:00      1                                4130 06 May 2012 09:00      1                                4131 06 May 2012 10:00      1                                4132 06 May 2012 11:00      1                                4133 06 May 2012 12:00      1                                4134 06 May 2012 13:00      1
Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 4128 Begin Snapshot Id specified: 4128

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

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

Enter value for report_name:

The report will be generated and in the current working directory.


No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: