How Oracle locks rows on behalf of transactions? How does the data block grant the locks? Where will it store the locking information for the rows the transaction is asking for? Here comes the structure – Interested Transaction List (ITL). This is a small structure in oracle block, which holds the information about the list of transactions, which are interested in some of the rows of that block. That’s why it’s called Interested Transaction List. ITL is present in the variable portion of the Oracle block.
Variable header info grows from the top down (just below the fixed header) if necessary and rows are inserted from the bottom up (just above the tail but depends on PCTFREE and also the size of variable part of data block header cannot exceed 50% of block size. ). It is this portion of data block, which holds the information about locking.
This variable portion of the data block contains slots for transactions to put the locking information. 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.
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 if there are not enough slots available in ITL? What will happen? Obviously the new transaction asking for the lock on the rows have to wait until the existing transaction is finished and frees up the slot. This is called ITL wait event. Once the existing transaction finishes and frees up the slot, the new transaction can proceed.
When the ITL can’t grow due to the lack of space in the block, the session waits will the event “enq: TX - allocate ITL entry”
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 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 this also means that there is less space in the block for actual data, increasing wasted space.
Note:- MAXTRANS parameter starting from 10G is deprecated.Whatever value we set it is completely ignored by oracle.
Source:- http://avdeo.com , http://www.proligence.com/itl_waits_demystified.html
Variable header info grows from the top down (just below the fixed header) if necessary and rows are inserted from the bottom up (just above the tail but depends on PCTFREE and also the size of variable part of data block header cannot exceed 50% of block size. ). It is this portion of data block, which holds the information about locking.
This variable portion of the data block contains slots for transactions to put the locking information. 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.
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 if there are not enough slots available in ITL? What will happen? Obviously the new transaction asking for the lock on the rows have to wait until the existing transaction is finished and frees up the slot. This is called ITL wait event. Once the existing transaction finishes and frees up the slot, the new transaction can proceed.
When the ITL can’t grow due to the lack of space in the block, the session waits will the event “enq: TX - allocate ITL entry”
ITL Wait in detail
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 below.
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.
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.
Then another transaction, Txn2, updates the row Row2 and wants to lock the row. However, there are no more slots in the ITL available to service the transaction. The MAXTRANS entry is 11, meaning the ITL can grow up to 11 slots and the block has empty space. Therefore, ITL can grow by another slot and Slot number two is created and allocated to Txn2 (refer to figure 4).
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.
How to Reduce ITL Waits
The primary cause of ITL waits is the unavailability of free slots in the ITL. These 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 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 this also means that there is less space in the block for actual data, increasing wasted space.
Note:- MAXTRANS parameter starting from 10G is deprecated.Whatever value we set it is completely ignored by oracle.
Source:- http://avdeo.com , http://www.proligence.com/itl_waits_demystified.html
No comments:
Post a Comment