計算sysaux中各主件對sysaux空間的使用！

透過$ORACLE_HOME/rdbm/admin/utlsyxsz.sql指令碼可以估算當前sysaux中各主件對空間的使用情況.

[@more@]

SQL> @ E:oracleproduct10.2.0db_1RDBMSADMINutlsyxsz.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 to continue, otherwise enter an alternative.

Enter value for report_name: c:aa.txt

Using the report name c:aa.txt
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Estimated at
14:35:55 on 8月 31, 2008 ( 日曜日 ) in Timezone +08:00

DB_NAME HOST_PLATFORM INST STARTUP_TIME PAR

----------- ---------------------------------------- ----- ----------------- ---

* ORCL XYS - Microsoft Windows IA (32-bit) 1 14:25:35 (08/31) NO

~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size: 141.1 MB
|
| Total size of SM/AWR 31.9 MB ( 22.6% of SYSAUX )
| Total size of SM/OPTSTAT 24.3 MB ( 17.2% of SYSAUX )
| Total size of EM 49.6 MB ( 35.2% of SYSAUX )
| Total size of WM 6.9 MB ( 4.9% of SYSAUX )
| Total size of LOGMNR 5.9 MB ( 4.2% of SYSAUX )
| Total size of SM/ADVISOR 5.7 MB ( 4.0% of SYSAUX )
| Total size of SM/OTHER 4.9 MB ( 3.5% of SYSAUX )
| Total size of EM_MONITORING_USER 1.6 MB ( 1.1% of SYSAUX )
| Total size of LOGSTDBY 0.9 MB ( 0.6% of SYSAUX )
| Total size of AO 0.8 MB ( 0.5% of SYSAUX )
| Total size of XSOQHIST 0.8 MB ( 0.5% 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 6.9 MB ( 4.9% 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: 7.00 days
| otherwise enter an alternative
|
Enter value for retention:

** Value for 'Retention Setting': 7

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

** Value for 'Number of Instances': 1

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

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

| ***************************************************
| Estimated size of AWR: 125.9 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 7.00 days
| Num Instances - 1
| Active Sessions - 3.05
| Datafiles - 5
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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: 48.0
| otherwise enter an alternative
|
Enter value for number_of_tables:

** Value for 'Number of Tables': 48

|
| For 'Number of Partitions',
| Press to use the current value: 0.00
| otherwise enter an alternative
|
Enter value for number_of_partitions:

** Value for 'Number of Partitions': 0

|
| 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:

** Value for 'DML Activity': 2

| ***************************************************
| Estimated size of Stats history 16.7 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 48
| Indexes - 149
| Columns - 486
| 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: 125.9 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 7.00 days
| Num Instances - 1
| Active Sessions - 3.05
| Datafiles - 5
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history 16.7 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 48
| Indexes - 149
| Columns - 486
| 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 EM 49.6 MB
| Est size of WM 6.9 MB
| Est size of LOGMNR 5.9 MB
| Est size of SM/ADVISOR 5.7 MB
| Est size of SM/OTHER 4.9 MB
| Est size of EM_MONITORING_USER 1.6 MB
| Est size of LOGSTDBY 0.9 MB
| Est size of AO 0.8 MB
| Est size of XSOQHIST 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 6.9 MB

| Est size of SM/AWR 125.9 MB
| Est size of SM/OPTSTAT 16.7 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size: 227.6 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************

End of Report
SQL>