Overview on the AWR

The automatic workload repository “AWR” was introduced with Oracle 10g. It collects performance data in intervals (default: 60 minutes) and keeps them by default for one week. The data is stored in the SYSAUX tablespace.

The AWR collects:

• Wait events
• time model statistics (view: V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)
• active session hitory information (view: V$ACTIVE_SESSION_HISTORY)
• system statistics (view: V$SYSSTAT, V$SESSTAT)
• resource intensive sql statements
• information on object usage

For AWR to collect these data the database parameter STATISTICS_LEVEL has to be set to TYPICAL (= the defaul) or ALL. Setting it to BASIC disables data collection!

Changing the AWR settings

Changing the snapshot interval and retention time

For changing the snapshot interval and/or the retention time use the following syntax:

exec dbms_workload_repository.modify_snapshot_settings(
interval => 60
retention => 525600);

The example printed above changes the retention time to one year (60 minutes per hour * 24 hours a day * 365 days per year = 525600). It does not alter the snapshot interval (60 minutes is the default). If you want to you can alter this as well but keep in mind you need aditional storage to do so. Setting the interval value to zero completely disables data collection.

According to the oracle documentation depending on the size of the database the SYSAUX tablespace which contains the AWR objects can be as big as 5 gb in default configuration (60 minutes snapshot interval, 7 days retention time). So if you want to keep your data for one year you might end up with a SYSAUX of 260 GB (assuming a linear growth with the retention time).

Manually creating a snapshot

You can of course create a snapshot maually by executing:

exec dbms_workload_repository.create_snapshot;

Query available snapshots

To query the available snapshot you can use this query:

select snap_id, begin_interval_time, end_interval_time
from dba_hist_snapshot
order by snap_id;

Removing a snapshot

To remove a snapshot just do:

exec dbms_workload_repository.drop_snapshot_range
  (low_snap_id=>1, high_snap_id=>10);