Indexes and Initrans (Blackstar)

It’s been a very tough week so to help keep my mind off rather sad events, thought I’ll finish off one of my unpublished articles.

Initrans is a physical attribute that determines the initial number of concurrent transaction entries allocated within each data block for a given table/index/cluster. Every transaction that updates a block has to acquire an Interested Transaction List (ITL) slot, in order to store the transaction id, rollback information and ultimately lock the necessary row/index entry within the block.

For a table, the inability to acquire a necessary ITL can lead to hanging transactions and the resultant poor performance. A simple demonstration.

I’ll first create and populate a table with PCTFREE 0 so that the table blocks are full with no free space:

SQL> create table ziggy (id number, code number, name varchar2(42)) pctfree 0;

Table created.

SQL> insert into ziggy select rownum, mod(rownum,100), ‘Ziggy Stardust’
from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

If we look at a partial block dump of an example block from the table (which happens to hold the first rows inserted):

Block header dump: 0x01835b37
Object id on Block? Y
seg/obj: 0x190b7 csc: 0x00.2affca5 itc: 2 flg: E typ: 1 – DATA
brn: 0 bdba: 0x1835b30 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.010.0000d52e 0x014001a4.0576.0e –U- 302 fsc 0x0000.02affcf5
0x02 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
bdba: 0x01835b37
data_block_dump,data header at 0x1fc8264
===============
tsiz: 0x1f98
hsiz: 0x26e
pbl: 0x01fc8264
76543210
flag=——–
ntab=1
nrow=302
frre=-1
fsbo=0x26e
fseo=0x283
avsp=0x15
tosp=0x15
0xe:pti[0] nrow=302 offs=0
0x12:pri[0] offs=0x718
0x14:pri[1] offs=0x730
0x16:pri[2] offs=0x748
0x18:pri[3] offs=0x760
0x1a:pri[4] offs=0x778

….

0x26a:pri[300] offs=0x6e6
0x26c:pri[301] offs=0x6ff
block_row_dump:
tab 0, row 0, @0x718
tl: 24 fb: –H-FL– lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [ 2] c1 02
col 2: [14] 5a 69 67 67 79 20 53 74 61 72 64 75 73 74
tab 0, row 1, @0x730
tl: 24 fb: –H-FL– lb: 0x1 cc: 3
col 0: [ 2] c1 03
col 1: [ 2] c1 03
col 2: [14] 5a 69 67 67 79 20 53 74 61 72 64 75 73 74
tab 0, row 2, @0x748
tl: 24 fb: –H-FL– lb: 0x1 cc: 3
col 0: [ 2] c1 04
col 1: [ 2] c1 04
col 2: [14] 5a 69 67 67 79 20 53 74 61 72 64 75 73 74
tab 0, row 3, @0x760
tl: 24 fb: –H-FL– lb: 0x1 cc: 3
col 0: [ 2] c1 05
col 1: [ 2] c1 05
col 2: [14] 5a 69 67 67 79 20 53 74 61 72 64 75 73 74
tab 0, row 4, @0x778
tl: 24 fb: –H-FL– lb: 0x1 cc: 3
col 0: [ 2] c1 06
col 1: [ 2] c1 06
col 2: [14] 5a 69 67 67 79 20 53 74 61 72 64 75 73 74

….

The key aspect to note here is that the table is allocated two ITL entries by default. Therefore, two concurrent transactions can safely update this block but as there is no free space sufficient for an additional ITL to be dynamically added, what happens if we have three concurrent transactions ?

In session one:

SQL> delete ziggy where id=1;

1 row deleted.

In session two:

SQL> delete ziggy where id=2;

1 row deleted.

But in session three:

SQL> delete ziggy where id=3;

 

It hangs and will do so until one of the other transactions complete.

So Initrans can preallocate more ITL entries (up to 255) to prevent these types of scenarios.

But what of indexes, is the story the same ? Well, no as we’ll see.

I’ll create another table, but this time give the table its default 10% of freespace but create the index with a PCTFREE 0:

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

Table created.

SQL> insert into bowie select rownum, mod(rownum,100), ‘David Bowie’
from dual connect by level <=1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create index bowie_id_i on bowie(id) pctfree 0;

Index created.

If we look at a partial block dump of the first leaf block in the index:

Block header dump: 0x018ad71c
Object id on Block? Y
seg/obj: 0x190b6 csc: 0x00.2aff9d5 itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x18ad718 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 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.02aff9d5
Leaf block dump
===============
header address 33325668=0x1fc8264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 540
kdxcofbo 1116=0x45c
kdxcofeo 1120=0x460
kdxcoavs 4
kdxlespl 0
kdxlende 0
kdxlenxt 25876253=0x18ad71d
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 80 01 57 00 00
row#1[8012] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 80 01 57 00 01
row#2[4523] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 80 01 57 00 02
row#3[4535] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 80 01 57 00 03
row#4[4547] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 80 01 57 00 04

We notice that it also has two ITL entries by default.

However, the first ITL entry is reserved exclusively for Oracle recursive operations on the index (such as changes as a result of an index block split). An index leaf block can therefore only handle one concurrent transaction by default.

Notice also that the leaf block currently contains 540 index entries.

So what happens if we have 2 concurrent transactions wanting to update the same leaf block ?

In session one:

SQL> delete bowie where id=1;

1 row deleted.

In session two:

SQL> delete bowie where id=2;

1 row deleted.

Both complete successfully !! So what has occurred here ? Let’s look at a fresh partial block dump of this leaf block:

Block header dump: 0x018ad71c
Object id on Block? Y
seg/obj: 0x190b6 csc: 0x00.2affa76 itc: 3 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x18ad718 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0007.00b.0000ce8a 0x0140da06.0568.01 CB– 0 scn 0x0000.02affa76
0x02 0x0001.013.0000cd57 0x01400083.0559.02 —- 1 fsc 0x000e.00000000
0x03 0x0005.01a.0000d062 0x014009eb.0532.13 —- 1 fsc 0x000e.00000000
Leaf block dump
===============
header address 33325692=0x1fc827c
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 278
kdxcofbo 592=0x250
kdxcofeo 4499=0x1193
kdxcoavs 3907
kdxlespl 0
kdxlende 2
kdxlenxt 25675901=0x187c87d
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8012
row#0[4499] flag: —DS–, lock: 2, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 80 01 57 00 00
row#1[4511] flag: —D—, lock: 3, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 80 01 57 00 01
row#2[4523] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 80 01 57 00 02
row#3[4535] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 80 01 57 00 03
row#4[4547] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 80 01 57 00 04

So we have a few interesting things happening here.

Firstly, Oracle has allocated an additional ITL entry, we now have three in this leaf block. And indeed, both deleted rows have the deleted byte (D) set. But how is this possible when the leaf block was previously full ?

When an index is full but requires additional data to be inserted (as in the case of a new index entry), it has a mechanism to cater for this. It’s called an index block split and this is exactly what has occurred here. The second transaction needed additional space within the leaf block for an new ITL entry, so Oracle performed a 50-50 index block split thus providing a half empty block in which to now insert the new ITL entry.

If we look at the number of index entries now in the leaf block (kdxconro), we notice it has gone down from 540 to just 278. If we look at the next leaf block point (kdxlenxt), we notice it now points to a different block (25675901=0x187c87d).

An index block split, which ordinarily occurs frequently enough within an index, is a much better option than having a hanging transaction suffering from ITL contention issues.

As we now have heaps of free space within the leaf block, if we want to now perform yet another concurrent transaction:

In session three:

SQL> delete bowie where id=3;

1 row deleted.

It has also completed successfully. A fresh partial block dump of the index leaf block will show that Oracle has just added yet another ITL entry:

Block header dump: 0x018ad71c
Object id on Block? Y
seg/obj: 0x190b6 csc: 0x00.2affb79 itc: 4 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x18ad718 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0007.00b.0000ce8a 0x0140da06.0568.01 CB– 0 scn 0x0000.02affa76
0x02 0x0001.013.0000cd57 0x01400083.0559.02 —- 1 fsc 0x000e.00000000
0x03 0x0005.01a.0000d062 0x014009eb.0532.13 —- 1 fsc 0x000e.00000000
0x04 0x0006.015.0000cfbe 0x0140040f.055e.18 —- 1 fsc 0x000e.00000000
Leaf block dump
===============
header address 33325716=0x1fc8294
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 278
kdxcofbo 592=0x250
kdxcofeo 4475=0x117b
kdxcoavs 3883
kdxlespl 0
kdxlende 3
kdxlenxt 25675901=0x187c87d
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 7988
row#0[4475] flag: —DS–, lock: 2, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 80 01 57 00 00
row#1[4487] flag: —D—, lock: 3, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 80 01 57 00 01
row#2[4499] flag: —D—, lock: 4, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 80 01 57 00 02
row#3[4511] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 80 01 57 00 03
row#4[4523] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 80 01 57 00 04

So indexes are far more forgiving than tables when it comes to concurrency issues in relation to transactions acquiring ITL entries.

If we want to perhaps avoid (the generally minimal) overheads of dynamically allocating new ITL entries within indexes or perhaps unnecessary wasted space in monotonically increasing indexes from such block split operations on compactly created indexes, we can use Initrans to preallocate more than the default two ITL entries for indexes:

SQL> drop index bowie_id_i;

Index dropped.

SQL> create index bowie_id_i on bowie(id) initrans 10;

Index created.

Block header dump: 0x018ad71d
Object id on Block? Y
seg/obj: 0x190b2 csc: 0x00.2aff443 itc: 10 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x18ad718 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 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.02aff443
0x03 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x04 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x05 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x06 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x07 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x08 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x09 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x0a 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
Leaf block dump
===============
header address 33325860=0x1fc8324
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 466
kdxcofbo 968=0x3c8
kdxcofeo 1791=0x6ff
kdxcoavs 823
kdxlespl 0
kdxlende 0
kdxlenxt 25876254=0x18ad71e
kdxleprv 25876252=0x18ad71c
kdxledsz 0
kdxlebksz 7844
row#0[7831] flag: ——-, lock: 0, len=13
col 0; len 3; (3): c2 05 4b
col 1; len 6; (6): 01 80 01 53 00 a6
row#1[7818] flag: ——-, lock: 0, len=13
col 0; len 3; (3): c2 05 4c
col 1; len 6; (6): 01 80 01 53 00 a7

But this is generally unnecessary for indexes and just wastes space within index leaf blocks.

Now back to my grieving 😦