What is INITRANS and MAXTRANS

PURPOSE
-------

This article explains the concept of allocating the initial ITLs in the block
header and its relationship with the block size.

What is INITRANS and MAXTRANS ?
=================================

INITRANS is a block level storage parameter which can be specified while creating a object (table).
INITRANS and MAXTRANS parameters are used to control the concurrent access to the same block. There
can be a maximum of 255 concurrent sessions that can access a block at any given time. So the maximum
value for MAXTRANS parameter is 255. The value specified through INITRANS are taken into consideration
for creating the initial number of ITLs (Intersted Transaction Entries) in the block.

While creating the table if INITRANS 20 is specified, then 20 different ITL slots will be created in the block
transaction variable header.


BLOCK SIZE and INITRANS :
=========================

Each and every ITL entry in the block transaction variable header takes 24 bytes. Though a block can
have a maximum of 255 different ITLs , the block is quite limited to allocate only some defined ITLs in the
header. The database block size plays a important role in allocating the number of inital ITLs for the blocks.
The rule is
"the total size allocated for initial ITLs SHOULD be LESS THAN 50% of the database block size" 
ie :  sizeof(INITIAL ITLs)
Examining ITL allocation (a brief experiment)
==========================

STEP 1 : Create a table with INITRANS 10.
SQL> CREATE TABLE CHANDRA (I INT) INITRANS 10 ;

STEP 2: Insert 1 record for testing purpose. You can dump this block later.
SQL> INSERT INTO CHANDRA VALUES (10);
SQL> COMMIT;

Step 3: Findout the block number and the file id for dumping the block:
SQL> SELECT BLOCK_ID,FILE_ID,RELATIVE_FNO FROM DBA_EXTENTS WHERE SEGMENT_NAME='CHANDRA';

Step 4: Dump the block:
SQL> ALTER SYSTEM DUMP DATAFILE BLOCK +1 ;

Step 5: Open the dump trace file located in USER_DUMP_DEST directory and check the following:

Block header dump:  0x0040ad12
Object id on Block? Y
seg/obj: 0x5881  csc: 0x5dc.33121987  itc: 10  flg: O  typ: 1 - DATA
fsl: 0  fnx: 0x0 ver: 0x01

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   xid:  0x0003.05b.0000009b    uba: 0x008005f3.005e.42  --U-    1  fsc 0x0000.33121989
0x02   xid:  0x0000.000.00000000    uba: 0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   xid:  0x0000.000.00000000    uba: 0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x04   xid:  0x0000.000.00000000    uba: 0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x05   xid:  0x0000.000.00000000    uba: 0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x06   xid:  0x0000.000.00000000    uba: 0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x07   xid:  0x0000.000.00000000    uba: 0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x08   xid:  0x0000.000.00000000    uba: 0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x09   xid:  0x0000.000.00000000    uba: 0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x0a   xid:  0x0000.000.00000000    uba: 0x00000000.0000.00  ----    0  fsc 0x0000.00000000


The above dump confirms that the ITL allocation worked as specified. However this may not the case for all
the valid values for INITRANS. Assuming that your database has 2k block size, if
you set INITRANS to 45, not 45 ITLs can be found in the dump. There will only be 41 ITL slots in the header. So
for a 2k block size, any value greater than 41 for INITRANS would result only in allocating 41 ITL entries in
the block header.

Likewise , if you have a 4k block size, the maximum number of inital ITL entries are only 83. Any value over and
above 83 for INITRANS are ignored.

BLOCK SIZE NO OF ITLs allocated in block header
========== ===================================

2048 41
4096 83
8192        169


As I mentioned before, not more than 50 % of the block size are utilized for these headers.  For a 2k block
sized database, the maximum ITLs during intial allocation is 41. Each ITL takes 24 bytes of space in the
header. So
41 * 24 = 984 bytes.   This is 48 % of the total block size.

For 4k block:   83 * 24 = 1992 bytes.

For 8k block:   169 * 24 = 4056 bytes.   (Same 48%)

Follow the above 5 steps to check the ITL allocation for different block sizes. So don't be surprised when
you don't see the value in the dump you set for INITRANS.


@
@ INTERNALS :
@
@ The function ktbfrm() in KTB.C is responsible for formatting the blocks. The
@ ITL count for the block is set by the function:
@
@ KTBHSITC(tbh,itlc);
@
@ The count for the initial number of ITL allocation (itlc) is calculated by
@ the macro KTBMIT:
@
@ #DEFINE KTBMIT(bsz) (min((((bsz)>>1)-sizeof(ktbbh)) /sizeof(ktbit)+1,UB1MAXVAL))
@
@ eg: bsz is data area available in each physical block
@     (Hence for a 2k block size, bsz = 2048 - 24 byte block verhead = 2024)
@
@ ( bsz >> 1 ) is equivalent to (bsz / 2).
@ sizeof(KTBBH) is 48 bytes (Standard structure for transaction fixed header)
@ sizeof(KTBIT) is 24 bytes (Standard structure for transaction variable header)
@      = (min((((2024)>>1) - 48)/24+1,UB1MAXVAL))
@            = 41.166 (= 41 when rounded).
@
@ The right shift operator ( >> ) above takes 50 % of the value
@
@ The above macro calculates the maximum number of intial ITLs for allocation.
@ If your INITRANS is less than this count then, the minimum of the 2 is taken
@ for ITL allocation:
@
@ itcl = (b2) min(initrans,mtrans);
@
@ Where mtrans is the value obtained from the macro.
@

Note: This article is applicable for 8.0.x to 8.1.x versions of Oracle only.