itl竞争模拟与解决系列(二)
1,itl竞争的原因
2,itl竞争的分类
3,如何解决itl竞争
4,示例
5,小结

1,itl竞争的原因
    1,表或索引的并行很多
    2,表或索引的initrans配置过低
    3,可能还有其它原因，暂未知
   
2,itl竞争的分类
    1，分为表和索引

3,如何解决itl竞争
    1,运行:SELECT * FROM V$SEGMENT_STATISTICS WHERE STATISTIC_NAME LIKE '%ITL%' and wner='SCOTT' ORDER BY VALUE
      查看是否有基于itl的等待
    2,增加表或索引的initrans
    3,语句为：
       --表：alter table t_lock initrans 2;
       --索引：alter index idx_t_lock rebuild online intrans 10;
4,示例      

--与itl相关的等待
holdlock>select name from v$event_name where name like '%ITL%';

NAME
---------------------------------------------------------------

enq: TX - allocate ITL entry
 

--构造等待事件 enq: TX - allocate ITL entry,如高并发引发此事件
--并发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 - allocate ITL entry
select event from v$session where username='SCOTT';

---对表索引的itl等待较多
SQL> SELECT * FROM V$SEGMENT_STATISTICS WHERE STATISTIC_NAME LIKE '%ITL%' and wner='SCOTT' ORDER BY VALUE;
 
OWNER                          OBJECT_NAME                    SUBOBJECT_NAME                 TABLESPACE_NAME                       TS#       OBJ#   DATAOBJ# OBJECT_TYPE        STATISTIC_NAME                                                   STATISTIC#      VALUE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ------------------ ---------------------------------------------------------------- ---------- ----------
SCOTT                          T_LOCK                                                        TBS_HANG                                8      69685      69685 TABLE              ITL waits                                                                14          0
SCOTT                          T_DETAIL                                                      TBS_HANG                                8      69558      69558 TABLE              ITL waits                                                                14          0
SCOTT                          IDX_T_DETAIL                                                  TBS_HANG                                8      69559      69559 INDEX              ITL waits                                                                14         12
 
 
SQL> /
 
OWNER                          OBJECT_NAME                    SUBOBJECT_NAME                 TABLESPACE_NAME                       TS#       OBJ#   DATAOBJ# OBJECT_TYPE        STATISTIC_NAME                                                   STATISTIC#      VALUE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ------------------ ---------------------------------------------------------------- ---------- ----------
SCOTT                          T_LOCK                                                        TBS_HANG                                8      69685      69685 TABLE              ITL waits                                                                14          0
SCOTT                          T_DETAIL                                                      TBS_HANG                                8      69558      69558 TABLE              ITL waits                                                                14          0
SCOTT                          IDX_T_DETAIL                                                  TBS_HANG                                8      69559      69559 INDEX              ITL waits                                                                14         16
 
--查看等待表的pctfree及transaction相关参数
SQL> select pct_free,ini_trans,max_trans from user_tables where table_name='T_DETAIL';
 
  PCT_FREE  INI_TRANS  MAX_TRANS
---------- ---------- ----------
        10          1        255

--查询索引的初始及最大事务数
SQL> select index_name,INI_TRANS,max_trans from user_indexes where index_name='IDX_T_DETAIL';
 
INDEX_NAME                      INI_TRANS  MAX_TRANS
------------------------------ ---------- ----------
IDX_T_DETAIL                            2        255
 
 
--index itl等待很多，加大index transaction 
SQL> alter index IDX_T_DETAIL rebuild online initrans 10;
 
Index altered 

--加大index transaction后itl等待马上就消失了
SQL> SELECT * FROM V$SEGMENT_STATISTICS WHERE STATISTIC_NAME LIKE '%ITL%' and wner='SCOTT' ORDER BY VALUE;
 
OWNER                          OBJECT_NAME                    SUBOBJECT_NAME                 TABLESPACE_NAME                       TS#       OBJ#   DATAOBJ# OBJECT_TYPE        STATISTIC_NAME                                                   STATISTIC#      VALUE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ------------------ ---------------------------------------------------------------- ---------- ----------
SCOTT                          T_LOCK                                                        TBS_HANG                                8      69685      69685 TABLE              ITL waits                                                                14          0
SCOTT                          T_DETAIL                                                      TBS_HANG                                8      69558      69558 TABLE              ITL waits                                                                14          0
SCOTT                          IDX_T_DETAIL                                                  TBS_HANG                                8      69559      69686 INDEX              ITL waits                                                                14          0
 
SQL> /
 
OWNER                          OBJECT_NAME                    SUBOBJECT_NAME                 TABLESPACE_NAME                       TS#       OBJ#   DATAOBJ# OBJECT_TYPE        STATISTIC_NAME                                                   STATISTIC#      VALUE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ------------------ ---------------------------------------------------------------- ---------- ----------
SCOTT                          T_LOCK                                                        TBS_HANG                                8      69685      69685 TABLE              ITL waits                                                                14          0
SCOTT                          T_DETAIL                                                      TBS_HANG                                8      69558      69558 TABLE              ITL waits                                                                14          0
SCOTT                          IDX_T_DETAIL                                                  TBS_HANG                                8      69559      69686 INDEX              ITL waits                                                                14          0
 
SQL> /
--增大index itl减少itl contention,减少呢
SQL> alter index IDX_T_DETAIL rebuild online initrans 2;
 
Index altered

--可知把index transaction减少后，itl wait增大了
SQL> SELECT * FROM V$SEGMENT_STATISTICS WHERE STATISTIC_NAME LIKE '%ITL%' and wner='SCOTT' ORDER BY VALUE;
 
OWNER                          OBJECT_NAME                    SUBOBJECT_NAME                 TABLESPACE_NAME                       TS#       OBJ#   DATAOBJ# OBJECT_TYPE        STATISTIC_NAME                                                   STATISTIC#      VALUE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ------------------ ---------------------------------------------------------------- ---------- ----------
SCOTT                          T_LOCK                                                        TBS_HANG                                8      69685      69685 TABLE              ITL waits                                                                14          0
SCOTT                          T_DETAIL                                                      TBS_HANG                                8      69558      69558 TABLE              ITL waits                                                                14          0
SCOTT                          IDX_T_DETAIL                                                  TBS_HANG                                8      69559      69689 INDEX              ITL waits                                                                14          0
 
SQL> /
 
OWNER                          OBJECT_NAME                    SUBOBJECT_NAME                 TABLESPACE_NAME                       TS#       OBJ#   DATAOBJ# OBJECT_TYPE        STATISTIC_NAME                                                   STATISTIC#      VALUE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ------------------ ---------------------------------------------------------------- ---------- ----------
SCOTT                          T_LOCK                                                        TBS_HANG                                8      69685      69685 TABLE              ITL waits                                                                14          0
SCOTT                          T_DETAIL                                                      TBS_HANG                                8      69558      69558 TABLE              ITL waits                                                                14          0
SCOTT                          IDX_T_DETAIL                                                  TBS_HANG                                8      69559      69689 INDEX              ITL waits                                                                14          7

--查看与itl相关的等待事件
SQL> select event from v$session where type='USER' and event like '%ITL%';
 
EVENT
----------------------------------------------------------------
enq: TX - allocate ITL entry

5，小结：
        1,itl竞争与table和index的initrans有关
        2,itl竞争与并发会话有关