Re: UNDO: 10g-style behaviour in 9.2.0.8?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 13 Apr 2009 09:16:56 +0100
Message-ID: <_JGdnR-JnqFpaX_UnZ2dnUVZ8sudnZ2d_at_bt.com>

"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>

Jonathan,

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:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/logical.htm#sthref454

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#ADMIN10180

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows): http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/

Randolf,

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.

---

and

---

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
segment.

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.html

Received on Mon Apr 13 2009 - 03:16:56 CDT