jump to navigation

Index Block Dumps: Final Demo (Come Together) November 4, 2010

Posted by Richard Foote in Block Dumps, Leaf Blocks, Oracle Indexes.
trackback

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: 0×0000.003bb7e9 seq: 0×01 flg: 0×04 tail: 0xb7e90601
frmt: 0×02 chkval: 0x285e type: 0×06=trans data
Hex dump of block: st=0, typ_found=1
Block header dump:  0x018000ab
 Object id on Block? Y
 seg/obj: 0x12ad3  csc: 0×00.3bb7e9  itc: 2  flg: O  typ: 2 – INDEX
     fsl: 0  fnx: 0×0 ver: 0×01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0005.013.00000d48  0x00c01082.0263.02  CB–    0  scn 0×0000.003bb7e1
0×02   0×0003.017.00000d3e  0x00c049a3.021a.03  C—    0  scn 0×0000.003bb7e3
Leaf block dump
===============
header address 211493468=0xc9b225c
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: 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=0×0
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: 0×0000.003bb95e seq: 0×01 flg: 0×06 tail: 0xb95e0601
frmt: 0×02 chkval: 0x1f40 type: 0×06=trans data
Hex dump of block: st=0, typ_found=1

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0005.013.00000d48  0x00c01082.0263.02  CB–    0  scn 0×0000.003bb7e1
0×02   0×0004.016.00000d65  0x00c00e88.029f.03  –U-    1  fsc 0×0000.003bb95e
0×03   0×0007.00a.00000d5c  0x00c02578.0261.02  –U-    1  fsc 0×0000.003bb95a
Leaf block dump
===============
header address 211493492=0xc9b2274
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 302
kdxcofbo 640=0×280
kdxcofeo 2655=0xa5f
kdxcoavs 2015
kdxlespl 0
kdxlende 0
kdxlenxt 0=0×0
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 (0×0000.003bb95e) is the last transaction to have changed the block and hence is also stored in the block header (scn: 0×0000.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: 0×0000.003c327c seq: 0×02 flg: 0×06 tail: 0x327c0602
frmt: 0×02 chkval: 0xb367 type: 0×06=trans data
Hex dump of block: st=0, typ_found=1

Block header dump:  0x018000ab
 Object id on Block? Y
 seg/obj: 0x12ad3  csc: 0×00.3c327b  itc: 3  flg: O  typ: 2 – INDEX
     fsl: 0  fnx: 0×0 ver: 0×01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0005.013.00000d48  0x00c01082.0263.02  CB–    0  scn 0×0000.003bb7e1
0×02   0×0004.016.00000d65  0x00c00e88.029f.03  C—    0  scn 0×0000.003bb95e
0×03   0×0003.010.00000d6c  0x00c015a9.0221.08  –U-    1  fsc 0×0000.003c327c
Leaf block dump
===============
header address 211493492=0xc9b2274
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 303
kdxcofbo 642=0×282
kdxcofeo 2630=0xa46
kdxcoavs 1988
kdxlespl 0
kdxlende 0
kdxlenxt 0=0×0
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 (0×0000.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: 0×0000.003c3e8a seq: 0×01 flg: 0×06 tail: 0x3e8a0601
frmt: 0×02 chkval: 0x139e type: 0×06=trans data

Block header dump:  0x018000ab
 Object id on Block? Y
 seg/obj: 0x12ad3  csc: 0×00.3c3e85  itc: 3  flg: O  typ: 2 – INDEX
     fsl: 0  fnx: 0×0 ver: 0×01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0005.013.00000d48  0x00c01082.0263.02  CB–    0  scn 0×0000.003bb7e1
0×02   0×0005.01a.00000d73  0x00c011bf.0268.05  C-U-    0  scn 0×0000.003c3b42
0×03   0×0004.01f.00000d72  0x00c01f0a.02a1.25  –U-  300  fsc 0x171a.003c3e8a
Leaf block dump
===============
header address 211493492=0xc9b2274
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 302
kdxcofbo 640=0×280
kdxcofeo 2630=0xa46
kdxcoavs 2009
kdxlespl 0
kdxlende 300
kdxlenxt 0=0×0
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: 0×0000.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: 0×0000.003c42b0 seq: 0×02 flg: 0×06 tail: 0x42b00602
frmt: 0×02 chkval: 0×0191 type: 0×06=trans data

Block header dump:  0x018000ab
 Object id on Block? Y
 seg/obj: 0x12ad3  csc: 0×00.3c42ae  itc: 3  flg: O  typ: 2 – INDEX
     fsl: 0  fnx: 0×0 ver: 0×01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0005.013.00000d48  0x00c01082.0263.02  CB–    0  scn 0×0000.003bb7e1
0×02   0×0009.001.00000d80  0x00c044f5.029a.03  C—    0  scn 0×0000.003c418d
0×03   0×0001.013.00000e05  0x00c0423b.0267.02  –U-    1  fsc 0×0000.003c42b0
Leaf block dump
===============
header address 211493492=0xc9b2274
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: 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=0×0
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 (0×0000.003c42b0) in the block header (scn: 0×0000.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 …

About these ads

Comments»

1. Tweets that mention Index Block Dumps: Final Demo (Come Together) « Richard Foote’s Oracle Blog -- Topsy.com - November 5, 2010

[...] This post was mentioned on Twitter by Surachart Opun, Richard Foote. Richard Foote said: Blog Post: Index Block Dump-Final Demo: http://tiny.cc/qboum . All dumped out, time for something entirely new. [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,712 other followers

%d bloggers like this: