A wait for the TX enqueue in mode 4 is normally due to one of the following reasons:
- ITL (interested transaction list) shortage
- Unique key enforcement
- Bitmap index entry
Here, we will talk about the ITL, which is a transaction slot in a data block. The initial number of ITL slots is defined by the INITRANS clause and is limited by the MAXTRANS clause. By default, a table has 1 ITL and an index has 2 ITLs. Each ITL takes up 24 bytes and contains the transaction ID in the format of USN.SLOT#.WRAP#. Every DML transaction needs to acquire its own ITL space within a block before data can be manipulated. Contention for ITL occurs when all the available ITLs within a block are currently in use and there is not enough space in the PCTFREE area for Oracle to dynamically allocate a new ITL slot. In this case, the session will wait until one of the transactions is committed or rolled back, and it will reuse that ITL slot. ITL is like a building parking space. Everyone who drives to the building needs a parking space. If the parking lot is full, you have to circle the lot until someone leaves the building.
Starting in Oracle9i Database, each data block has a minimum of two ITL slots by default. Even if you specify one, you still get two. The DBA_TABLES view will show just one, but the block dump will show two.
The following listing shows an example of the TX enqueue wait in mode 4 that is caused by ITL shortage, as seen in the V$LOCK view.
Because a TX enqueue wait in mode 4 is not always caused by ITL shortage, your first step should be to validate this premise. (You do not have to do this exercise in Oracle Database 10g because the enqueue name is enq: TX—allocate ITL entry, and you know it right off the bat.) The V$SESSION row of the waiting session contains the information about the object of the enqueue. The columns in particular are ROW_WAIT_FILE# and ROW_WAIT_BLOCK#. Using these values, you can dump the block and see the number of active ITLs (“–U-”) in the block and decide if ITL is the problem. If it is, the fix is to recreate the object with a higher INITRANS value. (Recreating the object with a higher PCTFREE value will also help because Oracle can dynamically allocate new ITL slots in the area.)
Starting in Oracle9i Database, Oracle keeps track of the number of ITL waits by object and publishes the information in the V$SEGMENT_STATISTICS view. Execute the following query to see the magnitude of ITL waits in your database: