AWR – automatic workload repository

to change the collection interval and retention policy

exec dbms_workload_repository.modify_snapshot_settings ( retention => 20160, -
interval => 30);

example above will change to 14 days (value in minutes) with 30mins snapshots

retention (default  8 days) and interval (default 1 hour)  in minutes

be aware – increasing the retention time or increase the frequence of snapshots the sysaux tablespace will increase also !

to show the current settings or the latest snapshot

col systimestamp format a35
col most_recent_snap_time format a25
col snap_interval format a17
col retention format a17

select systimestamp, most_recent_snap_time, snap_interval, retention
 from wrm$_wr_control 
  where dbid = (select dbid from v$database);

create manually a snapshot

exec dbms_workload_repository.create_snapshot

enable/disable Diagnostic&Tuning pack

If you havent’t licensed the packs you can disable the collection of awr datas.

This helps you to avoid license offending e.g. running awrrpt

alter system set control_management_pack_access='NONE';

Running now a awrrpt.sql (located in $ORACLE_HOME/rdbms/admin) will no longer show datas of the accordand areas

control_management_pack_access can be set to

none

diagnostic

diagnostic+tuning

transfering awr-information

sometimes its neccessary to backup / transfer awr datas to a single database (or to oracle support 🙂 )

with awrextr.sql ($ORACLE_HOME/rdbms/admin) you can export (via expdp) your awr-datas to disk.

create a directory where the dump should be written

sql> @?/rdbms/admin/awrextr

follow the instructions

now transfer the dumpfile to the desired location / instance

create a directory where the dump is located

sql>@?/rdbms/admin/awrload

follow the instructions as well

now it’s possible to run awr-reports on the imported awr datas

BUT, don’t use awrrpt.sql (as usually) because this script will generate awrreports for the running instance

USE awrrpti.sql and enter the desired dbid you want to query.

retrieve informations regarding AWR

@?/rdbms/admin/awrinfo.sql

References

Troubleshooting: AWR Snapshot Collection issues (Doc ID 1301503.1 on http://support.oracle.com)