Understanding of Oracle ITL (transaction slots)

First, ITL describes:

The ITL (interested Transaction List) is an integral part of the Oracle data block, located in the block header, which consists of Xid,uba,flag,lck and SCN/FSC, Used to record all occurrences of the block, an ITL can be viewed as a transaction record. Of course, if the transaction has already been committed, then the ITL position can be reused, because ITL resembles the record, so sometimes it is called an ITL slot. If a transaction has not been committed, then the transaction will always occupy an ITL slot, the transaction information is recorded in ITL, the entry of the rollback segment, the type of transaction, and so on. If the transaction has already been committed, then the SCN number in the ITL slot is also saved with the transaction submission time.

ITL has a minimum value of 1, controlled by the parameter Initrans (for compatibility reasons, Oracle allocates two ITL in the object's storage block, so the minimum value of Initrans is actually 2), the maximum value is 255, and is controlled by the parameter Maxtrans. The maximum parameter can not be modified after 10g, ITL is the concept of block-level, an ITL occupies 46B of space, the parameter Initrans means that the block is removed from the header part of the storage space can not be used (46b*initrans), When there is a certain free space in the block, Oracle can use free space to build the ITL for transactional use, and if no space is available, the block may have ITL waiting because it cannot allocate a new ITL.
If the concurrency is particularly large in the system, it is best to allocate enough ITL number, in fact, it does not waste too much space, or, set enough pctfree to ensure that ITL can be extended, but Pctfree is likely to be consumed by the data of the row, such as update, so, It is also possible that there is not enough space inside the block to cause ITL to wait.

Dump a block to see the ITL information similar to the following:
Itl Xid Uba Flag Lck SCN/FSC
0x01 0x0006.002.0000158e 0x0080104d.00a1.6e--u-734 FSC 0x0000.6c9deff0
0x02 0x0000.000.00000000 0x00000000.0000.00----0 FSC 0x0000.00000000

Xid: Transaction ID, which has a record in the Rollback Segment transaction table and corresponds to this transaction

UBA: Rollback segment address, corresponding rollback segment address for the transaction

First paragraph address: The address of the rollback data block, including the rollback segment file number and the data block number

Second paragraph address: Roll back serial number

Third paragraph address: rollback record number

SELECT ubafil Rollback segment file number, UBABLK data block number, UBASQN rollback sequence number, Ubarec rollback record number from v$transaction--View Uba

Flag: The transaction flag bit. This flag bit records the operation of this transaction, the meanings of each flag are:

-----= transaction is active, or the transaction is committed before block cleanup

C---= transaction has been committed and row locking has been cleared.

-b--= This undo record contains the undo for this ITL entry

--u-= The transaction has been committed (the SCN is already the maximum), but the lock has not been cleared (fast cleanup).

---T = when the SCN of a block purge is logged, the transaction is still active, and if there are committed transactions on the block, the block is cleared when clean ount, but the transaction inside the block is not cleared.

Lck: Number of records affected

SCN/FSC: Fast commit (Fast commit FSC) SCN or commit SCN.

The row-level locks in each record correspond to the ordinal number in the ITL list, which is the lock that the transaction produces on that record.

Second, ITL waits

The scene where the wait occurs:

1. Maximum number of ITL over Maxtrans configuration

2.initrans insufficient, not enough free space to expand ITL

Workaround:

1.maxtrans Insufficient: This situation is caused by high concurrency: The amount of transactions on the same block of data has exceeded the number of ITL that it actually allows. Therefore, to solve such problems, we need to start from the application, reduce the concurrency of the transaction, long transaction, in the premise of guaranteeing data integrity, increase the frequency of commit, modify to short transaction, reduce the resource occupancy event. For OLAP systems (for example, it has a high concurrency Data entry module), you can consider increasing the size of the data block.

2.initrans Insufficient: The number of ITL on the data block does not reach Max Trans's limit, and the table that occurs usually is frequently update, resulting in the reserved space (PCTFREE) being filled. If we find that this type of ITL waits for the system to have an impact, it can be resolved by increasing the Initrans or pctfree of the table (depending on the amount of concurrent transactions on the table, usually, if the concurrency is high, it is recommended to increase the Initrans first, or vice versa).

One thing to note is that if you modify these 2 parameters by using ALTER TABLE, only the new data blocks will be affected, not the data blocks of the existing data--to do this, you need to export/import the data and rebuild the table.

How to implement the pre-ITL read consistency after ITL reuse :

Oracle finds the rollback segment through the rollback segment address recorded in the ITL entry, implements read consistency, and if the transaction is committed, the ITL can be reused, but how is the previous ITL's read consistency implemented if the previous ITL is reused?

Assuming that the block has only one ITL, the first transaction is assumed to have ITL-0

The second transaction came, produced the ITL-1, ITL-1 inside the UBA can find the rollback segment address, the rollback segment in addition to record the block user data before image also recorded ITL-0 information.

The third transaction came, resulting in a ITL-2, ITL-2 UBA point to the rollback segment, and the ITL-1 information is also recorded in the rollback segment.

In this case, if a query needs ITL-0 information, then find the current block, found that the ITL-2, according to Uba find the rollback segment to roll to change before block, this time found that block is ITL-1. is not enough to meet the demand. Then according to ITL-1 in the UBA and go back to the rolling section to find data to roll, get a block data, this time the block has ITL-0.

Data is found in a recursive manner based on the current ITL, enabling the prior ITL to be single-consistent.

Line Lock principle:

The lock mechanism of Oracle is a lightweight locking mechanism that does not lock the data by building a lock list, but instead directly stores the lock as a block of data in the block header. This is done through ITL, where a transaction modifies the data in the block and must obtain an ITL (either pre-allocated via Initrans or built through free space) in the block. The transaction table in the ITL and Undo segment header lets you know whether the transaction is in the active phase or has been completed. When a transaction modifies a block (in fact, it modifies a row), it checks the flag bit in the row header of the line, and if the flag bit is 0 (the row is not locked by the active transaction, it is possible to work with deferred block cleanout), The flag bit is modified to the ordinal of the ITL that the transaction obtains in the block, so that the current transaction has a lock on the record, and then the row data can be modified, which is the principle of the Oracle row lock implementation.

zhuanzai:http://blog.csdn.net/gyb2013/article/details/6893639