Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Tuesday, March 24, 2009

TEMP tablespace grows continuously

Recently we had the problem of TEMP tablespace growing uncontrollably in a Production instance:

Following WARNING was appearing in DB alert log:

WARNING: Detected too many memory locking problems.
WARNING: Performance degradation may occur.
Tue Mar 24 23:19:49 2009
Incremental checkpoint up to RBA [0x3b2.800e.0], current log tail at RBA [0x3b2.872c.0]
Tue Mar 24 23:34:35 2009
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
Tue Mar 24 23:39:53 2009
Incremental checkpoint up to RBA [0x3b2.9185.0], current log tail at RBA [0x3b2.9511.0]
Tue Mar 24 23:59:58 2009
Incremental checkpoint up to RBA [0x3b2.a21f.0], current log tail at RBA [0x3b2.a57f.0]
Wed Mar 25 00:20:02 2009
Incremental checkpoint up to RBA [0x3b2.c6fc.0], current log tail at RBA [0x3b2.ce49.0]

This is very similar to the issue mentioned in Metalink Note 357765.1

Cause is Incorrect statistics on FIXED objects

Bad execution plan needed much sort-space.
Multiple waitevents on 'direct path write temp'.

Fix: As per Metalink Note 357765.1

SQL> exec dbms_stats.gather_fixed_objects_stats;

You need to run the above command, anytime you change init.ora parameters.

No comments: