​[20231027]Index ITL Limit 2.txt

[20231027]Index ITL Limit 2.txt

--//連結重複測試
--//如果例子插入語句
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無限等待下去,有空再次驗證看看,主要是檢測時間有點長.