[20231031]Index ITL Limit 4.txt

[20231031]Index ITL Limit 4.txt

--//昨天做了Index ITL Limi的測試,參考連結=>[20231027]Index ITL Limit 3.txt.
--//我想看看這個邊界大概在那裡,測試看看.

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

SYS@book> @ hidez ^processes$
SYS@book> @ pr
==============================
NAME                          : processes
DESCRIPTION                   : user processes
DEFAULT_VALUE                 : FALSE
SESSION_VALUE                 : 250
SYSTEM_VALUE                  : 250
ISSES_MODIFIABLE              : FALSE
ISSYS_MODIFIABLE              : FALSE
PL/SQL procedure successfully completed.
--//測試需要processes=250.

2.建立測試指令碼：

--//drop table itl_limit purge;
create table itl_limit(n1 number) pctfree 0;
create unique index il_01 on itl_limit(n1) pctfree 0;
--//insert into itl_limit values(0);
--//commit ;
--//分析略.

--//測試的最後我使用bbed觀察:
--//透過bbed觀察:
BBED> set dba 4,691
        DBA             0x010002b3 (16777907 4,691)

BBED> map
 File: /mnt/ramdisk/book/users01.dbf (4)
 Block: 691                                   Dba:0x010002b3
------------------------------------------------------------
 KTB Data Block (Index Leaf)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 4080 bytes                   @20
 struct kdxle, 32 bytes                     @4108
 sb2 kd_off[169]                            @4140
 ub1 freespace[1722]                        @4478
 ub1 rowdata[1928]                          @6200
 ub4 tailchk                                @8188

--//freespace還剩下1722.
--//1722/168 = 10.25
--//1722-168*10 = 42

--//也就是我建立的索引每個鍵值增加10個位元組,應該還會出現類似問題.

--//drop table itl_limit purge;
create table itl_limit(n1 number) pctfree 0;
create unique index il_01 on itl_limit(n1,'123456789') pctfree 0;
--//增加9個字元,因為索引欄位每個前面有1個位元組的長度指示器.

$ cat itl_4.txt
set head off
set verify off
set feedback off
host sleep $(echo &&1/5 | bc -l )
select 'ITL'||&1,s.sid,s.serial# ,s.process,s.server,p.spid from v$session s,v$process p where s.sid in (select sid from v$mystat where rownum=1) and s.paddr=p.addr;
insert into itl_limit values(&&1);
host sleep 3000
commit ;
quit
--//注執行前加入sleep $(echo &&1/5 | bc -l ),保證執行按照自己定義的順序插入資料,這樣可以按照順序使用ITL槽。
--//整個測試需要至少3000+35秒，需要足夠的耐心。

$ cat itl_limit.sh
#! /bin/bash
# test enq TX - allocate ITL entry (index)
zzdate | tee /tmp/itl_trace.txt
zzdate | tee /tmp/itl.txt

# 168/5 = 33.6秒後,保證全部回話執行itl_4.txt的指令碼執行到select * from t where x = &&1 for update ;
seq 168 | xargs -IQ echo "sqlplus -s -l scott/book @itl_4.txt Q &" | bash | tee -a /tmp/itl.txt > /dev/null &
echo sleep 35
sleep 35
# sleep 35,保證前面的全部回話執行itl_4.txt的指令碼執行到insert into itl_limit values(&&1);
sqlplus -s -l scott/book <<EOF  | tee -a /tmp/itl_trace.txt
@ spid
@ t
@ 10046on 8
insert into itl_limit values(169);
@ xid
commit;
@10046off
EOF

zzdate | tee -a /tmp/itl_trace.txt
zzdate | tee -a /tmp/itl.txt

--//注:索引最大169個ITL槽,其中第1個ITL作為索引分裂使用,供事務使用168個ITL槽.

3.測試1:
$ source itl_limit.sh
trunc(sysdate)+08/24+37/1440+37/86400 == 2023/11/01 08:37:37 == timestamp'2023-11-01 08:37:37'
trunc(sysdate)+08/24+37/1440+37/86400 == 2023/11/01 08:37:37 == timestamp'2023-11-01 08:37:37'
sleep 35
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        81        133 21787                    DEDICATED 21789      196         16 alter system kill session '81,133' immediate;

TRACEFILE
----------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_21789.trc

Session altered.

^C
trunc(sysdate)+08/24+38/1440+56/86400 == 2023/11/01 08:38:56 == timestamp'2023-11-01 08:38:56'
trunc(sysdate)+08/24+38/1440+56/86400 == 2023/11/01 08:38:56 == timestamp'2023-11-01 08:38:56'
--//出現等待就ctrl+c中斷.

SYS@book> @ ashtop event 1=1 &1min
    Total                                                                                                      Distinct Distinct
  Seconds     AAS %This   EVENT                                      FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- --------
       28      .5   74% | enq: TX - allocate ITL entry               2023-11-01 08:38:13 2023-11-01 08:38:40          1       28
        9      .2   24% |                                            2023-11-01 08:37:43 2023-11-01 08:38:11          2        9
        1      .0    3% | ADR block file read                        2023-11-01 08:38:12 2023-11-01 08:38:12          1        1


$ grep "^WAIT.*TX" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_21789.trc |awk '{print  $10}' | paste -d" " $(seq 12 | xargs -IQ echo -n '- ')| nl
     1  1000597 1001010 1000943 1000860 1000913 1000864 1000945 1000892 1000936 1000882 1000907 1000822
     2  2000868 2001012 2000763 2001005 2000945 2000732 2001201 2000701 2000811 2000979 2001023 2000644
     3  4001977 3333963

4.測試2:
--//$ pkill -9 sqlplus
--//小心!1我的測試環境沒有問題.生產系統不能這樣操作.

--//drop table itl_limit purge;
create table itl_limit(n1 number) pctfree 0;
create unique index il_01 on itl_limit(n1,'0123456789') pctfree 0;
--//增加到10字元.
--//這樣應該就不會出現前面測試遇到等待事件enq: TX - allocate ITL entry情況.

$ source itl_limit.sh
trunc(sysdate)+08/24+43/1440+04/86400 == 2023/11/01 08:43:04 == timestamp'2023-11-01 08:43:04'
trunc(sysdate)+08/24+43/1440+04/86400 == 2023/11/01 08:43:04 == timestamp'2023-11-01 08:43:04'
sleep 35
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        84        231 23211                    DEDICATED 23213      196         19 alter system kill session '84,231' immediate;

TRACEFILE
----------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_23213.trc
Session altered.

1 row created.

XIDUSN_XIDSLOT_XIDSQN
------------------------------
154.8.6

Commit complete.
Session altered.

trunc(sysdate)+08/24+43/1440+39/86400 == 2023/11/01 08:43:39 == timestamp'2023-11-01 08:43:39'
trunc(sysdate)+08/24+43/1440+39/86400 == 2023/11/01 08:43:39 == timestamp'2023-11-01 08:43:39'
--//可以發現很快完成沒有任何問題.

SYS@book> @ ashtop event 1=1 trunc(sysdate)+08/24+43/1440+04/86400 trunc(sysdate)+08/24+43/1440+39/86400
    Total                                                                                                      Distinct Distinct
  Seconds     AAS %This   EVENT                                      FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- --------
        4      .1  100% |                                            2023-11-01 08:43:12 2023-11-01 08:43:37          1        4

$ grep "^WAIT.*TX" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_23213.trc|awk '{print  $10}' | paste -d" " $(seq 12 | xargs -IQ echo -n '- ')| nl

5.繼續看看索引的情況.
SCOTT@book> select * from itl_limit;
        N1
----------
       169
--//僅僅提交了1條,其它還在等待sleep 3000秒的時間.

SCOTT@book> @ seg2 il_01
SCOTT@book> @ pr
==============================
SEG_MB                        : 0
SEG_OWNER                     : SCOTT
SEG_SEGMENT_NAME              : IL_01
SEG_PARTITION_NAME            :
SEG_SEGMENT_TYPE              : INDEX
SEG_TABLESPACE_NAME           : USERS
BLOCKS                        : 8
HDRFIL                        : 4
HDRBLK                        : 690
PL/SQL procedure successfully completed.

SCOTT@book> @ treedump il_01
 OBJECT_ID
----------
     90608
Session altered.

--//跟蹤檔案內容.
*** 2023-11-01 08:57:22.195
----- begin tree dump
branch: 0x10002b3 16777907 (0: nrow: 2, level: 1)
   leaf: 0x10002b6 16777910 (-1: nrow: 165 rrow: 165)
   leaf: 0x10002b7 16777911 (0: nrow: 4 rrow: 4)
----- end tree dump

--//可以發現索引發生了分裂.1佔165條.另外1塊佔4條.

SCOTT@book> alter system dump datafile 4 block 691;
System altered.

Block header dump:  0x010002b3
 Object id on Block? Y
 seg/obj: 0x161f0  csc: 0x03.1773badd  itc: 1  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10002b0 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0077.009.00000007  0x00c006c2.0009.01  -BU-    1  fsc 0x0000.1773bb64
Branch block dump
=================
header address 139900878246476=0x7f3d3626d24c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 1
kdxcofbo 30=0x1e
kdxcofeo 8047=0x1f6f
kdxcoavs 8017
kdxbrlmc 16777910=0x10002b6
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8047] dba: 16777911=0x10002b7
col 0; len 3; (3):  c2 02 43
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 691 maxblk 691
--//可以發現節點僅僅佔1個ITL.它是索引的root節點。

--//16777910 = set dba 4,694 = alter system dump datafile 4 block 694 = 0x10002b6
--//16777911 = set dba 4,695 = alter system dump datafile 4 block 695 = 0x10002b7

SCOTT@book> alter system dump datafile 4 block 695;
System altered.

Block header dump:  0x010002b7
 Object id on Block? Y
 seg/obj: 0x161f0  csc: 0x03.1773bd5a  itc: 166  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10002b0 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0077.009.00000007  0x00c006c4.0009.02  CB--    0  scn 0x0003.1773bb64
0x02   0x0077.002.00000007  0x00c006c0.0009.04  ----    1  fsc 0x0000.00000000
0x03   0x0096.01a.00000006  0x00c010e5.0007.04  ----    1  fsc 0x0000.00000000
0x04   0x0079.00c.00000006  0x00c00f1e.0001.04  ----    1  fsc 0x0000.00000000
0x05   0x009a.008.00000006  0x00c0112e.0001.06  C---    0  scn 0x0003.1773bb69 --//xid=154.8.6 =  0x9a.0x8.0x6
0x06   0x0055.015.00000006  0x00c00cd7.0002.07  ----    0  fsc 0x0000.00000000
...
0xa4   0x0071.00a.00000008  0x00c00637.0013.06  ----    0  fsc 0x0000.00000000
0xa5   0x007b.018.00000006  0x00c00f32.0007.14  ----    0  fsc 0x0000.00000000
0xa6   0x0052.00e.00000006  0x00c00616.0004.04  ----    0  fsc 0x0000.00000000
--//0xa6 = 166.
Leaf block dump
===============
header address 139900878250436=0x7f3d3626e1c4
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 4004=0xfa4
kdxcoavs 3960
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 16777910=0x10002b6
kdxledsz 6
kdxlebksz 4096
row#0[4073] flag: ------, lock: 2, len=23, data:(6):  01 00 02 af 00 21
col 0; len 3; (3):  c2 02 43
col 1; len 10; (10):  30 31 32 33 34 35 36 37 38 39
row#1[4050] flag: ------, lock: 3, len=23, data:(6):  01 00 02 ae 00 21
col 0; len 3; (3):  c2 02 44
col 1; len 10; (10):  30 31 32 33 34 35 36 37 38 39
row#2[4027] flag: ------, lock: 4, len=23, data:(6):  01 00 02 ad 00 21
col 0; len 3; (3):  c2 02 45
col 1; len 10; (10):  30 31 32 33 34 35 36 37 38 39
row#3[4004] flag: ------, lock: 0, len=23, data:(6):  01 00 02 ac 00 21
col 0; len 3; (3):  c2 02 46 --//提交
col 1; len 10; (10):  30 31 32 33 34 35 36 37 38 39
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 695 maxblk 695

SCOTT@book> select dump(169,16) from dual ;
DUMP(169,16)
--------------------
Typ=2 Len=3: c2,2,46

--//測試你可以發現只要索引佔用空間大一些,達到23字元,就看不到這樣的情況了.
--//因為當ITL槽達到169個時,前面的索引資料塊還有剩餘空間,而受到ITL槽數量169的限制,索引第1個ITL僅僅用於分裂.
--//這樣如果有168個事務使用同一索引資料塊,再有事務進來就必須等待ITL槽的釋放,出現測試遇到的情況.
--//實際上這類的情況在實際的生產環境很難遇到,要滿足幾個條件,索引鍵值很小並且要許多事務使用同一資料塊.

--//但是有1個情況可以遇到,就是順序插入的資料,索引可能集中在1個資料塊操作,這樣瞬間會消耗許多ITL槽,而11g下索引分裂時會繼承
--//前面的ITL槽數量.導致大量的索引資料塊都被ITL槽空間佔用浪費了磁碟空間.據說12c以後oracle解決這個問題,我給繼續測試看看.

--//補充測試我的kd_off.sh指令碼看的情況.
$ source kd_off.sh 4,695 nc
x /rnc dba 4,695 *kd_off[2]
x /rnc dba 4,695 *kd_off[3]
x /rnc dba 4,695 offset 8063
x /rnc dba 4,695 offset 8040

sb2 kd_off[0]                               @4072     4073
sb2 kd_off[1]                               @4074     4050
sb2 kd_off[2]                              @4076      4027
sb2 kd_off[3]                              @4078      4004


BBED> x /rnc dba 4,695 *kd_off[2]
rowdata[73]                                 @8109
-----------
flag@8109:     0x00 (NONE)
lock@8110:     0x02
keydata[6]:    0x01  0x00  0x02  0xaf  0x00  0x21

data key:
col    0[3] @8118: 166
col   1[10] @8122: 0123456789


BBED> x /rnc dba 4,695 *kd_off[3]
rowdata[50]                                 @8086
-----------
flag@8086:     0x00 (NONE)
lock@8087:     0x03
keydata[6]:    0x01  0x00  0x02  0xae  0x00  0x21

data key:
col    0[3] @8095: 167
col   1[10] @8099: 0123456789


BBED> x /rnc dba 4,695 offset 8063
rowdata[27]                                 @8063
-----------
flag@8063:     0x00 (NONE)
lock@8064:     0x04
keydata[6]:    0x01  0x00  0x02  0xad  0x00  0x21

data key:
col    0[3] @8072: 168
col   1[10] @8076: 0123456789


BBED> x /rnc dba 4,695 offset 8040
rowdata[4]                                  @8040
----------
flag@8040:     0x00 (NONE)
lock@8041:     0x00
keydata[6]:    0x01  0x00  0x02  0xac  0x00  0x21

data key:
col    0[3] @8049: 169
col   1[10] @8053: 0123456789

--//補充一點我機器上的rlbbed是定義為函式，這樣呼叫
$ ./kd_off.sh 4,695 nc

--//提示找不到rlbbed命令，只能採用source kd_off.sh 4,695 nc的方式執行命令。
--//好久不用bbed有點生疏了。