SYSAUX Tablespace Sizing

 SYSAUX Tablespace Sizing

The focus of this article on an undocumented script, UTLSYXSZ.SQL, located in the $ORACLE_HOME/rdbms/admin directory.

When the UTLSYXSZ.SQL script is run it creates a PL/SQL package named UTLSYXSZ_UTIL. The script then runs an anonymous block that collects user information and outputs a multi-section report. The final action the script performs is to drop the built-in package and undefine the variables it created.

SQL> @?/rdbms/admin/utlsyxsz.sql

Run the above script to get the current sysaux tablespace current/future utilization and AWR space estimation for future growth. A very useful script to plan the size of sysaux tablespace.

Helpful script  $ORACLE_HOME/rdbms/admin/utlsyxsz.sql.  It provides a prediction of the space needed when creating the SYSAUX tablespace to begin with.

The report depends on many input values such as

|   - Interval Setting (minutes)
|   - Retention Setting (days)
|   - Number of Instances
|   - Average Number of Active Sessions
|   - Number of Datafiles

Parameter Collection

After the script defines a number of session variables the package it creates is then called to gather

metadata and produced the following reports.

 SYSAUX Size Estimation Report

 CURRENT SYSAUX Usage Report

 AWR Space Estimation Report

 Optimizer Stat History Space Estimation Report

 Estimated SYSAUX Usage Report

 Summary of SYSAUX Space Estimation

 Finally reporting out the Total Estimated SYSAUX Size

Some of the information in the reports is collected by querying data dictionary tables and dynamic performance views. Other information it solicits from the user by making calls to UTLSYXSZ_UTIL which require the user to input a value (or accept the default), and in a few cases, for example the variable ash_hist which defines how much ASH (Active Session History) data will be examined as part of the report's calculation, the value is partially hard-coded into the package and a script edit is required to alter the value as you can see in the listing below.

Estimated size of Stats history

Note prompted for the number of Indexes or Columns. Generation of these numbers, again, is hard-coded into the package and can be changed before the script is run to produce a value more in line with your actual usage.

Conclusion of the report we see that Oracle is estimating that a system that corresponds to the parameters entered should result in a SYSAUX tablespace sized at about 8.5GB. 

The current SYSAUX, as the result of self-sizing is about 3.9GB so an additional 4.6GB is a good estimate of the space that will be required for expansion should the system suddenly experience an increase in tables, users, and DML activity as anticipated.