Since this morning i am confused with the initrans values of the table on a 10.2.0.4 version, for which i am writing this blog expecting some help in understanding it 🙂
Well, what is initrans??? i couldn’t find anything much simpler than this (By Tim Hall) —
Let’s say you have a block that has five records in it. Let’s also say that simultaneously five people update each of the records in the block. The block is taking part in 5 transactions. Space is required in the block to keep track of all this stuff. That’s was these parameters are for. The initrans reserves a minimum amount of space in the block that can be used, the maxtrans specifies the maximum amount of space in the block that can be used to hold this information.
SYS at matrix >select table_name,ini_trans from dba_tables where table_name in ('TAB_ASSM','TAB_MSSM');
TABLE_NAME INI_TRANS
------------------------------ ----------
TAB_ASSM 1
TAB_MSSM 1
Elapsed: 00:00:00.21
SYS at matrix >
The table TAB_ASSM is created in tablespace with Automatic segment space management and TAB_MSSM on a manual segment space management tablespace.Checked the header blocks of the table
ANAND at matrix >select segment_name,tablespace_name,header_file, header_block from dba_segments where segment_name in ('TAB_ASSM','TAB_MSSM');
SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
--------------------------------------------------------------------------------- ------------------------------ ----------- ------------
TAB_ASSM MAIN 6 19
TAB_MSSM COMP_TBS 7 9
Elapsed: 00:00:00.10
Lets dump one of the data block.To do so needed the block_id which can be taken from dba_extents
ANAND at matrix >select SEGMENT_NAME,BLOCK_ID,FILE_ID,TABLESPACE_NAME from dba_extents where SEGMENT_NAME in ('TAB_ASSM','TAB_MSSM');
SEGMENT_NAME BLOCK_ID FILE_ID TABLESPACE_NAME
--------------------------------------------------------------------------------- ---------- ---------- ------------------------------
TAB_ASSM 17 6 MAIN
TAB_ASSM 25 6 MAIN
...............
TAB_ASSM 89 6 MAIN
...............
TAB_ASSM 649 6 MAIN
TAB_ASSM 777 6 MAIN
TAB_MSSM 9 7 COMP_TBS
TAB_MSSM 17 7 COMP_TBS
....................
TAB_MSSM 121 7 COMP_TBS
....................
TAB_MSSM 649 7 COMP_TBS
42 rows selected.
Elapsed: 00:00:00.28
ANAND at matrix >
Dumped one of the data block from both tables .
SYS at matrix >alter system dump datafile 6 block 89; System altered. Elapsed: 00:00:00.28 SYS at matrix >alter system dump datafile 7 block 121; System altered. Elapsed: 00:00:00.23 16:04:16 SYS at matrix >
The dump trace for the blocks of the two different tables showed Initrans values as 3.
Dump of block 89 from datafile 6.The datafile 6 is automatic segment space management tablespace.
*** SESSION ID:(138.45) 2010-12-18 12:57:37.286
Start dump data blocks tsn: 7 file#: 6 minblk 89 maxblk 89
buffer tsn: 7 rdba: 0x01800059 (6/89)
scn: 0x0000.001a51b7 seq: 0x02 flg: 0x04 tail: 0x51b70602
frmt: 0x02 chkval: 0x6bac type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x053B6600 to 0x053B8600
53B6600 0000A206 01800059 001A51B7 04020000 [....Y....Q......]
53B6610 00006BAC 00000001 0000D2D6 001A5196 [.k...........Q..]
......................................
......................................
Block header dump: 0x01c00069
Object id on Block? Y
seg/obj: 0xd326 csc: 0x00.1b2220 itc: 3 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.001b2220
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x53b6674
Dump of block 121 from datafile 7.The datafile 7 is manual segment space management tablespace.
Start dump data blocks tsn: 8 file#: 7 minblk 121 maxblk 121
buffer tsn: 8 rdba: 0x01c00079 (7/121)
scn: 0x0000.001b223d seq: 0x02 flg: 0x04 tail: 0x223d0602
frmt: 0x02 chkval: 0xb33e type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x04C36600 to 0x04C38600
4C36600 0000A206 01C00079 001B223D 04020000 [....y...="......]
4C36610 0000B33E 00000001 0000D326 001B2220 [>.......&... "..]
..................................
..................................
Block header dump: 0x01c00079
Object id on Block? Y
seg/obj: 0xd326 csc: 0x00.1b2220 itc: 3 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.001b2220
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Now, what if i increase the initrans to 5 and then check data block dump??Lets find out –
ANAND at matrix >alter table TAB_ASSM initrans 5;
Table altered.
Elapsed: 00:00:00.03
ANAND at matrix >alter table TAB_MSSM initrans 5;
Table altered.
Elapsed: 00:00:00.03
ANAND at matrix >select table_name,ini_trans from dba_tables where table_name in ('TAB_ASSM','TAB_MSSM');
TABLE_NAME INI_TRANS
------------------------------ ----------
TAB_ASSM 5
TAB_MSSM 5
Elapsed: 00:00:00.12
ANAND at matrix >insert into TAB_MSSM select * from TAB_MSSM;
101360 rows created.
Elapsed: 00:00:05.01
ANAND at matrix >commit;
Commit complete.
Elapsed: 00:00:00.01
ANAND at matrix >insert into TAB_ASSM select * from TAB_ASSM;
101282 rows created.
Elapsed: 00:02:11.98
ANAND at matrix >commit;
Taking the dump of blocks and checking the initrans values.
SYS at matrix >conn / as sysdba Connected. SYS at matrix >alter system dump datafile 7 block 2441; System altered. Elapsed: 00:00:00.15 SYS at matrix >alter system dump datafile 6 block 2056; System altered. Elapsed: 00:00:00.09
The data block dump shows
*** SESSION ID:(159.27) 2010-12-18 19:18:06.687
Start dump data blocks tsn: 8 file#: 7 minblk 2441 maxblk 2441
buffer tsn: 8 rdba: 0x01c00989 (7/2441)
scn: 0x0000.001b92c3 seq: 0x01 flg: 0x06 tail: 0x92c30601
frmt: 0x02 chkval: 0xf78b type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x04C36600 to 0x04C38600
4C36600 0000A206 01C00989 001B92C3 06010000 [................]
4C36610 0000F78B 00000001 0000D326 001B9258 [........&...X...]
.......................................
.......................................
Block header dump: 0x01c00989
Object id on Block? Y
seg/obj: 0xd326 csc: 0x00.1b9258 itc: 5 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0007.024.0000019b 0x008002a0.01a2.21 --U- 70 fsc 0x0000.001b92c3
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
0x05 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x4c366a4
=====================================================================
*** SESSION ID:(159.31) 2010-12-18 19:23:40.937
Start dump data blocks tsn: 7 file#: 6 minblk 2056 maxblk 2056
buffer tsn: 7 rdba: 0x01800808 (6/2056)
scn: 0x0000.001b9436 seq: 0x01 flg: 0x06 tail: 0x94360601
frmt: 0x02 chkval: 0x2c1a type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x04C36600 to 0x04C38600
4C36600 0000A206 01800808 001B9436 06010000 [........6.......]
4C36610 00002C1A 00000001 0000D2D6 001B9303 [.,..............]
..................................
..................................
Block header dump: 0x01800808
Object id on Block? Y
seg/obj: 0xd2d6 csc: 0x00.1b9303 itc: 5 flg: E typ: 1 - DATA
brn: 0 bdba: 0x180078a ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.02b.000001bc 0x0080018f.01b0.1e --U- 71 fsc 0x0000.001b9436
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
0x05 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x4c366ac
So i believe that though the data dictionary shows INITRANS as 1, but in actual the table have 3 ITL slots, by default (atleast in 10.2.0.4).
Reference http://www.oracle-base.com/forums/viewtopic.php?f=1&t=4078
Learn. Share. Repeat. 
Hi Anand,
Perhaps Oracle 10g defaults to minimum of 3 ITLs. Earlier, for 9i it was 2. A similar sort of question was raised to Tom Kyte in which he confirmed it to be 2 for 9i. In that case the Data Dictionary view may be misleading and your conclusion is correct.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:25473691409150
Regards,
S.K.
Hi Santosh,
Thanks for visiting the blog and the inputs.That was something new for me,so blogged it 🙂
Regards,
Anand
Just to add little bit more on this.
http://desaitaral.wordpress.com/2010/12/27/initrans/
Hi Taral,
Thanks for visiting the blog 🙂
Regards,
Anand
I was always under the impression that INITRANS value would be decided internally by Oracle for ASSM.
not so?