Re: UNDO: 10g-style behaviour in 9.2.0.8?
From: joel garry <joel-garry_at_home.com>
Date: Wed, 8 Apr 2009 11:03:24 -0700 (PDT)
Message-ID: <97b44513-4030-4c28-aaec-1fbee67a65cf_at_k41g2000yqh.googlegroups.com>
On Apr 8, 2:30 am, Hemant K Chitale <hemantkchit..._at_gmail.com> wrote:
> 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 Chitalehttp://hemantoracledba.blogspot.com
Date: Wed, 8 Apr 2009 11:03:24 -0700 (PDT)
Message-ID: <97b44513-4030-4c28-aaec-1fbee67a65cf_at_k41g2000yqh.googlegroups.com>
On Apr 8, 2:30 am, Hemant K Chitale <hemantkchit..._at_gmail.com> wrote:
> 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 Chitalehttp://hemantoracledba.blogspot.com
Thanks for that, I had forgotten. Notes 7291739.8 and 742035.1for those wondering about 10.2.0.4, I take it? I have an undo nearly as big as my db data, from previous version testing of app upgrades. I hadn't acknowledged any problems, but now I have a clue of what to look for. I'm not autoextend, though I do have some nasty DSS query problems at times - I may have become complacent because of previous research convincing me it's just the way it is if users are going to do stuff like that.
One nugget like this can make all the online time worth it.
jg
-- _at_home.com is bogus. http://www3.signonsandiego.com/stories/2009/apr/08/1n8fraud225746-24-people-indicted-mortgage-fraud-p/?uniontribReceived on Wed Apr 08 2009 - 13:03:24 CDT