Reusing UNEXPIRED UNDO blocks 2006-04-04 - By Powell, Mark D
If would appear from these responses to Daniel that the datafile in
question should be altered to non-extendable.
HTH -- Mark D Powell --
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Stephen Barr
Sent: Tuesday, April 04, 2006 12:02 PM
To: danielwfink@(protected); oracle-l@(protected)
Subject: Re: Reusing UNEXPIRED UNDO blocks
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@(protected)>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@(protected)> 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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
|
|
