Re: Reusing UNEXPIRED UNDO blocks
- From: Stephen Barr <ascaroth969@xxxxxxxxxxx>
- To: danielwfink@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 4 Apr 2006 17:01:57 +0100 (BST)
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@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.
--- Daniel Fink <danielwfink@xxxxxxxxx> wrote:
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
