SYSAUX Tablespace Grows Heavily Due To AWR

출처 :  https://support.oracle.com

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.4 - Release: 10.1 to 10.2
Information in this document applies to any platform.

Symptoms

SYSAUX tablespace is growing abnormally and consuming so much of space.

AWR is identified to be consuming more space.

Cause

  

v$sysaux_occupants shows the following:-

********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name Schema Name Space Usage
| -------------------- -------------------- ----------------
| SM/AWR SYS 30,821.9 MB <= Heavy usage
| EM SYSMAN 54.2 MB
| SM/OPTSTAT SYS 38.8 MB
| SM/ADVISOR SYS 38.3 MB
| SM/OTHER SYS 23.6 MB

Solution

SQL> select * from DBA_HIST_WR_CONTROL;

DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ---------------------------------------- ---------------------------------------- ----------
3406903167 +00000 01:00:00.0 +00004 00:00:00.0 DEFAULT <= Retention is 4 days


SQL> select min(snap_id),MAX(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
8377 17324 <= Huge snapshots are there. Can be dropped.


SQL> show parameter statistics

NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
statistics_level string ALL <= Should be set TYPICAL
timed_os_statistics integer 5
timed_statistics boolean TRUE

Check whether the retention period is set too high. In this case, it is set to 4 and so no need to modify the retention.

If it is set to high value, then consider reducing the retention period to avoid growing of space.

By default, it is set to 7 days.

The new retention time is specified in minutes.

SQL> execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 4320);

Here 4320 is 3*24*60 ( Convert the days to minutes). For 3 days, it 4320 minutes.

Next thing is to check the amount of snapshots have been generated and available.

If this is too high, then try to drop the unwanted snapshots to reclaim the space.

SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id,high_snap_id);


low_snap_id : The low snapshot id of snapshots to drop.
high_snap_id : The high snapshot id of snapshots to drop.


Then check whether the statistics_level parameter is set to typical.

If it is set to ALL, please change it to TYPICAL. Because statistics_level=ALL will gather lot of additional information in AWR repository which would consume more space.

Most of the cases, if the statistics_level is set to TYPICAL then the growth would be stopped.

SQL> alter system set statistics_level=typical;