Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Reusing UNEXPIRED UNDO blocks

Re: Reusing UNEXPIRED UNDO blocks

From: Daniel Fink <danielwfink_at_yahoo.com>
Date: Tue, 4 Apr 2006 08:50:02 -0700 (PDT)
Message-ID: <20060404155007.34520.qmail@web37201.mail.mud.yahoo.com>


Stephen,

Several questions come to mind.

1) Is the autoextended datafile at/near it's limit?
2) How many undo segments do you have?
3) What version of Oracle?
4) Is undo_retention guaranteed?

In terms of expired/unexpired, it is supposed to mark the block as expired after the last commit time + undo_retention > current time. However, in practice, this is usually not the case. In testing, I (and others) have seen blocks expire almost immediately after a commit and other blocks not expire even days after the last commit time + undo_retention.

If a block is not being used by a current transaction, not part of the undo segment minimum, unexpired and undo_retention is not guaranteed, a current transaction should steal it (and you can see that stealing is occurring in the query you ran).

Regards,
Daniel Fink

Stephen Barr <ascaroth969_at_yahoo.co.uk> wrote: When do oracle reuse an unexpired undo block?

We have a situation where there are four datafiles associated with the UNDO tablespace -

READONLY_at_MIDDWHP>select file_id, autoextensible   2 from dba_data_files
  3 where tablespace_name = 'UNDOTBS1'
  4 /

   FILE_ID AUT
---------- ---

       164 NO
       163 NO
       162 NO
         2 YES

You can see that one of the datafiles is marked as autoextensible - an oversight by the DBA's.

However, we're seeing transactions fail with ORA-01562: failed to extend rollback segment number 17.

The strange thing is that we checked just before this transaction failed and there was tons of room in the tablespace - why would this occur?

The entry in v$undostat for this time period looks like this -

BEGIN_TIME                    : 04-apr-2006 10:35:03

END_TIME                      : 04-apr-2006 10:45:03

UNDOTSN                       : 1

UNDOBLKS                      : 47115

TXNCOUNT                      : 272225

MAXQUERYLEN                   : 93649

MAXQUERYID                    : frma9q6tqbuwd

MAXCONCURRENCY                : 11

UNXPSTEALCNT                  : 8

UNXPBLKRELCNT                 : 516

UNXPBLKREUCNT                 : 0

EXPSTEALCNT                   : 1663

EXPBLKRELCNT                  : 279212

EXPBLKREUCNT                  : 0

SSOLDERRCNT                   : 0

NOSPACEERRCNT                 : 2

ACTIVEBLKS                    : 105344

UNEXPIREDBLKS                 : 4307412

EXPIREDBLKS                   : 0

TUNED_UNDORETENTION           : 43402


The dba_undo_extents view currently looks like this -

READONLY_at_MIDDWHP>select count(*), status   2 from dba_undo_extents
  3 group by status
  4 /

  COUNT(*) STATUS
---------- ---------

       401 EXPIRED
     22014 UNEXPIRED


Any ideas?   



NEW Yahoo! Cars - sell your car and browse thousands of new and used cars online! http://uk.cars.yahoo.com/
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Apr 04 2006 - 10:50:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US