I'll create a table with two rows and an index:
SQL> create table test as 2 select level n 3 from dual 4 connect by level <= 2; Table created SQL> create index i_test on test (n); Index createdSession 1
I'll update one row in my first session and leave the transaction open:
SQL> update test set n=3 where n=1; 1 row updatedHere is xid for this transaction:
SQL> select '0x'||to_char(XIDUSN, 'fm000x')||'.'|| 2 to_char(XIDSLOT, 'fm00x')||'.'|| 3 to_char(XIDSQN, 'fm0000000x') xid 4 from v$transaction 5 where addr=( 6 select taddr 7 from v$session 8 where sid=sys_context('userenv','sid') 9 ); XID ---------------------- 0x0004.01c.00001fd5Index Block Dump 1
Since I only have two rows in the table I will end up with a special case where my index root block will be able to hold all the data essentially playing a role of both the root block and a leaf block at the same time. This makes it easier for me to dump the relevant index block because I know there is only one index block to dump:
Block header dump: 0x0100008b Object id on Block? Y seg/obj: 0x12f46 csc: 0x00.1efcb3c itc: 2 flg: E typ: 2 - INDEX brn: 0 bdba: 0x1000088 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x02 0x0004.01c.00001fd5 0x00c011ac.09a5.0b ---- 2 fsc 0x000e.00000000We have two rows locked in the index block because the row with value=1 got deleted and a row with value=3 got inserted, as per our update. Let's notice block cleanout scn (csc) value: 0x00.1efcb3c
I'll update another row in the second session leaving the transaction open as well:
SQL> update test set n=4 where n=2; 1 row updated XID ---------------------- 0x0003.01f.00001eabIndex Block Dump 2
Here is how index block dump looks right now:
Block header dump: 0x0100008b Object id on Block? Y seg/obj: 0x12f46 csc: 0x00.1efcd0c itc: 3 flg: E typ: 2 - INDEX brn: 0 bdba: 0x1000088 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x02 0x0004.01c.00001fd5 0x00c011ac.09a5.0b ---- 2 fsc 0x000e.00000000 0x03 0x0003.01f.00001eab 0x00c00e73.0982.31 ---- 2 fsc 0x000e.00000000Notice that csc value has changed from 0x00.1efcb3c to 0x00.1efcd0c
What happened is just another variation of the theme we saw in part I -- when our second session updates the index block it notices an active transaction in the ITL list and tries to perform a cleanout. It will do the same for the table block as well but since I've shown all the relevant mechanics in the previous post I'll leave it at that.
Undo Segment Header Checks
The important consequence from all the above is that a session which tries to perform a cleanout will have to look into the other transaction(-s) undo segment header block in order to find out whether the other transaction has committed or not:
SQL> select 2 trn.xidusn, 3 rbs.file_id, 4 rbs.block_id header_block, 5 trn.ubablk undo_block, 6 '0x'||to_char(trn.XIDUSN, 'fm000x')||'.'|| 7 to_char(trn.XIDSLOT, 'fm00x')||'.'|| 8 to_char(trn.XIDSQN, 'fm0000000x') xid 9 from v$transaction trn, dba_rollback_segs rbs 10 where trn.XIDUSN=rbs.segment_id 11 order by 1; XIDUSN FILE_ID HEADER_BLOCK UNDO_BLOCK XID ---------- ---------- ------------ ---------- ---------------------- 3 3 160 3699 0x0003.01f.00001eab 4 3 176 4524 0x0004.01c.00001fd5Our first session xid was 0x0004.01c.00001fd5 so when our second session performed the update it had to look into block 176 (undo header block) to check the other transaction status and block 4524 (undo block) in order to rollback the other session changes for write consistency checks:
WAIT #140055864053216: nam='db file sequential read' ela= 341 file#=3 block#=176 blocks=1 obj#=0 tim=1392400391392719 WAIT #140055864053216: nam='db file sequential read' ela= 675 file#=3 block#=4524 blocks=1 obj#=0 tim=1392400391393679I'll continue setting the up stage for a perfect disaster with delayed block cleanout and parallel DML in the upcoming series.