Re: UNDO: 10g-style behaviour in 22.214.171.124?
Date: Mon, 13 Apr 2009 09:16:56 +0100
"Randolf Geist" <mahrah_at_web.de> wrote in message news:2136c9b5-fe13-4a32-b27c-8864f2ce0ea6_at_z19g2000vbz.googlegroups.com... On Apr 11, 3:52 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
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:
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)
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.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Mon Apr 13 2009 - 03:16:56 CDT