SAP Knowledge Base Article - Preview

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


SM/OPTSTAT occupies the most space within SYSUAX Tablespace.

SQL> SELECT occupant_name, space_usage_kbytes FROM V$SYSAUX_OCCUPANTS;

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)!



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


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 ONE Support launchpad (Login required).

Search for additional results

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