Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.4 [Release 11.1 to 11.2]
Information in this document applies to any platform.
Updated 4 2014 -- this note was originally written on information from 10g and up to 11.1
These older algorithms have been superceded and notably improved as of 11.2.0.x including 11.2.0.3 and higher
The content can be used for investigating the subject matter, but reviewing alternative notes may provide more useful information for more current versions
such as AWR Data Uses Significant Space in the SYSAUX Tablespace (Doc ID 287679.1) which provides quick results for the most common AWR + SYSAUX growth problems

Symptoms

Observed increased storage requirements, exceptional tablespace growth or both for the SYSAUX tablespace

The following information provides some suggestions on how to approach and understand factors contributing to SYSAUX tablespace growth

NOTE:

Each newer version of the RDBMS have improved algorithms for detecting and purging internal metric data collection stored in SYSAUX
While this should reduce the likelihood of needing to perform manual maintenance as described in the lower sections of this note, there are several potential factor we should discuss before declaring SYSAUX storage issues as a problem of the past.
That stated, by drilling into the AWRinfo.sql script, V$SYSAUX_OCCUPANTs and other related views the information and methods here may still prove useful for diagnosis of SYSAUX issues

Comment:

RAC and Exadata will force proportionately higher SYSAUX tablespace growth to maintain performance and auditing statistics.

Engineered systems -- Moving to Exadata and multiple node RAC can notably increase the number of database instances
and resources used as well as introducing new metric sets compared to previous configurations

Changes

Moving to RAC based configuration
New ORACLE database usage
Increase in metric usage or dependence
Closer or new monitoring of SYSAUX TBS growth leading to questions regarding allocation and growth sizes

Cause

There is no single source for higher than expected growth in the SYSAUX tablespace: There are however a few common sources that can be investigated and confirmed quickly using a few views and scripts

One important view is V$SYSAUX_OCCUPANTS
Another very useful and provided script is the AWRINFO.SQL script found in the Oracle_home/rdbms/admin directory

Lets start by taking a closer look at V$SYSAUX_OCCUPANTS*

   select OCCUPANT_NAME,OCCUPANT_DESC
      from V$SYSAUX_OCCUPANTS
      order by SPACE_USAGE_KBYTES desc

This view will change between Oracle Versions so consider the following information as an example

START your investigation by confirming what the top SYSAUX storage consumers are:

OCCUPANT_NAME            OCCUPANT_DESC
------------------------ ------------------------------------------------------
EM                     Enterprise Manager Repository
XDB                    XDB
SM/AWR                   Server Manageability* - Automatic Workload Repository      << This is a common area for high storage usage and growth
SM/OPTSTAT             Server Manageability* - Optimizer Statistics History           <<   This is a common area for high storage usage and growth
SDO                      Oracle Spatial
XSOQHIST                 OLAP API History Tables
AO                       Analytical Workspace Object Table
SM/ADVISOR               Server Manageability* - Advisor Framework
ORDIM/ORDDATA            Oracle Multimedia ORDDATA Components
LOGMNR                   LogMiner
XSAMD                    OLAP Catalog
SM/OTHER                 Server Manageability* - Other Components
TEXT                     Oracle Text
EXPRESSION_FILTER        Expression Filter System
WM                       Workspace Manager
SMON_SCN_TIME            Transaction Layer - SCN to TIME mapping
JOB_SCHEDULER            Unified Job Scheduler
SQL_MANAGEMENT_BASE      SQL Management Base Schema
PL/SCOPE                 PL/SQL Identifier Collection
LOGSTDBY               Logical Standby
EM_MONITORING_USER Enterprise Manager Monitoring User
STREAMS                  Oracle Streams
ORDIM                    Oracle Multimedia ORDSYS Components
AUTO_TASK                Automated Maintenance Tasks
ORDIM/ORDPLUGINS         Oracle Multimedia ORDPLUGINS Components
TSM                      Oracle Transparent Session Migration User
...
ULTRASEARCH            Oracle Ultra Search
STATSPACK            Statspack Repository
...
AUDIT_TABLES           DB audit tables
...

* Server Managability features are usually the most source for unexpected SYSAUX storage area and growth

Server Managability

When data collected and used for Server Managability (SM) including AWR and similar OPTSTAT tables reaches an internally defined threshold volume of data, Oracle will automatically create partitions
One important aspect off SM is that this can be scheduled implicitely (without dba intervention) or adhoc based on need. As this can be invisible to the DBA this area can generate unanticipated impact
within SYSAUX

SM/AWR                Server Manageability - Automatic Workload Repository
SM/OPTSTAT            Server Manageability - Optimizer Statistics History
SM/ADVISOR            Server Manageability - Advisor Framework -- Can result in holding information beyond the
                                                                  normal/ expected retention duration
SM/OTHER              Server Manageability - Other Component

COMMENT:

One reason potential reason this data is not purged during the retention threshold is that Advisors can require a portion of the data that is collected and shared with AWR snapshots or historic optimizer statistics If a snapshot range is automatically purged and the AWR retention period AND part of this data is required for SQLSETs and/or baselines, these snapshots will not automatically be dropped.
This can lead to the hidden accumulation of snapshots that also share data with the SQLSETs and BASELINEs. These sqlset and baseline dependent snapshots will only be freed if the SQLSETS and BASELINES are dropped first.

There is no warning if there are any snapshots which have not been dropped when manually or automatically purging by a range.

As of the writing of this note you will only receive the feedback that the "operation completed".
This can mislead the user into believing that old snapshot data has been freed even though this many not be the case

The above query will give a quick assessment of the top storage consumers in the SYSAUX tablespace but not the actual storage used: We can alter the query...

select SPACE_USAGE_KBYTES, OCCUPANT_NAME
from V$SYSAUX_OCCUPANTS
order by 1 desc

SPACE_USAGE_KBYTES OCCUPANT_NAME
------------------ --------------------------------
(1)        118144 SM/AWR
(2)             23360 SM/OPTSTAT
            13696 LOGMNR
(3)             10368 SM/ADVISOR ***
             6720 SM/OTHER
             3584 WM
             3264 SMON_SCN_TIME
...
...

Excerpts from Doc ID 329984.1

Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER

1) SM/AWR refers to the Automatic Workload Repository

2) SM/OPTSTAT stores older version of optimizer statistics. -
     While part of Server Managability SM/OPSTAT is not considered as part of SM/AWR (and stored in a different portion of the AWRinfo.sql Report)
   Stats in SM / OPSTAT are useful when we want to investigate query performance using the SQLT, SQL Monitor, sqltrace or other query analysis tools and scripts.
     We can also restore old stats in case plans may result in better or more consistent performance. encounter execution plan regression when the stats are refreshed
     OPSTAT collection is also influenced by automatic collection, object count, type and collection intervals as well as advisors and other settings which use these statistics

3) SM/ADVISOR
This refers to the advisor frameworks tables. Advisors like SQL Tuning Advisor, SQL Access Advisor, ADDM use this infrastructure.
These advisors use the data in the AWR to build their advise, but once created this will be stored in their own tables, and that is classed under this heading.

... or use the awrinfo.sql script for a simple, quick assessment of several areas of the SYSAUX tablespace

AWRINFO.SQL

Lets go through the sections of the AWRINFO.SQL report output

The AWRinfo.sq. Report HEADER information includes

Generated at (report creation time)
Snapshot interval (duration of snapshot interval)
Retention duration snapshots will be saved (Least number of days we keep snapshots)
System and database information (What and where report is generated against)

~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~

Report generated at
06:09:44 on Apr 24, 2014 ( Thursday ) in Timezone -06:00

Warning: Non Default AWR Setting!
--------------------------------------------------------------------------------
Snapshot interval is 60 minutes and Retention is 8 days <<

         DB_ID    DB_NAME   HOST_PLATFORM                             INST STARTUP_TIME       LAST_ASH_SID    PAR
    ------------ ---------    ---------------------------------------         -----       -----------------    ------------         ------
* 123456789   TESTDB    odx - Linux x86 64-bit                     1    17:22:58 (04/16)             649226            NO

SECTION 1 -- AWRINFO.SQL

The FIRST SECTION will show you what components exist in the SYSAUX Tablespace and their by size and TBS storage by %.

Essentially this is a query against DBA_SEGMENTS where TABLESPACE_NAME is SYSAUX

(1a) by SCHEMA

select owner, sum(bytes/1024) as Kbytes
from DBA_SEGMENTS
group by owner order by 2 desc

(1b) by Occupant Size

col OCCUPANT_NAME format a32
col SCHEMA_NAME format a24
col SPACE_USAGE_KBYTES format 999999999

      select OCCUPANT_NAME,SCHEMA_NAME,SPACE_USAGE_KBYTES
        from V$SYSAUX_OCCUPANTS
       order by 3 desc;

If you do not find a significant portion of the SYSAUX storage space identified in Section 1a or 1b you
can review (1c) and (1d) to investigate if storage is occupied by objects improperly stored in SYSAUX tbs.

########################################################
(I) AWR Snapshots Information
########################################################

*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size    .....
...

********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
...
...

******************************************
(1c) SYSAUX usage - Unregistered Schemas
******************************************

| This section displays schemas that are not registered
| in V$SYSAUX_OCCUPANTS
...
...

*************************************************************
(1d) SYSAUX usage - Unaccounted space in registered schemas
*************************************************************
|
| This section displays unaccounted space in the registered
| schemas of V$SYSAUX_OCCUPANTS.
|
| Unaccounted space in SYS/SYSTEM
...
...

SECTION 2 -- AWR Snapshots can be a major consumer of SYSAUX Storage Space:

Section II in the AWRINFO report is good place to monitor for current and future plus anticipated AWR snapshot size

Factors to consider in Section II that impact storage include:

Average AWR size per snapshot
Number of Snapshots    - Fifteen minute snapshots may be helpful in identifying problems, however, they can
per interval duration (day)         incur up to 4x the potential snapshot storage volume of a single 60 minute snapshot
                                          as the frequency increases to 96 vs. 24 snaps per day
Number of Instances if RAC - RAC AWR includes CR, GC and other metrics which are collected at the INSTANCE and the DATABASE level
Snapshot Retention duration - This is for the total number of snapshot per day * #of days - This can be a week, a month, or user defined, however,
the lack of enforcement of purging AWR based on the retention period was a problem source in older RDBMS versions

Retention is one of the four major factors for AWR Storage

Size_per_snapshot * Size_per_day * #_of_retention_days * #_of_instances

Comment: Engineered systems which are often RAC based also have their own unique contribution to increased metric collection including AWR storage requirements

Engineered systems factors - Cell offloading, smart scan metrics, Flash based metrics, IB stats and compression
are only a few examples of new and enhanced metrics used within Engineered Systems

*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 60 mins snapshot INTERVAL:                                        << The shorter the interval the higher the data collection Rate
|    AWR size/day                           xxx MB (652 K/snap * 24 snaps/day)
|    AWR size/wk                           xxxx MB (size_per_day * 7) per instance     < RAC is a major contributor to overall SYSAUX growth attibutable to AWR reports
|
| Estimates based on 23 snaps in past 24 hours:
|    AWR size/day                           xxx MB (652 K/snap and 23 snaps in past 24 hours)
|    AWR size/wk                           xxxx MB (size_per_day * 7) per instance
...

SECTION 3

a) Space usage by AWR components
b) Space usage within AWR Components

(3a) Space usage by AWR components (per database)
**********************************

COMPONENT        MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%
--------- --------- ------ ------------ ---------- ----------- ----------------
FIXED      ...
EVENTS ...
...
...

**********************************
(3b) Space usage within AWR Components (> xxxxK)
**********************************

COMPONENT        MB SEGMENT_NAME - % SPACE_USED                                           SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
...
...

SECTION 4

**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************

COMPONENT     MB     SEGMENT_NAME                                                              SEGMENT_TYPE
------------    --------- ---------------------------------------------------------------------            ---------------
NON_AWR        453.2   XDB.SYS_LOB0000057465C00025$$                                        LOBSEGMENT
NON_AWR        343.0   SYSMAN.MGMT_METRICS_RAW_PK                                          INDEX
NON_AWR        340.0   SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST                             INDEX
NON_AWR        225.0   SYS.WRI$_OPTSTAT_HISTGRM_HISTORY                               TABLE
NON_AWR        221.0   SYSMAN.MGMT_CURRENT_METRICS_PK                                 INDEX
NON_AWR        220.0   SYSMAN.MGMT_METRICS_COMP_KEYS_IDX_02                       INDEX
NON_AWR        119.0   SYS.I_WRI$_OPTSTAT_H_ST                                           INDEX
NON_AWR        119.0   SYSMAN.MGMT_ESA_REPORT_PK                                           INDEX
NON_AWR        119.0   SYSMAN.MGMT_CURRENT_METRICS_IDX_01                           INDEX
NON_AWR        118.0   SYSMAN.MGMT_STRING_METRIC_HISTORY_PK                          INDEX
NON_AWR        118.0   MDSYS.SYS_LOB0000064008C00006$$                                  LOBSEGMENT
NON_AWR        116.2   SYS.SYS_LOB0000065858C00004$$                                     LOBSEGMENT
NON_AWR        113.0   SYSMAN.MGMT_METRICS_COMPOSITE_KEYS                             TABLE

...
...

In Section Four you may notice SYSMAN ( EM / Cloud ) objects as well as Optimizer statistics (OPTSTAT) as the highest consumers
- Use the SEGMENT portion of the awrinfo report to determine the impact and context of this sections output

Also this section can be essential in determining SYSAUX growth IF non-default objects were populated in the SYSAUX.
- While creating User or Application objects in SYSAUX is fairly rare, some vendors do populate this tablespace with their own objects. This is important to note not only for storage impact, but important for migration and upgrades also

SECTION 5

Historic information on snapshot creation including: Instance, Database, start and end times (usually a fixed interval) and status errors or problems

**********************************
(5a) AWR snapshots - last 50
**********************************

Total snapshots in DB 17718412345 Instance 1 = 68

      DBID   SNAP_ID INST FLUSH_ELAPSED        ENDTM             STARTUP_TIME      STATUS ERRCNT
---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------
1771848785       2632     1 +00000 00:00:07.7    11:00:34 (04/22) 08:52:27 (04/22)       0      0
1771848785       2633     1 +00000 00:00:08.6    12:00:02 (04/22) 08:52:27 (04/22)       0      0
1771848785       2634     1 +00000 00:00:10.5    13:00:38 (04/22) 08:52:27 (04/22)       0      0
1771848785       2635     1 +00000 00:00:10.2    17:10:05 (04/22) 16:59:00 (04/22)       0      0
1771848785       2636     1 +00000 00:00:51.8    20:25:44 (04/22) 20:14:50 (04/22)       0      0
...

**********************************
(5b) AWR snapshots with errors or invalid
**********************************
...

**********************************
(5c) AWR snapshots -- OLDEST Non-Baselined snapshots
**********************************

      DBID    INST     SNAP_ID   ENDTM                  STATUS      ERROR_COUNT
----------    -----      ---------- -----------------          ------          -----------
1771812345     1         1024 13:54:55 (01/20)      0                 0

SECTION 6

**********************************
(6) AWR Control Settings - interval, retention
**********************************

   DBID           LSNAPID LSPLITID    LSNAPTIME    LPURGETIME       FLAG    INTERVAL                  RETENTION        VRSN
-------------------    -------- --------------      -------------------     -----------------     ------    ----------------       --------------------------- ---
17718412345     2681       2679    04/28 10:00:28    04/28 09:23:57    2     +00000 01:00:00.0   +00008 00:00:00.0    5

SECTION 7

**********************************
(7a) AWR Contents - row counts for each snapshots
**********************************

   SNAP_ID INST        ASH        SQL      SQBND      FILES      SEGST     SYSEVT
----------      -----    ---------- ----------    ----------      ----------    ---------- ----------
      2632       1          13           76       200            7           62           75
      2633       1        26       86         227            7           63           75
...
...

**********************************
(7b) AWR Contents - average row counts per snapshot
**********************************

SNAP_COUNT INST        ASH    SQLSTAT    SQLBIND      FILES    SEGSTAT   SYSEVENT
--------------- ---------- ---------- ---------- ---------- ---------- ----------     --------------
          68    1     55.26      83.62    234.81             6.9      57.21      72.41

**********************************
(7c) AWR total item counts - names, text, plans
**********************************

   SQLTEXT    SQLPLAN   SQLBMETA     SEGOBJ   DATAFILE   TEMPFILE
----------    ----------    ----------    ----------    ----------      ----------
       435           3559      1357      355         7        1

PART II

Advisor Framework Info

########################################################
(II) Advisor Framework Info
########################################################

**********************************
(1) Advisor Tasks - Last 50
**********************************

OWNER/ADVISOR TASK_ID/NAME                       CREATED          EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ----------
SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK       05:49:07 (11/03)        328 ########## AUTO COMPLETED
SYS/ADDM      8198/ADDM:1771848785_1_2613      23:01:06 (04/19)          4          4 AUTO COMPLETED
...
...

**********************************
(2) Advisor Task - Oldest 5
**********************************

OWNER/ADVISOR      TASK_ID/NAME                           CREATED          EXE_DURATN   EXE_CREATN HOW_C       STATUS
---------------------- --------------------------------                ----------------       ----------    ---------- -----          ------------
SYS/Segment Ad 6508/SYS_AUTO_SPCADV_07101930032   19:10:07 (03/30)         31              35       CMD    COMPLETED


SYS/ADDM       6518/ADDM:1771848785_1_2420      20:00:47 (03/30)          3          4 AUTO COMPLETED
...

**********************************
(3) Advisor Tasks With Errors - Last 50
**********************************
...
...

PART III

ASH Information

########################################################
(III) ASH Usage Info
########################################################

**********************************
(1a) ASH histogram (past 3 days)
**********************************

NUM_ACTIVE_SESSIONS   NUM_SAMPLES
--------------------    ------------
0000 - 0099                                 1395
0100 - 0199                                 1123
...
...

**********************************
(1b) ASH histogram (past 1 day)
**********************************

NUM_ACTIVE_SESSIONS   NUM_SAMPLES
--------------------                  ------------
0000 - 0099                   334

**********************************
(2a) ASH details (past 3 days)
**********************************

INST MIN_TIME         MAX_TIME          NUM_SAMPLES     NUM_ROWS AVG_ACTIVE
---- ---------------- ---------------- ------------ ------------ ----------
   1 11:34:42 (04/25) 09:59:44 (04/28)        2,453          829       0.34

**********************************
(2b) ASH details (past 1 day)
**********************************

INST MIN_TIME         MAX_TIME          NUM_SAMPLES     NUM_ROWS AVG_ACTIVE
---- ---------------- ---------------- ------------ ------------ ----------
   1 09:10:28 (04/28) 09:59:44 (04/28)          295           50       0.17

**********************************
(2c) ASH sessions (Fg Vs Bg) (past 1 day across all instances in RAC)
**********************************

Foreground %           36.0
Background %           64.0
MMNL %                0.0

End of Report
Report written to awrinfo.txt

Solution

Tips regarding
SYSAUX Space Usage:
================

Make sure the SYSAUX tablespace is set to AUTOEXTEND off -- This allows storage to be re-used vs. appended to
Confirm the STATISTICS_LEVEL value ** -- ALL is known to potentially be resource intensive while Basic and Typical are typically not
Check for the usage of advisors, baselines or sql tuning sets: -- These require trapping information which is retained in snapshots even if the snapshot range was scheduled to be dropped
Run awrinfo.sql scripts on each instance -- To better report and verify which objects are consuming the most space in the SYSAUX tablespace.
Run queries against sysaux_occupants -- A simple method to get general information on SYSAUX storage consumption

A nicely formated report can be generated by running the awrinfo.sql script

SQL> connect / as sysdba
SQL> @?/rdbms/admin/awrinfo.sql --- For Windows - The script ( = @ ) was executed from the Oracle_Home (= ? ) if set

In the report you may tend to see the following or similar tables are consuming the most storage space

1. WRH$_ACTIVE_SESSION_HISTORY
2. WRI$_OPTSTAT_HISTGRM_HISTORY
3. WRI$_OPTSTAT_HISTHEAD_HISTORY

Example from the AWRINFO.SQL script output which is created by default as awrinfo.txt in the current directory of sqlplus.

...
...
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 60 mins snapshot INTERVAL:

| AWR size/day    745.8 MB (31,823 K/snap * 24 snaps/day)
| AWR size/wk   5,220.9 MB (size_per_day * 7) per instance
| AWR size/wk 10,441.8 MB (size_per_day * 7) per database
|
| Estimates based on 24 snaps in past 24 hours:

| AWR size/day    745.8 MB (31,823 K/snap and 24 snaps in past 24 hours)
| AWR size/wk   5,220.9 MB (size_per_day * 7) per instance
| AWR size/wk 10,441.8 MB (size_per_day * 7) per database
|

i.e.per the above information 10GB is the expected weekly size

However in this example we find that the stats show 37gb even after several purge attempts

########################################################
(I) AWR Snapshots Information
########################################################
*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size 37,933.8 MB ( 97% of 39,292.9 MB MAX with AUTOEXTEND OFF )
|
| Schema SYS       occupies   37,178.3 MB ( 98.0% )   <
| Schema SYSTEM     occupies    731.6 MB ( 1.9% )
| Schema ABC       occupies    18.7 MB ( 0.0% )
| Schema WMSYS    occupies             3.1 MB ( 0.0% )
| Schema DBSNMP    occupies             2.2 MB ( 0.0% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name     Schema Name Space Usage
| --------------------------------------------------------
| SM/OPTSTAT             SYS      30,778.6 MB << OPTSTAT 30gb - Optimizer historic statistics
| SM/AWR                    SYS 5,283.1 MB << AWR
| SM/ADVISOR            SYS             940.4 MB << Advisors can 'trap' older snapshot data if an automatic or manual purge overlaps date range of the advisors collected data*
| LOGMNR             SYSTEM            722.3 MB
| SM/OTHER            SYS                32.1 MB

largest consumers
-----------------------
ASH 737.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_1198427434_5673
- 98% TABLE PARTITION

ASH 144.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_1198427434_5993
- 97% TABLE PARTITION

ASH 96.0 WRH$_ACTIVE_SESSION_HISTORY_PK.WRH$_ACTIVE_1198427434_5673
- 84% INDEX PARTITION

ASH 24.0 WRH$_ACTIVE_SESSION_HISTORY_PK.WRH$_ACTIVE_1198427434_5993
- 78% INDEX PARTITION

> />

SQLPLANS need to be reviewed as they can "trap" Snapshot information*

see

Doc ID 790039.1 How to Drop Plans from the SQL Plan Management (SPM) Repository
Doc ID 329984.1 Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER
Doc ID 287679.1 Space Management In Sysaux Tablespace with AWR in Use

++++++++++++++++++++++++++
SQLPLAN 656.0 MB WRH$_SQL_PLAN - 93% TABLE << Similar to advisors: sqlsets and baselines can also 'trap' older snapshot data if an automatic or manual purge overlaps date range of required collected data*
SQLPLAN 136.0 MB WRH$_SQL_PLAN_PK - 77% INDEX

* Snapshots cannot be dropped IF the data is required for SQLPLANs or BASELINEs even though the Snapshot purge appears to be successful

- i.e. you receive no feedback that the Snapshots cannot be dropped due to SQLPLAN / Baseline required data shared by snapshots

Below we see where the information is not being released

**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************
COMPONENT   MB     SEGMENT_NAME                    SEGMENT_TYPE
--------- --------- -----------------------------------------
NON_AWR 11,376.3   SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
NON_AWR 8,098.9   SYS.WRI$_OPTSTAT_HISTGRM_HISTORY    TABLE
NON_AWR 5,813.3   SYS.I_WRI$_OPTSTAT_H_ST             INDEX
NON_AWR 1,988.0   SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY   TABLE
NON_AWR 1,475.3   SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST   INDEX
NON_AWR    908.6   SYS.I_WRI$_OPTSTAT_HH_ST            INDEX
NON_AWR    636.0   SYS.SYS_LOB0000006213C00038$$ LOBSEGMENT
NON_AWR    380.0   SYS.WRI$_ADV_OBJECTS                TABLE
NON_AWR    256.0   SYS.I_WRI$_OPTSTAT_SYNOPSIS         INDEX
NON_AWR    224.0   SYS.WRI$_OPTSTAT_TAB_HISTORY        TABLE
NON_AWR    216.6   SYS.I_WRI$_OPTSTAT_TAB_ST           INDEX
NON_AWR    208.0   SYS.WRI$_ADV_OBJECTS_IDX_01         INDEX
NON_AWR    201.5   SYS.I_WRI$_OPTSTAT_TAB_OBJ#_ST      INDEX
NON_AWR    200.0   SYS.WRI$_OPTSTAT_SYNOPSIS$          TABLE
NON_AWR    160.0   SYS.SYS_LOB0000006207C00004$$ LOBSEGMENT
NON_AWR     96.0   SYS.WRI$_ADV_OBJECTS_PK             INDEX

Another helpful script is $ORACLE_HOME/rdbms/admin/utlsyxsz.sql. It provides a prediction of the space needed when creating the SYSAUX tablespace to begin with.

ACTION PLAN

Should be driven by the component and based on storage consumption or growth

Note: 387914.1 WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged
NOTE: Splitting partitions by itself does not purge data: it allow for smaller segment size and granularity to allow for a more efficient set of data to be purged

1. Check how many partitions do exist for the offending table

select table_name,partition_name from dba_tab_partitions
where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';

2. Try to force the creation of multiple partitions by splitting any current oversized partitions:

alter session set "_swrf_test_action" = 72;

3. Now that we have more partitions and less data per partition for a more unique period of time
Drop snapshots manually using a small[er] range.

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id IN NUMBER,
high_snap_id IN NUMBER
dbid IN NUMBER DEFAULT NULL);

- or -

exec dbms_stats.purge_stats(sysdate-&days);

4. If the drop snapshot did not finish or consume for UNDO space. Terminate the session and wait for a day or two the default auto purge job should automatically drop the old snapshots

Statistics_Level - Parameter
- see

Levels of data collection are dependent on the Statistics_Level which are retained in tables in the SYSAUX TBS

Oracle Advisory	BASIC	TYPICAL	ALL
Buffer Cache Advice	-----	yes	yes
MTTR Advice	-----	yes	yes
Shared Pool Advice	-----	yes	yes
Segment Level Statistics	-----	yes	yes
PGA Advice	-----	yes	yes
Timed Statistics	-----	yes	yes
Timed OS Statistics	-----	-----	yes
Plan Execution Statistics	-----	-----	yes

References

NOTE:255452.1 - Values for init.ora parameter STATISTICS_LEVEL
NOTE:957630.1 - Health Check Alert: Automatic statistics collection is excessive
NOTE:1369591.1 - Master Note: Optimizer Statistics
NOTE:790039.1 - How to Drop Plans from the SQL Plan Management (SPM) Repository
NOTE:287679.1 - How to Address Issues Where AWR Data Uses Significant Space in the SYSAUX Tablespace
NOTE:387914.1 - WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy

【AWR】Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large