Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large
Last updated on APRIL 03, 2018
Applies to:Oracle Database - Enterprise Edition - Version 220.127.116.11 to 18.104.22.168 [Release 11.1 to 9.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 22.214.171.124 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
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.
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
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
- Moving to RAC based configuration
- New ORACLE database usage
- Increase in metric usage or dependence
- Closer or new monitoring of SYSAUX TBS growth leading to questions regarding allocation and growth sizes
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