Automatic Workload Repository

A very short howto for the ‘Automatic Workload Repository’, the successor of perfstat.

Interval

Some queries;

Listing below shows one snapshot is taken every 20 minutes, the snapshot is stored for a week (7 days).

[SYSTEM@DB01.REMIDIAN.COM]
SQL> select snap_interval, retention
  2  from   dba_hist_wr_control
  3  /

SNAP_INTERVAL                    RETENTION
-------------------------------- ------------------------------
+00000 00:20:00.0                +00007 00:00:00.0

Alter the interval to once every hour and store it for a month (31 days);

[SYSTEM@DB01.REMIDIAN.COM]
SQL> begin
  2     dbms_workload_repository.modify_snapshot_settings
  3        ( interval   => 60
  4        , retention  => 31 * 24 * 60);
  5  end;
  6  /

PL/SQL procedure successfully completed.

[SYSTEM@DB01.REMIDIAN.COM]
SQL> select snap_interval, retention
  2  from   dba_hist_wr_control
  3  /

SNAP_INTERVAL                    RETENTION
-------------------------------- ------------------------------
+00000 01:00:00.0                +00031 00:00:00.0

History

Five latest snapshots

[SYSTEM@DB01.REMIDIAN.COM]
SQL> select *
  2  from   (
  3         select snap_id, startup_time, begin_interval_time, end_interval_time
  4         from   dba_hist_snapshot
  5         order by snap_id
  6         )
  7  where rownum < 6
  8  /

   SNAP_ID STARTUP_TIME              BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- ------------------------- -------------------------
        23 30-JUN-06 05.59.11.000 PM 30-JUN-06 05.59.11.000 PM 30-JUN-06 06.02.04.711 PM
        24 30-JUN-06 05.59.11.000 PM 30-JUN-06 06.02.04.711 PM 30-JUN-06 07.00.29.599 PM
        25 30-JUN-06 05.59.11.000 PM 30-JUN-06 07.00.29.599 PM 30-JUN-06 08.00.33.577 PM
        26 30-JUN-06 05.59.11.000 PM 30-JUN-06 08.00.33.577 PM 30-JUN-06 09.00.39.837 PM
        27 30-JUN-06 05.59.11.000 PM 30-JUN-06 09.00.39.837 PM <strong>30-JUN-06 10.00.38.742 PM</strong>

[SYSTEM@DB01.REMIDIAN.COM]
SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

[SYSTEM@DB01.REMIDIAN.COM]
SQL> select *
  2  from   (
  3         select snap_id, startup_time, begin_interval_time, end_interval_time
  4         from   dba_hist_snapshot
  5         order by snap_id
  6         )
  7  where rownum < 6
  8  /

   SNAP_ID STARTUP_TIME              BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
---------- ------------------------- ------------------------- -------------------------
        23 30-JUN-06 05.59.11.000 PM 30-JUN-06 05.59.11.000 PM 30-JUN-06 06.02.04.711 PM
        24 30-JUN-06 05.59.11.000 PM 30-JUN-06 06.02.04.711 PM 30-JUN-06 07.00.29.599 PM
        25 30-JUN-06 05.59.11.000 PM 30-JUN-06 07.00.29.599 PM 30-JUN-06 08.00.33.577 PM
        26 30-JUN-06 05.59.11.000 PM 30-JUN-06 08.00.33.577 PM 30-JUN-06 09.00.39.837 PM
<strong>        27 30-JUN-06 05.59.11.000 PM 30-JUN-06 09.00.39.837 PM 30-JUN-06 10.00.38.742 PM</strong>

@see

Posted in 10g

Leave a Reply

Your email address will not be published. Required fields are marked *