Tools and Utilities - Expert Oracle RAC Performance Diagnostics and Tuning - page 147

Automatic Workload Repository
The Automatic Workload Repository (AWR) is an enhanced version of the STATSPACK utility with a more user-friendly
interface. Statistics and the entire workload information is collected (as a snapshot) automatically by the MMON
background process every 60 minutes (default) and stored in the wrh$ and wri$ tables in the SYSAUX tablespace. The
collected data is retained in the AWR for seven days (default) and then automatically purged. During the retention
period, database performance and workload statistics can be generated into a report by comparing two snapshot
periods. AWR collects data in two levels: TYPICAL (default) and ALL .
The following will change the default settings to 30-minute interval with a retention period of 30 days:

EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (interval =>30, retention => 43200);

AWR snapshots can also be captured manually using the DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT()
procedure. To capture a snapshot at the ALL level, a value of ALL is passed to the flush_level parameter of the
CREATE_SNAPSHOT procedure. For example,

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(flush_level=>ALL);

The number of top SQL queries reported by the AWR is also controlled by the STATISTICS_LEVEL parameter.
When the value is TYPICAL , the top 30 queries are listed and when the value is ALL , the top 100 queries are list. This
can be overridden using the following procedure:

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (topnsql => 200);

During typical benchmarking cycles when a set of performance metrics needs to be saved as baseline for
comparisons, the traditional methods used by DBAs have been to make an export of the performance data. AWR
makes it more convenient using the following procedure:

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (<START SNAP ID>,<END SNAP ID>,<BASLINE NAME><DB ID>);

For example, the following procedure will create a baseline QAR1BLINE of the current database instance (default)
represented by DB ID in the syntax above:

execute DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (459,476,'QAR1BLINE');

Querying the DB_HIST_BASELINE table can validate the baseline definition.

COL BASELINE_NAME FORMAT A25
SELECT DBID,
BASELINE_NAME,
START_SNAP_ID,
END_SNAP_ID
FROM DBA_HIST_BASELINE;

DBID BASELINE_NAME START_SNAP_ID END_SNAP_ID
---------- ------------------------- ------------- -----------
4275027223 QAR1BLINE 459 476

■ Note Creation of a baseline will override aWr automatic purging for the baseline snapshot range.