askMLabs

SQL> select
2        extract( day from snap_interval) *24*60+
3        extract( hour from snap_interval) *60+
4        extract( minute from snap_interval ) "Snapshot Interval",
5        extract( day from retention) *24*60+
6        extract( hour from retention) *60+
7        extract( minute from retention ) "Retention Interval"
8  from dba_hist_wr_control;

Snapshot Interval Retention Interval
----------------- ------------------
60              10080

SQL> set linesize 100
col snap_interval format a20
col retention format a20
col topnsql format a20
select * from dba_hist_wr_control;SQL> SQL> SQL> SQL>

DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- --------------------
3438851159 +00000 01:00:00.0    +00007 00:00:00.0    DEFAULT

SQL> execute dbms_workload_repository.modify_snapshot_settings( interval => 30,retention => 43200);

PL/SQL procedure successfully completed.

NOTE : all the values are specified in minutes

SQL> select
2        extract( day from snap_interval) *24*60+
3        extract( hour from snap_interval) *60+
4        extract( minute from snap_interval ) "Snapshot Interval",
5        extract( day from retention) *24*60+
6        extract( hour from retention) *60+
7        extract( minute from retention ) "Retention Interval"
8  from dba_hist_wr_control;

Snapshot Interval Retention Interval
----------------- ------------------
30              43200

SQL> set linesize 100
col snap_interval format a20
col retention format a20
col topnsql format a20
select * from dba_hist_wr_control;SQL> SQL> SQL> SQL>

DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- --------------------
3438851159 +00000 00:30:00.0    +00030 00:00:00.0    DEFAULT

SQL>

==> SYSAUX tablespace size can be estimated using the script utlsyxsz.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlsyxsz.sql

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 <return> to continue, otherwise enter an alternative.

Enter value for report_name: askm

Using the report name askm
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Estimated at
12:10:21 on Mar 01, 2011 ( Tuesday ) in Timezone -05:00

DB_NAME     HOST_PLATFORM                             INST STARTUP_TIME      PAR
----------- ---------------------------------------- ----- ----------------- ---
* EPPERF    atlsdbrfl01.seo.int - Linux x86 64-bit       1 09:58:44 (02/28)  NO

~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size:                         119.3 MB
|
| Total size of SM/AWR                        71.1 MB (  59.6% of SYSAUX )
| Total size of SM/OPTSTAT                    18.8 MB (  15.7% of SYSAUX )
| Total size of SM/ADVISOR                     6.4 MB (   5.3% of SYSAUX )
| Total size of LOGMNR                         6.0 MB (   5.0% of SYSAUX )
| Total size of SM/OTHER                       4.8 MB (   4.0% of SYSAUX )
| Total size of EM_MONITORING_USER             1.6 MB (   1.3% of SYSAUX )
| Total size of LOGSTDBY                       0.9 MB (   0.7% of SYSAUX )
| Total size of XSOQHIST                       0.8 MB (   0.6% of SYSAUX )
| Total size of AO                             0.8 MB (   0.6% of SYSAUX )
| Total size of STREAMS                        0.5 MB (   0.4% of SYSAUX )
| Total size of JOB_SCHEDULER                  0.4 MB (   0.3% of SYSAUX )
| Total size of TSM                            0.3 MB (   0.2% of SYSAUX )
| Total size of Others                         7.1 MB (   6.0% 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 <return> to use the current value:     30.0 minutes
|   otherwise enter an alternative
|
Enter value for interval:

**   Value for 'Interval Setting': 30

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

**   Value for 'Retention Setting': 30

|
| For 'Number of Instances',
|   Press <return> 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 <return> to use the current value:   0.06
|   otherwise enter an alternative
|
Enter value for active_sessions: 50

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

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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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 <return> to use the current value:  4,488.0
|   otherwise enter an alternative <a positive integer>
|
Enter value for number_of_tables:

**   Value for 'Number of Tables': 4488

|
| For 'Number of Partitions',
|   Press <return> to use the current value:   0.00
|   otherwise enter an alternative <a positive integer>
|
Enter value for number_of_partitions:

**   Value for 'Number of Partitions': 0

|
| For 'Statistics Retention',
|   Press <return> to use the current value:     31.0 days
|   otherwise enter an alternative <a positive integer>
|
Enter value for stats_retention:

**   Value for 'Statistics Retention': 31

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

**   Value for 'DML Activity': 2

| ***************************************************
| Estimated size of Stats history            102.2 MB
|
|   The space for Optimizer Statistics history was
|   estimated using the following values:
|
|                         Tables -   4,488
|                        Indexes -   6,282
|                        Columns -  37,471
|                     Partitions -       0
|          Indexes on Partitions -       0
|          Columns in Partitions -       0
|        Stats Retention in Days -      31
|          Level of DML Activity -  Medium
| ***************************************************

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

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR:                   3,921.8 MB
|
|   The AWR estimate was computed using
|   the following values:
|
|            Interval -        30 minutes
|           Retention -     30.00 days
|       Num Instances -         1
|     Active Sessions -     50.00
|           Datafiles -        79
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history            102.2 MB
|
|   The space for Optimizer Statistics history was
|   estimated using the following values:
|
|                         Tables -   4,488
|                        Indexes -   6,282
|                        Columns -  37,471
|                     Partitions -       0
|          Indexes on Partitions -       0
|          Columns in Partitions -       0
|        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 SM/ADVISOR                       6.4 MB
| Est size of LOGMNR                           6.0 MB
| Est size of SM/OTHER                         4.8 MB
| Est size of EM_MONITORING_USER               1.6 MB
| Est size of LOGSTDBY                         0.9 MB
| Est size of XSOQHIST                         0.8 MB
| Est size of AO                               0.8 MB
| Est size of STREAMS                          0.5 MB
| Est size of JOB_SCHEDULER                    0.4 MB
| Est size of TSM                              0.3 MB
| Est size of Others                           7.1 MB

| Est size of SM/AWR                       3,921.8 MB
| Est size of SM/OPTSTAT                     102.2 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size:             4,053.4 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************

End of Report
SQL>

NOTE :

STATSPACK has many more settings that can be configured.  These are kept in the stats$statspack_parameter table. This table stores a single row for the database parameters with the corresponding STATSPACK settings.  These settings influence the amount of information STATSPACK gathers from the v$ views.

Hope It helps

SRI