ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120601]ITL的问题补充.txt

[20120601]ITL的问题补充.txt

原创 Linux操作系统 作者:lfree 时间:2012-06-01 15:08:47 0 删除 编辑
前一阵子,遇到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.

$ . 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博客 ” ,链接:http://blog.itpub.net/267265/viewspace-731657/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2488
  • 访问量
    6294844