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

From: Hemant K Chitale <hemantkchitale_at_gmail.com>
Date: Wed, 8 Apr 2009 02:30:13 -0700 (PDT)
Message-ID: <5e7ad83e-61a2-4d9d-849b-b29235fc7706_at_c9g2000yqm.googlegroups.com>

Yes, there is one significant difference (other than Retention Guarantee)

It is called "Automatic Undo Retention" . Automatic Undo Management behaviour differs depending on whether the datafiles in the Undo tablespace are set to AutoExtend ON or AutoExtend OFF. In the AutoExtend ON case, the value you set for "undo_retention" is the floor value but Oracle automatically ajdusts the actual undo_retention it uses on the basis of query durations -- this you will see in MAXQUERYLEN and and TUNED_UNDORETENTION in V$UNDOSTAT. In the AutoExtend OFF case, Oracle will just grow undo usage till it uses the whole of the Undo Tablespace datafile even if the datafile size is excesively larger than required for either undo_retention or what would be the maxquerylen.

This behaviour is controlled by the parameter "_undo_autotune" which defaults to TRUE.

IF you search for this, you will find a number of MetaLink Notes and bugs and suggeestions to set "_undo_autotune" to FALSE in pre 10.2.0.4 (even in 10.2.0.4 !)

See MetaLink Notes 461480.1 and 420525.1

Hemant K Chitale
http://hemantoracledba.blogspot.com Received on Wed Apr 08 2009 - 04:30:13 CDT