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

On Apr 3, 9: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.

As long as it's possible to do so.

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

Not necessarily. By design Oracle, even in 9.2.0.x, will begin reusing unexpired UNDO blocks should the need arise, which can throw ORA-01555 errors. Unexpired UNDO blocks are those which have not yet met or exceeded the configured UNDO_RETENTION but are not being written as part of an active transaction. If no such blockx exist then the UNDO tablespace, if not set to autoextend, will throw errors from running 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.
>

But not because Oracle wouldn't use unexpired UNDO blocks. You see these errors because there are no unexpired UNDO blocks which are not being written.

> 10.2 Oracle will ignore UNDO_RETENTION and attempt to keep undo blocks
> as long as possible without running out of space.

So will 9.2.

> This means that if
> more undo blocks
> get generated the retention period will be shorter.

No, it means that Oracle is reusing unexpired UNDO blocks, the same behaviour listed in the 9.2.0 Administrator's Guide. It only APPEARS to adjust the UNDO_RETENTION.

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

You need to read again, I think, since the 9.2 Administrator's Guide says this about the usage of retained UNDO blocks:

"The effect of the UNDO_RETENTION parameter is immediate, but it can only be honored if the current undo tablespace has enough space for the active transactions. If an active transaction requires undo space and the undo tablespace does not have available space, the system starts reusing unexpired undo space. Such action can potentially cause some queries to fail with the "snapshot too old" error."

> However after applying Patch Set 9.2.0.8 to 9.2.0.7 database we are
> observing 10.2-style
> behaviour.

You mean you're seeing Oracle reusing unexpired UNDO blocks just like it says in the 9.2.0 Administrator's Guide?

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

Yes, because unexpired UNDO blocks are being reused for other transactions.

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

I would, as this behaviour was noted in the 9.2.0 Administrator's Guide when 9.2.0 was the current release. See quote above.

> but it is not mentioned anywhere in the Release Notes for 9.2.0.8
> Patch Set.

That's because there is nothing to mention.

David Fitzjarrell Received on Mon Apr 06 2009 - 10:22:35 CDT