jump to navigation

Quiz Time. Why Do Deletes Cause An Index To Grow ? (Up The Hill Backwards) June 25, 2015

Posted by Richard Foote in Oracle Indexes.
trackback

OK, time for a little quiz.

One of the things I’ve seen at a number of sites is the almost fanatical drive to make indexes as small as possible because indexes that are larger than necessary both waste storage and hurt performance.

Or so the theory goes …   :)

In many cases, this drives DBAs to create or rebuild indexes with a PCTFREE set to 0 as this will make the index as compact and small as possible.

Of course, this is often the very worst setting for an index to remain small because the insert of a new index entry is likely to cause a 50-50 block split and result in two 1/2 empty leaf blocks (unless the index entry is the maximum current value). Before very long, the index is back to a bloated state and in some sad scenarios, the process is repeated again and again.

A point that is often missed though is that it doesn’t even take an insert to cause the index to expand out. A few delete statements is all that’s required.

To illustrate I create my favorite little table and populate it with a few rows:

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

Table created.

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

10000 rows created.

SQL> commit;

Commit complete.

OK, time to create an index but because I’m paranoid about having indexes larger than necessary, I’ll create it with a pctfree of 0:

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

Index created.

If we look at a tree dump of the index:

SQL> select object_id from dba_objects where object_name='BOWIE_ID_I';

OBJECT_ID
----------
98884

SQL> alter session set events 'immediate trace name treedump level 98884';

Session altered.

—– begin tree dump
branch: 0x180050b 25167115 (0: nrow: 19, level: 1)
leaf: 0x180050c 25167116 (-1: row:540.540 avs:4)
leaf: 0x180050d 25167117 (0: row:533.533 avs:10)
leaf: 0x180050e 25167118 (1: row:533.533 avs:11)
leaf: 0x180050f 25167119 (2: row:533.533 avs:10)
leaf: 0x1800510 25167120 (3: row:533.533 avs:10)
leaf: 0x1800511 25167121 (4: row:533.533 avs:11)
leaf: 0x1800512 25167122 (5: row:533.533 avs:10)
leaf: 0x1800513 25167123 (6: row:533.533 avs:10)
leaf: 0x1800514 25167124 (7: row:533.533 avs:11)
leaf: 0x1800515 25167125 (8: row:533.533 avs:10)
leaf: 0x1800516 25167126 (9: row:533.533 avs:10)
leaf: 0x1800517 25167127 (10: row:533.533 avs:11)
leaf: 0x1800519 25167129 (11: row:533.533 avs:10)
leaf: 0x180051a 25167130 (12: row:533.533 avs:10)
leaf: 0x180051b 25167131 (13: row:533.533 avs:11)
leaf: 0x180051c 25167132 (14: row:533.533 avs:10)
leaf: 0x180051d 25167133 (15: row:533.533 avs:10)
leaf: 0x180051e 25167134 (16: row:533.533 avs:11)
leaf: 0x180051f 25167135 (17: row:399.399 avs:2019)
—– end tree dump

We note the index only has 19 leaf blocks and that most leaf blocks have 533 index entries and only an avs (available free space) of some 10 or 11 bytes. Only the last leaf block is partly full with some 2019 free bytes.

That’s fantastic, the index really is a small as can be. Trying to use index compression will be futile as the indexed values are effectively unique.

I’m now going to delete just a few rows. Surely deleting rows from the table (and hence entries from the index) can only have a positive impact (if any) on the index structure.

In one session, I delete some 20 odd rows:

SQL> delete bowie where id in (select rownum*499 from dual connect by level<=20);

20 rows deleted.

Meanwhile, in another session, I delete another 20 different rows:

SQL> delete bowie where id in (select rownum*500 from dual connect by level<=20); 

20 rows deleted.

I then commit the delete statement in both sessions.

So in total, I’ve just deleted some 40 rows in total out of the 10000 rows in the table.

If I look at a tree dump of the index now:

—– begin tree dump
branch: 0x180050b 25167115 (0: nrow: 37, level: 1)
leaf: 0x180050c 25167116 (-1: row:278.278 avs:3931)
leaf: 0x1800524 25167140 (0: row:262.260 avs:4049)
leaf: 0x180050d 25167117 (1: row:271.271 avs:3938)
leaf: 0x1800525 25167141 (2: row:262.260 avs:4048)
leaf: 0x180050e 25167118 (3: row:271.271 avs:3938)
leaf: 0x1800526 25167142 (4: row:262.260 avs:4049)
leaf: 0x180050f 25167119 (5: row:271.271 avs:3937)
leaf: 0x1800527 25167143 (6: row:262.260 avs:4049)
leaf: 0x1800510 25167120 (7: row:271.271 avs:3938)
leaf: 0x1800520 25167136 (8: row:262.260 avs:4048)
leaf: 0x1800511 25167121 (9: row:271.271 avs:3938)
leaf: 0x1800521 25167137 (10: row:262.260 avs:4049)
leaf: 0x1800512 25167122 (11: row:271.271 avs:3937)
leaf: 0x1800522 25167138 (12: row:262.260 avs:4049)
leaf: 0x1800513 25167123 (13: row:271.269 avs:3914)
leaf: 0x1800523 25167139 (14: row:262.262 avs:4072)
leaf: 0x1800514 25167124 (15: row:271.269 avs:3914)
leaf: 0x1800529 25167145 (16: row:262.262 avs:4073)
leaf: 0x1800515 25167125 (17: row:271.269 avs:3913)
leaf: 0x180052a 25167146 (18: row:262.262 avs:4073)
leaf: 0x1800516 25167126 (19: row:271.269 avs:3914)
leaf: 0x180052e 25167150 (20: row:262.262 avs:4072)
leaf: 0x1800517 25167127 (21: row:271.269 avs:3914)
leaf: 0x180052f 25167151 (22: row:262.262 avs:4073)
leaf: 0x1800519 25167129 (23: row:271.269 avs:3913)
leaf: 0x180052b 25167147 (24: row:262.262 avs:4073)
leaf: 0x180051a 25167130 (25: row:271.269 avs:3914)
leaf: 0x180052c 25167148 (26: row:262.262 avs:4072)
leaf: 0x180051b 25167131 (27: row:271.269 avs:3914)
leaf: 0x180052d 25167149 (28: row:262.260 avs:4049)
leaf: 0x180051c 25167132 (29: row:271.271 avs:3937)
leaf: 0x1800534 25167156 (30: row:262.260 avs:4049)
leaf: 0x180051d 25167133 (31: row:264.264 avs:4042)
leaf: 0x1800535 25167157 (32: row:269.267 avs:3944)
leaf: 0x180051e 25167134 (33: row:271.271 avs:3938)
leaf: 0x1800536 25167158 (34: row:262.260 avs:4049)
leaf: 0x180051f 25167135 (35: row:399.397 avs:1995)
—– end tree dump

I notice the index has basically doubled in size (37 leaf blocks up from the original 19), with most index leaf blocks now 1/2 empty.

So the key question. How could just deleting a handful of rows result in my beautifully compact index bloating to double the size ???

I’ve given away one clue within the post. I’ll post the answer in the coming days🙂

Comments»

1. Jonathan Lewis - June 25, 2015

Me, me, me, me,me, I know !!!

But I’m not saying – it’s more interesting to see what ideas come from people who don’t already know the answer.

Richard Foote - June 26, 2015

Yes, I need to apply a Jonathan Lewis free zone for all quizzes🙂

2. zhwsh - June 25, 2015

I think another session at delete,create itl,request 24 bytes.
free space not enough, index block split .

3. Chadders - June 25, 2015

I agree… no block (apart from the last one) has enough room for the 24 byte information about the transactions, so 18 out of the 19 blocks have to be block split.

The last block goes from avs 2019 to avs 1995 (24 byte extra usage).

4. @FranckPachot - June 25, 2015

I thought that the first session already had to split, but it seems that leaves are created with 2 ITL (it’s only 1 in branches).

Richard Foote - June 26, 2015

Hi Franck

Indeed there are key differences in how initrans are treated between branch and leaf index blocks.

5. vishaldesai - June 26, 2015

Root block dump shows 1 ITL but when I created index with pctfree 0 and initrans 3, leaf blocks did not double in size but the root block dump still shows only 1 ITL.

Object id on Block? Y
seg/obj: 0x4469a csc: 0x7ea.2eb70207 itc: 1 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x103acb8 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x07ea.2eb70207
Branch block dump

vishaldesai - June 26, 2015
Richard Foote - June 26, 2015

Perhaps you need therefore to dump of different block to see the impact of changing the initrans🙂

vishaldesai - June 27, 2015

I guess during 50-50 split its reserving first ITL for block split (ITL flag B)

first half of block
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0021.021.0001fe08 0x221dec14.2e2b.01 -BU- 1 fsc 0x0000.412a09f3
0x02 0x0026.004.00024c89 0x22215446.35b5.24 —- 0 fsc 0x0000.00000000

second half of block
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0021.021.0001fe08 0x221dec16.2e2b.01 CB– 0 scn 0x07ea.412a09f3
0x02 0x0026.004.00024c89 0x22215446.35b5.24 —- 1 fsc 0x000f.00000000
0x03 0x0021.017.0001fe28 0x221dec12.2e2b.03 –U- 1 fsc 0x000e.412a0c62

6. Martin Preiss - June 26, 2015

Hi Richard,
since you do not add the block dumps I guess the answer is there – of course another reason not to add the block dumps could be that the answer is not there…

If I compare the block header of a leaf block before and after the split I see:

— before the split:
Block header dump: 0x02800c4c
Object id on Block? Y
seg/obj: 0x16827 csc: 0x00.200fd9 itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x2800c48 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.00200fd9
Leaf block dump
===============
header address 140008825032292=0x7f5658480e64
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 1116=0x45c
kdxcoavs 0
kdxlespl 0
kdxlende 0
kdxlenxt 41946189=0x2800c4d
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8020] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 02 80 0c 24 00 00

— after the split:
Block header dump: 0x02800c4c
Object id on Block? Y
seg/obj: 0x16827 csc: 0x00.20139a itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x2800c48 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.010.00000832 0x01000502.011a.01 -BU- 1 fsc 0x0000.002013b0
0x02 0x0002.018.000007d9 0x0100026e.013f.10 —- 0 fsc 0x0000.00000000
Leaf block dump
===============
header address 140008825032292=0x7f5658480e64
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 1
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 278
kdxcofbo 592=0x250
kdxcofeo 4519=0x11a7
kdxcoavs 3927
kdxlespl 0
kdxlende 0
kdxlenxt 41946215=0x2800c67
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[4519] flag: ——-, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 02 80 0c 24 00 00

The differences I see are:
– kdxcolok changes from 0 to 1. According to your description in https://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf, page 16ff that “denotes whether structural block transaction is occurring”. Ok, maybe.
– kdxcosdc: that’s a “count of index structural changes involving block”. Sounds plausible.
– the ITL entries: now I could reread the details in Oracle Core, but my first suspect is Lck = 1 – and I guess that’s the sign of a lock byte that had been added to an index entry; and though the corresponding entry is no longer in the block the lck entry remains there as a task for following DML operations. So I guess the lock byte is “the straw that breaks the camel’s back” (is that really an idiom as my dictionary tells me?) and the reason for the 50-50-split.

Regards
Martin

Richard Foote - June 26, 2015

Hi Martin

I think you’ll find the block dump of the other half of the 50-50 split more illuminating with regard to what has changed, as this is where the actual deletes took place🙂

Martin Preiss - June 27, 2015

Hi Richard,
ok, seems to be just the additional ITL entry: so I just add the details to anwer 2ff. in the comment section:

Block header dump: 0x02808d65
Object id on Block? Y
seg/obj: 0x16829 csc: 0x00.201cca itc: 3 flg: E typ: 2 – INDEX
brn: 1 bdba: 0x2808d58 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.008.000007d9 0x010006fa.0137.01 CB– 0 scn 0x0000.00201cca
0x02 0x0002.019.000007da 0x01000274.013f.24 —- 1 fsc 0x000f.00000000
0x03 0x0007.020.000006f1 0x01001ad3.0129.1a —- 1 fsc 0x000e.00000000
Leaf block dump
===============
header address 140013993693308=0x7f578c5b907c
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 262
kdxcofbo 560=0x230
kdxcofeo 4605=0x11fd
kdxcoavs 4045
kdxlespl 0
kdxlende 2
kdxlenxt 41953013=0x28026f5
kdxleprv 41953012=0x28026f4
kdxledsz 0
kdxlebksz 8008
row#0[4605] flag: ——-, lock: 0, len=13
col 0; len 3; (3): c2 03 50
col 1; len 6; (6): 02 80 0c 7c 01 16

Regards

Martin

7. Top 50 Oracle SQL Blogs for 2016 - Complete IT Professional - May 3, 2016

[…] Post: Quiz Time. Why Do Deletes Cause An Index To Grow? […]


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

%d bloggers like this: