ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 如何解决enq: TX- index contention

如何解决enq: TX- index contention

Linux操作系统 作者:lhrbest 时间:2015-07-07 16:39:59 0 删除 编辑

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(此块下文研究)
  

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-1725763/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1367
  • 访问量
    8351787