Automatic Workload Repository (AWR) space usage

AWR is very powerful diagnostic tool which is used for problem detection and tuning. By default, snapshots of database performance statistics and metrics are taken every hour, retained for 7 days and stored in SYSAUX tablespace.

If possible, I prefer to change default settings to increase retention period and reduce snapshot interval. Before changing default values it is important to think about space consumption by the Automatic Workload Repository.


Nice tool for estimating amount of space required for the SYSAUX tablespace is - utlsyxsz.sql stored in “$ORACLE_HOME/rdbms/admin” directory.


In this example I will estimate size of AWR data with 15 min intervals and 60 days of retention.

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: /tmp/estimate_awr.out

Using the report name /tmp/estimate_awr.out
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Estimated at
10:03:56 on Srp 21, 2010 ( Srijeda ) in Timezone +02:00


DB_NAME HOST_PLATFORM INST STARTUP_TIME PAR
----------- ---------------------------------------- ----- ----------------- ---
* TESTDB l01test1 - Linux IA (32-bit) 1 22:08:09 (10/13) NO

~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size: 653.9 MB
|
| Total size of SM/AWR 334.8 MB ( 51.2% of SYSAUX )
| Total size of SM/OPTSTAT 104.2 MB ( 15.9% of SYSAUX )
| Total size of EM 48.8 MB ( 7.5% of SYSAUX )
| Total size of XDB 48.2 MB ( 7.4% of SYSAUX )
| Total size of SM/ADVISOR 45.2 MB ( 6.9% of SYSAUX )
| Total size of SDO 33.1 MB ( 5.1% of SYSAUX )
| Total size of WM 7.1 MB ( 1.1% of SYSAUX )
| Total size of LOGMNR 5.9 MB ( 0.9% of SYSAUX )
| Total size of SM/OTHER 4.9 MB ( 0.8% of SYSAUX )
| Total size of TEXT 4.6 MB ( 0.7% of SYSAUX )
| Total size of EXPRESSION_FILTER 3.6 MB ( 0.6% of SYSAUX )
| Total size of EM_MONITORING_USER 1.6 MB ( 0.2% of SYSAUX )
| Total size of LOGSTDBY 0.9 MB ( 0.1% of SYSAUX )
| Total size of XSOQHIST 0.8 MB ( 0.1% of SYSAUX )
| Total size of AO 0.8 MB ( 0.1% of SYSAUX )
| Total size of JOB_SCHEDULER 0.8 MB ( 0.1% of SYSAUX )
| Total size of ORDIM 0.5 MB ( 0.1% of SYSAUX )
| Total size of STREAMS 0.5 MB ( 0.1% of SYSAUX )
| Total size of TSM 0.3 MB ( 0.0% of SYSAUX )
| Total size of ODM 0.3 MB ( 0.0% of SYSAUX )
| Total size of Others 7.2 MB ( 1.1% 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: 60.0 minutes
| otherwise enter an alternative
|
Enter value for interval: 15

** Value for 'Interval Setting': 15

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

** Value for 'Retention Setting': 60

|
| 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.43
| otherwise enter an alternative
|
Enter value for active_sessions:

** Value for 'Average Number of Active Sessions': .43

| ***************************************************
| Estimated size of AWR: 2,816.7 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 15 minutes
| Retention - 60.00 days
| Num Instances - 1
| Active Sessions - 0.43
| Datafiles - 17
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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: 463.0
| otherwise enter an alternative [a positive integer]
|
Enter value for number_of_tables:

** Value for 'Number of Tables': 463

|
| 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 33.4 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 463
| Indexes - 496
| Columns - 5,734
| 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: 2,816.7 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 15 minutes
| Retention - 60.00 days
| Num Instances - 1
| Active Sessions - 0.43
| Datafiles - 17
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history 33.4 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 463
| Indexes - 496
| Columns - 5,734
| 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 48.8 MB
| Est size of XDB 48.2 MB
| Est size of SM/ADVISOR 45.2 MB
| Est size of SDO 33.1 MB
| Est size of WM 7.1 MB
| Est size of LOGMNR 5.9 MB
| Est size of SM/OTHER 4.9 MB
| Est size of TEXT 4.6 MB
| Est size of EXPRESSION_FILTER 3.6 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 JOB_SCHEDULER 0.8 MB
| Est size of ORDIM 0.5 MB
| Est size of STREAMS 0.5 MB
| Est size of TSM 0.3 MB
| Est size of ODM 0.3 MB
| Est size of Others 7.2 MB

| Est size of SM/AWR 2,816.7 MB
| Est size of SM/OPTSTAT 33.4 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size: 3,065.0 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************

End of Report

Another nice script to mention - awrinfo.sql stored in “$ORACLE_HOME/rdmbs/admin” directory. This script displays general AWR information such as current space usage, data distribution, etc. I like to check space usage with awrinfo.sql script when SYSAUX starts to increase rapidly.