Any Idea About "INITRANS"

What is "INITRANS"?
Specify the initial number of concurrent transaction entries allocated within each data block allocated to the database object. This value can range from 1 to 255 and defaults to 1, with the following exceptions: (on10g MAX_TRANS 's ignored )

* The default INITRANS value for a cluster is 2 or the default INITRANS value of the tablespace in which the cluster resides, whichever is greater.

* The default value for an index is 2.

In general, you should not change the INITRANS value from its default.

Each transaction that updates a block requires a transaction entry in the block. The size of a transaction entry depends on your operating system. This parameter ensures that a minimum number of concurrent transactions can update the block and helps avoid the overhead of dynamically allocating a transaction entry.

If you have global indexes, a global index segment and global index blocks are shared by server processes of the same parallel DML statement. Even if the operations are not performed against the same row, the server processes can share the same index blocks. Each server transaction needs one transaction entry in the index block header before it can make changes to a block.

Therefore, in the CREATE INDEX or ALTER INDEX statements,

you should set INITRANS, the initial number of transactions allocated within each data block, to a large value, such as the maximum DOP against this index

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

Each ITL entry in the block transaction variable header takes 24 bytes. Though a block can have a maximum of 255 different ITLs, the ITLs allocation is limited by block size. The database block size plays an important role when allocating the number of inital ITLs for the block. The rule is "the total size allocated for initial ITLs SHOULD be LESS THAN 50% of the database block size".

ie : sizeof(INITIAL ITLs) < ( 50 % of the DATABASE BLOCK SIZE )

Examining ITL allocation (a brief experiment)
==========================

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

STEP 2: Insert 1 record for testing purpose. A blockdump can be taken later.
SQL> INSERT INTO TEST VALUES (10);
SQL> COMMIT;

Step 3: Find out the block number and the file id for dumping the block:
SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test;

Step 4: Dump the block:
SQL> ALTER SYSTEM DUMP DATAFILE x BLOCK y;

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

Example:

SQL> CREATE TABLE TEST (I NUMBER) INITRANS 10;

Table created.

SQL> INSERT INTO TEST VALUES (10);

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------------------------------------------
4 36477

SQL> ALTER SYSTEM DUMP DATAFILE 4 block 36477;

System altered.

Check tracefile:

Block header dump: 0x01008e7d
Object id on Block? Y
seg/obj: 0x18fec csc: 0x00.278431ed itc: 10 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1008e79 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.019.0017e02b 0x01800ce1.17d9.08 --U- 1 fsc 0x0000.278431ef
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
0x06 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x07 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x08 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x09 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x0a 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

The above dump confirms that the ITL allocation worked as specified. However this may not be the case for all the valid values for INITRANS.

Assuming that your database has 2k block size (db_block_size=2048), if you set INITRANS to 45, only 41 ITLs will be found in the dump because of the consideration of the block size. 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 is utilized for ITLs. 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

For 2k Block: 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.
==================================================================================

We can modify INITRANS attribute by use ALTER

EXAMPLE
SQL> ALTER TABLE TABLE_NAME INITRANS 10;

Example: Test on 8k ... initrans =255

SQL> CREATE TABLE TEST2 (I NUMBER) INITRANS 255;

Table created.

SQL> INSERT INTO TEST2 VALUES (10);

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test2;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 36485

SQL> ALTER SYSTEM DUMP DATAFILE 4 block 36485;

System altered.

Check trace file:

Block header dump: 0x01008e85
Object id on Block? Y
seg/obj: 0x18fed csc: 0x00.2784367f itc: 169 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1008e81 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.008.0011e01c 0x01800c72.154d.05 --U- 1 fsc 0x0000.27843681
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
.
.
.
0xa5 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa6 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa7 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0xa8 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000


So, use shell command to help

$ grep fsc oradb1_ora_15394.trc | wc -l
169

So, 8k block: 169 * 24 = 4056 bytes. (Same 48%)... not 255

You should know before modify =>

Cautions on Altering Tables Physical Attributes The values you specify in this clause affect the table as follows:

* For a nonpartitioned table, the values you specify override any values specified for the table at create time.

* For a range-, list-, or hash-partitioned table, the values you specify are the default values for the table and the actual values for every existing partition, overriding any values already set for the partitions. To change default table attributes without overriding existing partition values, use the modify_table_default_attrs clause.

* For a composite-partitioned table, the values you specify are the default values for the table and all partitions of the table and the actual values for all subpartitions of the table, overriding any values already set for the subpartitions. To change default partition attributes without overriding existing subpartition values, use the modify_table_default_attrs clause with the FOR PARTITION clause.

---------------------------------------