Shulou(Shulou.com)06/01 Report--

1. ITL description:

ITL (Interested Transaction List) is a part of the Oracle data block, which is located in the data block (block header). Itl is composed of xid,uba,flag,lck and scn/fsc, which is used to record all transactions in the block. An itl can be regarded as a transaction record. Of course, if the transaction has been committed, then the location of the itl can be used repeatedly, because the itl is similar to records, so it is sometimes called the itl slot. If a transaction has not been committed, then the transaction will always occupy an itl slot, and the itl records the transaction information, the entry of the rollback segment, the transaction type, and so on. If the transaction has been committed, then the itl slot also holds the SCN number when the transaction was committed.

The minimum number of ITL is 1, which is controlled by the parameter initrans (because of compatibility, oracle allocates two itl in the storage block of the object, so the minimum value of initrans is actually 2), the maximum value is 255.It is controlled by the parameter maxtrans, and the maximum parameter cannot be modified after 10g. Itl is a concept of block level, and an itl occupies the space of block 46B. The parameter initrans means that a portion of the storage space except block header in the block cannot be recorded (46B*initrans). When there is still a certain free space in the block, oracle can use free space to build itl for transaction use. Without free space, itl waiting may occur because the block cannot allocate a new itl.

If in a system with a particularly large concurrency, it is best to allocate enough itl, in fact, it does not waste too much space, or, set enough pctfree to ensure that the itl can be expanded, but the pctfree may be consumed by row data, such as update, so it is also possible to cause insufficient space inside the block and cause itl to wait.

For a block of dump, you can see the ITL information similar to the following:

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0006.002.0000158e 0x0080104d.00a1.6e-- U734 fsc 0x0000.6c9deff0

0x02 0x0000.000.00000000 0x00000000.0000.00-0 fsc 0x0000.00000000

Xid: transaction id. In the rollback segment transaction table, there is a record corresponding to this transaction.

Uba: the address of the rollback segment, the address of the rollback segment corresponding to the transaction

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

Second paragraph address: rollback serial number

Address of the third paragraph: rollback record number

SELECT UBAFIL rollback segment file number, UBABLK block number, UBASQN rollback sequence number, UBAREC rollback record number FROM v$transaction-View UBA

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

-= the transaction is active or committed before the block is cleared

Cmurmer-= the transaction has been committed and the row lock has been cleared.

-Bmuri-= this undo record contains the undo for this ITL entry

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

-T = when the SCN of block cleanup is recorded, the transaction is still active. If there is a committed transaction on the block, the block will be cleared during clean ount, but the transaction in this block will not be cleared.

Lck: number of records affected

Scn/Fsc: SCN or Commit SCN for Fast Commit Fsc.

The row-level lock in each record corresponds to the sequence number in the Itl list, that is, which transaction generated the lock on that record.

II. ITL waiting

The scenario in which the wait occurs:

1. Exceeds the maximum number of ITL configured by maxtrans

Insufficient 2.initrans, not enough free space to extend ITL

Solution:

Insufficient 1.maxtrans: this situation is caused by high concurrency: the number of transactions on the same data block has exceeded the actual number of ITL allowed. Therefore, to solve this kind of problems, we need to start from the application, reduce the concurrency of transactions; long transactions, on the premise of ensuring data integrity, increase the frequency of commit, modify to short transactions, and reduce resource occupation events. For OLAP systems (for example, there is a data entry module with high concurrency), you can consider increasing the block size.

Insufficient 2.initrans: the number of ITL on the block does not reach the MAX TRANS limit, and tables that occur are usually UPDATE frequently, causing the reserved space (PCTFREE) to be filled. If we find that this kind of ITL wait has affected the system, we can increase the INITRANS or PCTFREE of the table (depending on the amount of concurrent transactions on the table, generally, if the concurrency is high, it is recommended to increase the INITRANS first, otherwise, the increase of PCTFREE is preferred).

It is important to note that if you use ALTER TABLE to modify these two parameters, it will only affect the new data block, not the existing data block-- to do this, you need to export / import the data and rebuild the table.

How to achieve pre-ITL read consistency after ITL reuse:

ORACLE finds the rollback segment through the address of the rollback segment recorded in the ITL entry to achieve read consistency. If the transaction has been committed, the ITL can be reused, but if the previous ITL is reused, how can the read consistency of the previous ITL be achieved?

Suppose that block has only one itl, and that ITL-0 is generated in the first transaction.

The second transaction comes, and the ITL-1 is generated. The UBA in the ITL-1 can find the address of the rollback segment, and the rollback segment records the information of the ITL-0 in addition to the before image that records the block user data.

The third transaction comes, and the ITL-2 is generated. The UBA points to the rollback segment in the ITL-2, and the information of the ITL-1 is also recorded in the rollback segment.

In this way, when a query needs the information of ITL-0, it finds the current block and finds that it is ITL-2. According to the UBA, it finds the rollback segment and roll to get the block before the change. At this time, it is found that the block is ITL-1. It can't meet the demand yet. Then according to the UBA in ITL-1 to find the data in the rollback section to roll, get a block data, at this time there is ITL-0 in the block.

The read consistency of the previous ITL is achieved by finding the data recursively according to the current ITL.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.