This event indicates the session is currently waiting on the Undo Segments and is often related to using system managed undo with an auto tuned undo retention period. In 10g, Oracle added the _undo_autotune parameter which allows the database instance to extend and override “UNDO_RETENTION” settings. This is especically true when the datafiles for the Undo Tablespace are set to automatically extend. However, there are limitations and issues with this feature discussed in more detail in the Solutions section below.
Here are more details about Automatic Tuning of Undo.
Solutions
Review Bug 7291739 which affected Oracle 10.2.0.4 and is fixed in 10.2.0.4.4 / 10.2.0.5 and 11.2. It is caused by runaway queries inflating the undo retention time. This fix allows users to specify a high threshold for undo retention via the “_highthreshold_undoretention” parameter. The tuned value of undo retention will then not exceed the set value.
Ensure the UNDO tablespace has enough space and/or can autoextend to accomodate the undo settings.
If all else fails or you cannot install the latest patchset, disable automatic undo tuning by setting “_undo_autotune” = false.
Leave a Reply