1783696 - Huge space consumption of SM/OPTSTAT in SYSAUX Tablespace

Symptom

SM/OPTSTAT occupies the most space within SYSUAX Tablespace.

SQL> SELECT occupant_name, space_usage_kbytes FROM V$SYSAUX_OCCUPANTS;

OCCUPANT_NAME            SPACE_USAGE_KBYTES
------------------------------------------------------------------
LOGMNR                                     93.824
LOGSTDBY                                    1.408
SMON_SCN_TIME                           3.328
PL/SCOPE                                    1.600
STREAMS                                     1.024
AUDIT_TABLES                                 0
XDB                                                   0
AO                                              1.408
XSOQHIST                                   1.408
XSAMD                                               0
SM/AWR                                6.098.880
SM/ADVISOR                            420.672
SM/OPTSTAT                        31.647.360

Warning: This KBA is not dealing with space issues of SM/ADVISOR (e.g. huge growth and size of WRH$_ACTIVE_SESSION_HISTORY)!

Environment

Oracle Database Release 10g
Oracle Database Release 11g
Oracle Database Release 12c
Oracle Database Release 18c
Oracle Database Release 19c
SAP Release independent

Keywords

AWR, Automatic Workload Repository, growth, space, SM/OPTSTAT, grow, statistic, retention, DBMS_STATS, PURGE_STATS, sysaux, waste, SM, OPTSTAT, WRI$, history, AWRInfo, AWRINFO.SQL , KBA , BC-DB-ORA , Oracle , BC-DB-ORA-DBA , Database Administration , How To

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.