Terry : AWR Reports

This page last changed on Aug 28, 2012 by terry.

Automatic Workload Repository reports

Introduction

AWR periodically gathers and stores system activity and workload data which is then analyzed by ADDM. Every layer of Oracle is equipped with instrumentation that gathers information on workload which will then be used to make self-managing decisions. AWR is the place where this data is stored. AWR looks periodically at the system performance (by default every 60 minutes) and stores the information found (by default up to 7 days). AWR runs by default and Oracle states that it does not add a noticeable level of overhead. A new background server process (MMON) takes snapshots of the in-memory database statistics (much like STATSPACK) and stores this information in the repository. MMON also provides Oracle10G with a server initiated alert feature, which notifies database administrators of potential problems (out of space, max extents reached, performance thresholds, etc.). The information is stored in the sysaux tablespace under the SYS Schema. This information is the basis for all self-management decisions. For example, it is thus possible to identify the SQL statements that have the

  • largest CPU consumption
  • most buffer gets
  • disk reads
  • most parse calls
  • shared memory

To access Automatic Workload Repository through Oracle Enterprise Manager Database Control:

On the Administration page, select the Workload Repository link under Workload. From the Automatic Workload Repository page, you can manage snapshots or modify AWR settings.

To manage snapshots, click the link next to Snapshots or Preserved Snapshot Sets. On the Snapshots or Preserved Snapshot Sets pages, you can:

  • View information about snapshots or preserved snapshot sets (baselines).
  • Perform a variety of tasks through the pull-down Actions menu, including creating additional snapshots, preserved snapshot sets 

from an existing range of snapshots, or an ADDM task to perform analysis on a range of snapshots or a set of preserved snapshots.

To modify AWR settings, click the Edit button. On the Edit Settings page, you can set the Snapshot Retention period and Snapshot Collection interval.

Generate AWR snapshots

exec dbms_workload_repository.create_snapshot;

You can see what snapshots are currently in the AWR by using the DBA_HIST_SNAPSHOT view as seen in this example:

SELECT snap_id, to_char(begin_interval_time,'dd/MON/yy hh24:mi') Begin_Interval,
       to_char(end_interval_time,'dd/MON/yy hh24:mi') End_Interval
FROM dba_hist_snapshot
ORDER BY 1;

Sometimes you might want to drop snapshots manually. The dbms_workload_repository.drop_snapshot_range procedure can be used to remove a range of snapshots from the AWR. This procedure takes two parameters, low_snap_id and high_snap_id, as seen in this example:

exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>1107, high_snap_id=>1108);

Finally , you can use the following query to identify the occupants of the SYSAUX Tablespace

select substr(occupant_name,1,40), space_usage_kbytes
   from v$sysaux_occupants;

Generate WR 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

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:

Linux and Unix

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

Windows

@%ORACLE_HOME%\rdbms\admin\awrrpt.sql
@%ORACLE_HOME%\rdbms\admin\awrrpti.sql

AWR Automated Snapshots

Oracle Database 10g uses a scheduled job, GATHER_STATS_JOB, to collect AWR statistics. This job is created, and enabled automatically, when you create a new Oracle database under Oracle Database 10g. To see this job, use the DBA_SCHEDULER_JOBS view as seen in this example:

SELECT a.job_name, a.enabled, c.window_name, c.schedule_name, c.start_date, c.repeat_interval
FROM dba_scheduler_jobs a, dba_scheduler_wingroup_members b, dba_scheduler_windows c
WHERE job_name='GATHER_STATS_JOB'
  And a.schedule_name=b.window_group_name
  And b.window_name=c.window_name;

You can disable this job using the dbms_scheduler.disable procedure as seen in this example:

exec dbms_scheduler.disable('GATHER_STATS_JOB');

And you can enable the job using the dbms_scheduler.enable procedure as seen in this example:

exec dbms_scheduler.enable('GATHER_STATS_JOB');

Reference

Oracle Database Performance Tuning Guide 11gR2  (11.2) - 5. Automatic Performance Statistics

http://www.pafumi.net/oracle_base.htm

http://www.oracle-base.com/articles/10g/automatic-workload-repository-10g.php

How to Generate an AWR Report and Create Baselines (Doc ID 748642.1)