INITRANS and MAXTRANS parameter in Oracle 11gR2

A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block. Every transaction which modifies a block must acquire an entry in the Interested Transaction List (ITL) in block. Space for this list is defined by INITRANS. The space required for Each and every ITL entry is operating system dependent; however, ITL entry in most operating systems requires approximately 23 bytes.

The database block size plays an 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) <  ( 50 % of the DATABASE BLOCK SIZE )

INITRANS

The default value is 1 for tables and 2 for clusters and indexes.

MAXTRANS

The default value is an operating system-specific function of block size, not exceeding 255.

Example:

Let's say you have a block that has 50 records and 5 people simultaneously trying to update 5 records in the block. Let's say INITRANS parameter value is 1 for this block, therefore, at a time only one update will proceed and others update will wait. If INITRANS parameter value increase to 5 then at a time 5 record will update concurrently. Those will boost up the performance.

Initrans Parameter Modify:

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 11 11:44:45 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

  

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL>

SQL> select INI_TRANS,owner from dba_tables where table_name='F_VERSION_LINK_DEFAULTS';

 INI_TRANS OWNER

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

         1 ***

         1 ***

         1 CREDITTEST

SQL> conn credittest/credittest

Connected.

SQL>

SQL>

SQL> select count(1) from F_VERSION_LINK_DEFAULTS;

  COUNT(1)

----------

     28296

SQL>

SQL>

SQL> alter table F_VERSION_LINK_DEFAULTS initrans 100;

Table altered.

SQL>

SQL>

SQL>

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

$

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Sep 11 11:45:42 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL>

SQL> select INI_TRANS,owner from dba_tables where table_name='F_VERSION_LINK_DEFAULTS';

 INI_TRANS OWNER

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

         1 ***

         1 ***

       100 CREDITTEST

SQL>

SQL>