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

When a transaction DML an index block, the index block is split because there is no free space in the index block, and wait occurs when other transactions are DML the index block until the index block split is completed.

Here are several scenarios in which index split contention occurs:

1) normally, splitting an index block is millisecond. If a session holds a lock for a long time, that is to say, it takes a long time for the session to split the index block, then it is necessary to check the waiting events and read objects of the session. It is likely that it takes more time to find an available empty block in the bitmap block.

2) A large number of application concurrency leads to hot block competition, which usually occurs with the waiting of gc classes.

3) in the RAC environment, due to the contention between the global buffer block and the global queue, it will occur when there is a performance bottleneck in the private network transmission, such as large private network traffic or loss during transmission, which leads to the inability to quickly apply for an empty index block, which is usually accompanied by the waiting of the gc class.

After you have identified the cause of the index split competition, you need to check which object is waiting and confirm it from the AWR.

Segments by RowLock Waits

% of Capture shows% of row lock waits for each top segment compared

With total rowlock waits for all segments captured by the Snapshot

In addition, you can find out what kind of split is in the AWR.

Instance Activity Stats

Ordered by statistic name

According to the split object:

1) Root node splitting: a special branch node split requires two new data blocks. The original data is transferred to two new nodes, and two records are generated on the original node pointing to the new data block.

2) Branch node splitting: the split of the leaf node below it will cause a record to be added to the node pointing to the newly added node, and will split when the node space is insufficient.

3) Leaf node splitting: there is not enough space on the leaf node to accommodate the newly inserted data

Divided according to the proportion of split data blocks:

1) 9-1 split: usually a primary key unidirectional growth index, when a transaction inserts a piece of data greater than or equal to the maximum value on the existing index block into the rightmost leaf node of the index block

2) 5-5 split: usually a normal index, when the newly inserted value of the transaction into the index block is less than the maximum value in the index

Solution:

1) periodically rebuild the index for the first scenario

2) for the second scenario, create a reverse key index, which can eliminate index splitting competition, but its disadvantage is that it may affect the performance of index range scanning. In addition, the index can also be transformed into HASH partitions (usually 8 partitions). The aim is to distribute the increasing index key values to multiple partitions through the HASH algorithm, so as to avoid index splitting competition in insert or update.

3) for the third scenario, if the private network traffic is large, it is necessary to analyze whether SQL can be optimized, and if there is packet loss in the network, it needs to be analyzed by the system network layer.

Index splitting how to find a free block:

Oracle's spatial management module (metadata) uses five states in bitmap blocks BITMAPBLOCK (L3, L2, L1) to describe the space usage of data blocks, so that when inserting data, available empty blocks can be selected for reuse (no further expansion can be found). The five states are as follows:

1) 0,25% free

2) 2550% free

3) 50% 75% free

4) 75% / 100% free-75% / 100% free space in the block

5) Full Blocks-the block does not have any space and is not allowed to act as an empty block

There is no state in a bitmap block that indicates that a data block is completely empty, with only a "75 percent 100% free" state. If the data to be inserted is exactly the "75~100%free" size, a data block with a state of "75~100%free" is found in the bitmap block and then used If the data to be inserted happens to be in the "75~100%free" and a "75~100%free" block is also found, but because the data cannot be inserted into the block, Oracle updates the bitmap information of the block to the full state (although there is still room for the block), and the data block will not be used for subsequent inserts.

| | author profile |

Guan Haitao, senior database engineer of Walk Technology

Familiar with the internal mechanism of Oracle database, rich experience in database and RAC cluster layer fault diagnosis, performance tuning, OWI, database backup recovery and migration.

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.