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: Stephen Barr <ascaroth969_at_yahoo.co.uk>
Date: Tue, 4 Apr 2006 17:01:57 +0100 (BST)
Message-ID: <20060404160157.51489.qmail@web86901.mail.ukl.yahoo.com>


Hi Daniel,

   Answers below.
   My theory based on how I think it all works - we have a high undo_retention (9 hours) because we are performing a migration.

   The unexpired blocks look normal considering our undo_retention. However, because one of the files was marked as autoextensible, then instead of steal one of the other unexpired blocks it just tried to extend the datafile and failed when it couldn't.

   Also, would this explain why we are seeing some statements fail quite quickly even though the undo usage from v$transaction was only hovering ~6GB (we have a 67GB UNDO).

   Does the above scenario sound realistic?

>> 1) Is the autoextended datafile at/near it's limit?

Yes - the filesystem that the datafile sits on is full - this file cannot extend.

>> 2) How many undo segments do you have?

I assume I just get this info from dba_undo_extents?

READONLY_at_MIDDWHP>select count(distinct segment_name)   2 from dba_undo_extents
  3 /

COUNT(DISTINCTSEGMENT_NAME)


                         58

>> 3) What version of Oracle?
10.2.0.1.0

>> 4) Is undo_retention guaranteed?
No.

> 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
                



How much free photo storage do you get? Store your holiday snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.com
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 04 2006 - 11:01:57 CDT

Original text of this message

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