RE: Changing initrans

Does the tablespace that the index is allocated to use ASSM by any chance?

• Mark D Powell -- Phone (313) 592-5148

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of richa03_at_gmail.com
Sent: Thursday, August 21, 2008 11:29 AM To: Mark W. Farnham
Cc: kaygopal_at_gmail.com; oracle-l_at_freelists.org Subject: Re: Changing initrans

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

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l