Index Block Dumps: Final Demo (Come Together)

The intent of this blog piece is just to bring together the whole discussion of block dumps and how we can use block dumps to demonstrate Oracle behaviour.

First, let’s start with a fresh little demo, creating an index on a NAME column with 500 entries (note this specific demo uses an 11.2.0.1 database running on windows). The column all have a value of ‘BOWIE’ with a distinct number concatenated on the end.

 

SQL> create table bowie (id number, name varchar2(20));

Table created.

SQL> create index bowie_name_i on bowie(name);

Index created.

SQL> insert into bowie select rownum, 'BOWIE' || rownum from dual connect by level <= 500;

500 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', cascade=> true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

We notice this index is a blevel 1 index, consisting of a root block pointing down to just 2 leaf blocks:

SQL> select blevel, leaf_blocks from dba_indexes where index_name = 'BOWIE_NAME_I';

    BLEVEL LEAF_BLOCKS
---------- -----------
         1           2

 

I’m just going to show selected portions from the different block dumps, focusing on the dump from disk section (hence flush the buffer cache before each block dump):

SQL> alter system flush buffer_cache;

System altered.

SQL> select header_file, header_block from dba_segments where segment_name='BOWIE_NAME_I';

HEADER_FILE HEADER_BLOCK
----------- ------------
          6          168

  

The specific block of interest will be the second (or last) index leaf block, so I just add 3 to the header block value (note index is in a non ASSM LMT):

SQL> alter system dump datafile 6 block 171;

System altered.

 
Block dump from disk:
buffer tsn: 6 rdba: 0x018000ab (6/171)
scn: 0x0000.003bb7e9 seq: 0x01 flg: 0x04 tail: 0xb7e90601
frmt: 0x02 chkval: 0x285e type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Block header dump:  0x018000ab
 Object id on Block? Y
 seg/obj: 0x12ad3  csc: 0x00.3bb7e9  itc: 2  flg: O  typ: 2 – INDEX
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.013.00000d48  0x00c01082.0263.02  CB–    0  scn 0x0000.003bb7e1
0x02   0x0003.017.00000d3e  0x00c049a3.021a.03  C—    0  scn 0x0000.003bb7e3
Leaf block dump
===============
header address 211493468=0xc9b225c
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 300
kdxcofbo 636=0x27c
kdxcofeo 2722=0xaa2
kdxcoavs 2086
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 25165994=0x18000aa
kdxledsz 0
kdxlebksz 8036
row#0[4414] flag: ——, lock: 0, len=17
col 0; len 7; (7):  42 4f 57 49 45 32 38
col 1; len 6; (6):  01 80 00 a1 00 1b
row#1[4431] flag: ——, lock: 0, len=18
col 0; len 8; (8):  42 4f 57 49 45 32 38 30
col 1; len 6; (6):  01 80 00 a1 01 17
row#2[4449] flag: ——, lock: 0, len=18
col 0; len 8; (8):  42 4f 57 49 45 32 38 31
col 1; len 6; (6):  01 80 00 a1 01 18

…

We currently have 2 ITL entries in the index leaf block, the first entry used by Oracle to deal with the leaf block split required when loading the data, the second entry for the actual transaction loading the table/index. The kdxcronro count is 300 meaning we currently have 300 index entries in this block. Note the kdxlenxt value is 0 meaning there is no next pointer, ensuring we are indeed looking at the second (or last) index leaf block within the index structure. We’re now going to add a couple of new index entries that will have greater values than all our BOWIEs guaranteeing they’ll be inserted into this leaf block. We’re going to do this by running a couple of separate concurrent transactions running in different sessions:

In one session:

SQL> insert into bowie values (501, 'MAJOR TOM');

1 row created.

In another session:

SQL> insert into bowie values (502, 'ZIGGY STARDUST');

1 row created.

SQL> commit;

Commit complete.

 
Back in the first session:

SQL> commit;

Commit complete.

 
So there were 2 concurrent transactions inserting index entries, with the transaction inserting the value “MAJOR TOM” committing last. Looking at a dump of the index block now:

     
Block dump from disk:
buffer tsn: 6 rdba: 0x018000ab (6/171)
scn: 0x0000.003bb95e seq: 0x01 flg: 0x06 tail: 0xb95e0601
frmt: 0x02 chkval: 0x1f40 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.013.00000d48  0x00c01082.0263.02  CB–    0  scn 0x0000.003bb7e1
0x02   0x0004.016.00000d65  0x00c00e88.029f.03  –U-    1  fsc 0x0000.003bb95e
0x03   0x0007.00a.00000d5c  0x00c02578.0261.02  –U-    1  fsc 0x0000.003bb95a
Leaf block dump
===============
header address 211493492=0xc9b2274
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 302
kdxcofbo 640=0x280
kdxcofeo 2655=0xa5f
kdxcoavs 2015
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 25165994=0x18000aa
kdxledsz 0
kdxlebksz 8012
row#0[4390] flag: ——, lock: 0, len=17
col 0; len 7; (7):  42 4f 57 49 45 32 38
col 1; len 6; (6):  01 80 00 a1 00 1b
row#1[4407] flag: ——, lock: 0, len=18
col 0; len 8; (8):  42 4f 57 49 45 32 38 30
col 1; len 6; (6):  01 80 00 a1 01 17
row#2[4425] flag: ——, lock: 0, len=18
col 0; len 8; (8):  42 4f 57 49 45 32 38 31

…
row#300[2679] flag: ——, lock: 2, len=19
col 0; len 9; (9):  4d 41 4a 4f 52 20 54 4f 4d
col 1; len 6; (6):  01 80 00 a2 00 55
row#301[2655] flag: ——, lock: 3, len=24
col 0; len 14; (14):  5a 49 47 47 59 20 53 54 41 52 44 55 53 54
col 1; len 6; (6):  01 80 00 a2 00 56
—– end of leaf block dump —–

     
We notice we now have an additional ITL entry. The first entry is reserved for Oracle service operations (such as block splits). The second entry was therefore grabbed by the first transaction (which inserted “MAJOR TOM”) while a new third ITL entry had to be added to accommodate the second concurrent transaction. At the bottom of the block we can see the 2 new index entries, one currently marked as locked by the transaction in ITL 2 and the other entry containing “ZIGGY STARDUST” locked by the second transaction in ITL 3. These lock bytes (which are no longer required as the transactions have now completed) will be subsequently cleaned out as we shall see …

As the transaction in ITL 2 was the last to commit, its corresponding Scn/fsc (0x0000.003bb95e) is the last transaction to have changed the block and hence is also stored in the block header (scn: 0x0000.003bb95e).

Let’s now add another index entry:

SQL> insert into bowie values (503, 'THIN WHITE DUKE');

1 row created.

SQL> commit;

Commit complete.

Block dump from disk:
buffer tsn: 6 rdba: 0x018000ab (6/171)
scn: 0x0000.003c327c seq: 0x02 flg: 0x06 tail: 0x327c0602
frmt: 0x02 chkval: 0xb367 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

Block header dump:  0x018000ab
 Object id on Block? Y
 seg/obj: 0x12ad3  csc: 0x00.3c327b  itc: 3  flg: O  typ: 2 – INDEX
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.013.00000d48  0x00c01082.0263.02  CB–    0  scn 0x0000.003bb7e1
0x02   0x0004.016.00000d65  0x00c00e88.029f.03  C—    0  scn 0x0000.003bb95e
0x03   0x0003.010.00000d6c  0x00c015a9.0221.08  –U-    1  fsc 0x0000.003c327c
Leaf block dump
===============
header address 211493492=0xc9b2274
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 303
kdxcofbo 642=0x282
kdxcofeo 2630=0xa46
kdxcoavs 1988
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 25165994=0x18000aa
kdxledsz 0
kdxlebksz 8012
row#0[4390] flag: ——, lock: 0, len=17
col 0; len 7; (7):  42 4f 57 49 45 32 38
col 1; len 6; (6):  01 80 00 a1 00 1b
row#1[4407] flag: ——, lock: 0, len=18
col 0; len 8; (8):  42 4f 57 49 45 32 38 30
col 1; len 6; (6):  01 80 00 a1 01 17
row#2[4425] flag: ——, lock: 0, len=18
col 0; len 8; (8):  42 4f 57 49 45 32 38 31
col 1; len 6; (6):  01 80 00 a1 01 18

…
row#300[2679] flag: ——, lock: 0, len=19
col 0; len 9; (9):  4d 41 4a 4f 52 20 54 4f 4d
col 1; len 6; (6):  01 80 00 a2 00 55
row#301[2630] flag: ——, lock: 3, len=25
col 0; len 15; (15):  54 48 49 4e 20 57 48 49 54 45 20 44 55 4b 45
col 1; len 6; (6):  01 80 00 a2 00 57
row#302[2655] flag: ——, lock: 0, len=24
col 0; len 14; (14):  5a 49 47 47 59 20 53 54 41 52 44 55 53 54
col 1; len 6; (6):  01 80 00 a2 00 56
—– end of leaf block dump —–

We notice the previous lock information has now been cleaned out with only this last transaction (reusing the ITL entry of the previously oldest transaction, ITL 3) now having a lock byte set for its corresponding row (“THIN WHITE DUKE”). This transaction’s scn/fsc (0x0000.003c327c) is now the scn marking the block header.

Let’s delete a few rows now, firstly the row containing “MAJOR TOM”:

SQL> delete bowie where name = 'MAJOR TOM';

1 row deleted.

SQL> commit;

Commit complete.

 

And now all the rows that start with BOWIE as a separate transaction:

SQL> delete bowie where name like 'BOWIE%';

500 rows deleted.

SQL> commit;

Commit complete.

 
Block dump from disk:
buffer tsn: 6 rdba: 0x018000ab (6/171)
scn: 0x0000.003c3e8a seq: 0x01 flg: 0x06 tail: 0x3e8a0601
frmt: 0x02 chkval: 0x139e type: 0x06=trans data

Block header dump:  0x018000ab
 Object id on Block? Y
 seg/obj: 0x12ad3  csc: 0x00.3c3e85  itc: 3  flg: O  typ: 2 – INDEX
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.013.00000d48  0x00c01082.0263.02  CB–    0  scn 0x0000.003bb7e1
0x02   0x0005.01a.00000d73  0x00c011bf.0268.05  C-U-    0  scn 0x0000.003c3b42
0x03   0x0004.01f.00000d72  0x00c01f0a.02a1.25  –U-  300  fsc 0x171a.003c3e8a
Leaf block dump
===============
header address 211493492=0xc9b2274
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 302
kdxcofbo 640=0x280
kdxcofeo 2630=0xa46
kdxcoavs 2009
kdxlespl 0
kdxlende 300
kdxlenxt 0=0x0
kdxleprv 25165994=0x18000aa
kdxledsz 0
kdxlebksz 8012
row#0[4390] flag: —D–, lock: 3, len=17
col 0; len 7; (7):  42 4f 57 49 45 32 38
col 1; len 6; (6):  01 80 00 a1 00 1b
row#1[4407] flag: —D–, lock: 3, len=18
col 0; len 8; (8):  42 4f 57 49 45 32 38 30
col 1; len 6; (6):  01 80 00 a1 01 17
row#2[4425] flag: —D–, lock: 3, len=18

…
row#299[7995] flag: —D–, lock: 3, len=17
col 0; len 7; (7):  42 4f 57 49 45 39 39
col 1; len 6; (6):  01 80 00 a1 00 62
row#300[2630] flag: ——, lock: 0, len=25
col 0; len 15; (15):  54 48 49 4e 20 57 48 49 54 45 20 44 55 4b 45
col 1; len 6; (6):  01 80 00 a2 00 57
row#301[2655] flag: ——, lock: 0, len=24
col 0; len 14; (14):  5a 49 47 47 59 20 53 54 41 52 44 55 53 54
col 1; len 6; (6):  01 80 00 a2 00 56

  
The first transaction used the now oldest ITL slot 2. The second transaction then went on to use ITL slot 3, cleaning out the lock information of the first transaction in ITL 2. It deleted all 300 index entries within the block starting with BOWIE, marking them all as deleted with the D flag in all the index entries and with a 3 lock byte set. Note however the index entry for MAJOR TOM as deleted in the first transaction has already been physically removed from the leaf block …

Again, the transaction in ITL 3 being the last transaction now has its scn/fsc (0x171a.003c3e8a) in the block header (scn: 0x0000.003c3e8a).

Let’s add a couple new rows with 2 transactions to cycle through both ITL entries …

SQL> insert into bowie values (504, 'DAVID JONES');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into bowie values (505, 'SCREAMING LORD BYRON');

1 row created.

SQL> commit;

Commit complete.

 
Block dump from disk:
buffer tsn: 6 rdba: 0x018000ab (6/171)
scn: 0x0000.003c42b0 seq: 0x02 flg: 0x06 tail: 0x42b00602
frmt: 0x02 chkval: 0x0191 type: 0x06=trans data

Block header dump:  0x018000ab
 Object id on Block? Y
 seg/obj: 0x12ad3  csc: 0x00.3c42ae  itc: 3  flg: O  typ: 2 – INDEX
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.013.00000d48  0x00c01082.0263.02  CB–    0  scn 0x0000.003bb7e1
0x02   0x0009.001.00000d80  0x00c044f5.029a.03  C—    0  scn 0x0000.003c418d
0x03   0x0001.013.00000e05  0x00c0423b.0267.02  –U-    1  fsc 0x0000.003c42b0
Leaf block dump
===============
header address 211493492=0xc9b2274
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 2579=0xa13
kdxcoavs 7868
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 25165994=0x18000aa
kdxledsz 0
kdxlebksz 8012
row#0[2609] flag: ——, lock: 0, len=21
col 0; len 11; (11):  44 41 56 49 44 20 4a 4f 4e 45 53
col 1; len 6; (6):  01 80 00 a2 00 55
row#1[2579] flag: ——, lock: 3, len=30
col 0; len 20; (20):  53 43 52 45 41 4d 49 4e 47 20 4c 4f 52 44 20 42 59 52 4f 4e
col 1; len 6; (6):  01 80 00 a2 00 58
row#2[2630] flag: ——, lock: 0, len=25
col 0; len 15; (15):  54 48 49 4e 20 57 48 49 54 45 20 44 55 4b 45
col 1; len 6; (6):  01 80 00 a2 00 57
row#3[2655] flag: ——, lock: 0, len=24
col 0; len 14; (14):  5a 49 47 47 59 20 53 54 41 52 44 55 53 54
col 1; len 6; (6):  01 80 00 a2 00 56
—– end of leaf block dump —–

      
We now notice all the 300 BOWIE entries have now been physically cleaned out of the block as well, cleaned out as part of the block changes required for these final transactions. The leaf block now only contains these 4 index entries, as shown with a kdxconro 4.  The last transaction (inserting “SCREAMING LORD BYRON”) using ITL 3 is the only transaction with its lock byte still set and has its scn/fsc (0x0000.003c42b0) in the block header (scn: 0x0000.003c42b0).

So each concurrent transaction within the index block requires an ITL entry (and Oracle will add them as necessary providing there’s sufficient free space within the block). A transaction will not only make its necessary changes, locking just those index entries associated with the transaction but will also clean out data from previous transactions if present (including index entries marked as deleted by a previous transaction). Finally, it will generally stamp the block header with the corresponding transaction scn.

Hopefully, this highlights how block dumps can be useful to both see and demonstrated Oracle behaviour.

Next, time to look at a number of 11g index related new features …