Oracle Scratchpad

July 23, 2009

Index Quiz 2

Filed under: Index Explosion,Indexing,Infrastructure — Jonathan Lewis @ 7:54 am BST Jul 23,2009

This quiz is easier than Index Quiz 1 because you can just run the code, dump blocks, and find the answer. But see if you can work out what’s going to happen before you do the test. Note that I’ve set initrans to 4 on both the table and index.

create table t1 (
	v1	varchar2(20)
)
initrans 4
;

create index t1_i1 on t1(v1) initrans 4;

insert into t1 values(lpad('0',20));
commit;

The following output is the ITL section I get from dumping the first block of the table – notice that we have four entries (itc = 4) obeying my initrans 4. The question is: if you dump the first block of the index how many ITL entries will there be (from 9i onwards) ?

Block header dump:  0x01400c8a
 Object id on Block? Y
 seg/obj: 0x183e2  csc: 0x00.5528efc  itc: 4  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000b.014.00004c37  0x008010b7.0259.1e  --U-    1  fsc 0x0000.05528efe
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x04   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

Question 2:

I now execute the pl/sql loop below to insert 300 consecutive rows in the table. Using the standard 8KB block size this is enough data to cause a leaf node split, which will be a 90/10 leaf node split leaving one leaf block full and the other with 52 rows:

begin
	for i in 1..300 loop
		insert into t1 values(lpad(i,20));
		commit;
	end loop;
end;
/

If you dump the three resulting index blocks (the root block and two leaf blocks) how many entries will each ITL have ?

Question 3:

Drop and recreate the table and its index but don’t insert any data into it straight away. Start up 10 new sessions and from each session insert one row into the table – but don’t commit. Use the values lpad(1,20) to lpad(10,20) in turn. Dump the single index block – how many entries should you see in the ITL.

Now commit each session, then execute the following pl/sql loop from one of them (it starts from 11, not 1, but that’s not particularly significant, just my tidy mind):

begin
	for i in 11..300 loop
		insert into t1 values(lpad(i,20));
		commit;
	end loop;
end;
/

Again, if you dump the resulting three index blocks what will the ITLs look like ?

[See my answers]
[Further reading on Index ITL Explosion]

14 Comments »

  1. I suspect that the default values of 1 initrans for tables and 2 for indexes are a hint.

    Comment by lascoltodelvenerdi — July 23, 2009 @ 9:47 am BST Jul 23,2009 | Reply

  2. My answers are

    1- 2 with kdxlebksz 7988
    2-
    Branch Block 1
    Leaf Block1 2 with kdxlebksz 8036
    Leaf Block2 2 with kdxlebksz 8036
    3-
    Branch Block 1
    Leaf Block 1 11 with kdxlebksz 7820 (I think 11 because I ran it with controller session other than 1 of 10)
    Leaf Block 2 11 with kdxlebksz 7820

    My conclusion after further research setting initrans does not have any change for index unless index is created after population or rebulded with the initrans settings

    Otherwise initrans behaves as you explained previous quiz

    Comment by coskan — July 23, 2009 @ 11:37 am BST Jul 23,2009 | Reply

    • I find a bit strange that the initrans does “nothing” to the index.

      Initrans is used for controlling the ITS (Initian Transection Slot) so I would expect some change. But this index is a “right-hand index” so something must “be strange” with it.

      Coskan, what was the itc in your dumps?

      Comment by lascoltodelvenerdi — July 23, 2009 @ 12:53 pm BST Jul 23,2009 | Reply

      • 1- itc=2
        2-
        Branch Block itc=1
        Leaf Block1 itc=2
        Leaf Block2 itc=2
        3-
        Branch Block itc=1
        Leaf Block-1 itc=11
        Leaf Block-2 itc=11

        but the 11 one is because I ran the multiple insert from the 11th session I think I am not sure

        Initran for index behaviour is also covered in this ML: 729445.1

        Comment by coskan — July 23, 2009 @ 2:05 pm BST Jul 23,2009 | Reply

    • @Coskan

      It looks like Oracle is ignoring the initial initrans.

      We did not ask one thing: the index is created in an ASSM tablespace?

      If this is true, the initrans parameter (and others) is ignored.

      Comment by lascoltodelvenerdi — July 23, 2009 @ 2:48 pm BST Jul 23,2009 | Reply

  3. By the way Jonathan it would be excellent, if you can write a post to cover the explanations of sections of a data/index block dump headers. Just a wish from a regular reader

    Comment by coskan — July 23, 2009 @ 11:47 am BST Jul 23,2009 | Reply

  4. Hi,

    Here is my guess:
    There is no need to reserve a number of ITL because it is always possible to split a block in order to have free space. So initrans is ignored.
    However, the transaction that splits the block must have an ITL entry for itself, so I guess that oracle always keep one free ITL entry in order to be sure that a transaction can split the block.

    So here is what I guess that happens:
    1- the insert needs 1 ITL entry, and adds 1 free ITL free, so result is 2 ITL entries
    2- when the block was split, ITL entries were copied in the two resulting blocks, so both blocks have 2 ITL entries
    3- each concurrent insert need their own ITL (so 10) and keep 1 free entry, so result is 11.
    After the block split, they are copied in both blocks.

    Regards,
    Franck.

    Comment by Franck Pachot — July 24, 2009 @ 7:02 am BST Jul 24,2009 | Reply

    • Franck,
      That’s just about the perfect answer.

      I’m hoping to find time to write up my own answer some time this evening, but that will just add a couple of extra details.

      Comment by Jonathan Lewis — July 24, 2009 @ 11:03 am BST Jul 24,2009 | Reply

  5. [...] by Index Quiz 2 « Oracle Scratchpad — July 23, 2009 @ 7:55 am UTC Jul 23,2009 | [...]

    Pingback by Index Quiz 1 « Oracle Scratchpad — July 24, 2009 @ 9:08 pm BST Jul 24,2009 | Reply

  6. [...] Jonathan Lewis with his Index Quiz 2 , which is easier, he promises, than Index Quiz [...]

    Pingback by Log Buffer #156: a Carnival of the Vanities for DBAs | Pythian Group Blog — July 31, 2009 @ 10:42 pm BST Jul 31,2009 | Reply

  7. [...] files — Jonathan Lewis @ 9:04 pm UTC Jul 24,2009 I was planning to supply the answers to Index Quiz 2 as a comment – but there’s a lot of block dumps involved, and it’s easier to do [...]

    Pingback by IQ2 – Answers « Oracle Scratchpad — October 4, 2009 @ 7:11 am BST Oct 4,2009 | Reply


RSS feed for comments on this post.

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

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,453 other followers