Oracle 11g: Automatic Workload Repository

General points

• The MMON (Managability Monitor) database process generates a snapshot of the database every 60 minutes.
• Snapshots are logged over a period of 8 days. (Retention policy by default, could be changed using the enterprise Manager console or through DBMS_WORKLOAD_REPOSITORY package)
• The AWR repository is the property of the SYSMAN user and stored in the SYSAUX tablespace.

Baseline AWR
A baseline AWR is a set of snapshots between two snapshot id or two dates.
A baseline is identified using a unique name.
A baseline is keep in database until the baseline is deleted.

To create a baseline use :

Function
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
    START_SNAP_ID   IN NUMBER,
    END_SNAP_ID     IN NUMBER,
    BASELINE_NAME   IN VARCHAR2, 
    DBID            IN NUMBER DEFAULT,
    EXPIRATION      IN NUMBER DEFAULT
) RETURNS NUMBER;

or

Procedure
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
    START_TIME      IN DATE,
    END_TIME        IN DATE,
    BASELINE_NAME   IN VARCHAR2,
    DBID            IN NUMBER DEFAULT,
    EXPIRATION      IN NUMBER DEFAULT
);

To drop a baseline use :

Procedure
DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(
    BASELINE_NAME   IN VARCHAR2,
    CASCADE         IN BOOLEAN  DEFAULT,
    DBID            IN NUMBER   DEFAULT
);

Baseline information can be queried from the DBA_HIST_BASELINE view.

How to generate an extra snapshot
Write the following sql command :

SQL>exec dbms_workload_repository.create_snapshot('ALL');

How to list all snapshot registered in the AWR repository?
Select the DBA_HIST_SNAPSHOT to retrive the snapshot id from all snapshot registered in the AWR repository.
This table contains the following information :

SQL>desc DBA_HIST_SNAPSHOT;

SNAP_ID             NOT NULL NUMBER
DBID                NOT NULL NUMBER
INSTANCE_NUMBER     NOT NULL NUMBER
STARTUP_TIME        NOT NULL TIMESTAMP(3)
BEGIN_INTERVAL_TIME NOT NULL TIMESTAMP(3)
END_INTERVAL_TIME   NOT NULL TIMESTAMP(3)
FLUSH_ELAPSED       INTERVAL DAY(5) TO SECOND(1)
SNAP_LEVEL          NUMBER
ERROR_COUNT         NUMBER
SNAP_FLAG           NUMBER

Statistics level
Adjust the STATISTICS_LEVEL database parameter :

• ALL : Generaly not used
• TYPICAL : Use default preferences, gives more advise of the database
• BASIC : Statistics collections not active

This parameter is dynamic.

Changing the snapshot frequency and retention policy
By default snapshots of the relevant data are taken every hour and retained for 8 days.
The default values for these settings can be altered using the following procedure.

BEGIN
      DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
        retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
        interval  => 30);          -- Minutes. Current value retained if NULL.
    END;
    /

An interval of "0" switches off snapshot collection, which in turn stops much of the self-tuning functionality, hence this is not recommended. Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics.

To retirve the retention delay and snapshot interval :

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
2058702964 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT

Workload Repository Views
The following workload repository views are available:

V$ACTIVE_SESSION_HISTORY     - Displays the active session history (ASH) sampled every second.
    V$METRIC                     - Displays metric information.
    V$METRICNAME                 - Displays the metrics associated with each metric group.
    V$METRIC_HISTORY             - Displays historical metrics.
    V$METRICGROUP                - Displays all metrics groups.
    DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
    DBA_HIST_BASELINE            - Displays baseline information.
    DBA_HIST_DATABASE_INSTANCE   - Displays database environment information.
    DBA_HIST_SNAPSHOT            - Displays snapshot information.
    DBA_HIST_SQL_PLAN            - Displays SQL execution plans.
    DBA_HIST_WR_CONTROL          - Displays AWR settings.

Workload Repository Reports

Oracle provides 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

or for a specific database instance

@$ORACLE_HOME/rdbms/admin/awrrpti.sql

to compare two periods of snapshot :

@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

or for a specific database instance

@$ORACLE_HOME/rdbms/admin/awrddrpi.sql

More references
See more @ http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/autostat.htm