[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有点生疏了。