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

From: ddf <oratune_at_msn.com>
Date: Mon, 6 Apr 2009 08:26:03 -0700 (PDT)
Message-ID: <574d30e7-d8f7-45f1-b7db-f0df7ac8897d_at_s19g2000vbp.googlegroups.com>



On Apr 4, 4:20 pm, johnbhur..._at_sbcglobal.net wrote:
> On Apr 3, 10:29 pm, ca111..._at_gmail.com wrote:
>
>
>
>
>
> > Hi All,
>
> > It is my understanding that UNDO works differently in 9.2 and 10.2.
> > Size of undo datafiles is fixed, AUTOEXTEND is OFF.
>
> > 9.2: Oracle will maintain undo blocks according to UNDO_RETENTION.
> > If UNDO tablespace is large and UNDO_RETENTION * undo bloc generation
> > is low then the tablespace will be almost empty.
> > If UNDO tablespace is small and UNDO_RETENTION * undo block generation
> > exceeds tablespace size then UNDO tablespace will run out of space.
> > This will cause transactions to fail with ORA-30036 error, these
> > errors will be displayed in alert.log. I observed this behaviour in
> > 9.2.0.7
> > many times.
>
> > 10.2 Oracle will ignore UNDO_RETENTION and attempt to keep undo blocks
> > as long as possible without running out of space. This means that if
> > more undo blocks
> > get generated the retention period will be shorter. UNDO tablespace is
> > always 95 - 98% full. Again, I observed this behaviour in 10.2.0.3 and
> > 10.2.0.4 databases.
> > This is consistent with what Admin Guide says.
>
> > However after applying Patch Set 9.2.0.8 to 9.2.0.7 database we are
> > observing 10.2-style
> > behaviour. In this database UNDO_RETENTION is set to  200,000 seconds
> > (almost three days) as this is very large database and some extracts
> > take very long time to run.
> > UNDO tablespace is 30 GB, block size is 8K. There are some spikes in
> > undo generation,
> > and after such spikes we observe queries failing with ORA-1555 after
> > 30 - 40 min.
> > There are no 30036 errors in alert.log. Tablespace is 98-99% full.
>
> > I wouldn't be surprised if Oracle "backported" 10.2-style behaviour in
> > 9.2.0.8,
> > but it is not mentioned anywhere in the Release Notes for 9.2.0.8
> > Patch Set.
>
> I would not be especially surprised either ... can you provide some
> sample sql ( aka reproducible test case ) and we can run it in various
> environments on some of our test systems?
>
> Have you volleyed it up to oracle support?- Hide quoted text -
>
> - Show quoted text -

Why? If you read here

http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/undo.htm#11028

you'll find the quote I posted regarding UNDO_RETENTION behaviour in 9.2.0.

David Fitzjarrell Received on Mon Apr 06 2009 - 10:26:03 CDT

Original text of this message