Thursday, September 22, 2011

How to estimate the size of SYSAUX tablespace.


Starting with Oracle 10g , Oracle introduced SYSAUX  tablespace, where it maintains the information about components that are not MUST for a database to be up and running .

If SYSAUX is not available ,  core functionality is not impacted , but AWR  , ADDM , SQL plan management , Diagnostic Pack , ... .( to name a few )   will be impacted.

Oracle provides a script to estimate the size of the SYSAUX tablespace. This script is located under ORACLE_HOME/admin.

The script interview you with series of questions and comes up with the report.

Here is an example.
Comments welcome...!



c:\app\oracle\product\11.2.0\dbhome_2\RDBMS\ADMIN>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 22 16:41:23 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @utlsyxsz
This script estimates the space required for the SYSAUX tablespace.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is utlsyxsz.txt.  To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name:


...

....


c:\app\oracle\product\11.2.0\dbhome_2\RDBMS\ADMIN>type utlsyxsz.txt
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Estimated at
16:29:00 on Sep 22, 2011 ( Thursday ) in Timezone -04:00


DB_NAME     HOST_PLATFORM                             INST STARTUP_TIME      PAR
----------- ---------------------------------------- ----- ----------------- ---
* DBDEV     COR-DB02 - Microsoft Windows x86 64-bi     1 12:14:51 (09/16)  NO
            t


~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size:                       1,173.8 MB
|
| Total size of SM/AWR                       254.9 MB (  21.7% of SYSAUX )
| Total size of SM/OPTSTAT                   264.4 MB (  22.5% of SYSAUX )
| Total size of XDB                          127.8 MB (  10.9% of SYSAUX )
| Total size of SM/ADVISOR                   106.6 MB (   9.1% of SYSAUX )
| Total size of EM                            96.6 MB (   8.2% of SYSAUX )
| Total size of SDO                           45.3 MB (   3.9% of SYSAUX )
| Total size of AO                            41.9 MB (   3.6% of SYSAUX )
| Total size of XSOQHIST                      41.9 MB (   3.6% of SYSAUX )
| Total size of SQL_MANAGEMENT_BASE           28.6 MB (   2.4% of SYSAUX )
| Total size of ORDIM/ORDDATA                 13.6 MB (   1.2% of SYSAUX )
| Total size of LOGMNR                        12.3 MB (   1.0% of SYSAUX )
| Total size of SM/OTHER                       6.4 MB (   0.5% of SYSAUX )
| Total size of SMON_SCN_TIME                  6.3 MB (   0.5% of SYSAUX )
| Total size of JOB_SCHEDULER                  6.2 MB (   0.5% of SYSAUX )
| Total size of PL/SCOPE                       5.9 MB (   0.5% of SYSAUX )
| Total size of XSAMD                          5.1 MB (   0.4% of SYSAUX )
| Total size of EXPRESSION_FILTER              3.6 MB (   0.3% of SYSAUX )
| Total size of TEXT                           3.6 MB (   0.3% of SYSAUX )
| Total size of WM                             2.0 MB (   0.2% of SYSAUX )
| Total size of EM_MONITORING_USER             1.7 MB (   0.1% of SYSAUX )
| Total size of LOGSTDBY                       1.4 MB (   0.1% of SYSAUX )
| Total size of STREAMS                        1.0 MB (   0.1% of SYSAUX )
| Total size of ORDIM                          0.4 MB (   0.0% of SYSAUX )
| Total size of AUTO_TASK                      0.3 MB (   0.0% of SYSAUX )
| Total size of Others                        95.9 MB (   8.2% of SYSAUX )
|

~~~~~~~~~~~~~~~~~~~~
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 to use the current value:     60.0 minutes
|   otherwise enter an alternative
|
Enter value for interval:

**   Value for 'Interval Setting': 60

|
| For 'Retention Setting',
|   Press to use the current value:   8.00 days
|   otherwise enter an alternative
|
Enter value for retention: 30

**   Value for 'Retention Setting': 30

|
| For 'Number of Instances',
|   Press to use the current value:   1.00
|   otherwise enter an alternative
|
Enter value for num_instances: 1

**   Value for 'Number of Instances': 1

|
| For 'Average Number of Active Sessions',
|   Press to use the current value:   0.09
|   otherwise enter an alternative
|
Enter value for active_sessions: 50

**   Value for 'Average Number of Active Sessions': 50

| ***************************************************
| Estimated size of AWR:                   3,558.0 MB
|
|   The AWR estimate was computed using
|   the following values:
|
|            Interval -        60 minutes
|           Retention -     30.00 days
|       Num Instances -         1
|     Active Sessions -     50.00
|           Datafiles -         6
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Optimizer Stat History Space Estimation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Optimizer Statistics History
| we need the following values:
|
|     - Number of Tables in the Database
|     - Number of Partitions in the Database
|     - Statistics Retention Period (days)
|     - DML Activity in the Database (level)

|
| For 'Number of Tables',
|   Press to use the current value:  1,114.0
|   otherwise enter an alternative
|
Enter value for number_of_tables: 2000

**   Value for 'Number of Tables': 2000

|
| For 'Number of Partitions',
|   Press to use the current value:  1,233.0
|   otherwise enter an alternative

|
Enter value for number_of_partitions: 2500

**   Value for 'Number of Partitions': 2500

|
| For 'Statistics Retention',
|   Press to use the current value:     31.0 days
|   otherwise enter an alternative

|
Enter value for stats_retention:

**   Value for 'Statistics Retention': 31
|
| For 'DML Activity',
|   Press to use the current value:        2
|   otherwise enter an alternative <1=low, 2=medium, 3=high>
|
Enter value for dml_activity: 2

**   Value for 'DML Activity': 2

| ***************************************************
| Estimated size of Stats history            146.7 MB
|
|   The space for Optimizer Statistics history was
|   estimated using the following values:
|
|                         Tables -   2,000
|                        Indexes -   2,758
|                        Columns -  32,097
|                     Partitions -   2,500
|          Indexes on Partitions -     684
|          Columns in Partitions -  65,142
|        Stats Retention in Days -      31
|          Level of DML Activity -  Medium
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~
Estimated SYSAUX usage
~~~~~~~~~~~~~~~~~~~~~~

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR:                   3,558.0 MB
|
|   The AWR estimate was computed using
|   the following values:
|
|            Interval -        60 minutes
|           Retention -     30.00 days
|       Num Instances -         1
|     Active Sessions -     50.00
|           Datafiles -         6
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history            146.7 MB
|
|   The space for Optimizer Statistics history was
|   estimated using the following values:
|
|                         Tables -   2,000
|                        Indexes -   2,758
|                        Columns -  32,097
|                     Partitions -   2,500
|          Indexes on Partitions -     684
|          Columns in Partitions -  65,142
|        Stats Retention in Days -      31
|          Level of DML Activity -  Medium
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|   For all the other components, the estimate
|   is equal to the current space usage of
|   the component.
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
| ***************************************************
| Summary of SYSAUX Space Estimation
| ***************************************************

| Est size of XDB                            127.8 MB
| Est size of SM/ADVISOR                     106.6 MB
| Est size of EM                              96.6 MB
| Est size of SDO                             45.3 MB
| Est size of AO                              41.9 MB
| Est size of XSOQHIST                        41.9 MB
| Est size of SQL_MANAGEMENT_BASE             28.6 MB
| Est size of ORDIM/ORDDATA                   13.6 MB
| Est size of LOGMNR                          12.3 MB
| Est size of SM/OTHER                         6.4 MB
| Est size of SMON_SCN_TIME                    6.3 MB
| Est size of JOB_SCHEDULER                    6.2 MB
| Est size of PL/SCOPE                         5.9 MB
| Est size of XSAMD                            5.1 MB
| Est size of EXPRESSION_FILTER                3.6 MB
| Est size of TEXT                             3.6 MB
| Est size of WM                               2.0 MB
| Est size of EM_MONITORING_USER               1.7 MB
| Est size of LOGSTDBY                         1.4 MB
| Est size of STREAMS                          1.0 MB
| Est size of ORDIM                            0.4 MB
| Est size of AUTO_TASK                        0.3 MB
| Est size of Others                          95.9 MB

| Est size of SM/AWR                       3,558.0 MB
| Est size of SM/OPTSTAT                     146.7 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size:             4,359.1 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************

End of Report

c:\app\oracle\product\11.2.0\dbhome_2\RDBMS\ADMIN>

1 comment:

  1. hi what comes under the " Total size of Others"? I am getting 32G used for others...

    ReplyDelete