itl競爭模擬與解決系列(二)

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競爭與併發會話有關