Index ITL

Jonathan Lewis wrote a series of posts on topic of Index ITLs (interested transaction list), how and when they become huge and make index bigger in size – see this post with links to several other articles. As it turned out in a recent thread on SQL.ru forum, one of possible solutions to the issue – calling ALTER index COALESCE – appears to be very limited in it’s appliance. Why? Because COALESCE doesn’t clean out ITLs of all index leaf blocks – most important, it doesn’t clean out ITL of the right-most index leaf block.


Here is a demonstration:

drop table tmp cascade constraints purge;
create table tmp(id int constraint tmp$pk primary key);

declare
  procedure ins(n int)
  is
    pragma autonomous_transaction;
  begin
    insert into tmp(id) values (-n);
    if n > 0 then
      ins(n - 1);
    end if;
    commit;
  end ins;
begin
  ins(100);
end;
/

insert into tmp(id) select level + (select max(id) from tmp) from dual connect by level <= 1000;
commit;

col object_id new_value obj_id
select object_id from user_objects where object_name = 'TMP$PK';

@tmp_dump
  
alter index tmp$pk coalesce;

@tmp_dump

insert into tmp(id) select level + (select max(id) from tmp) from dual connect by level <= 1000;
commit;

@tmp_dump

alter index tmp$pk coalesce;

@tmp_dump

This test script uses tmp_dump.sql which looks like this:

col fil new_value fil
col block new_value block

alter system flush buffer_cache;
-- dump index tree
@treedump &obj_id
-- dump two right-most index leaf blocks - here is an assumptions of increasing block#
select dbms_rowid.rowid_relative_fno(r) fil
     , dbms_rowid.rowid_block_number(r) block 
  from (select sys_op_lbid(&obj_id, 'L', rowid) r
             , dense_rank() over (order by sys_op_lbid(&obj_id, 'L', rowid) desc) n
          from tmp)
 where n = 2 and rownum = 1;

alter system dump datafile &fil block &block;

select dbms_rowid.rowid_relative_fno(r) fil
     , dbms_rowid.rowid_block_number(r) block 
  from (select sys_op_lbid(&obj_id, 'L', rowid) r
             , dense_rank() over (order by sys_op_lbid(&obj_id, 'L', rowid) desc) n
          from tmp)
 where n = 1 and rownum = 1;

alter system dump datafile &fil block &block;

Excerpts of trace file:

branch: 0x10102a3 16843427 (0: nrow: 3, level: 1)
   leaf: 0x10102a5 16843429 (-1: nrow: 407 rrow: 407)
   leaf: 0x10102a6 16843430 (0: nrow: 400 rrow: 400)
   leaf: 0x10102a7 16843431 (1: nrow: 294 rrow: 294)

Block header dump:  0x010102a6
 Object id on Block? Y
 seg/obj: 0xea0b  csc: 0x00.e15b4  itc: 102  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10102a0 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000d.00b.00000002  0x00c00134.0000.01  -BU-    1  fsc 0x0000.000e15b7
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
...
0x65   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x66   0x004e.009.00000002  0x00c007d2.0000.19  --U-  400  fsc 0x0000.000e15b9

Block header dump:  0x010102a7
 Object id on Block? Y
 seg/obj: 0xea0b  csc: 0x00.e15b8  itc: 102  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10102a0 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000d.00b.00000002  0x00c00135.0000.01  CB--    0  scn 0x0000.000e15b7
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
...
0x65   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x66   0x004e.009.00000002  0x00c007d3.0000.04  --U-  294  fsc 0x0000.000e15b9

This is starting point: there are three leaf blocks, each has 102 ITL slots.

=================== after coalesce ===================

branch: 0x10102a3 16843427 (0: nrow: 3, level: 1)
   leaf: 0x10102a5 16843429 (-1: nrow: 407 rrow: 407)
   leaf: 0x10102a6 16843430 (0: nrow: 400 rrow: 400)
   leaf: 0x10102a7 16843431 (1: nrow: 294 rrow: 294)

Block header dump:  0x010102a6
 Object id on Block? Y
 seg/obj: 0xea0b  csc: 0x00.e15c1  itc: 102  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10102a0 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000d.00b.00000002  0x00c00134.0000.01  CB--    0  scn 0x0000.000e15b7
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
...
0x65   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x66   0x004e.009.00000002  0x00c007d2.0000.19  C---    0  scn 0x0000.000e15b9

Block header dump:  0x010102a7
 Object id on Block? Y
 seg/obj: 0xea0b  csc: 0x00.e15c1  itc: 102  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10102a0 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000d.00b.00000002  0x00c00135.0000.01  CB--    0  scn 0x0000.000e15b7
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
...
0x65   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x66   0x004e.009.00000002  0x00c007d3.0000.04  C---    0  scn 0x0000.000e15b9

Nothing happened – have no idea why.

=================== after 1000 rows insert ===================

branch: 0x10102a3 16843427 (0: nrow: 6, level: 1)
   leaf: 0x10102a5 16843429 (-1: nrow: 407 rrow: 407)
   leaf: 0x10102a6 16843430 (0: nrow: 400 rrow: 400)
   leaf: 0x10102a7 16843431 (1: nrow: 400 rrow: 400)
   leaf: 0x10102a4 16843428 (2: nrow: 400 rrow: 400)
   leaf: 0x10102a9 16843433 (3: nrow: 400 rrow: 400)
   leaf: 0x10102aa 16843434 (4: nrow: 94 rrow: 94)

Block header dump:  0x010102a9
 Object id on Block? Y
 seg/obj: 0xea0b  csc: 0x00.e15da  itc: 102  flg: E  typ: 2 - INDEX
     brn: 1  bdba: 0x10102a0 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0023.008.00000002  0x00c00523.0000.01  -BU-    1  fsc 0x0000.000e15de
0x02   0x003f.009.00000002  0x00c006e3.0000.03  --U-  400  fsc 0x0000.000e15df
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
...
0x66   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

Block header dump:  0x010102aa
 Object id on Block? Y
 seg/obj: 0xea0b  csc: 0x00.e15de  itc: 102  flg: E  typ: 2 - INDEX
     brn: 1  bdba: 0x10102a0 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0023.008.00000002  0x00c00524.0000.01  CB--    0  scn 0x0000.000e15de
0x02   0x003f.009.00000002  0x00c006e3.0000.05  --U-   94  fsc 0x0000.000e15df
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
...
0x66   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

Three more leaf blocks were added to the index structure. Two right-most of them have 102 ITL slots.

=================== after coalesce ===================

branch: 0x10102a3 16843427 (0: nrow: 5, level: 1)
   leaf: 0x10102a5 16843429 (-1: nrow: 520 rrow: 520)
   leaf: 0x10102a6 16843430 (0: nrow: 513 rrow: 513)
   leaf: 0x10102a7 16843431 (1: nrow: 513 rrow: 513)
   leaf: 0x10102a9 16843433 (2: nrow: 461 rrow: 461)
   leaf: 0x10102aa 16843434 (3: nrow: 94 rrow: 94)

Block header dump:  0x010102a9
 Object id on Block? Y
 seg/obj: 0xea0b  csc: 0x00.e15ed  itc: 2  flg: E  typ: 2 - INDEX
     brn: 1  bdba: 0x10102a0 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0039.009.00000002  0x00c00687.0000.01  CB--    0  scn 0x0000.000e15ed
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

Block header dump:  0x010102aa
 Object id on Block? Y
 seg/obj: 0xea0b  csc: 0x00.e15ee  itc: 102  flg: E  typ: 2 - INDEX
     brn: 1  bdba: 0x10102a0 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0023.008.00000002  0x00c00524.0000.01  CB--    0  scn 0x0000.000e15de
0x02   0x003f.009.00000002  0x00c006e3.0000.05  C---    0  scn 0x0000.000e15df
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
...
0x66   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

Here is COALESCE have worked. It cleaned out ITL slots of block 16843433, but not ITLs of 16843434. Situation remains the same if you repeat same actions several times – right-most leaf block will always have 102 ITL slots. So, if you have mostly increasing values in an indexed columns, which goes to the right side of the index, COALESCE is probably not what you might want to apply as a way to handle the ITL issue, because it is a temporary patch, not a permanent solution even when your index experienced burst of transaction activity just once.

This entry was written by Timur Akhmadeev, posted on April 27, 2010 at 23:57, filed under Oracle and tagged , , . Bookmark the permalink. Follow any comments here with the RSS feed for this post. Post a comment or leave a trackback: Trackback URL.

One Trackback

  1. By Index Explosion – 4 « Oracle Scratchpad on May 2, 2010 at 15:28

    […] 27th April 2010: Timur Akhmadeev has written a nice article discussing the limitations of using the coalesce to address the problem of an exploding […]

Leave a comment