前一阵子,遇到ITL的问题,写了一篇BLOG.
http://space.itpub.net/?uid-267265-action-viewspace-itemid-717089
最近看一篇blog ,才知道真正的问题在那里?
http://antognini.ch/2011/04/itl-waits-changes-in-recent-releases/
http://antognini.ch/2011/06/itl-waits-%E2%80%93-changes-in-recent-releases-script/
我拿上面的scripts测试一下,我的测试数据库11.2.0.1.
--补充一点,为了测试的方便,我加入了时间的显示.为了测试的方便,我仅仅测试大约20分钟.
--文章提到了ITL wait算法的改变,摘要如下:
ITL Waits in 11gR1
In 11.1.0.6 and 11.1.0.7 a session waits at most one time on every slot. For all slots but one it waits up to 5 seconds.
For the other one it might wait indefinitely. The following pseudo code illustrates this (you should consider the variable
called "itl" as an array referencing/containing all ITL slots).
The problem of this algorithm is that an "unlucky" session might wait much longer than necessary. In fact, once it enters
the WAIT FOREVER status, it no longer considers the other slots.
ITL Waits in 11gR2
In 11.2.0.1 and 11.2.0.2 a session might wait several times for the same slot. Initially the wait is short. As the time
passes, the wait time increases exponentially based on the formula "wait time = power(2,iteration-1)". For all slots but one
there is a maximum wait time of 5 seconds, though. For the other one, and for the first 10 iterations only, the wait time is
computed with the very same formula. Then, during the 11th iteration, the session waits indefinitely. The following pseudo
code illustrates this.
The advantage of this algorithm is that a session might probe several time all the available slots and, as a result, enters
the WAIT FOREVER status after about 20 minutes only.
ITL Waits in 9i/10g
Up to 10.2.0.4 the behavior. is similar to 11gR1. The only noticeable difference is that the wait time is not always 5 seconds.
Instead, it is either 3 or 6 seconds. I was not able to spot a rule behind the choice between the two durations. So, there might
be some randomness involved.
In 10.2.0.5 the behavior. is similar to 11gR2. Also in this case the only noticeable difference is that the maximum wait time
is not always 5 seconds. Instead, as in releases up to 10.2.0.4, it is either 3 or 6 seconds.
--11GR2版本的改进还好一点.实际上我上次的测试不够耐心http://space.itpub.net/?uid-267265-action-viewspace-itemid-717089
--如果等待也是半分钟,应该可以提交.
--我上次等待的顺序应该如下,按照上面的算法:
1 1 1
2 2 2
4 4 4
5 5 8
5 5 16
5 5 32
5 5 64
1+1+1+2+2+2+4+4+4+5+5+8+5+5=49秒.也就是出现阻塞49秒,如果前面的itl释放,last slot不释放,要等16秒.第4个事务才能提交.
--如果在10.2.0.4,问题会更加严重:
10.2.0.4
--如果blocking_session=158不commit或者rollback,也就是itl.last不释放,也就是经历6+3+6+6=21秒后(指文章中的例子),阻塞的事务要处于等待的情况.
--这个也是我生产系统遇到的问题.
--这个问题很容易演示.好在一般出现"enq: TX– allocate ITL entry"不多.
--------------------------------测试:
-我建立了一个表，pctfree=0,这样块内空余空间基本没有，仅仅两个ITL槽(实际上是3个)，不能在增加空间分配ITL槽。
SQL> select rowid from t where id=1;
ROWID
------------------
AAAUSUAAEAAAAO7AAA
SQL> select min(id),max(id) from t where rowid between 'AAAUSUAAEAAAAO7AAA' and 'AAAUSUAAEAAAAO7DDD';
MIN(ID) MAX(ID)
---------- ----------
1 581
--可以确定id从1到581都在一个块中。
3.测试数据：
--修改数据，不commit.
打开会话1：
update t set name='TEST' where id=1;
打开会话2：
update t set name='TEST' where id=2;
打开会话3：
update t set name='TEST' where id=3;
打开会话4：
update t set name='TEST' where id=4;
--出现阻塞情况.
--在会话1执行
SQL> exec dbms_lock.sleep(21);
SQL> SELECT blocking_session, seconds_in_wait FROM v$session WHERE blocking_session is not null;
BLOCKING_SESSION SECONDS_IN_WAIT
---------------- ---------------
166 261
SQL> SELECT blocking_session, seconds_in_wait FROM v$session WHERE blocking_session is not null;
BLOCKING_SESSION SECONDS_IN_WAIT
---------------- ---------------
166 264
--rollback另外2个会话的事务.
--可以发现即使我释放了2个itl slot,阻塞的事务依旧等待第3个事务槽的释放.
SQL> SELECT blocking_session, seconds_in_wait FROM v$session WHERE blocking_session is not null;
BLOCKING_SESSION SECONDS_IN_WAIT
---------------- ---------------
164 1296
http://space.itpub.net/?uid-267265-action-viewspace-itemid-717089
最近看一篇blog ,才知道真正的问题在那里?
http://antognini.ch/2011/04/itl-waits-changes-in-recent-releases/
http://antognini.ch/2011/06/itl-waits-%E2%80%93-changes-in-recent-releases-script/
我拿上面的scripts测试一下,我的测试数据库11.2.0.1.
$ . itl.sh scott xxxxx $ORACLE_SID 1204
Setup correctly performed: YES
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
09:36:56.639596000 000000 blocking_session=191 sleep=0
09:36:57.649726000 000001 blocking_session=191 sleep=1
09:36:58.659690000 000002 blocking_session=9 sleep=1
09:36:59.669591000 000003 blocking_session=135 sleep=1
09:37:00.679531000 000004 blocking_session=10 sleep=1
09:37:01.689475000 000005 blocking_session=199 sleep=1
09:37:03.711303000 000007 blocking_session=191 sleep=2
09:37:05.644207000 000009 blocking_session=9 sleep=2
09:37:07.664006000 000011 blocking_session=135 sleep=2
09:37:09.684102000 000013 blocking_session=10 sleep=2
09:37:11.703930000 000015 blocking_session=199 sleep=2
09:37:15.657604000 000019 blocking_session=191 sleep=4
09:37:19.698203000 000023 blocking_session=9 sleep=4
09:37:23.737856000 000027 blocking_session=135 sleep=4
09:37:27.676508000 000031 blocking_session=10 sleep=4
09:37:31.735170000 000035 blocking_session=199 sleep=4
09:37:36.683508000 000040 blocking_session=191 sleep=5
09:37:41.733081000 000045 blocking_session=9 sleep=5
09:37:46.681696000 000050 blocking_session=135 sleep=5
09:37:51.755176000 000055 blocking_session=10 sleep=5
09:37:59.733417000 000062 blocking_session=199 sleep=8
09:38:04.683026000 000067 blocking_session=191 sleep=5
09:38:09.748439000 000072 blocking_session=9 sleep=5
09:38:14.696828000 000077 blocking_session=135 sleep=5
09:38:19.746381000 000082 blocking_session=10 sleep=5
09:38:35.715690000 000098 blocking_session=199 sleep=16
09:38:40.765254000 000103 blocking_session=191 sleep=5
09:38:45.728904000 000108 blocking_session=9 sleep=5
09:38:50.678387000 000113 blocking_session=135 sleep=5
09:38:55.727914000 000118 blocking_session=10 sleep=5
09:39:27.776097000 000150 blocking_session=199 sleep=32
09:39:32.725625000 000154 blocking_session=191 sleep=5
09:39:37.687242000 000159 blocking_session=9 sleep=5
09:39:42.736854000 000164 blocking_session=135 sleep=5
09:39:47.698552000 000169 blocking_session=10 sleep=5
09:40:51.774986000 000233 blocking_session=199 sleep=64
09:40:56.728513000 000237 blocking_session=191 sleep=5
09:41:01.786793000 000242 blocking_session=9 sleep=5
09:41:06.742866000 000247 blocking_session=135 sleep=5
09:41:11.807490000 000252 blocking_session=10 sleep=5
09:43:19.787092000 000379 blocking_session=199 sleep=128
09:43:24.740738000 000384 blocking_session=191 sleep=5
09:43:29.794293000 000389 blocking_session=9 sleep=5
09:43:34.742853000 000394 blocking_session=135 sleep=5
09:43:39.800442000 000399 blocking_session=10 sleep=5
09:47:55.746642000 000652 blocking_session=199 sleep=256
09:48:00.796100000 000657 blocking_session=191 sleep=5
09:48:05.744717000 000662 blocking_session=9 sleep=5
09:48:10.811302000 000667 blocking_session=135 sleep=5
09:48:15.760652000 000672 blocking_session=10 sleep=5
09:56:47.804452000 001178 blocking_session=199 sleep=512
09:56:52.775000000 001183 blocking_session=191 sleep=5
09:56:57.824431000 001188 blocking_session=9 sleep=5
09:57:02.773131000 001193 blocking_session=135 sleep=5
09:57:07.835712000 001198 blocking_session=10 sleep=5
09:57:09.047576000 001199 blocking_session=199 sleep=1
--补充一点,为了测试的方便,我加入了时间的显示.为了测试的方便,我仅仅测试大约20分钟.
--文章提到了ITL wait算法的改变,摘要如下:
ITL Waits in 11gR1
In 11.1.0.6 and 11.1.0.7 a session waits at most one time on every slot. For all slots but one it waits up to 5 seconds.
For the other one it might wait indefinitely. The following pseudo code illustrates this (you should consider the variable
called "itl" as an array referencing/containing all ITL slots).
FOR i IN itl.FIRST..itl.LAST
LOOP
EXIT WHEN itl(i) IS FREE
IF i <> itl.LAST
THEN WAIT ON itl(i) FOR 5 SECONDS
ELSE WAIT ON itl(i) FOREVER
END IF
END LOOP
The problem of this algorithm is that an "unlucky" session might wait much longer than necessary. In fact, once it enters
the WAIT FOREVER status, it no longer considers the other slots.
ITL Waits in 11gR2
In 11.2.0.1 and 11.2.0.2 a session might wait several times for the same slot. Initially the wait is short. As the time
passes, the wait time increases exponentially based on the formula "wait time = power(2,iteration-1)". For all slots but one
there is a maximum wait time of 5 seconds, though. For the other one, and for the first 10 iterations only, the wait time is
computed with the very same formula. Then, during the 11th iteration, the session waits indefinitely. The following pseudo
code illustrates this.
iteration = 0
LOOP
iteration++
FOR i IN itl.FIRST..itl.LAST
LOOP
EXIT WHEN itl(i) IS FREE
IF i <> itl.LAST
THEN WAIT ON itl(i) FOR min(power(2,iteration-1),5) SECONDS
ELSIF iteration <= 10
THEN WAIT ON itl(i) FOR power(2,iteration-1) SECONDS
ELSE WAIT ON itl(i) FOREVER
END IF
END LOOP
EXIT WHEN free_itl_found
END LOOP
The advantage of this algorithm is that a session might probe several time all the available slots and, as a result, enters
the WAIT FOREVER status after about 20 minutes only.
ITL Waits in 9i/10g
Up to 10.2.0.4 the behavior. is similar to 11gR1. The only noticeable difference is that the wait time is not always 5 seconds.
Instead, it is either 3 or 6 seconds. I was not able to spot a rule behind the choice between the two durations. So, there might
be some randomness involved.
In 10.2.0.5 the behavior. is similar to 11gR2. Also in this case the only noticeable difference is that the maximum wait time
is not always 5 seconds. Instead, as in releases up to 10.2.0.4, it is either 3 or 6 seconds.
--11GR2版本的改进还好一点.实际上我上次的测试不够耐心http://space.itpub.net/?uid-267265-action-viewspace-itemid-717089
--如果等待也是半分钟,应该可以提交.
--我上次等待的顺序应该如下,按照上面的算法:
1 1 1
2 2 2
4 4 4
5 5 8
5 5 16
5 5 32
5 5 64
1+1+1+2+2+2+4+4+4+5+5+8+5+5=49秒.也就是出现阻塞49秒,如果前面的itl释放,last slot不释放,要等16秒.第4个事务才能提交.
--如果在10.2.0.4,问题会更加严重:
10.2.0.4
000000 blocking_session=136 sleep=0
000005 blocking_session=136 sleep=6
000010 blocking_session=140 sleep=3
000015 blocking_session=152 sleep=6
000020 blocking_session=159 sleep=6
029995 blocking_session=158 sleep=29979
--如果blocking_session=158不commit或者rollback,也就是itl.last不释放,也就是经历6+3+6+6=21秒后(指文章中的例子),阻塞的事务要处于等待的情况.
--这个也是我生产系统遇到的问题.
--这个问题很容易演示.好在一般出现"enq: TX– allocate ITL entry"不多.
--------------------------------测试:
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table t pctfree 0 as select rownum id ,'test' name from dual connect by level<=1000;
SQL> create unique index i_t_id on t(id);
SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T');
-我建立了一个表，pctfree=0,这样块内空余空间基本没有，仅仅两个ITL槽(实际上是3个)，不能在增加空间分配ITL槽。
SQL> select rowid from t where id=1;
ROWID
------------------
AAAUSUAAEAAAAO7AAA
SQL> select min(id),max(id) from t where rowid between 'AAAUSUAAEAAAAO7AAA' and 'AAAUSUAAEAAAAO7DDD';
MIN(ID) MAX(ID)
---------- ----------
1 581
--可以确定id从1到581都在一个块中。
3.测试数据：
--修改数据，不commit.
打开会话1：
update t set name='TEST' where id=1;
打开会话2：
update t set name='TEST' where id=2;
打开会话3：
update t set name='TEST' where id=3;
打开会话4：
update t set name='TEST' where id=4;
--出现阻塞情况.
--在会话1执行
SQL> exec dbms_lock.sleep(21);
SQL> SELECT blocking_session, seconds_in_wait FROM v$session WHERE blocking_session is not null;
BLOCKING_SESSION SECONDS_IN_WAIT
---------------- ---------------
166 261
SQL> SELECT blocking_session, seconds_in_wait FROM v$session WHERE blocking_session is not null;
BLOCKING_SESSION SECONDS_IN_WAIT
---------------- ---------------
166 264
--rollback另外2个会话的事务.
--可以发现即使我释放了2个itl slot,阻塞的事务依旧等待第3个事务槽的释放.
SQL> SELECT blocking_session, seconds_in_wait FROM v$session WHERE blocking_session is not null;
BLOCKING_SESSION SECONDS_IN_WAIT
---------------- ---------------
164 1296
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-731657/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
[20120605]逻辑读那个高.txt
请登录后发表评论
登录
全部评论
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
