|
ROLLBACK SEGMENT
|
Hello everyone. In this issue, I will examine the cause of ORA-01554 we encountered
last time.
ORA-01554: Out of transaction slots in transaction tables
Cause: Too many concurrent transaction
I use Oracle8.1.6 for Windows-NT and set DB_BLOCK_SIZE to 2K.
What is transaction table?
Transaction table exists in a segment header of rollback segment, and contains transaction
information. For example, when UPDATE is performed, information before update is stored
in rollback segment. At this moment, transaction table is updated before anything else.
Now, I analyze the transaction table.
Last time, I let the rollback segment rbs_find50 handle twenty-one transactions.
Let's take a look at block dump of that moment.
As the transaction table exists in segment header, we can identify the location by
using HEADER_FILE and HEADER_BLOCK of DBA_SEGMENTS.
|
SQL> col SEGMENT_NAME format a15
SQL> SELECT SEGMENT_NAME, HEADER_FILE, HEADER_BLOCK FROM DBA_SEGMENTS
WHERE SEGMENT_NAME='RB_FIND50';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
------------- ------------- ------------
RB_FIND50 16 537
|
Based on this result, I get the block dump while concurrent
transactions occur.
(Block dump is output to a location specified by initialization parameter user_dump_dest.)
SQL> ALTER SYSTEM DUMP DATAFILE 16 BLOCK 537;
Following is an abstract of the transaction table (TRN TBL)
|
|
TRN TBL
index state cflags wrap# uel scn dba parent-xid nub
------------------------------------------------------------------------------------------------
0x00 10 0xc0 0x0023 0x0000 0x0000.00ebcf59 0x0400021e 0x0000.000.00000000 0x00000001
0x01 10 0xc0 0x0023 0x0000 0x0000.00ebcf5a 0x0400021e 0x0000.000.00000000 0x00000001
0x02 10 0xc0 0x0023 0x0000 0x0000.00ebcf5a 0x0400021d 0x0000.000.00000000 0x00000001
0x03 10 0xc0 0x0023 0x0000 0x0000.00ebcf5a 0x0400021c 0x0000.000.00000000 0x00000001
0x04 10 0xc0 0x0023 0x0000 0x0000.00ebcf5a 0x0400021f 0x0000.000.00000000 0x00000001
0x05 10 0xc0 0x0023 0x0000 0x0000.00ebcf5a 0x04000220 0x0000.000.00000000 0x00000001
0x06 10 0xc0 0x0023 0x0000 0x0000.00ebcf5a 0x04000221 0x0000.000.00000000 0x00000001
0x07 10 0xc0 0x0023 0x0000 0x0000.00ebcf5a 0x04000223 0x0000.000.00000000 0x00000001
0x08 10 0xc0 0x0023 0x0000 0x0000.00ebcf5a 0x04000222 0x0000.000.00000000 0x00000001
0x09 10 0xc0 0x0023 0x0000 0x0000.00ebcf5d 0x04000224 0x0000.000.00000000 0x00000001
0x0a 10 0xc0 0x0022 0x0001 0x0000.00ebcf57 0x00800229 0x0000.000.00000000 0x00000001
0x0b 10 0xc0 0x0022 0x0001 0x0000.00ebcf58 0x0080022c 0x0000.000.00000000 0x00000001
0x0c 10 0xc0 0x0022 0x0001 0x0000.00ebcf59 0x0080022d 0x0000.000.00000000 0x00000001
0x0d 10 0xc0 0x0022 0x0001 0x0000.00ebcf57 0x00800228 0x0000.000.00000000 0x00000001
0x0e 10 0xc0 0x0022 0x0001 0x0000.00ebcf57 0x0080022a 0x0000.000.00000000 0x00000001
0x0f 10 0xc0 0x0022 0x0001 0x0000.00ebcf59 0x0080022e 0x0000.000.00000000 0x00000001
0x10 10 0xc0 0x0022 0x0000 0x0000.00ebcf59 0x0400021a 0x0000.000.00000000 0x00000001
0x11 10 0xc0 0x0022 0x0001 0x0000.00ebcf57 0x0080022b 0x0000.000.00000000 0x00000001
0x12 10 0xc0 0x0022 0x0001 0x0000.00ebcf59 0x00800231 0x0000.000.00000000 0x00000001
0x13 10 0xc0 0x0022 0x0001 0x0000.00ebcf59 0x00800230 0x0000.000.00000000 0x00000001
0x14 10 0xc0 0x0022 0x0001 0x0000.00ebcf59 0x0080022f 0x0000.000.00000000 0x00000001
|
How many rows are there? You can see that there are twenty-one
rows in this transaction table. This value matches exactly the number of concurrent
transactions that one rollback segment can handle.
This is why TRANSACTIONS_PER_ROLLBACK_SEGMENT can be
specified up to 21.
Definition
index: match XIDSLOT column in V$TRANSACTION (Slot number)
state: switch 9 to 10 when transaction is active
wrap#: match XIDSQN column in V$TRANSACTION (Sequence number)
cflags: switch 0x00 to 0xco after the slot is used. Details unknown
uel: Details unknown
scn: System Change Number
dba: indicate dba (datablock address) of undo block
parent-xid: parent transaction number used for parallel DML. When parallel DML
is not used, 0x0000.000.00000000 is stored.
nub: match USED_UBLK column of V$TRANSACTION (Number of used rollback segments)
Lastly, I execute the SQL statement to prove that dba is truly dba of rollback segment.
If I convert the first column [0x0400021e] from hexadecimal dba to decimal dba, it will be
[67109406]. With this value, I execute the SQL statement, which proves that dba is truly dba of
rollback segment.
|
|
SELECT SEGMENT_NAME, SEGMENT_TYPE FROM DBA_EXTENTS
WHERE FILE_ID=DBMS_UTILITY. DATA_BLOCK_ADDRESS_FILE (67109406)
AND DBMS_UTILITY. DATA_BLOCK_ADDRESS_BLOCK (67109406) BETWEEN
BLOCK_ID AND BLOCK_ID+BLOCKS;
SEGMENT_NAME SEGMENT_TYPE
--------------- --------------
RB_FIND50 ROLLBACK
|
DATA_BLOCK_ADDRESS_FILE, and DATA_BLOCK_ADDRESS_BLOCK are functions to
get file number and block number from dba.
That's it for today. See you next week.
|
|
Ora! Ora! Oracle