|
X$BH
|
Last time, I examined the relationship between transaction entry and the data
in a block to see how a row was locked. In this issue, I will analyze the relationship
between the transaction entry and UNDO data.
Suppose a user attempts an UPDATE query to a block but the target block
has already been updated by another session. In such case, since the data version (SCN) to be
accessed is different from the data version the user wants (i.e. SCN is larger
than the required SCN.), CR block is created to ensure the consistent reads.
To create a CR block, UNDO data is required and is acquired from the transaction
entry.
I will analyze how the UNDO data is acquired from the transaction entry.
The following is the testing environment.
*************************************************************
(Testing environment)
Linux 2.4.2-2
Oracle9i EE Release 9.2.0.1.0
(Table configuration)
SQL> desc test
Name Type
--------- ------------------
ID1 NUMBER
ID2 NUMBER
TEXT VARCHAR2(2000)
*Grant INDEX (TEST_IDX) to ID1.
Table contains the following data.
ID1 ID2 TEXT
---------- ---------- --------------------
1 1 insight
2 2 insight
3 3 insight
*************************************************************
|
I will follow the steps below:
(1) Check the updated block in X$BH.
(2) Find the transaction entry (ltl) of the updated block to check the block address (Uba)
that contains the target UNDO data.
(3) Get the dump of rollback information and check UNDO entry.
First, I will update two rows and examine the rollback information.
The following is the block header of the buffer dump.
*************************************************************
Block header dump: 0x00411502
Object id on Block? Y
seg/obj: 0x82da csc: 0x00.8921cd5 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
|[Wrap]
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0007.01c.00027fa1 0x00800a8d.0998.0f C-U- 0 scn 0x0000.0892196f
0x02 0x0007.00d.00027faa 0x00800a94.0998.59 ---- 2 fsc 0x0000.00000000
^^^ [Slot number] ^^ last UNDO record number (hexadecimal)
^^^ [Rollback number] ^^^ UNDO block DBA (hexadecimal)
*************************************************************
|
Transaction entry number 0x02 above is an updated block. Uba (Undo Block Address) indicates
the last UNDO entry address.
Uba : 0x00800a8d.0998.0f
Uba above indicates DBA, sequence, and record number
of the UNDO entry. The following is the value converted to decimals.
file number: 2
block number: 2708
record number: 89
Next, I will view X$BH to check the status of the record above on database buffer.
*************************************************************
[The status of each object on database buffer]
OBJ OBJECT_NAME STATE DBARFIL DBABLK BA
---------- -------------------- ----- ---------- ---------- --------
4294967295 xcur 2 2708 550CE000
33498 TEST xcur 1 70913 551E8000
33498 TEST xcur 1 70914 551EC000
33497 TEST_IDX xcur 1 96394 551EA000
*************************************************************
|
Take a look at the first row. The object number (OBJ) indicates 4294967295.
This number actually represents the rollback segment number.
I get the buffer dump of the block (rollback segment) to see how it is structured.
[Buffer header]
..
..
[Data block]
-UNDO entry map
-UNDO entry
UNDO entry map is like an record index in the UNDO block.
I will examine the 89th record; 89 converted to hexadecimal is 59.)
*************************************************************
[58th UNDO entry (hexadecimal)]
|
*-----------------------------
* Rec #0x58 slt: 0x0d objn: 33468(0x000082bc) objd: 33498 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x00 <-[*1]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x00800a94.0998.57 ctl max scn: 0x0000.0892196f prv tx scn: 0x0000.0892197e
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x00411502 hdba: 0x00411501
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 1
ncol: 3 nnew: 1 size: 0
col 0: [ 2] c1 03 <-[ID1 = 2 (pre-updated data)]
| [59th UNDO entry (hexadecimal)]
*-----------------------------
* Rec #0x59 slt: 0x0d objn: 33468(0x000082bc) objd: 33498 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x58 <-[*2]
Undo type: Regular undo Last buffer split: No
Temp Object: No ^^[Last UNDO entry]
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800a94.0998.58
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x00411502 hdba: 0x00411501
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 64
ncol: 3 nnew: 1 size: 0
col 0: [ 2] c1 04 <- [ID1 = 2 (preupdated data)]
*************************************************************
|
I briefly summarize the record above.
*************************************************************
Rec #0x58 slt: 0x0d rci 0x00[58th UNDO entry (hexadecimal)]
|
Rec #0x59 slt: 0x0d rci 0x58[59th UNDO entry (hexadecimal)]
*************************************************************
|
I restore the UNDO entry starting from the last UNDO entry (Rec #0x59)
to the first UNDO entry (Rec #0x58). CR block stops being created
there is no rci before itself.
Actually, UNDO entry for the index is included as follows.
*************************************************************
Rec #0x5a slt: 0x0d rci 0x59[59th UNDO entry (hexadecimal)]
index undo for leaf key operations
|
Rec #0x5b slt: 0x0d rci 0x5a[59th UNDO entry (hexadecimal)]
index undo for leaf key operations
*************************************************************
|
As I apply the previous UNDO entry, I restore the block version to previous one,
just like rewinding the tape.
In Vol. 161 and 162, I analyzed that UPDATE and SELECT queries at full scan
and found out that they did not require CR block to be created; If the index
were not used, CR block of the index would not be created.
CR block is created in following cases (multiple sessions)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
SELECT UPDATE INSERT
------------------------ ------- ------- -------
Full scan - TABLE Yes Yes --
- INDEX No No --
Index scan - TABLE Yes No --
- INDEX Yes Yes --
|
Yoshihiro Uratsuji
|
|
