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.
Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps
Tuesday, March 24, 2009
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment