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

This article mainly explains "what is Oracle ITL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is Oracle ITL".

1. ITL description:

ITL (Interested Transaction List) is an internal part of Oracle data block, which is located in the data block head (block header). Itl is composed of xid (Transaction XID), uba (Undo block address), flag,lck and scn/fsc.

Used to record all transactions that occur in the block, an itl can be thought of 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.

Normal0falsefalsefalseMicrosoftInternetExplorer4

The minimum number is 1, which is controlled by the parameter initrans (due to 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. The maximum parameter cannot be modified after 10g.

Itl is a block-level concept. An itl occupies the space of block 46B, and the parameter initrans means that in the block

Part of the storage space cannot be recorded except for block header (46B*initrans)

When there is still a certain free space in the block, oracle can use free space to build itl for transaction use

If there is no free space, an itl wait 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 www.2cto.com

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 cleared by the block is recorded, the transaction is still active, if there is a committed transaction on the block

Then in clean ount, the block will be cleared, but the transactions 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 is caused by high concurrency: the number of transactions on the same data block

The number of ITL actually allowed has been exceeded. Therefore, in order to solve this kind of problems, we need to start with application.

Reduce the concurrency of transactions and increase the frequency of commit on the premise of ensuring data integrity

Modify to short transactions to reduce resource occupancy 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 limit of MAX TRANS

Tables where this happens are usually UPDATE frequently, causing the reserved space (PCTFREE) to be filled up.

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). Www.2cto.com

One thing to note is that if you use ALTER TABLE to modify these two parameters

It only affects 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 ITL-1 can find the address of the rollback segment.

The rollback segment records not only the before image of block user data, but also the information of ITL-0.

The third transaction comes, and the ITL-2 is generated, and the UBA in ITL-2 points to the rollback segment.

The information of ITL-1 is also recorded in the rollback segment.

In this way, when a query needs information about ITL-0, it finds the current block and finds that it is ITL-2.

According to the UBA to find the rollback segment and roll to get the pre-change block, 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 unique consistency of the previous ITL is achieved by finding the data recursively according to the current ITL.

/ * Style Definitions * / table.MsoNormalTable {mso-style-name: "Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent: ""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt Mso-pagination:widow-orphan; font-size:10.0pt; font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400 Thank you for your reading. The above is the content of "what is Oracle ITL". After the study of this article, I believe you have a deeper understanding of what Oracle ITL is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.