Change AWR Retention & Interval

A. Check current retention and interval.

RETENTION = 8 days ( 11520 Mins ) ( Default )
INTERVAL = 1 hour ( 60 Mins ) ( Default )

SQL> desc dba_hist_wr_control
 Name Null? Type
 ----------------------------------------------------- -------- ------------------------------------
 DBID NOT NULL NUMBER
 SNAP_INTERVAL NOT NULL INTERVAL DAY(5) TO SECOND(1)
 RETENTION NOT NULL INTERVAL DAY(5) TO SECOND(1)
 TOPNSQL VARCHAR2(10)

SQL> select dbid from v$database;

DBID
----------
2920212463

SQL> col SNAP_INTERVAL format a30
SQL> col RETENTION format a30
SQL> select dbid,SNAP_INTERVAL,RETENTION from dba_hist_wr_control;

 DBID SNAP_INTERVAL RETENTION
---------- ------------------------------ ------------------------------
2920212463 +00000 01:00:00.0 +00008 00:00:00.0

SQL> col SNAP_INTERVAL_MINS format a40
SQL> col RETENTION_MINS format a40
SQL>  select dbid,SNAP_INTERVAL*24*60 SNAP_INTERVAL_MINS,RETENTION*24*60 RETENTION_MINS from dba_hist_wr_control;

 DBID SNAP_INTERVAL_MINS RETENTION_MINS
---------- ---------------------------------------- ----------------------------------------
2920212463 +000000060 00:00:00.000000000 +000011520 00:00:00.000000000

B. Change retention from 8 days (  11520 Mins )  —-> 31 days ( 44640 Mins )                                                        interval from 1 hour ( 60 Mins ) —-> 0.5 hour ( 30 Mins )

SQL>exec dbms_workload_repository.modify_snapshot_settings(retention=>44640, interval=>30, dbid=>2920212463);
PL/SQL procedure successfully completed.

C. Check the changes made.

SQL> select dbid,SNAP_INTERVAL,RETENTION from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION
---------- ------------------------------ ------------------------------
2920212463 +00000 00:30:00.0 +00031 00:00:00.0

SQL> select dbid,SNAP_INTERVAL*24*60 SNAP_INTERVAL_MINS,RETENTION*24*60 RETENTION_MINS from dba_hist_wr_control;

 DBID SNAP_INTERVAL_MINS RETENTION_MINS
---------- ---------------------------------------- ----------------------------------------
2920212463 +000000030 00:00:00.000000000 +000044640 00:00:00.000000000

D. Check the snapshot from dba_hist_snapshot.

E. Check the space usage of SYSAUX tablespace.

SQL> SELECT occupant_name, space_usage_kbytes/1024 MBS FROM V$SYSAUX_OCCUPANTS order by 2

OCCUPANT_NAME MBS
--------------------------------------------- ----------
TSM 0
EXPRESSION_FILTER 0
ULTRASEARCH_DEMO_USER 0
ULTRASEARCH 0
TEXT 0
EM 0
STATSPACK 0
ORDIM/SI_INFORMTN_SCHEMA 0
ORDIM/ORDPLUGINS 0
XSAMD 0
ORDIM/ORDDATA 0
ORDIM 0
SDO 0
XDB 0
AUTO_TASK .3125
JOB_SCHEDULER .875
STREAMS 1
LOGSTDBY 1.375
AO 1.5
XSOQHIST 1.5
PL/SCOPE 1.5625
SQL_MANAGEMENT_BASE 1.875
WM 3.5
EM_MONITORING_USER 4.375
LOGMNR 13.375
SM/OTHER 14.75
SMON_SCN_TIME 18.875
SM/ADVISOR 69.3125
AUDIT_TABLES 136.1875
SM/OPTSTAT 406.4375
SM/AWR 1633.3125

31 rows selected.
Advertisements
This entry was posted in AWR and tagged , , , , , , . Bookmark the permalink.

One Response to Change AWR Retention & Interval

  1. Pingback: Tablespace Usage History Report | James Huang – Databases Consultant

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s