SYSAUX TABLESPACE IS TOO LARGE BECAUSE OF OPTIMIZER STATISTICS HISTORY
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)
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
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
-- 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;
/
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.
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)
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)
Database : 11.2.0.4