Automatic Workload Repository

  • Published on Jul 2 2006
  • # 108
  • 10g

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 30-JUN-06 10.00.38.742 PM

[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
        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

@see

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>