Re: UNDO: 10g-style behaviour in

From: Jonathan Lewis <>
Date: Mon, 13 Apr 2009 09:16:56 +0100
Message-ID: <>

"Randolf Geist" <> wrote in message On Apr 11, 3:52 pm, "Jonathan Lewis" <>


it's documented behaviour in 10g automatic undo tuning that if the available space in the UNDO tablespace is restricted (autoextend off), the UNDO_RETENTION parameter is simply ignored and tuned to whatever is possible given the space available and the current workload, so it can well go below the defined UNDO_RETENTION value.

See e.g. here:


Oracle related stuff blog:

SQLTools++ for Oracle (Open source Oracle GUI for Windows):


Thanks for supplying a couple of references. My comment was specifically in response to this point, though:

    "In the AutoExtend ON case, the value you set for "undo_retention" is

     the floor value but Oracle automatically adjusts the actual
     undo_retention it uses on the basis of query durations"

i.e. the case when AutoExtend is ON - and my point was that I had seen the tuned_undoretention value in v$undostat drop BELOW the setting for parameter undo_retention with autoextend ON.

The two references you listed say:

For an AUTOEXTEND undo tablespace, the database tunes the undo retention period
to be slightly longer than the longest-running query, if space allows. In addition,
when there is adequate free space, the tuned retention period does not go below
the value of the UNDO_RETENTION initialization parameter.


For an undo tablespace with the AUTOEXTEND option enabled, the database attempts
to honor the minimum retention period specified by UNDO_RETENTION. When space is
low, instead of overwriting unexpired undo information, the tablespace auto-extends.
If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the
maximum size is reached, the database may begin to overwrite unexpired undo information.

Note that neither paragraph states that the UNDO_RETENTION is a (hard) floor. And
in my case there was a LOT of available space that should have been available to processes
that needed it - but the tuned_undoretention dropped below the UNDO_RETENTION,
and some processes failed with ORA-01555. The problem related (I think) to the fact that
all the space that was usable was tied up (as EXPIRED but stealable) in one large undo

I can't decide whether the problem is a documentation ambiguity, or an implementation bug.

In passing - if the files in the tablespace are autoextensible, my experience suggests that
tuned_undoretention is simply set to the value of UNDO_RETENTION.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Mon Apr 13 2009 - 03:16:56 CDT

Original text of this message