SYSAUX tablespace is growing like anything

SYSAUX tablespace is growing like anything
##################################

AWR is identified to be consuming more space.

SQL> select * from v$sysaux_occupants ;

SQL> SELECT occupant_name, occupant_desc, space_usage_kbytes FROM v$sysaux_occupants ORDER BY space_usage_kbytes DESC ;


OCCUPANT_NAME                   OCCUPANT_DESC                                          SPACE_USAGE_KBYTES

SM/AWR                  Server Manageability - Automatic Workload Repository               19906624
SM/OPTSTAT              Server Manageability - Optimizer Statistics History                  191808
EM                      Enterprise Manager Repository                                         59648


Solution
#########

1.) See the snapshots interval and retention period time

SQL> select * from DBA_HIST_WR_CONTROL;

Check whether the retention period is set too high. In such case we need to modify  the retention period.

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.



2). 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.



3) Then check whether the statistics_level parameter is set to typical.


SQL> show parameter statistics


NAME                                         TYPE                        VALUE

statistics_level string ALL   ----->  Should be set 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;