Automatic Undo Retention Tuning

Undo retention was introduced in Oracle 9i. This parameter is used to support the "flashback query" feature. This parameter, however, did not completely resolve all occurrences of the ORA-1555 "snapshot too old" error. The value of the parameter UNDO_RETENTION is specified in units of seconds. This parameter determines the lower threshold value of undo retention. The system retains undo for at least the time specified in this parameter. When you set the value of UNDO_RETENTION to 0, Oracle 10g automatically tunes undo retention to reduce the chances of "snapshot too old" errors during long-running queries.

When UNDO_RETENTION is set to 0, the minimum value of UNDO RETENTION will be 900 seconds (15 minutes). The MMON background process will calculate the length of the longest running query, MAXQUERYLEN, every 30 seconds. Using the calculated value of MAXQUERYLEN, MMON will adjust TUNED_UNDORETENTION, and UNDO RETENTION will be set to TUNED_UNDORETENTION.