1,enq: TX- index contention等待事件的概念
2,如何产生此等待事件
3,如何解决等待事件
4,小结
.
1,enq: TX- index contention等待事件的概念
1，当在基于索引的表中插入一条记录时，此时正好另一会话事务正在操作这个索引块正处于分裂状态（即索引块分裂）
2,如何产生此等待事件
--构造等待事件 enq: TX- index contention,如高并发引发此事件
--并发job
declare
v_job pls_integer;
begin
for i in 1..100 loop
dbms_job.submit(v_job,what => 'proc_arg;',next_date => sysdate,interval=>'sysdate+1');
commit;
end loop;
end;
/
--并发job调用的存储过程
create or replace procedure proc_arg
as
begin
insert into t_detail values(1);
--dbms_lock.sleep(120);
--commit;
end;
--查询是否发生了enq: TX- index contention事件
--先温习下v$session_wait视图几个重要列的含义：
1，WAIT_TIME_MICRO,
1,单位为毫秒
2,如当前会话处于等待，则为已等待占用的时间
3,如当前会话已停止等待,则为最近一次等待占用的时间
2，TIME_REMAINING_MICRO
1,本参数有几类值
2,若值>0，则表示还要等待多久时间
若值=0,则表示当前会话已超时
若值=-1,则表示不知还要等待多久
若值=null,表示当前会话未处于等待状态
--由下可知,不知还要等多久
SQL> select event,WAIT_TIME_MICRO,TIME_REMAINING_MICRO from v$session_wait where event like '%enq: TX - index contention%';
EVENT WAIT_TIME_MICRO TIME_REMAINING_MICRO
---------------------------------------------------------------- --------------- --------------------
enq: TX - index contention 804411 -1
enq: TX - index contention 804144 -1
enq: TX - index contention 821468 -1
enq: TX - index contention 759771 -1
enq: TX - index contention 766228 -1
--中间略
enq: TX - index contention 784128 -1
--判断上述的enq: TX - index contention是否和索引块分裂有关
SQL> select * from v$event_name where name like '%split%';
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------
169 1457266432 gc current split 3871361733 11 Cluster
769 67546339 index block split rootdba level childdba 1893977003 0 Other
794 695862004 kksscl hash split 1893977003 0 Other
994 1569316226 enq: AM - PST split check name|mode id1 id2 1893977003 0 Other
SQL> select sid,event,total_waits,average_wait from v$session_event where event in (select name from v$event_name where name like '%split%' and event#=769);
SID EVENT TOTAL_WAITS AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- ------------
--未看到索引块分裂
SQL> select sid,event,total_waits,average_wait from v$session_event where event like '%index%'
SID EVENT TOTAL_WAITS AVERAGE_WAIT
---------- ---------------------------------------------------------------- ----------- ------------
10 enq: TX - index contention 34 74.76
14 enq: TX - index contention 101 112.85
15 enq: TX - index contention 90 74.66
---中间内容略
17 enq: TX - index contention 17 63.72
20 enq: TX - index contention 101 64.37
170 enq: TX - index contention 107 74.87
171 enq: TX - index contention 107 66.2
172 enq: TX - index contention 3 63.76
59 rows selected
SQL> select * from v$event_name where name like '%enq%index%';
NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS
---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------------------------------------------------------------
enq: TX - index contention name|mode usn<<16 | slot sequence Concurrency
--获取与索引块分裂的统计
SQL> select * from v$sesstat where rownum=1;
SID STATISTIC# VALUE
---------- ---------- ----------
1 0 0
SQL> select * from v$statname where name like '%split%';
STATISTIC# NAME CLASS STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
382 leaf node splits 128 1417124053
383 leaf node 90-10 splits 128 1285138251
384 branch node splits 128 399027615
385 root node splits 128 708722985
400 queue splits 128 1168925905
--叶子块分裂的统计信息
SQL> select sum(ss.VALUE) from v$sesstat ss,v$statname st where ss.STATISTIC#=st.statistic# and st.statistic#=382;
SUM(SS.VALUE)
-------------
3
--运行并发会话后叶子块分裂的情况，确实发生了叶子块分裂
SQL> select sum(ss.VALUE) from v$sesstat ss,v$statname st where ss.STATISTIC#=st.statistic# and st.statistic#=382;
SUM(SS.VALUE)
-------------
437
SQL> /
SUM(SS.VALUE)
-------------
591
3,如何解决等待事件
1,重构索引，建立反向键索引
2,增大索引的pctfree
--查看index的pctfree
SQL> select pct_free from user_indexes where index_name='IDX_T_DETAIL';
PCT_FREE
----------
10
--可删除索引创建反向键索引
SQL> create index idx_t_detail on t_detail(a) reverse;
Index created
--反向键索引有了，是否会降低enq: TX - index contention竞争呢
--enq: TX - index contention等待事件还有;total_waits表示等待次数;average_wait表示平均每次等待多久时间,单位为in hundredths of a second
--但这个v$session_event不会保存下来，等待事件一结束就看不到了，主要想看平均等待事件，在dba_hist也没找到相应的表
select sid,event,total_waits,average_wait from v$session_event where event like 'enq: TX - index contention'；
---------- ---------------------------------------------------------------- ----------- ------------
8 enq: TX - index contention 58 62.29
18 enq: TX - index contention 86 82.81
19 enq: TX - index contention 89 69.25
29 enq: TX - index contention 112 72.57
38 enq: TX - index contention 114 72.14
55 enq: TX - index contention 107 69.71
61 enq: TX - index contention 111 66.13
--如下视图可以查询上述等待事件的变动信息,根据事件分类，并根据等待事件长短分为不同的桶，桶的标准为：
--from < 1 ms, < 2 ms, < 4 ms, < 8 ms, ... < 221 ms, < 222 ms, and >= 222 ms.
select event ,
wait_time_milli,
wait_count,
last_update_time
from V$EVENT_HISTOGRAM
where event like 'enq: TX - index contention';
--此为上述的历史信息
SQL> select * from dba_hist_event_histogram where event_name like 'enq: TX - index contention';
--我的思路通过运行b-tree index与反向键index，比对V$EVENT_HISTOGRAM上述的结果，有何区别
--v$event_histogram不会自动清除结果，须重启库方可（在此为了对比）
--为了对比，先删除之前的索引
SQL> drop index idx_t_detail;
Index dropped
--创建b-tree index
SQL> create index idx_t_detail on t_detail(a);
Index created
--运行文章前面的并发plsql脚本
--交替运行下面2个脚本，此脚本确保上述的并发plsql脚本运行完毕
SQL> select count(1) from v$session_event where event like 'enq: TX - index contention';
COUNT(1)
----------
1
--查看等待事件的统计信息
SQL> select event ,
2 wait_time_milli,
3 wait_count,
4 last_update_time
5 from V$EVENT_HISTOGRAM
6 where event like 'enq: TX - index contention';
EVENT WAIT_TIME_MILLI WAIT_COUNT LAST_UPDATE_TIME
---------------------------------------------------------------- --------------- ---------- -------------------------------------------------------------------------
enq: TX - index contention 1 159 18-MAR-13 04.41.39.178979 PM +08:00
enq: TX - index contention 2 45 18-MAR-13 04.41.41.478856 PM +08:00
enq: TX - index contention 4 58 18-MAR-13 04.41.38.528987 PM +08:00
enq: TX - index contention 8 56 18-MAR-13 04.41.41.183837 PM +08:00
enq: TX - index contention 16 97 18-MAR-13 04.41.37.391778 PM +08:00
enq: TX - index contention 32 163 18-MAR-13 04.41.41.606186 PM +08:00
enq: TX - index contention 64 289 18-MAR-13 04.41.41.372071 PM +08:00
enq: TX - index contention 128 485 18-MAR-13 04.41.40.770750 PM +08:00
enq: TX - index contention 256 654 18-MAR-13 04.41.39.239939 PM +08:00
enq: TX - index contention 512 1185 18-MAR-13 04.41.40.940983 PM +08:00
enq: TX - index contention 1024 1864 18-MAR-13 04.41.38.830764 PM +08:00
enq: TX - index contention 2048 876 18-MAR-13 04.41.40.670356 PM +08:00
enq: TX - index contention 4096 152 18-MAR-13 04.41.09.104519 PM +08:00
enq: TX - index contention 8192 23 18-MAR-13 04.39.27.721987 PM +08:00
enq: TX - index contention 16384 12 18-MAR-13 04.39.20.452465 PM +08:00
enq: TX - index contention 32768 14 18-MAR-13 04.39.47.426106 PM +08:00
--重启库为了清除v$event_histogram的统计信息，再看看反向键索引的竞争情况
EVENT WAIT_TIME_MILLI WAIT_COUNT LAST_UPDATE_TIME
---------------------------------------------------------------- --------------- ---------- -------------------------------------------------------------------------
enq: TX - index contention 1 136 18-MAR-13 05.11.10.278219 PM +08:00
enq: TX - index contention 2 35 18-MAR-13 05.11.10.243922 PM +08:00
enq: TX - index contention 4 43 18-MAR-13 05.11.09.834354 PM +08:00
enq: TX - index contention 8 60 18-MAR-13 05.11.07.219909 PM +08:00
enq: TX - index contention 16 74 18-MAR-13 05.11.08.621526 PM +08:00
enq: TX - index contention 32 135 18-MAR-13 05.11.10.028352 PM +08:00
enq: TX - index contention 64 311 18-MAR-13 05.11.09.444585 PM +08:00
enq: TX - index contention 128 485 18-MAR-13 05.11.09.972510 PM +08:00
enq: TX - index contention 256 669 18-MAR-13 05.11.09.793868 PM +08:00
enq: TX - index contention 512 1130 18-MAR-13 05.11.06.987832 PM +08:00
enq: TX - index contention 1024 1932 18-MAR-13 05.11.09.289674 PM +08:00
enq: TX - index contention 2048 1128 18-MAR-13 05.11.05.031096 PM +08:00
enq: TX - index contention 4096 166 18-MAR-13 05.10.37.715998 PM +08:00
enq: TX - index contention 8192 8 18-MAR-13 05.08.37.833097 PM +08:00
enq: TX - index contention 16384 5 18-MAR-13 05.08.37.820380 PM +08:00
结论：反向键索引的竞争反而更多了，我是算的上面的累计值
--看来还有问题，要么不能用反向键，要么我测试的方法不对：
--既然与索引块分裂有关，如果加大索引所在数据块大小，会不会减少竞争次数呢
--使用大数据块必备配置
SQL> alter system set db_16k_cache_size=20m scope=spfile;
System altered
--创建大数据表空间
SQL> create tablespace tbs_16k datafile
2 'c:\tbs16k_1.dbf' size 2g blocksize 16k;
Tablespace created
--切换索引到大数据块表空间
SQL> select index_name,tablespace_name from user_indexes where index_name='IDX_T_DETAIL';
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
IDX_T_DETAIL TBS_HANG
SQL> alter index idx_t_detail rebuild tablespace tbs_16k;
Index altered
SQL> select index_name,tablespace_name from user_indexes where index_name='IDX_T_DETAIL';
INDEX_NAME TABLESPACE_NAME
------------------------------ ---------
IDX_T_DETAIL TBS_16K
--大数据块测试的竞争如下
EVENT WAIT_TIME_MILLI WAIT_COUNT LAST_UPDATE_TIME
---------------------------------------------------------------- --------------- ---------- -------------------------------------------------------------------------
enq: TX - index contention 1 106 18-MAR-13 08.01.30.221162 PM +08:00
enq: TX - index contention 2 30 18-MAR-13 08.01.25.504420 PM +08:00
enq: TX - index contention 4 25 18-MAR-13 08.01.25.506781 PM +08:00
enq: TX - index contention 8 39 18-MAR-13 08.01.29.690659 PM +08:00
enq: TX - index contention 16 77 18-MAR-13 08.01.29.691672 PM +08:00
enq: TX - index contention 32 100 18-MAR-13 08.01.10.861044 PM +08:00
enq: TX - index contention 64 236 18-MAR-13 08.01.19.586879 PM +08:00
enq: TX - index contention 128 293 18-MAR-13 08.01.30.129016 PM +08:00
enq: TX - index contention 256 423 18-MAR-13 08.01.29.552920 PM +08:00
enq: TX - index contention 512 477 18-MAR-13 08.01.30.955651 PM +08:00
enq: TX - index contention 1024 681 18-MAR-13 08.01.24.291290 PM +08:00
enq: TX - index contention 2048 628 18-MAR-13 08.01.27.361559 PM +08:00
enq: TX - index contention 4096 252 18-MAR-13 08.01.27.762065 PM +08:00
enq: TX - index contention 8192 48 18-MAR-13 08.01.05.489611 PM +08:00
enq: TX - index contention 16384 10 18-MAR-13 07.59.39.765070 PM +08:00
15 rows selected
--加大数据块尺寸后，index contention大大减少
--哪么修改index pctfree效果表现如何呢
----修改前的pctfree
SQL> select pct_free from user_indexes where index_name='IDX_T_DETAIL';
PCT_FREE
----------
10
--有个问题要搞清楚，oracle在插入索引块时，是先在每个已有数据的索引块先查找是否有符合条件的空闲数据块，还是直接查找一个新的索引块
pctfree--为每个数据保留一个用于后来update的比例；减小此参数会不会减少index contention
SQL> drop index idx_t_detail;
Index dropped
SQL> create index idx_t_detail on t_detail(a) tablespace tbs_16k pctfree 0;
Index created
--贴上pctfree为0的index contention，可知竞争大为减少；
---------------------------------------------------------------- --------------- ---------- -------------------------------------------------------------------------
enq: TX - index contention 1 63 18-MAR-13 08.54.42.786397 PM +08:00
enq: TX - index contention 2 9 18-MAR-13 08.54.37.646487 PM +08:00
enq: TX - index contention 4 12 18-MAR-13 08.54.37.053155 PM +08:00
enq: TX - index contention 8 7 18-MAR-13 08.54.40.311190 PM +08:00
enq: TX - index contention 16 26 18-MAR-13 08.54.42.766359 PM +08:00
enq: TX - index contention 32 30 18-MAR-13 08.54.42.667444 PM +08:00
enq: TX - index contention 64 47 18-MAR-13 08.54.42.755426 PM +08:00
enq: TX - index contention 128 63 18-MAR-13 08.54.37.625092 PM +08:00
enq: TX - index contention 256 98 18-MAR-13 08.54.40.562709 PM +08:00
enq: TX - index contention 512 109 18-MAR-13 08.54.40.364472 PM +08:00
enq: TX - index contention 1024 138 18-MAR-13 08.54.42.479061 PM +08:00
enq: TX - index contention 2048 186 18-MAR-13 08.54.42.478623 PM +08:00
enq: TX - index contention 4096 109 18-MAR-13 08.54.27.103869 PM +08:00
enq: TX - index contention 8192 41 18-MAR-13 08.54.27.108269 PM +08:00
enq: TX - index contention 16384 12 18-MAR-13 08.54.01.466532 PM +08:00
4，小结
1,如出现enq: TX - index contention,说明此时正有会话正在操作分裂的索引块，而你所属会话要更新此数据块则发生此事件
2,高并发会产生此事件
3,把索引和表存储在独立的表空间，加大索引的block size(注意：其它附加副作用，暂未测试)
4,重构索引为反向键索引或全局hash index,说白了就是把索引块打散分布到多个数据块，不要集中存储；这样每个块竞争的机会就减少
5,修正index pctfree为0,大大减少index contention, 但要注意表是否只能insert，如还伴随大量的update（此块下文研究）
