AWR Reports Generation

Snapshots

By default snapshots of the relevant data are taken every hour and retained for 7 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;
    /

Extra snapshots can be taken and existing snapshots can be removed, as shown below.

    EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

    BEGIN
      DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
        low_snap_id  => 22,
        high_snap_id => 32);
    END;
    /
   
Baselines

A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing.

    BEGIN
      DBMS_WORKLOAD_REPOSITORY.create_baseline (
        start_snap_id => 210,
        end_snap_id   => 220,
        baseline_name => 'batch baseline');
    END;
    /

The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted.

    BEGIN
      DBMS_WORKLOAD_REPOSITORY.drop_baseline (
        baseline_name => 'batch baseline',
        cascade       => FALSE); -- Deletes associated snapshots if TRUE.
    END;
    /


Workload Repository 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


awrblmig.sql
awrddinp.sql
awrddrpi.sql
awrddrpt.sql  *
awrextr.sql 
awrgdinp.sql
awrgdrpi.sql
awrgdrpt.sql
awrginp.sql 
awrgrpti.sql
awrgrpt.sql 
awrinfo.sql 
awrinpnm.sql
awrinput.sql
awrload.sql 
awrrpti.sql 
awrrpt.sql  *
awrsqrpi.sql 
awrsqrpt.sql  *