Print Friendly, PDF & Email

SYMPTOMS
The SYSAUX tablespace uses too much space on the disk.

-- SYSAUX occupation by segment types

SELECT SEGMENT_TYPE,
       ROUND(SUM(BYTES)/1024/1024) MB
FROM
       DBA_SEGMENTS
WHERE
       TABLESPACE_NAME = 'SYSAUX'
GROUP BY
       SEGMENT_TYPE
ORDER BY 2;
SEGMENT_TYPE                 MB
-------------------- ----------
TABLE SUBPARTITION            2
NESTED TABLE                  2
LOB PARTITION                 2
CLUSTER                       5
LOBINDEX                     57
INDEX PARTITION             315
LOBSEGMENT                 4892
TABLE                     13572
TABLE PARTITION           55618
INDEX                    104863

10 rows selected.

By querying the V$SYSAUX_OCCUPANTS it’s determined that the SM/OPTSTAT option is taking up most of the allocated space.

SET LINES 300
SET PAGES 999
COL SCHEMA_NAME FOR A20
COL OCCUPANT_NAME FOR A25 
COL OCCUPANT_DESC FOR A55

SELECT SCHEMA_NAME,
       OCCUPANT_NAME,
       OCCUPANT_DESC,
       ROUND(SPACE_USAGE_KBYTES/1024) SPACE_USAGE_MB
FROM
       V$SYSAUX_OCCUPANTS
ORDER BY SPACE_USAGE_MB;
SCHEMA_NAME           OCCUPANT_NAME                  OCCUPANT_DESC                                          SPACE_USAGE_MB
--------------------- ------------------------------ ------------------------------------------------------ --------------
PERFSTAT              STATSPACK                      Statspack Repository                                                0
TSMSYS                TSM                            Oracle Transparent Session Migration User                           0
WK_TEST               ULTRASEARCH_DEMO_USER          Oracle Ultra Search Demo User                                       0
WKSYS                 ULTRASEARCH                    Oracle Ultra Search                                                 0
ORDSYS                ORDIM                          Oracle Multimedia ORDSYS Components                                 0
SYSMAN                EM                             Enterprise Manager Repository                                       0
SI_INFORMTN_SCHEMA    ORDIM/SI_INFORMTN_SCHEMA       Oracle Multimedia SI_INFORMTN_SCHEMA Components                     0
ORDPLUGINS            ORDIM/ORDPLUGINS               Oracle Multimedia ORDPLUGINS Components                             0
SYS                   AUTO_TASK                      Automated Maintenance Tasks                                         0
SYS                   STREAMS                        Oracle Streams                                                      1
SYSTEM                LOGSTDBY                       Logical Standby                                                     1
DBSNMP                EM_MONITORING_USER             Enterprise Manager Monitoring User                                  2
SYS                   PL/SCOPE                       PL/SQL Identifier Collection                                        3
EXFSYS                EXPRESSION_FILTER              Expression Filter System                                            4
CTXSYS                TEXT                           Oracle Text                                                         4
WMSYS                 WM                             Workspace Manager                                                   4
SYS                   SMON_SCN_TIME                  Transaction Layer - SCN to TIME mapping                             5
OLAPSYS               XSAMD                          OLAP Catalog                                                        5
SYS                   SQL_MANAGEMENT_BASE            SQL Management Base Schema                                         10
SYSTEM                LOGMNR                         LogMiner                                                           13
ORDDATA               ORDIM/ORDDATA                  Oracle Multimedia ORDDATA Components                               14
SYS                   SM/OTHER                       Server Manageability - Other Components                            21
SYS                   AO                             Analytical Workspace Object Table                                  38
SYS                   JOB_SCHEDULER                  Unified Job Scheduler                                              38
SYS                   XSOQHIST                       OLAP API History Tables                                            38
MDSYS                 SDO                            Oracle Spatial                                                     67
XDB                   XDB                            XDB                                                               126
SYS                   SM/ADVISOR                     Server Manageability - Advisor Framework                         1492
SYS                   AUDIT_TABLES                   DB audit tables                                                  2005
SYS                   SM/AWR                         Server Manageability - Automatic Workload Repository            25032
SYS                   SM/OPTSTAT                     Server Manageability - Optimizer Statistics History            150275

31 rows selected.

For the SYSAUX tablespace, the segments like the %OPTSTAT% are using up most of the tablespace

SET LINES 300
SET PAGES 999
COL OWNER FOR A15 
COL SEGMENT_NAME FOR A30 
COL SEGMENT_TYPE FOR A20

WITH TABLES AS (
SELECT OWNER,
       SEGMENT_NAME,
       SEGMENT_TYPE,
       ROUND(SUM(BYTES)/1024/1024) MB
FROM
       DBA_SEGMENTS
WHERE
       TABLESPACE_NAME='SYSAUX' AND
       SEGMENT_TYPE LIKE '%TABLE%'
GROUP BY
       OWNER,
       SEGMENT_NAME,
       SEGMENT_TYPE
HAVING
       -- List all SYSAUX tables larger than 100 MiB
       -- ROUND(SUM(BYTES)/1024/1024) > 100
       ROUND(SUM(BYTES)/1024/1024) > 0
),
INDEXES AS (
SELECT I.OWNER,
       I.INDEX_NAME
FROM
       DBA_INDEXES I,
       TABLES T
WHERE
       I.OWNER=T.OWNER AND
       I.TABLE_NAME=T.SEGMENT_NAME
)
SELECT * FROM TABLES
UNION ALL
-- List all indexes for the found tables 
SELECT D.OWNER,
       D.SEGMENT_NAME,
       D.SEGMENT_TYPE,
       ROUND(SUM(D.BYTES)/1024/1024) MB
FROM
       DBA_SEGMENTS D,
       INDEXES I
WHERE
       D.OWNER=I.OWNER AND
       D.SEGMENT_NAME=I.INDEX_NAME	   
GROUP BY
       D.OWNER,
       D.SEGMENT_NAME,
       D.SEGMENT_TYPE
ORDER BY 4;
OWNER           SEGMENT_NAME                   SEGMENT_TYPE                 MB
--------------- ------------------------------ -------------------- ----------
...             ...                            ...                         ...
SYS             I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST  INDEX                      1350
SYS             AUD$                           TABLE                      2005
SYS             WRH$_SQL_BIND_METADATA_PK      INDEX                      2036
SYS             WRH$_SQL_PLAN                  TABLE                      2192
SYS             WRM$_SNAPSHOT_DETAILS          TABLE                      2271
SYS             WRH$_SQL_BIND_METADATA         TABLE                      2418
SYS             WRM$_SNAPSHOT_DETAILS_INDEX    INDEX                      3406
SYS             I_WRI$_OPTSTAT_H_ST            INDEX                     35516
SYS             WRI$_OPTSTAT_HISTGRM_HISTORY   TABLE PARTITION           54155
SYS             I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX                     57495

452 rows selected

All last three segments of the listing are related to the SM/OPTSTAT option.
The WRI$_OPTSTAT_HISTGRM_HISTORY table is the table for storing data of the SM/OPTSTAT option, and the last 2 indices are the WRI$_OPTSTAT_HISTGRM_HISTORY table’s indices.

SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='WRI$_OPTSTAT_HISTGRM_HISTORY';

INDEX_NAME
------------------------------
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST
I_WRI$_OPTSTAT_H_ST

 
DETAILS
According to both Doc ID 329984.1 and Doc ID 452011.1, the SM/OPTSTAT option is used for storing old table statistics when new statistics have been gathered for a table.
It’s performed for the purpose of restoring the old table’s statistics if it is requested. For instance, when query performance degraded because new statistics were gathered for a table.

SM/OPTSTAT
This stores older version of optimizer statistics.
We have new capability in Oracle Database 10g that allows you restore old stats in case 
you encounter execution plan regression when the stats are refreshed. The default retention 
for this data is 31 days. More details on how to use this can be found in Note 452011.1. 
Strictly speaking, this is not part of AWR and is therefore not controlled by AWR retention 
parameter, instead we can use the ALTER_STATS_HISTORY_RETENTION procedure of the DBMS_STATS 
package to set and change the retention time for this data. 
Beginning with Oracle10G, when statistics are gathered for a table, the old 
statistics are retained so should there be any problem with performance of queries dependent 
on those statistics, the old ones can be restored.

By default, the old statistics are to be kept for 31 day.

SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

But it can be altered through the following statement

EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(NUMBER_OF_DAYS)

where the NUMBER_OF_DAYS is desired a number of days for keeping old optimizer statistics.

The following query lets us know the date since the available old statistics are kept

SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
15-JAN-21 12.13.42.374898000 AM +03:00

SQL> SELECT SYSDATE TODAY FROM DUAL;

TODAY
------------------
15-FEB-21

As it’s seen from the listing output the old statistics are available for the last 1 month (31 days)

NOTE

Although GET_STATS_HISTORY_AVAILABILITY shows that old statistics data are kept for 31 days only, the old statistics history can be still kept beyond that date.

 
All available old statisitcs history for a given table, for instance WRI$_OPTSTAT_HISTGRM_HISTORY table, can be found by querying the DBA_TAB_STATS_HISTORY table

SELECT TABLE_NAME, 
       STATS_UPDATE_TIME 
FROM
       DBA_TAB_STATS_HISTORY
WHERE
      TABLE_NAME='WRI$_OPTSTAT_HISTGRM_HISTORY';

TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
WRI$_OPTSTAT_HISTGRM_HISTORY   22-FEB-20 07.40.40.587077 PM +03:00
WRI$_OPTSTAT_HISTGRM_HISTORY   22-FEB-20 07.40.43.754653 PM +03:00

 
PURGING HISTORIC DATA
So, How the old statistics are purged when they’ve exceeded the retention period?

According to Doc ID 1055547.1, the MMON process is responsible for automatic purging of all old statistics history exceeded the defined retention period.
The same Doc ID says that MMON has a time limit that is equal to 5 minutes to process the old statistics history purging. If the purging operation takes more than 5 minutes the purging process will be aborted and NO history data will be purged in this case.

Default for Statistics History Retention is 31 days

MMON performs the purge of the optimizer stats history automatically. However it has an internal 
limit of 5 minutes to perform this job. If the operation takes more than 5 minutes, then it is 
aborted and stats not purged. No trace or alert message is reported.

That simply means if for some reason the historic data can’t be purged during 5 minutes time interval they will retain in the historic table.
The table will accumulate the data of the previous periods in this case and therefore the SYSAUX tablespace will grow, eg: because the WRI$_OPTSTAT_HISTHEAD_HISTORY table grows continually

This what exactly happend in one of our database. The historic table stores data for the several years

SET TIMING ON 
SET AUTOTRACE ON 
SET PAGES 999
SET LINES 300 

SELECT COUNT(*) FROM WRI$_OPTSTAT_HISTGRM_HISTORY;

  COUNT(*)
----------
1028378263

Elapsed: 01:29:44.91

SELECT MIN(SAVTIME_DATE) LOWER_DATE, MAX(SAVTIME_DATE) UPPER_DATE FROM WRI$_OPTSTAT_HISTGRM_HISTORY ;

LOWER_DATE         UPPER_DATE
------------------ ------------------
08-JUN-19          14-FEB-21

Elapsed: 00:09:16.23

SQL> SELECT SYSDATE TODAY FROM DUAL;

TODAY
------------------
15-FEB-21
NOTE

If the SAVTIME_DATE column is missing in the WRI$_OPTSTAT_HISTGRM_HISTORY table use the SAVTIME column instead

SELECT MIN(SAVTIME) LOWER_DATE, MAX(SAVTIME) UPPER_DATE FROM WRI$_OPTSTAT_HISTGRM_HISTORY ;

 
SOLUTION
If old statistics hitory data are not purged automatically they can be purged manually though the DBMS_STATS.PURGE_STATS procedure.
The DBMS_STATS.PURGE_STATS procedure can operate in 2 modes: DELETE rows or TRUNCATE historic tables

DELETE
-- Show how long historic data are kept

SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
15-JAN-21 12.13.42.374898000 AM +03:00

-- To purge all data older than 10 days from now

EXEC DBMS_STATS.PURGE_STATS(SYSDATE-10);

-- To purge all data before spesific timestamp 

EXEC DBMS_STATS.PURGE_STATS(TO_TIMESTAMP_TZ('14-02-2021 00:00:00 Europe/Moscow','DD-MM-YYYY HH24:MI:SS TZR'));

If there are too many stored historic data you can purge them in small batches. It will prevent running out of undo space(ORA-01555)

For instance, to keep historic data for the last 5 days

SET SERVEROUTPUT ON
BEGIN
FOR DAYS IN REVERSE 5..31
LOOP
  DBMS_OUTPUT.PUT_LINE('...');
  DBMS_OUTPUT.PUT_LINE('Purging data older than ' || DAYS || ' days');
  DBMS_OUTPUT.PUT_LINE('Started   : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
  DBMS_STATS.PURGE_STATS(SYSDATE-DAYS);
  DBMS_OUTPUT.PUT_LINE('Completed : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
END LOOP;
END;
/
NOTE

In this mode the DBMS_STATS.PURGE_STATS procedure does not release the space in the SYSAUX tablespace after historic data has been purged. It’s expected behaviour as the procedure in this mode just issue DELETE statement for purging data.
Review the Doc ID 454678.1 to know how to reclaim the space after old statistics history has been purged in the database.

TRUNCATE

This is the fastest way to purge all data of history tables. This acts like truncate.
But this option is available only if the 10279045 patch has been applied against the database. Review Doc ID 10279045.8 for details.

The following statement will truncate all statistics history from the database

EXEC DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL)
NOTE

It’s not recommend to interrupt the PURGE_STATS manually (hitting Ctrl+C) while it is running with PURGE_ALL option as it may lead to inconsistencies.

 
TESTCASE
In this testcase I’m about to delete all old statistics history data from the database by running the DBMS_STATS.PURGE_STATS procedure in TRUNCATE mode. I do it as all historical data are not required anymore in my specific case.

SQL> SET TIMING ON
SQL> EXEC DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.91

Veryfing that segments have reduced in size after TRUNCATE operation. Just do a comparison of the query output at the beginning of the article and the following query’s output.

SELECT SEGMENT_TYPE,
       ROUND(SUM(BYTES)/1024/1024) MB
FROM
       DBA_SEGMENTS
WHERE
       TABLESPACE_NAME = 'SYSAUX'
GROUP BY
       SEGMENT_TYPE
ORDER BY 2;
SEGMENT_TYPE                 MB
-------------------- ----------
TABLE SUBPARTITION            2
NESTED TABLE                  2
LOB PARTITION                 2
CLUSTER                       5
LOBINDEX                     57
INDEX PARTITION             316
TABLE PARTITION             548
LOBSEGMENT                 4892
INDEX                      9735
TABLE                     13507

10 rows selected.

Veryfing that SM/OPTSTAT option reduced its ocupying size in the SYSAUX tablespace

SET LINES 300
SET PAGES 999
COL SCHEMA_NAME FOR A20
COL OCCUPANT_NAME FOR A25 
COL OCCUPANT_DESC FOR A55

SELECT SCHEMA_NAME,
       OCCUPANT_NAME,
       OCCUPANT_DESC,
       ROUND(SPACE_USAGE_KBYTES/1024) SPACE_USAGE_MB
FROM
       V$SYSAUX_OCCUPANTS
ORDER BY SPACE_USAGE_MB;

SCHEMA_NAME        OCCUPANT_NAME                  OCCUPANT_DESC                                         SPACE_USAGE_MB
------------------ ------------------------------ ----------------------------------------------------  --------------
PERFSTAT           STATSPACK                      Statspack Repository                                      0
TSMSYS             TSM                            Oracle Transparent Session Migration User                 0
WK_TEST            ULTRASEARCH_DEMO_USER          Oracle Ultra Search Demo User                             0
WKSYS              ULTRASEARCH                    Oracle Ultra Search                                       0
ORDSYS             ORDIM                          Oracle Multimedia ORDSYS Components                       0
SYSMAN             EM                             Enterprise Manager Repository                             0
SI_INFORMTN_SCHEMA ORDIM/SI_INFORMTN_SCHEMA       Oracle Multimedia SI_INFORMTN_SCHEMA Components           0
ORDPLUGINS         ORDIM/ORDPLUGINS               Oracle Multimedia ORDPLUGINS Components                   0
SYS                AUTO_TASK                      Automated Maintenance Tasks                               0
SYS                STREAMS                        Oracle Streams                                            1
SYSTEM             LOGSTDBY                       Logical Standby                                           1
DBSNMP             EM_MONITORING_USER             Enterprise Manager Monitoring User                        2
SYS                PL/SCOPE                       PL/SQL Identifier Collection                              3
EXFSYS             EXPRESSION_FILTER              Expression Filter System                                  4
WMSYS              WM                             Workspace Manager                                         4
CTXSYS             TEXT                           Oracle Text                                               4
OLAPSYS            XSAMD                          OLAP Catalog                                              5
SYS                SMON_SCN_TIME                  Transaction Layer - SCN to TIME mapping                   5
SYS                SQL_MANAGEMENT_BASE            SQL Management Base Schema                               10
SYS                SM/OPTSTAT                     Server Manageability - Optimizer Statistics History      13
SYSTEM             LOGMNR                         LogMiner                                                 13
ORDDATA            ORDIM/ORDDATA                  Oracle Multimedia ORDDATA Components                     14
SYS                SM/OTHER                       Server Manageability - Other Components                  21
SYS                XSOQHIST                       OLAP API History Tables                                  38
SYS                AO                             Analytical Workspace Object Table                        38
SYS                JOB_SCHEDULER                  Unified Job Scheduler                                    38
MDSYS              SDO                            Oracle Spatial                                           67
XDB                XDB                            XDB                                                     126
SYS                M/ADVISOR                      Server Manageability - Advisor Framework               1492
SYS                AUDIT_TABLES                   DB audit tables                                        2005
SYS                SM/AWR                         Server Manageability - Automatic Workload Repository  25054

31 rows selected.

Veryfing that the old statistics history table is empty now

SELECT COUNT(*) FROM WRI$_OPTSTAT_HISTGRM_HISTORY;

  COUNT(*)
----------
         0

Verying that NO old stasticis history data are avaiable

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
------------------
15-FEB-21

SELECT TABLE_NAME, 
       STATS_UPDATE_TIME 
FROM
       DBA_TAB_STATS_HISTORY
WHERE
      TABLE_NAME='WRI$_OPTSTAT_HISTGRM_HISTORY';

no rows selected

 
REFERENCES
Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER (Doc ID 329984.1)
How to Restore Oracle Optimizer Statistics (Doc ID 452011.1)
How to View Table Statistics History (Doc ID 761554.1)
SYSAUX Grows Because Optimizer Stats History is Not Purged (Doc ID 1055547.1)
Statistics Space Used by SM/OPTSTAT in the SYSAUX Tablespace is not Reclaimed After Purging (Doc ID 454678.1)
Bug 10279045 – Slow Statistics purging (SYSAUX grows) (Doc ID 10279045.8)
DBMS_STATS GATHER_STATS_JOB Fails With “ORA-04020: deadlock detected while trying to lock object SYS.WRI$_OPTSTAT_HISTGRM_HISTORY” (Doc ID 2046742.1)

Master Note: Optimizer Statistics (Doc ID 1369591.1)
General Guidelines for SYSAUX Space Issues (Doc ID 552880.1)
Tips if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1)
Bug 14373728 – Old Statistics not Purged from SYSAUX Tablespace (Doc ID 14373728.8)
Bug 8553944 – SYSAUX tablespace grows (Doc ID 8553944.8)
SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1)
Database Upgrade is slow on history table wri$_optstat_histgrm_history (Doc ID 2177839.1)

 
 

Version  : 12:59 17.02.2021
Database : 11.2.0.4