Re: Changing initrans

  • From: richa03@xxxxxxxxx
  • To: "Mark W. Farnham" <mwf@xxxxxxxx>
  • Date: Thu, 21 Aug 2008 08:28:35 -0700

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

Tested on 64-bit 10.2.0.3 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 20...it 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
--
//www.freelists.org/webpage/oracle-l


Other related posts: