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

This article is about how to understand enq: TX-index contention waiting discussion and test, the editor feels very practical, so share with you to learn, I hope you can learn something after reading this article, say no more, follow the editor to have a look.

Discussion and Test on enq: TX-index contention waiting

Recently, there was a short wait for enq: TX-index contention on the production database, which caused the database hang to live:

The wait event is explained as follows:

Waits for TX in mode 4 also occur when a transaction inserting a row in an index has to wait for the end of an index block split being done by another transaction. This type of TX enqueue wait corresponds to the wait event enq: TX-index contention.

It can be considered that when a session inserts into an index block, the split of the index block is generated, and other session also inserts data into the index block. At this time, the other session must wait for the split to complete, thus raising the wait event.

When the transaction modifies the data in the index, and the relevant index block does not have enough space, the segmentation of the index block will occur. In the process of segmentation, the foreground process needs to wait for the segmentation to be completed before continuing the operation.

If other sessions also want to modify the data of this index block at this time, then there will be competition for index blocks. (enq: TX- index contention) In general, the segmentation of index blocks holds resources and releases very short, and will not have a serious impact on the database. However, a large amount of concurrency of table operations may lead to serious competition.

1. Create a test table

SQL > CREATE TABLE TEST (ID INT,NAME VARCHAR2 (50), CREATED DATE)

Table created.

SQL > BEGIN

2 FOR I IN 10000.. 20000 LOOP

3 INSERT INTO TEST VALUES (I, RPAD (I, 50,'X'), SYSDATE)

4 END LOOP

5 END

6 /

PL/SQL procedure successfully completed.

SQL > commit

Commit complete.

SQL > select count (*) from test

COUNT (*)

-

10001

SQL > select min (id), max (id) from test

MIN (ID) MAX (ID)

--

10000 20000

SQL > CREATE INDEX IDX_TEST_01 ON TEST (ID,NAME) PCTFREE 0

Index created.

First, you create a test table, TEST, and insert 10001 records into it, with a minimum ID of 10000 and a maximum of 20000. Then an ascending index is created on the ID,NAME column of TEST. At this point, the data in the index will be sorted first by ID and then by NAME column. Notice that I set PCTFREE to 0. This will cause the space of the leaf node block to fill up, except for the rightmost leaf block of the B-tree index (which may or may not be filled). The preparatory work is complete.

two。 Information about the index

First, let's analyze the situation of this index.

SQL > ANALYZE INDEX IDX_TEST_01 VALIDATE STRUCTURE

Index analyzed.

SQL > set lines 200

QL > SELECT HEIGHT,BLOCKS,NAME,PARTITION_NAME,LF_ROWS,DEL_LF_ROWS,LF_BLKS,PCT_USED FROM INDEX_STATS

HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS DEL_LF_ROWS LF_BLKS PCT_USED

2 88 IDX_TEST_01 10001 0 85 98

As you can see, the binary height of this index is 2 minutes blocks, the number of blocks is 88 (including root blocks, branch blocks, leaf blocks and some overhead blocks), the number of records of leaf blocks is 10001, and the number of leaf blocks is 85, because the space of the last leaf block has not been used up, so

Instead of 100%, PCT_USED shows 98%.

Percentage of space used by PCT_USED percent of space allocated in the b-tree that is being used

3. The influence of new records on index

SQL > INSERT INTO TEST VALUES (2001recorder RPAD (2000150recorder X'), SYSDATE)

1 row created.

SQL > commit

Commit complete.

Since 20001 is larger than the maximum value of 20000 in the table, the data will be inserted into the rightmost leaf node of the index number. Because the last leaf node of the index tree has free space to hold the record, the data can be inserted smoothly.

The number of leaf blocks in the index does not change.

SQL > ANALYZE INDEX IDX_TEST_01 VALIDATE STRUCTURE

Index analyzed.

SQL > SELECT HEIGHT,BLOCKS,NAME,PARTITION_NAME,LF_ROWS,DEL_LF_ROWS,LF_BLKS,PCT_USED FROM INDEX_STATS

HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS DEL_LF_ROWS LF_BLKS PCT_USED

--

2 88 IDX_TEST_01 10002 0 85 98

You can see that the number of records in the leaf blocks of the index has increased by 1 to 10002, but the number of leaf blocks has remained unchanged at 85.

-- if we perform the following SQL: INSERT INTO TEST VALUES (999999RPAD), SYSDATE)

Since 9999 is smaller than the minimum ID value of 10000 in the table, the data will be inserted into the leftmost leaf node of the index number. At this time, the leftmost leaf node of the index has no free space to accommodate the record, and the data cannot be inserted. ORACLE will perform a 5-5 split of the index block in the background, putting about half of the data into the new index block, and the original data will remain in the index block. Then insert 9999 of the records into the appropriate block.

SQL > INSERT INTO TEST VALUES (9999 ~ Rpad), SYSDATE)

1 row created.

SQL > commit

Commit complete.

SQL > ANALYZE INDEX IDX_TEST_01 VALIDATE STRUCTURE

Index analyzed.

SQL > SELECT HEIGHT,BLOCKS,NAME,PARTITION_NAME,LF_ROWS,DEL_LF_ROWS,LF_BLKS,PCT_USED FROM INDEX_STATS

HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS DEL_LF_ROWS LF_BLKS PCT_USED

--

2 88 IDX_TEST_01 10003 0 86 97

As you can see, the number of records in the leaf block of the index has increased by 1 to 10003, and the number of leaf blocks has increased to 86, that is, the segmentation of the index block causes a data block to be divided into two.

-- what happens if you continue to insert the following SQL statement at this point? INSERT INTO TEST VALUES (9998 SYSDATE Rpad)

Since the leftmost block has just been split, one block is split in two. So now the two blocks on the left still have about half the free space. So there is enough room to hold the record 9998.

As follows:

SQL > INSERT INTO TEST VALUES (9998 SYSDATE Rpad)

1 row created.

SQL > commit

Commit complete

SQL > ANALYZE INDEX IDX_TEST_01 VALIDATE STRUCTURE

Index analyzed.

SQL > SELECT HEIGHT,BLOCKS,NAME,PARTITION_NAME,LF_ROWS,DEL_LF_ROWS,LF_BLKS,PCT_USED FROM INDEX_STATS

HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS DEL_LF_ROWS LF_BLKS PCT_USED

--

2 88 IDX_TEST_01 10004 0 86 97

As you can see, the record increased, but the leaf block did not increase.

-- if you insert the following SQL:INSERT INTO TEST VALUES (14998 SYSDATE Rpad)

According to the previous analysis, and the current idle situation of the index block, the index block segmentation will also be carried out at this time.

SQL > select sid from v$mystat where rownum INSERT INTO TEST VALUES (14998 SYSDATE Rpad)

1 row created.

SQL > COMMIT

Commit complete.

SQL > COL NAME FORMAT A20

SQL > ANALYZE INDEX IDX_TEST_01 VALIDATE STRUCTURE

Index analyzed.

SQL > SELECT HEIGHT,BLOCKS,NAME,PARTITION_NAME,LF_ROWS,DEL_LF_ROWS,LF_BLKS,PCT_USED FROM INDEX_STATS

HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS DEL_LF_ROWS LF_BLKS PCT_USED

--

2 96 IDX_TEST_01 10005 0 87 96

You can see that the index block is segmented again.

You can also query the split number of index blocks through the following SQL statement.

SQL > SELECT B.NAME, A.VALUE

FROM v$SESSTAT A, V$STATNAME B

WHERE A.STATISTIC# = B.STATISTIC#

AND B.NAME LIKE'% split%'

AND A.SID = 30

NAME VALUE

-

Leaf node splits 2

Leaf node 90-10 splits 0

Branch node splits 0

Root node splits 0

Queue splits 0

Note: UPDATE can also cause index blocks to be split. For indexes, UPDATE is actually a DELETE plus an INSERT statement.

4. Concurrency raising enq: TX-index contention

Whenever there is no space in the index block to accommodate the new data, the segmentation of the index block occurs. If other processes also have to manipulate the corresponding index blocks during the segmentation process, then other processes will be waiting for enq:TX-index contention.

For demonstration purposes, rebuild to create a slightly larger table

SQL > DROP TABLE TEST

Table dropped.

SQL > CREATE TABLE TEST (ID NUMBER,NAME CHAR (10), CREATED DATE,CONTENTS VARCHAR2 (4000))

Table created.

SQL > CREATE INDEX IDX_TEST_01 ON TEST (CREATED,CONTENTS)

Index created.

-- divided into two windows: session 1:26 session:33

First, the statistics of the two sessions about index segmentation are as follows:

SELECT A.SID, B.NAME, A.VALUE

FROM v$SESSTAT A, V$STATNAME B

WHERE A.STATISTIC# = B.STATISTIC#

AND B.NAME LIKE'% split%'

AND A.SID IN (26 and 33)

ORDER BY 1, 2

SID NAME VALUE

-

26 branch node splits 0

26 leaf node 90-10 splits 0

26 leaf node splits 0

26 queue splits 0

26 root node splits 0

33 branch node splits 0

33 leaf node 90-10 splits 0

33 leaf node splits 0

33 queue splits 0

33 root node splits 0

10 rows selected.

-- then insert records into the table in both session 1 and session 2, and open a window to monitor wait events while inserting data

BEGIN

FOR I IN 0.. 100000 LOOP

INSERT INTO TEST VALUES (I, TO_CHAR (I), SYSDATE, RPAD ('Xrays, 2000,' X'))

END LOOP

END

/

Session 1: 26

SQL > SELECT USERENV ('SID') FROM DUAL

USERENV ('SID')

-

twenty-six

SQL > BEGINSQL > BEGIN

2 FOR I IN 0.. 60000 LOOP

3 INSERT INTO TEST VALUES (I, TO_CHAR (I), SYSDATE, RPAD ('Xrays, 2000,' X'))

4 END LOOP

5 END

6 /

Session 2:33

SQL > SELECT USERENV ('SID') FROM DUAL

USERENV ('SID')

-

thirty-three

SQL > BEGIN

2 FOR I IN 0.. 60000 LOOP

3 INSERT INTO TEST VALUES (I, TO_CHAR (I), SYSDATE, RPAD ('Xrays, 2000,' X'))

4 END LOOP

5 END

6 /

-- the query wait event before insertion is as follows:

SQL > set lines 200

SQL > col event for A30

SQL > col machine for A15

SQL > select inst_id,sid,sql_id,status,machine,event,blocking_session,wait_time,state,seconds_in_wait from gv$session where event like 'enq: TX-index contention'

No rows selected

SQL >

-- query wait event during insertion:

SQL > set lines 200

SQL > col event for A30

SQL > col machine for A15

SQL > select inst_id,sid,sql_id,status,machine,event,blocking_session,wait_time,state,seconds_in_wait from gv$session where event like 'enq: TX-index contention'

No rows selected

SQL > /

No rows selected

SQL > /

No rows selected

SQL > /

No rows selected

SQL > /

INST_ID SID SQL_ID STATUS MACHINE EVENT BLOCKING_SESSION WAIT_TIME STATE SECONDS_IN_WAIT

1 33 41vqxgnub01q1 ACTIVE wang enq: TX-index contention 26 0 WAITING 0

SQL > select sql_text from v$sql where sql_id='41vqxgnub01q1'

SQL_TEXT

-

INSERT INTO TEST VALUES (: B1, TO_CHAR (: B1), SYSDATE, RPAD ('Xrays, 2000,' X'))

SQL >

The wait event is described as follows:

Enq: TX-index contention

Waits for TX in mode 4 also occur when a transaction inserting a row in an index has to wait for the end of an index block split being done by another transaction. This type of TX enqueue wait corresponds to the wait event enq: TX-index contention.

Note: if there is no space allocation transaction slot in the index block, it will also cause competition for enq: TX-allocate ITL entry.

SQL > l

1 SELECT A.SID, B.NAME, A.VALUE

2 FROM v$SESSTAT A, V$STATNAME B

3 WHERE A.STATISTIC# = B.STATISTIC#

4 AND B.NAME LIKE'% split%'

5 AND A.SID IN (26 and 33)

6 * ORDER BY 1,2

SQL > /

SID NAME VALUE

-

26 branch node splits 7334

26 leaf node 90-10 splits 19705

26 leaf node splits 20142

26 queue splits 0

26 root node splits 6

33 branch node splits 7414

33 leaf node 90-10 splits 19918

33 leaf node splits 20149

33 queue splits 0

33 root node splits 2

10 rows selected.

Looking at the results, I saw a lot of splits.

From the crawled ash report, the waiting is generated by an insert statement, and the table into which the sql inserts data is a table that requires frequent delete every day, and the generation of the waiting event is closely related to frequent large quantities of delete. The final recommendation from the vendor is to rebuild the table periodically and increase the pctfree of the index.

Enq: TX-index contention

Most probable reasons are

O Indexes on the tables which are being accessed heavily from the application. O Indexes on table columns which are monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.

O Large data purge has been performed, followed by high concurrent insert (mass concurrent insert)

When running an OLTP systems, it is possible to see high TX enqueue contention on index associated with tables, which are having high concurrency from the application. This usually happens when the application performs lot of INSERTs and DELETEs concurrently. For RAC system, the concurrent INSERTs and DELETEs could happen from all the instances.

The reason for this is the index block splits while inserting a new row into the index. The transactions will have to wait for TX lock in mode 4, until the session that is doing the block splits completes the operations. (splitting of index blocks)

A session will initiate an index block split, when it can'??t find space in an index block where it needs to insert a new row. Before starting the split, it would clean out all the keys in the block to check whether there is enough sufficient space in the block.deleted

Splitter has to do the following activities:

O Allocate a new block.

O Copy a percentage of rows to the new buffer.

O Add the new buffer to the index structure and commit the operation.

In RAC environments, this could be an expensive operation, due to the global cache operations included. The impact will be more if the split is happening at a branch or root block level.

Solutions: the solution

A) Rebuild the index as reverse key indexes or hash partition the indexes which are listed in the Segments by Row Lock Waits' of the AWR reports re-index

B) Consider increasing the CACHE size of the sequences increases the value of Cache

C) Index contraction or reconstruction after mass data changes in Rebuild or shrink associated index after huge amount of data purge

D) Increase PCT_FREE for the index increases the PCT_FREE of the index block

The above is how to understand the discussion and testing of enq: TX-index contention waiting. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.