SYSAUX is growing

[Former Member]

Hi Experts,

SYSAUX tablespace is growing, in 2 months it had reached from 85% to 90%.

In DB02, When I checked space critical objects it is saying no critical objects.

Tablespace Size (kb) Free (kb) Used (%) Tab/ind Extents AutoExt (kb) Used (%) Status Backup

SYSAUX 512,000 47,296 90 1,688 3,034 33.554.416 1 ONLINE NOT ACTIVE

I have execute the following queries for your infomation.

select dbms_stats.get_stats_history_retention present_retention from dual;

PRESENT_RETENTION

-

---

31

SELECT occupant_name, space_usage_kbytes FROM V$SYSAUX_OCCUPANTS;

OCCUPANT_NAME SPACE_USAGE_KBYTES

-

---

-

---

LOGMNR 6016

LOGSTDBY 896

STREAMS 512

XDB 0

AO 768

XSOQHIST 768

XSAMD 0

SM/AWR 90944

SM/ADVISOR 14720

SM/OPTSTAT 94144

SM/OTHER 136704

STATSPACK 0

ODM 0

SDO 0

WM 0

ORDIM 0

ORDIM/PLUGINS 0

ORDIM/SQLMM 0

EM 53184

TEXT 0

ULTRASEARCH 0

ULTRASEARCH_DEMO_USER 0

EXPRESSION_FILTER 0

EM_MONITORING_USER 0

TSM 256

JOB_SCHEDULER 384

26 rows selected.

Environment: Oracle 10g, windows 2003, SAP R/3 4.6C

Kindly advice.

Thanks and Regards

Satya

[Former Member · Accepted solution]

Hi Satya,

Please issue the below two commands and let me the output of the same and put the content in the code tag.

SQL> select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,SPACE_USAGE_KBYTES from v$sysaux_occupants;

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where tablespace_name='SYSAUX';

Hope we'll get the better idea about your SysAux tablespace !

[Former Member · Accepted solution]

Hi,

Please find the output.

SQL> select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,SPACE_USAGE_KBYTES from v$sysaux_occupants;

OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAME SPACE_USAGE_KBYTES

-

---

-

---

LOGMNR LogMiner SYSTEM 6016

LOGSTDBY Logical Standby SYSTEM 896

STREAMS Oracle Streams SYS 512

XDB XDB XDB 0

AO Analytical Workspace Object Table SYS 768

XSOQHIST OLAP API History Tables SYS 768

XSAMD OLAP Catalog OLAPSYS 0

SM/AWR Server Manageability - Automatic Workload Repository SYS 90432

SM/ADVISOR Server Manageability - Advisor Framework SYS 14720

SM/OPTSTAT Server Manageability - Optimizer Statistics History SYS 94144

SM/OTHER Server Manageability - Other Components SYS 136704

STATSPACK Statspack Repository PERFSTAT 0

ODM Oracle Data Mining DMSYS 0

SDO Oracle Spatial MDSYS 0

WM Workspace Manager WMSYS 0

ORDIM Oracle interMedia ORDSYS Components ORDSYS 0

ORDIM/PLUGINS Oracle interMedia ORDPLUGINS Components ORDPLUGINS 0

ORDIM/SQLMM Oracle interMedia SI_INFORMTN_SCHEMA Components SI_INFORMTN_SCHEMA 0

EM Enterprise Manager Repository SYSMAN 53184

TEXT Oracle Text CTXSYS 0

ULTRASEARCH Oracle Ultra Search WKSYS 0

ULTRASEARCH_DEMO_USER Oracle Ultra Search Demo User WK_TEST 0

EXPRESSION_FILTER Expression Filter System EXFSYS 0

EM_MONITORING_USER Enterprise Manager Monitoring User DBSNMP 0

TSM Oracle Transparent Session Migration User TSMSYS 256

JOB_SCHEDULER Unified Job Scheduler SYS 384

26 rows selected.

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where tablespace_name='SYSAUX';

SUM(BYTES)/1024/1024/1024

-

---

.440734863

Sorry I don't know how to put in code tage

Thanks and Regards

Satya

Edited by: Satyanarayana Buddarapu on Jun 11, 2010 11:55 AM

Edited by: Satyanarayana Buddarapu on Jun 11, 2010 11:56 AM

[Former Member · Accepted solution]

Grt ... I was lil late .. but Stefan answerd it... and I will go with his response... and this is why I asked you to get the output... No Issues !

your biggest parts of the SYSAUX tablespace are:

SM/OTHER 136.704, SM/OPTSTAT 94.144, SM/AWR 90.944, EM 53.184 and SM/ADVISOR 14.720

The statistic history of 31 days is the default value. Even if you will reduce this value, you need to purge the space of SM/OPTSTAT manually Oracle note #454678.1)

[Former Member · Accepted solution]

Hi,

SYSAUX is less than 1GB, that is no reason for concern. the biggest ones are the ones that normally got cleaned up from time to time. From Oracle10g onward, I have SYSAUX to 2GB with a maxsize of 5GB especially since Oracle is using this tablespace as their "dumping" ground for some of its data instead of using SYSTEM.

SQL> select OCCUPANT_NAME,OCCUPANT_DESC,SCHEMA_NAME,SPACE_USAGE_KBYTES from v$sysaux_occupants;

OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAME SPACE_USAGE_KBYTES -

---

-

---

LOGMNR LogMiner SYSTEM 6016

LOGSTDBY Logical Standby SYSTEM 896

STREAMS Oracle Streams SYS 512

XDB XDB XDB 0

AO Analytical Workspace Object Table SYS 768

XSOQHIST OLAP API History Tables SYS 768

XSAMD OLAP Catalog OLAPSYS 0

SM/AWR Server Manageability - Automatic Workload Repository SYS 90432

SM/ADVISOR Server Manageability - Advisor Framework SYS 14720

SM/OPTSTAT Server Manageability - Optimizer Statistics History SYS 94144

SM/OTHER Server Manageability - Other Components SYS 136704

STATSPACK Statspack Repository PERFSTAT 0

ODM Oracle Data Mining DMSYS 0

SDO Oracle Spatial MDSYS 0

WM Workspace Manager WMSYS 0

ORDIM Oracle interMedia ORDSYS Components ORDSYS 0

ORDIM/PLUGINS Oracle interMedia ORDPLUGINS Components ORDPLUGINS 0

ORDIM/SQLMM Oracle interMedia SI_INFORMTN_SCHEMA Components SI_INFORMTN_SCHEMA 0

EM Enterprise Manager Repository SYSMAN 53184

TEXT Oracle Text CTXSYS 0

ULTRASEARCH Oracle Ultra Search WKSYS 0

ULTRASEARCH_DEMO_USER Oracle Ultra Search Demo User WK_TEST 0

EXPRESSION_FILTER Expression Filter System EXFSYS 0

EM_MONITORING_USER Enterprise Manager Monitoring User DBSNMP 0

TSM Oracle Transparent Session Migration User TSMSYS 256

JOB_SCHEDULER Unified Job Scheduler SYS 384

26 rows selected.

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where tablespace_name='SYSAUX';

SUM(BYTES)/1024/1024/1024

-

---

.440734863

[stefan_koehler]

Hello Satyanarayana,

your biggest parts of the SYSAUX tablespace are:

SM/OTHER     136.704 
SM/OPTSTAT   94.144  
SM/AWR       90.944  
EM           53.184  
SM/ADVISOR   14.720  

The statistic history of 31 days is the default value. Even if you will reduce this value, you need to purge the space of SM/OPTSTAT manually (check metalink note #454678.1) The advisor (SM/ADVISOR) should disabled in a SAP environment (check sapnote #974781). I would guess that you are using Grid or Database Control.

If you want to keep the advisors enabled and keep the statistics (which i would prefer), then everything is fine and you need to extend your tablespace. It depends on what you are using and what you want.

Regards

Stefan

[fjhernanz]

Hi,

Do you have many BW Loads ? In that case growth is expected in the SYSAUX (note 974781) You could try the cleanup option in the brconnect function. brconnect -f cleanup

Otherwise, the biggest reason for space used in SYSAUX is SM/OPTSTAT. this stores older version of optimizer statistics. In Oracle 10g, parameter retention tells you how long to restore old stats in case you encounter execution plan regression when the stats are refreshed. You can check note 588668 point 41 on this regard. You can either lower the retention date from default 31 to 14 or 7, or even completely disable it. This will not reduce the segment size but will prevent it from growing further.

Br,

Javier