[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无限等待下去,有空再次验证看看,主要是检测时间有点长.