enq: TX - allocate ITL entry fix

This is the fix for enq: TX - allocate ITL entry

First find the table your are having issues with

Check AWR report to verify you have ITL waits on the table

On the AWR it should look like this

Segments by ITL Waits

  * % of Capture shows % of ITL waits for each top segment compared
  * with total ITL waits for all segments captured by the Snapshot

Owner Tablespace Name Object Name Subobject Name Obj. Type       ITL  Waits % of Capture
PIN   HMA_ODS_DATA      CURR_ACX_PROFILE                  TABLE           3               84.30


Now we have 2 options

Increase INITRANS parameter.

Defualt value for tables is 1
Default value for index is 2

I found an article online which suggested to increase INITRANS to 100. I am not sure what happens if i just increase it to 3 or 4 depending on waits i see on AWR report. I will reupdate this article if i try again in future.

So i decided to change INITRANS to 100

If we change INITRANS using the below statement, only the newly modified values have this in effect.

alter table <table name> INITRANS 100;

To apply it for the whole table we need to use move

alter table <table_name> move;


After this rebuild the indexes as the above operation will invalidate them

alter index <index_name> rebuild INITRANS 100;

For me doing the above fixed the issue.

*************************************************************************

However if it did not fix the issue you can try adjusting PCTFREE

If the issue is not resolved by increasing INITRANS then try increasing PCTFREE. Increasing PCTFREE holds more space back and so spreads the same number of rows over more blocks. This means that there are more ITL slots available overall

alter table <table name>  PCTFREE 40;

alter table <table_name> move;

After this rebuild the indexes as the above operation will invalidate them

alter index <index_name> rebuild PCTFREE 40;

*************************************************************************

We can change both at the same time as well

alter table <table name>  PCTFREE 40 INITRANS 100;
alter table <table name> move;
alter index <index_name> rebuild PCTFREE 40 INITRANS 100;