What is ITL?

Ever wondered how Oracle locks rows on behalf of transactions? In some RDBMS vendor implementations, a lock manager maintains information on which row is locked by which transaction. This works great in theory, but soon the lock manager becomes a single point of contention, as each transaction must wait to get a lock from the manager and then wait again to release the lock. This severely limits the scalability of the applications. In fact, application developers of some RDBMS products despise holding locks for a long time, and often resort to a full table lock when all that's needed is to get a few rows locked. This creates further waits, and consequently, scalability suffers.

So how is that different in Oracle? For starters, there is no lock manager. When a row is locked by a transaction, that information is placed in the block header where the row is located. When another transaction wishes to acquire the lock on the same row, it has to travel to the block containing the row anyway, and upon reaching the block, it can easily tell that the row is locked from the block header. There is no need to queue up for some single resource like a lock manager. This makes applications immensely scalable.

So, what portion of the block header contains information on locking? It is a simple data structure called "Interested Transaction List" (ITL), a linked list data structure that maintains information on transaction address and rowid. ITL contains several slots or place holders for transactions. When a row in the block is locked for the first time, the transaction places a lock in one of the slots with the rowid of the row that is locked. In other words, the transaction makes it known that it is interested in the row (hence the name "Interested Transaction List"). When the same transaction or another one locks another row, the information is stored in another slot, and so on. After a transaction ends via commit or a rollback, the locks are released and so are the slots that were used to mark the blocks, and these newly freed slots are reused for the other transactions. So there is in fact a queue, but it's at a block level, not at the entire database level or even at a segment level.

The next logical question that comes up is, how many slots are typically available? During the table creation, the INITRANS parameter defines how many slots are initially created in the ITL. When the transactions exhaust all the available slots and a new transaction comes in to lock a row, the ITL grows to create another slot. The ITL can grow up to the number defined by the MAXTRANS parameter of the table, provided there is space in the block. Nevertheless, if there is no more room in the block, even if the MAXTRANS is high enough, the ITL cannot grow.

What Is an ITL Wait?

So, what happens when a transaction does not find a free slot to place its lock information? This can occur because either (i) the block is so packed that the ITL cannot grow to create a free slot, or (ii) the MAXTRANS has already been reached. In this case, the transaction that needs to lock a row has to wait until a slot becomes available. This wait is termed as ITL waits and can be seen from the view v$session_wait, in which the session is waiting on an event named "enqueue."

Let's see this description of the wait in action. Assume our table has INITRANS of one and MAXTRANS 11. A typical data block right after the creation of the table will look like figure 1.

Since the INITRANS is one, there is only one slot for the ITL. The rest of the block is empty. Now we inserted three rows into the table. These will go into this block, and the block will look like figure 2.

Note how the empty space is reduced. At this point, a transaction called Txn1 updates Row1, but does not commit. This locks Row1, and the transaction places the lock in the slot number one in the ITL as shown in figure 3.

Now the empty space in the block is severely limited, and it will not be able to fit another ITL slot. If at this time another transaction comes in to update the row three, it must have a free slot in the ITL. The MAXTRANS is 11 and currently only two slots have been created, so another one is possible; but since there is no room in the block to grow, the slot can't be created. Therefore, the Txn3 has to wait until either of the other transactions rolls back or commits and the slot held by it becomes free. At this time the session will experience an ITL waits event as seen from the view V$SESSION_WAIT.

Simulation

To better illustrate the concept, let's illustrate such waits using a case. Create the following table and then populate it with several rows. Note MAXTRANS value.

CREATE TABLE TAB1

(     COL1        NUMBER,

      COL2        VARCHAR2(200))

INITRANS 1 MAXTRANS 1

/

DECLARE

      I     NUMBER;

BEGIN

      FOR I IN 1..10000 LOOP

            INSERT INTO TAB1 VALUES

            (I,'SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS');

      END LOOP;

      COMMIT;

END;

/

Now update a row of the table from one session, but do not commit it.

UPDATE TAB1 SET COL2 = 'UPDATED' WHERE COL1 = 1;     

From another session, update row number two and do not update it.

UPDATE TAB1 SET COL2 = 'UPDATED' WHERE COL1 = 2;     

This session will wait. Why? It's updating a row for COL1 = 2, not the same row updated in the other session for COL1 = 1. So why is the session waiting? It's because the first transaction occupied the only available ITL slot. The second transaction needed another slot to place its lock information, but since the MAXTRANS I defined is one, the ITL could not grow to create another slot. Thus, the latter transaction has to wait until the former session releases the lock and makes the slot available.

Now increase the MAXTRANS of the table by issuing

ALTER TABLE TAB1 MAXTRANS 11;                                       

and redo the above test. The second session will not wait this time because the ITL had enough free slots for both transactions.

How to Reduce ITL Waits

The primary cause of ITL waits is that free slots in the ITL are not available. This can be due to

• low setting of the MAXTRANS, which places a hard limit on the number of transactions that can have locks on a block
• or, the block is so packed that there is no room for the ITL to grow OR
• or both

Therefore, setting a high value of INITRANS will make sure that there are enough free slots in the ITL, and there will be minimal or no dynamic extension of the ITL. However, doing so also means that there is less space in the block for actual data, increasing wasted space.

The other option is to making sure the data is less packed so that ITL can grow enough to accommodate the surges in ITL. This can be done by increasing PCTFREE, increasing FREELISTS and FREELIST GROUPS parameters for a table. This will make a block hold less data and more room for the ITL to grow. As a direct result of the reduction in packing, the table will experience fewer buffer busy wait events, and performance will be increased.

How to Diagnose the ITL Wait

How do you know that a segment is experiencing ITL waits? The best answer will be found in the Segment Level Statistics provided in Oracle9i Release 2. To check for ITL waits, set up the STATISTICS_LEVEL to TYPICAL in init.ora or via ALTER SYSTEM, then examine the segment statistics for the waits.

SELECT OWNER, OBJECT_NAME
FROM V$SEGMENT_STATISTICS
WHERE STATISTIC_NAME = 'ITL waits'                                     
AND VALUE > 0

This unearths the objects that were subjected to ITL waits since the start up of the instance. Note that this view resets when the instance bounces. (For a more detailed explanation of this view and how to set it up, please refer to the article by this author here in DBAzine.)

In versions earlier than 9i, checking for ITL waits is tricky. When you suspect that a database is suffering from these waits, query the view v$session_wait. If the event on which the system is waiting is "enqueue," then the session might be experiencing ITL waits. However, enqueue is a very broad event that encompasses any type of locks, so it does not accurately specify the ITL waits. When the wait event is experienced, issue the following complex query:

Select s.sid            SID,

      s.serial#   Serial#,

      l.type            type,

      ' '         object_name,

      lmode             held,

      request     request

      from v$lock l, v$session s, v$process p

      where s.sid = l.sid and

            s.username <> ' ' and

            s.paddr = p.addr and

            l.type <> 'TM' and

            (l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)

union

select      s.sid       SID,

      s.serial#   Serial#,

      l.type            type,

      object_name object_name,

      lmode       held,

      request           request

      from v$lock l, v$session s, v$process p, sys.dba_objects o

      where s.sid = l.sid and

            o.object_id = l.id1 and

            l.type = 'TM' and

            s.username <> ' ' and

            s.paddr = p.addr

union

select      s.sid       SID,

      s.serial#   Serial#,

      l.type            type,

      '(Rollback='||rtrim(r.name)||')' object_name,

      lmode             held,

      request           request

      from v$lock l, v$session s, v$process p, v$rollname r

      where s.sid = l.sid and

            l.type = 'TX' and

            l.lmode = 6 and

            trunc(l.id1/65536) = r.usn and

            s.username <> ' ' and

            s.paddr = p.addr

order by 5, 6

/

The output of the query will look something like this:

  SID SERIAL# TY OBJECT_NAM       HELD  REQUEST

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

   36    8428 TX                     0        4

   36    8428 TM TAB1                3        0

   52   29592 TM TAB1                3        0

   52   29592 TX (Rollback=RBS1_6)   6        0

Note how the sessions 36 and 52 both have a TM (DML) lock on the table TAB1 of type 3 (Row Exclusive), but session 52 also holds a TX (Transaction) lock on the rollback segment of mode 6 (Exclusive) and Session 36 is waiting for a mode 4 (Share) lock. If this combination of locking occurs, you can be sure that session 36 is waiting for ITL on the table TAB1. Beware of a similar but different diagnosis when two sessions try to insert the same key value (a real locking – primary key violation). In that case, you would also see an additional TX lock on a rollback segment from the session that is waiting; for ITL waits, this TX lock on the rollback segment would not be seen.

Needless to say, this is a rather convoluted and inaccurate way to diagnose the ITL waits in pre-Oracle9i Release 2 versions.

What INITRANS Value is Optimal?

Conversely, how do you know if the INITRANS setting is too high and the space is just being wasted? Ascertaining this is possible by using a few random block dumps from the segment in question. First, find out the header file# and header block# of the segment by issuing the following query:

SELECT HEADER_FILE, HEADER_BLOCK                                  
FROM DBA_SEGMENTS
WHERE OWNER = '...'
AND SEGMENT_NAME = '...';

Use the output of the query to do a block dump of the header block.

ALTER SYSTEM DUMP DATAFILE <file#> BLOCK MIN <block#> BLOCK MAX <block#>;

This will produce a trace file in the USER_DUMP_DESTINATION directory. Open the trace file and find out the section on extent control via the following:

Extent Control Header

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

Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 10 

last map 0x00000000 #maps: 0 offset: 2080 

Highwater:: 0x02011f87 ext#: 0 blk#: 0 ext size: 10 

#blocks in seg. hdr's freelists: 0 

#blocks below: 0 

mapblk 0x00000000 offset: 0 

Unlocked

Map Header:: next 0x00000000 #extents: 1 obj#: 53689 flag: 0x40000000

Extent Map

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

0x02011f87 length: 10

Find out the real number of blocks for the segment from dba_segments via the following:

SELECT BLOCKS FROM DBA_SEGMENTS

WHERE OWNER = '...' AND SEGMENT_NAME = '...';

Say this returns 12, and the #blocks shows 10; this means the first two blocks are header blocks; the data starts at the third block. Take a dump of the third block, which is obtained by adding two to the header block# obtained above.

ALTER SYSTEM DUMP DATAFILE <file#> BLOCK MIN <block#> BLOCK MAX <block#>;

This will produce another trace file in the USER_DUMP_DEST directory. If you issued it during the same session as above, then the trace will be written in the trace file opened earlier. Open the file and locate the following section.

buffer tsn: 8 rdba: 0x02011f88 (8/73608)

scn: 0x0000.389b8d81 seq: 0x01 flg: 0x04 tail: 0x8d810601

frmt: 0x02 chkval: 0x2ef5 type: 0x06=trans data

Block header dump: 0x02011f88

Object id on Block? Y

seg/obj: 0xd1ad csc: 0x00.389b8d81 itc: 4 flg: - typ: 1 - DATA

fsl: 0 fnx: 0x0 ver: 0x01

Itl  Xid                 Uba                Flag Lck Scn/Fsc

0x01 0x0003.003.000024cc 0x00804067.050a.13 C-U- 0 scn 0x0000.389b304e

0x02 0x0007.010.00002763 0x00801f49.0453.01 C--- 0 scn 0x0000.389b2628

0x03 0x0002.00a.000025d5 0x00804d42.04b2.25 C--- 0 scn 0x0000.389b2811

0x04 0x0006.006.00002515 0x00800962.03c8.18 CU 0 scn 0x0000.389b3044

This shows some very important information on the block, especially in the ITL section shown above. This table has an INITRANS entry of four, so there are four lines, one each per the ITL. The Flag column above the flag -U- indicates that the particular ITL was used. In this case, only two of the ITLs were used, and the other two were never used. However, this is the case for this block only. By selecting block dumps from other randomly selected blocks, you could have an idea how many ITLs are actually used. Then you may decide to reduce the INITRANS.

Automatic Block Management in Oracle9i

In Oracle9i, the process of space mangement inside a block is somewhat changed due to the introduction of the Automatic Block Management (ABM) feature, also known as Automatic Segment Space Management (ASSM). The option is specified at the tablespace level in the storage parameter as SEGMENT SPACE MANAGEMENT AUTO. For instance, the tablespace TS1 can be created as

CREATE TABESPACE TS1 

DATAFILE '...'

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

The last line of this code does the magic. In the ABM mode, Oracle maintains a bitmap for each segment with the information on the block. A bitmap is a data structure with a bit representing each block. When a block becomes available for INSERT, the information is made available simply by setting the corresponding bit in the bitmap rather than using freelists. So, what does this have to do with ITL waits? The very cause of ITL waits is not freespace management, but the unavailability of a slot in ITL waits. So you still have to look for ITL waits and correct them using INITRANS and MAXTRANS. In fact, the problem may become exacerbated because the block becomes quite packed following an efficient space management system, and that may lead to lack of space for ITL growth. You can prevent this by keeping a large INITRANS for the segment.

Conclusion

Proper setting of INITRANS and MAXTRANS and packing of the blocks is vital to avoid ITL waits in Oracle. It's interesting to note that locking doesn't cause waits, but rather, the mechanism for locking as well as and poor planning. However, the good news is that this situation can be easily fixed by reorganizing the table and adding more slots to the Interested Transaction List.

--

Arup Nanda has been an Oracle DBA for 10 years. He is the founder of Proligence, Inc., a New York-area company that provides specialized Oracle database services. He is also an editor of SELECT, the journal of the International Oracle User Group. He has written Oracle technical articles in several national and international journals like SELECT, Oracle Scene, SQLUPDATE and presented in several conferences like Oracle Technology Symposium, Atlantic Oracle Training Conference, IOUG Live! among others. Send feedback to Arup about this article at arup@proligence.com. Based on the feedback, an updated copy of this article can be found at www.proligence.com.