How to change initrans on table for avoiding enq: TX – allocate ITL entry

In this blog post I will show you how to change initrans on table in Oracle Database without recreate table, for tuning enq: TX – allocate ITL entry waits in database.

First of all what is initrans? it is initial number of concurrent entries allocated within each data block allocated to the database object, this value can range from 1 to 255 and default is 1 with following exceptions:

• 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.

Each transaction that updates a block requires a transaction entry in the block. 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.

1) check if table have child tables with pk-fk relationship, if table have child table(s) then consider to change initrans also for child table(s)

select *
  from dba_constraints a, dba_constraints b
 where a.constraint_name = b.r_constraint_name
   and a.table_name = 'TABLE_NAME';

2) check if Table have indexes, then you must rebuild indexes, and also change initrans for indexes.
commands will be like:

ALTER TABLE TABLE_NAME MOVE INITRANS 10;
ALTER INDEX TABLE_NAME_PK REBUILD INITRANS 20;
ALTER INDEX TABLE_NAME_I1 REBUILD INITRANS 20;
......

3) check invalid objects and compile if any.

Let's demonstrate small demo :

[oracle@testdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 9 16:42:12 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> set linesize 500
SQL> select *
  from dba_constraints a, dba_constraints b
 where a.constraint_name = b.r_constraint_name
   and a.table_name = 'TABLE_NAME';

no rows selected

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

TABLE_NAME                      INI_TRANS
------------------------------ ----------
TABLE_NAME                              1

SQL> SELECT table_name, index_name, status, ini_trans
  FROM DBA_INDEXES
 WHERE TABLE_NAME = 'TABLE_NAME'; 

TABLE_NAME                     INDEX_NAME                     STATUS    INI_TRANS
------------------------------ ------------------------------ -------- ----------
TABLE_NAME                     TABLE_NAME_PK                  VALID             2
TABLE_NAME                     TABLE_NAME_I1                  VALID             2

SQL> ALTER TABLE TABLE_NAME MOVE INITRANS 10;

Table altered.

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

TABLE_NAME                      INI_TRANS
------------------------------ ----------
TABLE_NAME                             10

SQL> SELECT table_name, index_name, status, ini_trans
  FROM DBA_INDEXES
 WHERE TABLE_NAME = 'TABLE_NAME';

TABLE_NAME                     INDEX_NAME                     STATUS    INI_TRANS
------------------------------ ------------------------------ -------- ----------
TABLE_NAME                     TABLE_NAME_PK                  UNUSABLE          2
TABLE_NAME                     TABLE_NAME_I1                  UNUSABLE          2

SQL> ALTER INDEX TABLE_NAME_PK REBUILD INITRANS 20;

Index altered.

SQL> ALTER INDEX TABLE_NAME_I1 REBUILD INITRANS 20;

Index altered.

SQL> SELECT table_name, index_name, status, ini_trans
  FROM DBA_INDEXES
 WHERE TABLE_NAME = 'TABLE_NAME';

TABLE_NAME                     INDEX_NAME                     STATUS    INI_TRANS
------------------------------ ------------------------------ -------- ----------
TABLE_NAME                     TABLE_NAME_PK                  VALID            20
TABLE_NAME                     TABLE_NAME_I1                  VALID            20

SQL>