How To Estimate Size For SYSAUX Tablespace

SYSAUX tablespace size depends on several variables, one or the principal is the AWR, since the SYSAUX tablespace is where keep all data from the AWR. An example of using for a SYSAUX tablespaces (from production enviroment) is the following;

~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size:                       4,550.8 MB
|
| Total size of SM/AWR                     4,017.4 MB (  88.3% of SYSAUX )
| Total size of SM/OPTSTAT                   289.1 MB (   6.4% of SYSAUX )
| Total size of LOGMNR                        92.8 MB (   2.0% of SYSAUX )
| Total size of SM/ADVISOR                    67.6 MB (   1.5% of SYSAUX )
| Total size of SM/OTHER                      27.1 MB (   0.6% of SYSAUX )
| Total size of WM                             7.4 MB (   0.2% of SYSAUX )
| Total size of SMON_SCN_TIME                  6.3 MB (   0.1% of SYSAUX )
| Total size of JOB_SCHEDULER                  4.8 MB (   0.1% of SYSAUX )
| Total size of EM_MONITORING_USER             3.2 MB (   0.1% of SYSAUX )
| Total size of SQL_MANAGEMENT_BASE            2.0 MB (   0.0% of SYSAUX )
| Total size of PL/SCOPE                       1.6 MB (   0.0% of SYSAUX )
| Total size of XSOQHIST                       1.4 MB (   0.0% of SYSAUX )
| Total size of AO                             1.4 MB (   0.0% of SYSAUX )
| Total size of LOGSTDBY                       1.4 MB (   0.0% of SYSAUX )
| Total size of STREAMS                        1.0 MB (   0.0% of SYSAUX )
| Total size of AUTO_TASK                      0.3 MB (   0.0% of SYSAUX )
| Total size of Others                        26.2 MB (   0.6% of SYSAUX )

Oracle provides the utlsyxsz.sql script, which is located in $ORACLE_HOME/rdbms/admin/, using it can estimate the physical size for datafile (or datafiles) that need the AWR to save its data, of course, there are other tools that also use the SYSAUX tablespace as a repository and can distort the estimation.
Run the script;

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

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

Specify the name of report;

Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is utlsyxsz.txt.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: anr_rpt.txt

We will estimate for executions every 30 minutes and store three months (90 days);

~~~~~~~~~~~~~~~~~~~~
AWR Space Estimation
~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Automatic Workload Repository (AWR)
| in SYSAUX, we need the following values:
|
|     - Interval Setting (minutes)
|     - Retention Setting (days)
|     - Number of Instances
|     - Average Number of Active Sessions
|     - Number of Datafiles

|
| For 'Interval Setting',
|   Press <return> to use the current value:     60.0 minutes
|   otherwise enter an alternative
|
Enter value for interval: 30

**   Value for 'Interval Setting': 30

|
| For 'Retention Setting',
|   Press <return> to use the current value:    180.0 days
|   otherwise enter an alternative
|
Enter value for retention: 90

It ask the number of instances (if RAC), and the value of active sessions, by default take the current value, which is what we use in this case;

Enter value for num_instances: 1

**   Value for 'Number of Instances': 1

|
| For 'Average Number of Active Sessions',
|   Press <return> to use the current value:   78.00
|   otherwise enter an alternative
|
Enter value for active_sessions:

We give the result for AWR;

| ***************************************************
| Estimated size of AWR:                   6,266.5 MB
|
|   The AWR estimate was computed using
|   the following values:
|
|            Interval -        30 minutes
|           Retention -     90.00 days
|       Num Instances -         1
|     Active Sessions -     78.00
|           Datafiles -      2819
| ***************************************************

The report also give us estimates for other repositories like LOGMNR, History Optimizer Statistics, etc.

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s