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

Last updated on AUGUST 07, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.4 [Release 11.1 to 11.2]
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

Engineered Systems or moving to 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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms