Jul 2, 2006
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 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
No Comments, Comment or Ping
Reply to “Automatic Workload Repository”