Oracle 12.1 on AIX
The database has a small table, only 4 columns and about 3,000 rows. The application continuously inserts and deletes rows from this table.
From time to time we get ITL waits (eng: TX - allocate ITL entry). We already increased INITRANS to 255 for the table and its indexes.
Simple calculations show that ITL waits shouldn't happen:
- Oracle doesn't use more than half of the block for ITL slots. Here block size is 8192, ITL slot = 24 bytes, so the "real" value of
INITRANS = 4096/24 = 170. This doesn't take into account other components of the block header, so let's say the real value of INITRANS is 150.
- The columns are NUMBER and VARCHAR2. I calculated the length of the column by running SELECT MIN(LENGTH(C1)), MAX(LENGTH(C1)) FROM TABLE_A for each column.
First 3 columns are fully populated, no row has NULL. Column#1 has length = 9 bytes, column#2 = 3 bytes, and for column#3 the length varies between 26 and 28 bytes.
Column#4 contains only NULLs. So row length = 9+3+26 = 38. If half of the block is occupied by rows there should be no more than 4096/35 = 117 rows per block (ignoring PCTFREE).
So we have more ITL slots than rows.
The table isn't part of foreign key relationship and there is no trigger defined on it. It is normal table, not IOT.
Currently PCTFREE = 20, I am thinking about increasing it, not sure whether it will help.
Another option is to run ALTER TABLE MINIMIZE RECORD_PER_BLOCK.