Oracle dbms_workload_repository

Oracle Tips by Burleson Consulting

Using Oracle dbms_workload_repository

The default collection for AWR data is only seven days, so many Oracle DBAs will increase the
storage of detail information over longer time periods using the new Oracle dbms package called dbms_workload_repository.modify_snapshot_settings. This will change the retention period
and collection frequency to provide you with longer timer periods of data:

execute dbms_workload_repository.modify_snapshot_settings(
interval => 60,
retention => 43200);

In this example the retention period is specified as 30 days (43200 min) and the interval between
each snapshot is 60 min. If you query the dba_hist_wr_control view after this procedure is executed,
you will see the changes to these settings.

Manual snapshot collection and retention

You can modify the snapshot collection interval using the Oracle dbms_workload_repository package.
The procedure dbms_workload_repository.modify_snapshot_settings is used in this example to modify
the snapshot collection so that it occurs every 15 minutes, and retention of snapshot data is fixed at
20160 minutes:

-- This causes the repository to refresh every 15 minutes
-- and retain all data for 2 weeks.
Exec dbms_workload_repository.modify_snapshot_settings
(retention=>20160, interval=> 15);

(Setting the interval parameter to 0 will disable all statistics collection.)

For more dbms_workload_repository tips see:

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).

Buy direct from the publisher and save 30%!

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books

Oracle dbms_workload_repository Oracle Tips by Burleson Consulting

Oracle dbms_workload_repository

Oracle Tips by Burleson Consulting