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竞争与并发会话有关
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-756386/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-756386/