1.General Information

Memory versions of statistics regularly transferred to disk by MMON.

AWR stores base statistics:-counters and value statistics.

ASH data is firstly captured first to memory in 1 second interval for active sessions only.ASH data is reduced by 10 times sample in memory data.ASH data is used by ADDM.

The advisor report produced by  ADDM reports are used by segment advisior and other advisiors.

AWR stores in SYSAUX.

By default 60 min interval.

On typical case,for 10 Active sessions,200MB to 300MB space is occupied for 7 days.

 

2.How to estimate size of SYSAUX in case of retention is changed

system. A sizing script, utlsyxsz.sql, includes factors such as the size of the current occupants of the SYSAUX tablespace, number of active sessions, frequency of snapshots, and retention time.

 

SQL> @?/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:

Using the report name utlsyxsz.txt
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Estimated at
23:53:17 on Apr 01, 2018 ( Sunday ) in Timezone -05:00

DB_NAME HOST_PLATFORM INST STARTUP_TIME PAR
———– —————————————- —– —————– —
* ORCL localhost01 – Linux x86 64-bit 1 12:37:35 (02/21) YES
ORCL localhost02 – Linux x86 64-bit 2 12:37:35 (02/21) YES

~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size: 4,700.1 MB
|
| Total size of SM/AWR 3,360.6 MB ( 71.5% of SYSAUX )
| Total size of SM/OPTSTAT 269.1 MB ( 5.7% of SYSAUX )
| Total size of SM/OTHER 368.5 MB ( 7.8% of SYSAUX )
| Total size of SDO 77.8 MB ( 1.7% of SYSAUX )
| Total size of XDB 68.6 MB ( 1.5% of SYSAUX )
| Total size of SM/ADVISOR 45.6 MB ( 1.0% of SYSAUX )
| Total size of XSOQHIST 38.8 MB ( 0.8% of SYSAUX )
| Total size of AO 38.8 MB ( 0.8% of SYSAUX )
| Total size of JOB_SCHEDULER 16.6 MB ( 0.4% of SYSAUX )
| Total size of ORDIM/ORDDATA 16.1 MB ( 0.3% of SYSAUX )
| Total size of LOGMNR 13.9 MB ( 0.3% of SYSAUX )
| Total size of WM 7.2 MB ( 0.2% of SYSAUX )
| Total size of TEXT 3.8 MB ( 0.1% of SYSAUX )
| Total size of AUDSYS 3.6 MB ( 0.1% of SYSAUX )
| Total size of SMON_SCN_TIME 3.3 MB ( 0.1% of SYSAUX )
| Total size of SQL_MANAGEMENT_BASE 2.4 MB ( 0.1% of SYSAUX )
| Total size of PL/SCOPE 1.6 MB ( 0.0% of SYSAUX )
| Total size of LOGSTDBY 1.5 MB ( 0.0% of SYSAUX )
| Total size of EM_MONITORING_USER 1.4 MB ( 0.0% of SYSAUX )
| Total size of STREAMS 1.0 MB ( 0.0% of SYSAUX )
| Total size of ORDIM 0.4 MB ( 0.0% of SYSAUX )
| Total size of AUTO_TASK 0.4 MB ( 0.0% of SYSAUX )
| Total size of Others 359.4 MB ( 7.6% 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:

** Value for ‘Interval Setting’: 60

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

** Value for ‘Retention Setting’: 90

|
| For ‘Number of Instances’,
| Press <return> to use the current value: 2.00
| otherwise enter an alternative
|
Enter value for num_instances: 2

** Value for ‘Number of Instances’: 2

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

** Value for ‘Average Number of Active Sessions’: 20

| ***************************************************
| Estimated size of AWR: 9,779.7 MB
| Estimated size of AWR per instance: 4,889.9 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval – 60 minutes
| Retention – 90.00 days
| Num Instances – 2
| Active Sessions – 20.00
| Datafiles – 10
| ***************************************************

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

** Value for ‘Number of Tables’: 137

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

** Value for ‘Number of Partitions’: 37

|
| 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 51.5 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables – 137
| Indexes – 201
| Columns – 807
| Partitions – 37
| Indexes on Partitions – 4
| Columns in Partitions – 111
| Stats Retention in Days – 31
| Level of DML Activity – Medium
| ***************************************************

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

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR: 9,779.7 MB
| Estimated size of AWR per instance: 4,889.9 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval – 60 minutes
| Retention – 90.00 days
| Num Instances – 2
| Active Sessions – 20.00
| Datafiles – 10
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history 51.5 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables – 137
| Indexes – 201
| Columns – 807
| Partitions – 37
| Indexes on Partitions – 4
| Columns in Partitions – 111
| 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/OTHER 368.5 MB
| Est size of SDO 77.8 MB
| Est size of XDB 68.6 MB
| Est size of SM/ADVISOR 45.6 MB
| Est size of XSOQHIST 38.8 MB
| Est size of AO 38.8 MB
| Est size of JOB_SCHEDULER 16.6 MB
| Est size of ORDIM/ORDDATA 16.1 MB
| Est size of LOGMNR 13.9 MB
| Est size of WM 7.2 MB
| Est size of TEXT 3.8 MB
| Est size of AUDSYS 3.6 MB
| Est size of SMON_SCN_TIME 3.3 MB
| Est size of SQL_MANAGEMENT_BASE 2.4 MB
| Est size of PL/SCOPE 1.6 MB
| Est size of LOGSTDBY 1.5 MB
| Est size of EM_MONITORING_USER 1.4 MB
| Est size of STREAMS 1.0 MB
| Est size of ORDIM 0.4 MB
| Est size of AUTO_TASK 0.4 MB
| Est size of Others 359.4 MB

| Est size of SM/AWR 9,779.7 MB
| Est size of SM/OPTSTAT 51.5 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size: 10,901.6 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************

End of Report

3.The awrinfo.sql script produces a report of the estimated growth rates of various occupants of the SYSAUX tablespace.

 

SQL> @?/rdbms/admin/awrinfo.sql

AWR handles space management for the snapshots. Every night the MMON process purges snapshots that are older than the retention period. If AWR detects that SYSAUX is out of space, it automatically reuses the space occupied by the oldest set of snapshots by deleting them. An alert is then sent to the DBA to indicate that SYSAUX is under space pressure.

 

4.Modify snapshot setting

 

Retention:-default 8 days. Minimum 1 day.0 to disable automatic purging.

Interval:-Minimum 10 min.Maximum 100 years.Default 60 min.

Topnsql:-top 30 for typical.Top 100 for ALL.

5.Create manual snapshot:-

 

SQL>DBMS_WORKLOAD_REPOSITORY.create_snapshot.

 

6.Generating AWR reports:-

 

SQL>@?/rdbms/admin/awrrpt.sql

The user must have SELECT_CATALOG_ROLE privilege.

DBA_HIST_SNAPSHOT will have range of SNAP_ID.

Script prompts for the following:
– Report format: HTML or text
– Number of days from which snapshots are to be chosen
– Beginning and ending snapshot IDs
– File name for the report File name for the report