Two Occupants That Affect The Most SYSAUX Tablespace Size

Is you SYSAUX tablespace running out of space? Are you wondering what is causing the tablespace growth?

Normally you would check the the segment with the largest size in the tablespace, to identify the culprit. Once you identify that, you might be wondering why is that segment growing so much?

As you know, SYSAUX tablespace was introduced in 10g version, as an auxiliary tablespace to the SYSTEM tablespace. It stores other database components that are not critical to the functioning of the database. In other words, if the SYSAUX tablespace is not available, the database is still running, maybe some components will not be working, but at least the database will be available.

If you check the V$SYSAUX_OCCUPANTS view, you will identify the occupants in the tablespace, and how much space they use up.

select OCCUPANT_NAME,SCHEMA_NAME,SPACE_USAGE_KBYTES 
from v$sysaux_occupants order by occupant_name;

The two components that influence the most the SYSAUX tablespace growth, are the AWR repository and the Optimizer Stats History (based on my experience):

SM/AWR: Server Manageability - Automatic Workload Repository
SM/OPTSTAT: Server Manageability - Optimizer Statistics History

The space that the AWR repository is taking up in SYSAUX is in direct proportion with the retention and frequency of the AWR snapshots. The longer the retention, the more space AWR needs. The more frequent the snapshots, meaning more snapshots need to be stored, the more space AWR is occupying in the SYSAUX tablespace.

The space that the Optimizer Statistics History is taking up is in direct proportion with the retention of the statistics history, and the frequency with which statistics are gathered. The more stats history you want to keep, the more space is needed. The more often you gather stats, the more space is needed to keep the history.

Let’s see how to determine the retention and the frequency for these two occupants, and how to change it, if needed.

To check the retention and frequency for the AWR snapshots:

select snap_interval, retention
from dba_hist_wr_control;

The defaults for AWR are: default retention 8 days, default interval is every hour.

To change the retention and the interval, you use DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS. Setting interval to 0, disables all stats collection.

exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
retention=>129600, interval=>45);

To check the retention for the Optimizer stats history:

select dbms_stats.get_stats_history_retention from dual;

The default retention for optimizer stats is 31 days.

If you want to modify the retention, you would use DBMS_STATS.ALTER_STATS_HISTORY_RETENTION.

exec dbms_stats.alter_stats_history_retention(15);

The frequency of the optimizer stats is dependent on your system. Do you use the default gather stats job or do you have your own job? I’ve seen databases where stats were gathered daily, with a scheduled script.

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com