Shulou(Shulou.com)05/31 Report--

How to carry out ITL and transaction processing, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

I. the relationship between ITL and transaction

The ITL (interested transaction list) transaction slot is an internal part of the Oracle data block and is located in the data block header (block header). ITL consists of xid, uba, flag, lck, and SCN/fsc to record all transactions that occur on that block. An ITL slot can be regarded as a transaction record, which is a key component of transaction processing in Oracle. If the transaction has been committed, the ITL slot can be used repeatedly. If the transaction has not been committed, the ITL slot has been occupied, recording transaction information, rollback segment entry, transaction type and so on. After the transaction is committed, the ITL slot still holds the SCN number when the transaction was committed.

The minimum value of ITL is 1, which is controlled by the parameter initrans (due to compatibility, Oracle allocates two ITL on the storage block of the object, so the minimum value of inittrans is actually 2). This is also the default value when you do not specify the initrans parameter when creating the table, and the maximum value is controlled by the parameter maxtrans. The maximum parameter cannot be modified after Oracle 10g. An ITL takes up the space of block 46B, and when there is still a certain amount of 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.

When a user issues a SQL statement, Oracle will record the SCN of this moment, then find the required block in buffer cache, or read it from disk. When another session modifies the data, or is in the process of modifying the data, it will record ITL on the corresponding block. When Oracle finds that the SCN recorded in ITL is greater than the SCN at the time of select, then Oracle will find the undo information according to the uba recorded in ITL and obtain the front image of the block. Then construct the CR (consistent read) block in the buffer cache, and Oracle will also check the SCN recorded by the ITL in the constructed block. If the SCN is still greater than the SCN at the time of select, it will continue to repeat the construction of the pre-image until the SCN recorded by the ITL in the front image is less than the SCN at the time of select. At the same time, check whether the transaction is committed or rolled back, and if not, continue to construct the pre-image until the desired block is found. If the undo information required during the mirroring process before construction is overwritten, the snapshot will be reported as an old error. So Oracle implements multi-version control, which is the nature of multiple versions of Oracle, which is why you always see consistent gets when you issue a select statement.

II. ITL waiting

There are two scenarios in which ITL waiting occurs:

1. The maximum number of ITL configured by maxtrans has been exceeded

2. Initrans is not configured enough, and there is not enough free space to expand ITL.

Solution:

Insufficient maxtrans: due to high concurrency, the number of transactions on the same data block has exceeded the allowed number of ITL. Therefore, it is necessary to reduce the concurrency of transactions. For long transactions, under the premise of ensuring data integrity, increase the frequency of commit, turn long transactions into short transactions, in order to reduce resource consumption.

Insufficient initrans: the number of ITL on the data block does not reach the maxtrans limit, and the table in which this occurs is usually filled with more update, causing the reserved space pctfree (default 10%) to be filled. At this time, you can increase the initrans or pctfree of the table to solve the problem. If the concurrency of transactions on the table is high, you can first increase the initrans and increase the initial allocation of ITL slots. Otherwise, you can give priority to increasing pctfree to improve the scalability of ITL slots.

Note: if you modify these two parameters of the table through alter table, only the new data block will be affected, not the existing data block.

Third, the experiment verifies the relationship between ITL and transaction.

Connect to scott user

Sqlplus scott/tiger

Create a test table with pctfree set to 0

Create table T1 (a number, b varchar2 (30)) pctfree 0

Begin

For i in 1.. 1000 loop

Insert into T1 values (I, 'data')

End loop

Commit

End

/

View interval allocation information for segments

Col segment_name for a20

Col tablespace_name for a20

Select segment_name, segment_type, tablespace_name, extent_id, file_id, block_id, blocks, bytes from dba_extents where owner = 'SCOTT' and segment_name =' T1'

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS BYTES

--

T1 TABLE USERS 0 4 168 8 65536

View block allocation information

Select distinct dbms_rowid.rowid_block_number (rowid) from scott.t1

DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID)

-

one hundred and seventy one

one hundred and seventy four

It can be seen that the data of the T1 table occupies two data blocks, and the starting number of the first interval of the block number 171 and 174 T1 segment is 168, which is composed of 8 data blocks.

Let's perform multiple transactions on the same data block 171 at the same time to see what happens.

Session1:

Update scott.t1 set b = 'Oracle data' where a 10 and a 20 and a

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.