SAP Knowledge Base Article - Preview

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


Read more...

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.