1783696 - Huge space consumption of SM/OPTSTAT in SYSAUX Tablespace | SAP Knowledge Base Article

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

  • as of Oracle Database Release 10g
  • SAP Release independent

Keywords

AWR,Automatic Workload Repository,growth,space,SM/OPTSTAT,grow,statistic,retention,DBMS_STATS,PURGE_STATS,sysaux,waste , KBA , BC-DB-ORA , Oracle , BC-DB-ORA-DBA , Database Administration with Oracle , 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.