RE: Changing initrans

From: Mark W. Farnham <>
Date: Thu, 21 Aug 2008 14:17:58 -0400
Message-ID: <>

Possibly this is just some version differences. Also I changed the initrans and did the rebuild in two separate steps. I didn't think that would matter. As for actually creating ITL entries, that would be a bad idea. It SHOULD just reserve room for them and only actually create them in that space as required. Usually at this level Oracle follows the lazyman decree: Don't do anything until I must do it, and I might never need to do it. The exceptions, if any, would be if someone made a case that the cost of doing something now was so cheap it wasn't worth deferring or so likely to happen and cheaper enough to do now that it is worthwhile.

The only bit I'm not tracking is why the kdxlebksz value didn't shrink. I *thought* that was the space remaining available, and that should change when you reserve space for more itl entries (ie. have a bigger initrans when the block is first used).

Were there entries in the leaf block you looked at? There is a weird pattern for the order of block usage in ASSM for tables and I haven't check indexes.


-----Original Message-----
From: [] On Behalf Of
Sent: Thursday, August 21, 2008 11:29 AM To: Mark W. Farnham
Cc:; Subject: Re: Changing initrans

Thanks all, but it still isn't 100% clear to me.

Tested on 64-bit Solaris x86 - block size 8KB.

create table t ( x ) as select rownum from dba_objects;

create index t_idx on t(x);

[from a treedump on the object id of t_idx]
branch: 0x10001ac 16777644 (0: nrow: 31, level: 1)

   leaf: 0x10001ad 16777645 (-1: nrow: 485 rrow: 485) - block number 429    leaf: 0x10001ae 16777646 (0: nrow: 479 rrow: 479) ... snipped ...

[dump the block] - 2 Itl slots - kdxlebksz 8032 - makes sense

alter index t_idx initrans 5;

[dump the block] - still 2 Itl slots and kdxlebksz 8032 - still makes sense

alter index t_idx rebuild online initrans 20;

[dump the block] - There are 2 slots shown in the header of the block
dump and kdxlebksz is still 8032 in the leaf block dump, however the distribution of rows changed in the treedump: branch: 0x10001dc 16777692 (0: nrow: 33, level: 1)

   leaf: 0x10001dd 16777693 (-1: nrow: 456 rrow: 456)    leaf: 0x10001de 16777694 (0: nrow: 450 rrow: 450) ... snipped ...

This indicates to me that the internal block allocation changed to allow for the larger number of slots, right?

Also, the header of the leaf block states: seg/obj: 0x9cf8 csc: 0x00.f77ddb54 itc: 2 flg: E typ: 2 - INDEX after the rebuild to initrans appears that there are only 2...

Why are the additional slots not shown in the leaf block dump? Why is kdxlebksz still 8032?

Thinking that maybe they are just not used, so are not shown, I added more rows to the table:
insert into t select rownum from dba_objects;

[another block dump and treedump]

Now, the block header shows:
seg/obj: 0x9cf9 csc: 0x00.f781129f itc: 20 flg: E typ: 2 - INDEX and there are 20 Itl slots...
Also, kdxlebksz is now 7600.

Block cleanout??

[for my reference - this was trace prod_ora_22692.trc]

BTW, the tree dump was done with:
select OBJECT_ID from DBA_OBJECTS where OBJECT_NAME = 'T_IDX'; alter session set events 'immediate trace name treedump level 40184'; -- using the OBJECT_ID number produced above

and the block dump with:
select dbms_utility.DATA_BLOCK_ADDRESS_FILE(16777694), dbms_utility.DATA_BLOCK_ADDRESS_BLOCK(16777694) from dual; -- using the decimal block addresses in the tree dump for the first leaf block
alter system dump datafile 4 block 429;
-- using the FILE and BLOCK numbers produced above


Received on Thu Aug 21 2008 - 13:17:58 CDT

Original text of this message