My Oracle Support Banner

Tips if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1)

Last updated on MAY 19, 2022

Applies to:

Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Information in this document applies to any platform.
Updated 4 2014 -- this note was originally written on information from 10g and up to 11.1
These older algorithms have been superceded and notably improved as of 11.2.0.x including 11.2.0.3 and higher
The content can be used for investigating the subject matter, but reviewing alternative notes may provide more useful information for more current versions.
such as AWR Data Uses Significant Space in the SYSAUX Tablespace (Doc ID 287679.1) which provides quick results for the most common AWR + SYSAUX growth problems




Symptoms

Observed increased storage requirements, exceptional  tablespace growth or both for the SYSAUX tablespace

The following information provides some suggestions on how to approach and understand factors contributing to SYSAUX tablespace growth based on methods and algorithms up to 11gR2.
While some of the tips provided in this document may be relevant for newer RDBMS versions and Clustered  / RAC configurations, referencing the documentation and relevant notes will help in diagnosing and resolving simlar issues. Consider this note a discussion of factors, methods and potential solutions when investigating exceptional growth in the SYSAUX tablespace and not a definitive document intended resolve every SYSAUX growth problem.

 

New Usage Factors:  Migrating to an Engineered Systems configuration including RAC

Perhaps the most influential factors increasing the total data collection stored in SYSAUX Tablespaces are RAC, SQL baselines and the expected metric collection increased with each new version of the RDBMS.
Each NODE and Instance arithmetically adds data collection stored in SYSAUX which can exceed previous expected data collection sizes. Simply put, expect more data with newer versions and especially if moving to RAC or adding instances.
Being aware of these factors and setting expectations that more instances, more capacity and more details collected in newer and higher performing systems will by default increase total diagnostic data collection storage requirements.
Engineered Systems including Exadata, SparcSuperCluster, Exalogic and the Oracle Database Appliance multiple node configurations using the newest diagnostic and automatic data collection will increase SYSAUX storage consumption.

 

NOTE:

Each newer version of the RDBMS have improved algorithms for detecting and purging internal metric data collection stored in SYSAUX
While this should reduce the likelihood of needing to perform manual maintenance as described in the lower sections of this note, there are several potential factor we should discuss before declaring SYSAUX storage issues as a problem of the past.
That stated, by drilling into the AWRinfo.sql script, V$SYSAUX_OCCUPANTs and other related views the information and methods here may still prove useful for diagnosis of SYSAUX issues

Comment:

RAC and Exadata will force proportionately higher SYSAUX tablespace growth to maintain performance and auditing statistics.

Engineered systems -- Moving to Exadata and multiple node RAC can notably increase the number of database instances
                                     and resources used as well as introducing new metric sets compared to previous configurations

 

Changes

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.