Reusing UNEXPIRED UNDO blocks 2006-04-04 - By Daniel Fink
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@(protected)> wrote: When do oracle reuse an unexpired
undo block?
We have a situation where there are four datafiles
associated with the UNDO tablespace -
READONLY@(protected)>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 (See ORA-01562.ora-code.com): 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@(protected)>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
Stephen,<br><br>Several questions come to mind.<br>1) Is the autoextended
datafile at/near it's limit?<br>2) How many undo segments do you have?<br>3)
What version of Oracle?<br>4) Is undo_retention guaranteed?<br><br>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.<br><br>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). <br><br>Regards,<br>Daniel
Fink<br><br><b><i>Stephen Barr <ascaroth969@(protected)></i></b> wrote:
<blockquote class="replbq" style="border-left:
2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"> When do
oracle reuse an unexpired undo block?<br><br>We have a situation where there
are four datafiles<br>associated with the UNDO tablespace -<br><br>READONLY
@(protected)>select file_id, autoextensible<br> 2 from dba_data_files<br> 3
where tablespace_name = 'UNDOTBS1'<br> 4 /<br><br> FILE_ID AUT<br>-- ------
- ---<br> 164 NO<br> 163 NO<br> 162 NO<br> 2 YES<br>
<br>You can see that one of the datafiles is marked as<br>autoextensible - an
oversight by the DBA's.<br><br>However, we're seeing transactions fail with<br
>ORA-01562 (See ORA-01562.ora-code.com): failed to extend rollback segment number<br>17.<br><br>The strange
thing is that we checked just before this<br>transaction failed and there was
tons of room in the<br>tablespace - why would this occur?<br><br>The entry in v
$undostat for this time period looks<br>like this -<br><br>BEGIN_TIME
: 04-apr-2006 10:35:03<br><br>END_TIME
: 04-apr-2006 10:45:03<br><br>UNDOTSN :
1<br><br>UNDOBLKS : 47115<br><br>TXNCOUNT
: 272225<br><br>MAXQUERYLEN : 93649<br><br>MAXQUERYID
: frma9q6tqbuwd<br><br>MAXCONCURRENCY : 11<br><br
>UNXPSTEALCNT : 8<br><br>UNXPBLKRELCNT : 516<br
><br>UNXPBLKREUCNT : 0<br><br>EXPSTEALCNT :
1663<br><br>EXPBLKRELCNT : 279212<br><br>EXPBLKREUCNT
: 0<br><br>SSOLDERRCNT : 0<br><br>NOSPACEERRCNT
: 2<br><br>ACTIVEBLKS : 105344<br><br
>UNEXPIREDBLKS : 4307412<br><br>EXPIREDBLKS :
0<br><br>TUNED_UNDORETENTION : 43402<br><br><br>The dba_undo_extents
view currently looks like this -<br><br>READONLY@(protected)>select count(*),
status<br> 2 from dba_undo_extents<br> 3 group
by status<br> 4 /<br><br> COUNT(*) STATUS<br>-- ---- -- -- ------<br>
401 EXPIRED<br> 22014 UNEXPIRED<br><br><br>Any ideas?<br><br><br><br><br>
<br>__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ______ <br>NEW Yahoo!
Cars - sell your car and browse thousands of new and used cars online! http:/
/uk.cars.yahoo.com/<br>--<br>http://www.freelists.org/webpage/oracle-l<br><br>
<br></blockquote><br>
|
|
