​[20231027]Index ITL Limit 2.txt

[20231027]Index ITL Limit 2.txt --//链接https://jonathanlewis.wordpress.com/2022/02/18/index-itl-limit/,重复测试 --//如果例子插入语句 insert into itl_limit values(200 - i_tx_count); --//修改为 insert into itl_limit values( i_tx_count); --//采用顺序插入,看看结果如何。 1.环境： SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试脚本： rem rem     Script:         itl_limit.sql rem     Author:         Jonathan Lewis rem     Dated:          Dec 2010 rem rem     Last tested rem             19.3.0.0 rem             12.2.0.1 rem             12.1.0.2 rem             11.1.0.6 rem create table itl_limit(n1 number) pctfree 0; create unique index il_01 on itl_limit(n1) pctfree 0; create or replace procedure recursive_itl(i_tx_count number) as         pragma autonomous_transaction; begin         if i_tx_count != &&1 then                 --//insert into itl_limit values(200 - i_tx_count);                 insert into itl_limit values(i_tx_count);                 --//recursive_itl(i_tx_count - 1);                 recursive_itl(i_tx_count + 1);                 commit;         end if; end; / alter session set events '10046 trace name context forever, level 8'; --//execute recursive_itl(200); execute recursive_itl(1); alter system checkpoint; alter session set events '10046 trace name context off'; prompt  ========================================== prompt  If there is no index on the table then you prompt  should see 169 rows in one block and 31 in prompt  the other. But if there is an index there prompt  should be no rows thanks to the rollback prompt  caused by the error. prompt  ========================================== select         dbms_rowid.rowid_block_number(rowid), count(*) from         itl_limit group by         dbms_rowid.rowid_block_number(rowid) ; prompt  ================================= prompt  Try for a tree dump on the index prompt  after which you can dump the root prompt  block to see the ITL entries prompt  ================================= column object_id new_value m_object_id select  object_id, object_type, object_name from    user_objects where   object_name = 'IL_01' / alter session set events 'immediate trace name treedump level &m_object_id '; 3.测试： SCOTT@book> @ itl_limit.txt 200 Table created. Index created. Procedure created. Session altered. BEGIN recursive_itl(1); END; * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource ORA-06512: at "SCOTT.RECURSIVE_ITL", line 7 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 ORA-06512: at "SCOTT.RECURSIVE_ITL", line 8 System altered. Session altered. ========================================== If there is no index on the table then you should see 169 rows in one block and 31 in the other. But if there is an index there should be no rows thanks to the rollback caused by the error. ========================================== no rows selected ================================= Try for a tree dump on the index after which you can dump the root block to see the ITL entries =================================  OBJECT_ID OBJECT_TYPE         OBJECT_NAME ---------- ------------------- ------------------------------      90552 INDEX               IL_01 --//0x10002b3 = set dba 4,691 = alter system dump datafile 4 block 691 = 16777907 --//可以发现测试结果一样,也是要找ITL槽.不需要倒序插入,正序也可以出现问题. --//测试输入参数=170就出现死锁情况,如果设置参数169,ok,插入168条记录. Leaf block dump =============== header address 140249315344396=0x7f8e569f000c kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 0 kdxconro 0 kdxcofbo 36=0x24 kdxcofeo 4024=0xfb8 kdxcoavs 3988 kdxlespl 0 kdxlende 0 kdxlenxt 0=0x0 kdxleprv 0=0x0 kdxledsz 6 kdxlebksz 4024 *** dummy key *** row#0[4013] flag: ------, lock: 2, len=11, data:(6):  01 00 02 ac 00 00 col 0; len 2; (2):  c1 02 ----- end of leaf block dump ----- End dump data blocks tsn: 4 file#: 4 minblk 691 maxblk 691 --//注意转储记录1条记录,lock=2.无效. $ grep "^WAIT.*TX"   /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_62284.trc >| itl.txt $ awk '{print  $10}' itl.txt | paste - - - - - - - - - - - - 1000309 1000822 1000985 1000886 1000882 1000922 1000921 1000886 1000870 1000954 1000907 1000872 2000916 2000884 2000471 2000775 2000658 2001180 2000924 2000651 2001133 2000608 2000884 2000920 4001892 4001905 4001902 4001914 4001884 4001929 4001877 4001930 4001894 4001906 4001869 4001914 5001824 5001897 5001904 5001884 5001867 5001892 5001919 5001878 5001927 5001894 5001902 3000864 $ awk '{print  $10}' itl.txt | paste $(seq 12 | xargs -IQ echo '-'| paste -sd' ') $ awk '{print  $10}' itl.txt | paste -d" " $(seq 12 | xargs -IQ echo -n '- ') 1000309 1000822 1000985 1000886 1000882 1000922 1000921 1000886 1000870 1000954 1000907 1000872 2000916 2000884 2000471 2000775 2000658 2001180 2000924 2000651 2001133 2000608 2000884 2000920 4001892 4001905 4001902 4001914 4001884 4001929 4001877 4001930 4001894 4001906 4001869 4001914 5001824 5001897 5001904 5001884 5001867 5001892 5001919 5001878 5001927 5001894 5001902 3000864 --//你可以发现一个特点.12个ITL槽为1组,开始1秒,然后2秒,然后4秒,然后5秒,最后1个检测3秒,死锁. $ awk '{print  $10}' itl.txt | paste -sd'+' | bc 142064688 --//共计142秒. --//ITL等待检测的伪代码如下: 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 --//最大12个ITL槽为1组,开始1秒,然后2秒,然后4秒,然后5秒,最后1个ITL等待秒数是 2^(迭代次数-1).再然后还是5秒,最后1个ITL等待秒数 --//是 2^(迭代次数-1),在迭代10次以后,第11次迭代,其它ITL等待5秒,最后1个ITL无限等待下去,有空再次验证看看,主要是检测时间有点长.