In an earlier blog entry, I mentioned that we’d run into pretty severe undo segment corruption problems using SMU on a high-throughput database. Whilst looking for something else on Metalink, I noticed Note 301432.1 which says
Symptoms
Severe database performance slowdown.
(Text snipped out here)
Cause
Large numbers of Undo Segment onlines are being performed.Undo Segment online and offline operations require Serialization on US enqueue.In transactional databases where incoming workload varies with time it is common for idle Undo Segments to be offlined (or even dropped before 9.2.0.4) by SMON after a while, only to be onlined once again during the next “wave” of incoming transactions.This repetitive offlining and onlining of Undo Segments causes performance problems due to the US enqueue operations needed.
Fix
Prevent SMON from offlining idle Undo Segments using event 10511.Set the following parameter in init.ora/spfile
event=”10511 trace name context forever, level 2″
Then restart the database.After the first onlinining of an Undo Segment, it will never be offlined and re-onlined.So we will only get the “US” serialization once (on the first online after a db restart.)
Although this note discusses the possible performance problems associated with offlining of unused segments, rather than any corruption concerns, it suggests a similar strategy of using event 10511 to prevent this activity. It makes me wonder if Oracle might consider changing the default behaviour?