Re: UNDO: 10g-style behaviour in 188.8.131.52?
Date: Sat, 4 Apr 2009 14:20:50 -0700 (PDT)
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
> 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 184.108.40.206 to 220.127.116.11 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
> but it is not mentioned anywhere in the Release Notes for 18.104.22.168
> 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? Received on Sat Apr 04 2009 - 16:20:50 CDT