如何理解enq: TX——索引争用等待的讨论和测试
关于enq: TX - 索引争用等待的讨论和测试
最近生产库遇到了短暂的enq: TX - 索引争用问题一段的时间。等待，导致数据库挂起：
该等待事件解释如下：
模式4中的等待TX，当事务插入时也会发生索引中的行必须等待另一个事务完成索引块拆分的结束。这种类型的 TX 排队等待对应于等待事件 enq: TX - index contention。
可以认为会话在插入到索引块时生成了索引块的分割。指数块，其他会话也向索引块插入数据。此时，其他会话必须等待拆分完成，从而触发 等待事件。
当事务修改索引中的数据并且相关索引块没有足够的空间时，索引块将被分裂。拆分过程中中前台进程需要等待拆分完成后才能继续操作。
如果此时其他会话也想修改这个索引块的数据，就会出现对索引块的竞争。 （enq：TX-索引争用）。一般情况下，索引块的划分对资源的持有和释放都很短暂，不会对数据库产生严重的影响。但如果并发表操作量较大，可能会出现严重的竞争。
1.创建测试表
SQL> CREATE TABLE TEST(ID INT,NAME VARCHAR2(50),CREATED DATE) ;
表已创建。
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 过程成功完成。
SQL> 提交；
提交完成。
< br /> SQL> select count(*) from test;
COUNT(*)
---- - -----
10001
SQL>从测试中选择min(id),max(id) ;
最小值(ID)最大值(ID)
---------- --- -- -----
10000 20000
SQL> 在 TEST(ID,NAME ) PCTFREE 上创建索引 IDX_TEST_01 0;
索引已创建。
首先创建测试表TEST，并插入10001条记录，最小ID为10000，最大值为 20000。然后在 TEST 的 ID 和 NAME 列上创建升序索引。此时，索引中的数据将排序先按ID排序，然后按NAME列排序。 注意，我将PCTFREE设置为0。这将导致叶子节点块的空间被填满除了B-最右边的叶子块之外树索引（可能被填充，也可能不被填充）。准备工作已经完成。
2.指数信息
首先我们来分析一下该指数的情况。
SQL> 分析索引 IDX_TEST_01 验证结构；
索引a已分析。
SQL> 设置第 200 行
QL> SELECT HEIGHT,BLOCKS ,NAME,PARTITION_NAME,LF_ROWS,DEL_LF_ROWS,LF_BLKS, PCT_USED FROM INDEX_STATS；
高度块名称 PARTITION_NAME LF_ROWS DEL_ LF_ROWS LF_BLKS PCT_USED
- ---------- ---------- ------------------------------------------ --------- ---- ---------- ---------- ---------- ------ ----
2 88 IDX_TEST_01 可以看到，该索引的二进制高度为2，的个数BLOCKS为88（包括根块、分支块、叶块和一个一些开销块），叶子块记录数为10001，叶子块数为85，因为最后一个叶子块的空间没有用完。 ，所以
PCT_USED 不显示 100%，而是 98%。
PCT_USED 正在使用的b树中分配的空间百分比已使用空间的百分比< /strong>
3.新记录对索引的影响
SQL> INSERT INTO TEST VALUES(20001,RPAD(20001,50,'X'),SYSDATE); strong>
已创建 1 行。
SQL>提交；
提交完成。
--由于20001大于表中的最大值20000，所以将数据插入到索引号中最右边的叶节点点。 由于索引树的最后一个叶子节点仍然有空闲空间来容纳这条记录，因此可以顺利插入数据。
索引中的叶块数量不会改变。
SQL> 分析索引 IDX_TEST_01 验证结构；
已分析索引。
SQL> SELECT HEIGHT,BLOCKS,NAME,PARTITION_NAME,LF_ROWS,DEL_LF_ROWS,LF_BLKS, PCT_USED FROM INDEX_STATS；
高度块名称 PARTITION_NAME LF_ROWS DEL_LF_ROWS LF_BLKS PCT_USED
---------- -- -------- --------------- - --------------- ---------- - --------- ---------- ----- -----
2 88 IDX_TEST_01 10002 0 85 98
可以看到索引叶子块中的记录数增加了1，达到了10002，但是叶子块仍然是85，没有改变。
--如果我们执行以下SQL： INSERT INTO TEST VALUES(9999,RPAD(9999,50,'X'),SYSDATE);
由于 9999 小于 ta 中的 IDble 的最小值 10000 还是很小，所以数据会被插入到索引号最左边的叶子子节点中。 此时索引号最左边的叶子节点没有空闲空间容纳这条记录，无法插入数据。 ORACLE会在后台将索引块分割5-5个，并将大约一半的数据放入新的索引块中。原始数据将继续保留在索引块中。中间。然后将9999的记录插入到对应的块中。
SQL> INSERT INTO TEST VALUES(9999,RPAD(9999,50,'X'),SYSDATE);
已创建 1 行。
SQL> 提交；
提交完成。
SQL> 分析索引 IDX_TEST_01 验证结构；
已分析索引。
SQL> SELECT HEIGHT,BLOCKS,NAME、PARTITION_NAME、LF_ROWS、DEL_LF_ROWS、LF_BLKS、PCT_USED FROM INDEX_STATS；
高度块名称 PARTITION_NAME LF_ROWS DEL_LF_ROWS LF_BLKS PCT_USED
- --------- ---------- --------------- -- ------------- - --------- ---------- ---------- ------ ----
2 88 IDX_TEST_01 10003 0 86 97
可以看到索引的叶子块中的记录数增加了1到10003，叶子块的数量增加到了86，这是索引块的分裂导致了一个数据块被分成两部分。
--如果继续插入以下SQL此时声明，会发生什么呢？ 插入测试值(9998,RPAD(9998,50,'X'),SYSDATE);
Bec因为最左边的块刚刚被分割，1块被分成了2个。所以现在左边的 2 个块大约还剩下一半的可用空间。因此有足够的空间容纳记录9998。
如下图：
SQL> INSERT INTO TEST VALUES(9998,RPAD(9998,50,'X'),SYSDATE);
已创建 1 行。
SQL> 提交；
提交完成
SQL> 分析索引 IDX_TEST_01 验证结构；
已分析索引。
SQL> SELECT HEIGHT,BLOCKS,NAME,PARTITION_NAME,LF_ROWS,DEL_LF_ROWS,LF_BLKS, PCT_USED FROM INDEX_STATS；
高度块名称 PARTITION_NAME LF_ROWS DEL_LF_ROWS LF_BLKSPCT_USED
---------- --- ------- --------------- --- ------------ ---------- --- -------- ----------------- ---
2 88IDX_TEST_0110004 0 86 97
可以看到记录增加了，但是叶子块没有增加。
--如果插入以下SQL：INSERT INTO 测试值(14998,RPAD(14998,50,'X'),SYSDATE);
根据前面的分析以及索引块当前的空闲状态，此时也会进行搜索块划分。
SQL> select sid from v$mystat where rownum<2;
SID
----------
30< /strong>
SQL> INSERT INTO TEST VALUES(14998, RPAD(14998,50,'X'),SYSDATE);
已创建 1 行。
SQL> COMMIT；
提交完成。
SQL> COL 名称格式 A20
SQL> 分析索引 IDX_TEST_01 验证结构；
已分析索引。
SQL> SELECT HEIGHT,BLOCKS, NAME,PARTITION_NAME,LF_ROWS,DEL_LF_ROWS,LF_BLKS, PCT_USED FROM INDEX_STATS；
高度块NAMEPARTITION_NAME LF_ROWS DEL_LF_ROWS LF_BLKS PCT_USED
---------- --- ------- --------------- ------ --------------- ------- --- ---------- -------- -- ----------
2 96 IDX_TEST_01 10005 87 96
可以看到索引块又被分割了。
您还可以使用以下内容SQL语句查询索引块的分割数。
SQL> 从 v$SESSTAT A、V$STATNAME 中选择 B.NAME、A.VALUE
B
WHERE A.STATISTIC# = B.STATISTIC#
AND B.NAME LIKE '%split%'
且 A.SID = 30；
姓名 ---------------------------- ----------
叶节点分裂 sp; 2
叶子节点90-10分裂0
分支节点分裂 />
注意：UPDATE也会导致索引块分裂。对于索引来说，UPDATE实际上就是一个块DELETE加上一条INSERT语句。
4.并发触发 enq: TX - 索引争用
每当索引块没有空间容纳新数据时，就会发生索引块分裂。 如果在分裂过程中，其他进程也想操作对应的索引块同时，那么其他进程将处于 enq:TX - 索引争用处于待处理状态。
为了演示方便，重建并创建一个稍微大一点的表
SQL> DROP TABLE测试;
表已删除。
SQL> CREATE TABLE TEST(ID NUMBER,NAME CHAR(10), CREATED DATE,CONTENTS VARCHAR2(4000));
表已创建。
SQL> CREATE INDEX IDX_TEST_01 ON TEST(CREATED,CONTENTS);
索引已创建。
--在两个窗口中继续：会话 1：26 会话： 33
首先统计这2个session的索引，拆分统计如下：
SELECT A.SID, B.NAME, A. VALUE
来自 v$SESSTAT A、V$STATNAME B
，其中 A.STATISTIC# = B.STATISTIC#
AND B.NAME LIKE '%split%'
AND A。 SID IN (26,33)
ORDER BY 1, 2;< br/>
SID NAMEVALUE
---------- -- -------------------------------- -------------------------- ----------
26 个分支节点分裂 0
26 个叶节点分裂 90-10 个 sp; 26 个队列分割 ; 0
26个根节点分裂 33个分支节点分裂 p; 33 个队列拆分 0\ >会话 1 和会话 2 向表中插入记录，并在插入数据时打开一个窗口来监视等待事件
BEGIN
FOR I IN 0 .. 100000 LOOP
INSERT INTO TEST VALUES (I, TO_CHAR(I), SYSDATE, RPAD('X', 2000, 'X'));
END LOOP;
END ;
/
会话 1: 26
SQL> SELECT USRE来自 DUAL 的 NV('SID')；
USERENV('SID')
--------------
; , TO_CHAR(I), SYSDATE, RPAD('X', 2000, 'X'));
4 END 循环;
5 END;
6 /
会话2：33
SQL> SELECT USERENV('SID') FROM DUAL;
USERENV('SID')
----------------
33
SQL> 开始
2 for I in 0 .. 6000 LOOP
3 插入 TEST VALUES (I, TO_CHAR(I), SYSDATE, RPAD('X', 2000, 'X'));
4 END LOOP;
5 END;
6 /
--插入前的查询等待事件如下：
SQL> 设置第 200 行
SQL> a30 的 col 事件
SQL> a15 的 col 机器
SQL> select inst_id,sid,sql_id,status,machine ,event,blocking_session, wait_time,state,seconds_in_wait 来自 gv$session，其中事件如“enq: TX - 索引争用”;
未选择任何行
SQL>
--插入时查询等待事件：
SQL> set rows 200
SQL> col event for a30
SQL> cola15 的机器
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';
< br/>未选择行
SQL> /
未选择行
SQL> /
未选择任何行
SQL> /
未选择任何行
SQL> /
INST_ID SID SQL_ID STATUS MACHINE EVENT BLOCKING_SESSION WAIT_TIME 状态 SECONDS_IN_WAIT -------------------------------- ------------------------ -- -- ---------- ------------------ ------------------ -- ------------------------------------------------ -- ----------
1 33 41vqxgnub01q1 ACTIVE wang enq: TX - 索引争用 26 0 WAITING 0
SQL> select sql_text from v$sql where sql_id='41vqxgnub01q1';
SQL_TEXT
------ ------------------------- ------------------------ ------------------------ ----------------------------------
插入整数O 测试值 (:B1 、 TO_CHAR(:B1 )、SYSDATE、RPAD('X', 2000, 'X'))
SQL>
< /strong>
该等待事件的描述如下：
enq: TX - 索引争用
等待当在索引中插入行的事务必须等待另一个事务完成索引块拆分结束时，也会发生模式 4 中的 TX。这种类型的TX入队等待对应于等待事件enq：TX - 索引争用。
注意：如果索引块中没有空间来分配事务槽，也会导致enq: TX - 分配ITL条目的竞争 挣扎。
SQL> l
1 SELECT A.SID、B.NAME、A.VALUE
2 FROM v$ SESSTAT A，V$STATNAME B
3 其中 A.STATISTIC# = B.STATISTIC#
4 AND B.NAME LIKE '%split%'
5 AND A.SID IN (26, 33)
6* ORDER BY 1, 2
SQL> /
SID NAME ------------------------------------------------------------ -- ---- ----
26个分支节点分裂 19705
26个叶节点分裂 ; 2014
26 队列分裂 0
26 根节点分裂 6
33 分支节点分裂19918
33 个叶节点分裂 nbsp; 20149
33 个队列拆分 33 个根节点拆分 2
已选择 10 行。
查看结果 大量的分裂已经收到
从捕获的灰分报告中，等待是由插入语句引起的， sql要插入数据的表是每天需要频繁删除的表，而等待事件的产生与频繁的大批量删除密切相关。厂家最后给出的建议是定期重建表，增加索引的pctfree。
enq：TX - 索引争用
最可能的原因是 > >
o 应用程序频繁访问的表上的索引。 o 单调增长的表列上的索引。换句话说，大多数索引插入只发生在索引的右边缘。
o 已进行大数据清除，随后进行高并发插入（大批量并发插入）
运行 OLTP 系统时，可能会在与应用程序具有高并发性的表关联的索引上看到高 TX 队列争用。当应用程序同时执行大量 INSERT 和 DELETE 时，通常会发生这种情况。对于RAC系统，所有实例都可能发生并发INSERT和DELETE。
原因是在将新行插入索引时索引块分裂。事务必须在模式 4 中等待 TX 锁定，直到正在进行块分割的会话完成操作。 （索引块分割）
当会话在索引块中找不到需要插入新行的空间时，它将启动索引块分割。在开始分割之前，它会清理块中的所有键，以检查块中是否有足够的空间。删除
Splitter必须执行以下活动：
o 分配新块。
o 将一定百分比的行复制到新缓冲区。
o 将新缓冲区添加到索引结构并提交操作。
在 RAC 环境中，这可能会由于包含全局缓存操作，这是一项昂贵的操作。如果拆分发生在分支或根块级别，影响会更大。
解决方案：解决方案
a) 重建作为反向键索引或散列分区的索引 AWR 报告的“按行锁定等待分段”中列出的索引 重建索引
b) 考虑增加序列的 CACHE 大小缓存值较大
c) 在大量数据清除后重建或收缩关联索引
d) 增加索引的PCT_FREE 增加索引的PCT_FREE block
以上是关于如何理解enq: TX - 索引争用等待的讨论和测试，小编相信有些知识点可能在我们的日常工作中看到或使用。希望您能从本文中了解更多信息。更多详情请关注行业资讯信息渠道。
2. 本站积分货币获取途径以及用途的解读，想在本站混的好，请务必认真阅读！
3. 本站强烈打击盗版/破解等有损他人权益和违法作为，请各位会员支持正版！
4. 编程技术 > 如何理解enq: TX——索引争用等待的讨论和测试
