Richard Foote's Oracle Blog

Indexes and Initrans (Blackstar) January 14, 2016

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 😦

David Bowie 1947-2016. My Memories. January 12, 2016

In mid-April 1979, as a nerdy 13 year old, I sat in my bedroom in Sale, North England listening to the radio when a song called “Boys Keep Swinging” came on by an singer called David Bowie who I never heard of before. I instantly loved it and taped it next time it came on the radio via my cheap radio-cassette recorder. A few weeks later I saw David Bowie for the first time on TV performing this song on the Kenny Everett Video Show and from that moment on I was hooked. His performance was not only funny, but the song rocked and Bowie had a swagger that I just thought was instantly cool. OK, I can never be as cool as Bowie but perhaps I can be cool just by being a fan of Bowie.

I had little money but saved my pennies and eventually bought the “Best of Bowie” K-tel album on cassette (which I still have) to check out his previous work and just couldn’t believe that I loved each and every track. Space Oddity, Life on Mars, Jean Genie, Sound and Vision, “Heroes”, etc. etc. How can one person have so many great songs ?? So began the thrilling journey during the following years of slowly buying his back catalogue, album by album. Hunky Dory, “Heroes”, Diamond Dogs, his first “David Bowie” album, each and every one of them brilliant and I can still remember that feeling of sitting in my bedroom or in the family room back in Sale, listening to each of them for the first time.

In 1980, Bowie released “Scary Monsters and Super Creeps”, his first album release while a Bowie fan. It was stunning and watching the “Ashes to Ashes” video for the first time was an amazing experience. It felt more like a movie than a music video. I was excited that the single made No 1 in the UK charts, a bit like my football team winning a match.

In 1981, my parents bought a record player and the first album I bought on record (previously they had all been on cassette) was “The Rise and Fall of Ziggy Stardust and the Spiders From Mars”. I remember mum being cranky for playing it so loud but I said I had to, look, it says so on the back cover: “To be played at maximum volume”.

That year , I got my first book on David Bowie for my birthday “David Bowie: An Illustrated Record” by Roy Carr and Charles Shaar Murray. I treasure it still, even though I now have over 70 books on Bowie. Yes, I love music and I love to read.

In 1982, we moved to Canberra Australia but my Bowie odyssey continued. The first album I bought soon after our move was Station to Station. What an album. That first Aussie Christmas, I got the “really” expensive “David Live” double-album and just played it to death. What a great time.

In 1983, Bowie toured Australia during the huge “Let’s Dance” world tour and my parents bought me a ticket and bus ride to see him live in Sydney. I couldn’t sleep the night before and after getting to the Sydney Show Grounds nice and early, managed to get myself to the second front row. I spent hours in eager excitement, reading through the program (they were like mini books in those days) and then the moment when I saw him live for the first time. I was THAT close to him and I know he looked at me several times during the show.

As the years passed, I finally bought all his albums and started making enough money that I didn’t have to sell some books or whatever in order to afford the latest magazine that featured Bowie on the cover. Having some money came particularly useful as his music got re-released on CD format !!

1987, Bowie toured Australia again, this time as part of the “Glass Spiders” world tour. I had my first full-time job but no leave. Tricky, especially as Bowie was playing 8 shows at the Sydney Entertainment Arena.  So I bought 2 tickets to each show and spent one of the most wonderful weeks of my life going to work, leaving at around 4pm, driving to Sydney with someone different at most shows and then driving home afterwards to be in bed by 2am to do it all again the next day. Yes, I’m a fully fledged David Bowie fan and yes I loved each and every show 🙂

While many people stopped following Bowie after his Tin Machine period (which I of course loved), I continue to follow his career keenly and just couldn’t wait to buy his latest album, be it “Black Tie White Noise”, “The Buddha of Suburbia” and one of my all-time favorites, “Outside”. The great albums continued on into the 2000’s when he joined forces again with the great Tony Visconti as record producer.

And so came February 2004 and Bowie’s last tour of Australia during his “Reality” world tour. This time I went to both of his concerts again at the Sydney Entertainment arena, now some 17 years since he last toured. He looked and sounded great and it was fantastic to have my wife with me this time to share the experience. I remember as he walked off after the final show I had a deep and sad feeling that I would never see him live again …

A few months later, he had a heart attack while on stage and the tour finished prematurely. Except for the odd single and public appearance he was rarely seen, before he finally disappeared from the scene in 2006. I continued to play and love his music but with rumours that he wasn’t well health wise, I really thought he had finally retired from making music. Or so it seemed.

While on holiday in Hawaii in January 2013, my son said “Hey Dad, Bowie’s releasing a new single”. I thought it was a joke but to my utter amazement and sheer joy, not only had he just released a new single “Where Are We Now?”, but also a new album “The Next Day”. Not only was Bowie back, but the album was just as good as anything he’s done. What a come back.

Last week on Friday (was it really just a few days ago !!), Bowie released his new album, Blackstar and again, I couldn’t contain my excitement. The Blackstar single and video were both stunning as was the follow-up single Lazarus. But I was a touch worried. He looked frail and thinner than he’d been for a while. But hey, this is Bowie, the Thin White Duke, he often looks thin and frail.

On Sunday night I had a dream about Bowie (not surprisingly really as his new songs were going through me mind), but I dreamt he had died (seriously). I was sooooo relived it was just a silly dream. But on Monday at 6pm, as I was leaving work in the car and the ABC news came on the radio, I was shocked, stunned, shattered to hear that David Bowie had indeed suddenly passed away. I just burst into tears in the car park (thankfully it was late and nobody was around) and then my wife rang and then messages from friends and colleagues come pouring in and so life post David Bowie began.

It’s funny how so many people have said to me how I was the first person they thought of after they heard the news David Bowie had passed away. Some people contacted me who I haven’t spoken to in years (and thank you, it really means a lot). I guess I wasn’t a closet fan. But that’s because David Bowie for the past 37 odd years has been a very real, important part of my life, one of my best ever friends even though I never actually (quite) met him in person. But I knew him like a best friend through his music and the incredible cultural influence he’s had on my life and those of so many many others. Hey, we were even both born in South London.

While it’s deeply sad that I’ll never again enjoy that new David Bowie album into the future, how lucky am I to have lived at the same time as the great genius that is/was David Bowie. And though he’s now gone, his music, legacy and wonderful memories will live on with me for the rest of my life. He was indeed the soundtrack of my life. The Starman is finally up among the stars.

Thank you David Bowie. RIP.