[20231031]Index ITL Limit 4.txt

来源：这里教程网时间：2026-03-03 19:00:48 作者：

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