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

Wednesday, October 1, 2008

undo_retention in 10g and 11g

undo_retention definition remains the same in 10g and 11g:

UNDO_RETENTION

PropertyDescription
Parameter typeInteger
Default value900
ModifiableALTER SYSTEM
Range of values0 to 232 - 1 (max value represented by 32 bits)
Real Application ClustersOracle recommends that multiple instances have the same value.

UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

The setting of this parameter should account for any flashback requirements of the system. Automatic tuning of undo retention is not supported for LOBs. The RETENTION value for LOB columns is set to the value of theUNDO_RETENTION parameter.

The UNDO_RETENTION parameter can only be honored if the current undo tablespace has enough space. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space. This action can potentially cause some queries to fail with a "snapshot too old" message.

The amount of time for which undo is retained for the Oracle Database for the current undo tablespace can be obtained by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT dynamic performance view.

There is a bug in Oracle 10g which causes space problems because of automatic tuning of undo_retention.  This is described in Metalink note 420525.1.  You need to apply DB patch  5387030 to resolve this issue.

No comments: