[20231027]Index ITL Limit 3.txt

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

[20231027]Index ITL Limit 3.txt --//链接https://jonathanlewis.wordpress.com/2022/02/18/index-itl-limit/,使用自治事务。 --//自己尝试不使用自治事务写一个看看。 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.建立测试脚本： 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 ; --//分析略. $ 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槽. --//我开始使用seq 169，结果前面执行时就出现阻塞了。 3.测试: $ source itl_limit.sh trunc(sysdate)+09/24+41/1440+59/86400 == 2023/10/31 09:41:59 == timestamp'2023-10-31 09:41:59' trunc(sysdate)+09/24+41/1440+59/86400 == 2023/10/31 09:41:59 == timestamp'2023-10-31 09:41:59' sleep 35 SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- -------------------------------------------------- 46 71 13125 DEDICATED 13127 194 8 alter system kill session '46,71' immediate; TRACEFILE ---------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_13127.trc Session altered. 1 row created. XIDUSN_XIDSLOT_XIDSQN ------------------------------ 122.15.4 --//122 = 0x007a Commit complete. Session altered. trunc(sysdate)+10/24+32/1440+30/86400 == 2023/10/31 10:32:30 == timestamp'2023-10-31 10:32:30' trunc(sysdate)+10/24+32/1440+30/86400 == 2023/10/31 10:32:30 == timestamp'2023-10-31 10:32:30' --//总共监测 (19+32)*60+30-59 = 3031秒。 4.分析: $ grep "^WAIT.*TX" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_13127.trc | head -24 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 1000974 name|mode=1415053316 usn<<16 | slot=2752525 sequence=4 obj#=90592 tim=1698716555273345 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 1000886 name|mode=1415053316 usn<<16 | slot=1900562 sequence=4 obj#=90592 tim=1698716556274337 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 1000912 name|mode=1415053316 usn<<16 | slot=6356998 sequence=4 obj#=90592 tim=1698716557275338 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 1000919 name|mode=1415053316 usn<<16 | slot=10682382 sequence=4 obj#=90592 tim=1698716558276345 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 1000909 name|mode=1415053316 usn<<16 | slot=1835031 sequence=4 obj#=90592 tim=1698716559277343 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 1000210 name|mode=1415053316 usn<<16 | slot=8847365 sequence=4 obj#=90592 tim=1698716560327376 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 1000884 name|mode=1415053316 usn<<16 | slot=6488067 sequence=4 obj#=90592 tim=1698716561328351 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 1000934 name|mode=1415053316 usn<<16 | slot=5636121 sequence=4 obj#=90592 tim=1698716562329366 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 1000918 name|mode=1415053316 usn<<16 | slot=1507349 sequence=4 obj#=90592 tim=1698716563330398 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 1000809 name|mode=1415053316 usn<<16 | slot=5570584 sequence=4 obj#=90592 tim=1698716564331346 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 1000232 name|mode=1415053316 usn<<16 | slot=2293765 sequence=4 obj#=90592 tim=1698716565358357 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 1000882 name|mode=1415053316 usn<<16 | slot=3211285 sequence=4 obj#=90592 tim=1698716566359329 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 2000882 name|mode=1415053316 usn<<16 | slot=2752525 sequence=4 obj#=90592 tim=1698716568360336 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 2000844 name|mode=1415053316 usn<<16 | slot=1900562 sequence=4 obj#=90592 tim=1698716570361333 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 2000871 name|mode=1415053316 usn<<16 | slot=6356998 sequence=4 obj#=90592 tim=1698716572362335 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 2000873 name|mode=1415053316 usn<<16 | slot=10682382 sequence=4 obj#=90592 tim=1698716574363337 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 2000900 name|mode=1415053316 usn<<16 | slot=1835031 sequence=4 obj#=90592 tim=1698716576364325 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 2000877 name|mode=1415053316 usn<<16 | slot=8847365 sequence=4 obj#=90592 tim=1698716578365322 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 2000972 name|mode=1415053316 usn<<16 | slot=6488067 sequence=4 obj#=90592 tim=1698716580366388 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 2000872 name|mode=1415053316 usn<<16 | slot=5636121 sequence=4 obj#=90592 tim=1698716582367391 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 2000892 name|mode=1415053316 usn<<16 | slot=1507349 sequence=4 obj#=90592 tim=1698716584368375 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 2000889 name|mode=1415053316 usn<<16 | slot=5570584 sequence=4 obj#=90592 tim=1698716586369358 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 2000940 name|mode=1415053316 usn<<16 | slot=2293765 sequence=4 obj#=90592 tim=1698716588370386 WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 2000900 name|mode=1415053316 usn<<16 | slot=3211285 sequence=4 obj#=90592 tim=1698716590371381 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//12槽为一组. --//3211285 = /2^16 %2^16 (Type | Mode) = 49,21 = 0x310015 $ grep "^WAIT.*TX" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_13127.trc |awk '{print $10}' | paste -d" " $(seq 12 | xargs -IQ echo -n '- ')| nl 1 1000974 1000886 1000912 1000919 1000909 1000210 1000884 1000934 1000918 1000809 1000232 1000882 2 2000882 2000844 2000871 2000873 2000900 2000877 2000972 2000872 2000892 2000889 2000940 2000900 3 4001843 4001887 4001895 4001879 4001897 4001944 4001884 4001789 4001887 4001933 4001861 4001892 4 5001852 5002854 5001789 5001119 5001804 5001199 5002033 5001865 5001855 5001240 5001699 8002247 5 5001912 5001350 5001526 5001117 5001587 5001393 5001721 5001736 5001386 5001940 5001315 16005698 6 5001831 5001398 5001579 5001788 5001121 5001659 5001154 5001189 5001680 5001492 5001256 32010333 7 5001827 5001502 5001223 5001635 5001771 5001462 5001705 5001907 5001924 5001089 5001245 64001797 8 5001808 5001641 5001653 5001074 5001103 5001793 5001257 5001419 5001177 5001313 5001577 128002932 9 5001756 5001215 5001764 5001742 5001105 5001368 5001736 5001615 5001722 5001501 5001295 256005454 10 5001742 5001887 5001860 5001927 5001907 5001834 5001907 5001897 5001887 5001854 5001995 512010965 11 5001809 5001556 5001572 5001300 5001800 5001833 5001119 5001134 5001617 5001626 5001291 1453223436 --//取出ela= NNNN的数值。 --//最大12个ITL槽为1组,开始1秒,然后2秒,然后4秒,然后5秒,最后1个ITL等待秒数是 2^(迭代次数-1).再然后都是5秒,最后1个ITL等待秒数 --//总是 2^(迭代次数-1),在迭代10次以后,第11次迭代,其它ITL等待5秒,最后1个ITL槽无限等待下去. $ grep "^WAIT.*TX" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_13127.trc |awk '{print $10}' |paste -sd'+' | bc | xargs -IQ echo Q/1000000 | bc -l 2993.445351 --//总共等待2993.445351秒.这是我看以前链接的疑惑。 --//最早出现： --//WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 1000974 name|mode=1415053316 usn<<16 | slot=2752525 sequence=4 obj#=90592 tim=1698716555273345 --//tim=1698716555273345 $ xdate 1698716555273345/1000000 1 2023-10-31 09:42:35.273345000 D:\> c:\windows\system32\date -d "1970-01-01 00:00:00 UTC 1698716555 seconds" +"%Y-%m-%d %T" 2023-10-31 09:42:35 --//trunc(sysdate)+10/24+32/1440+30/86400 == 2023/10/31 10:32:30 == timestamp'2023-10-31 10:32:30' --//从出现enq: TX - allocate ITL entry到结束测试的时间间隔 --//(18+32)*60+30-35 = 2995,还给加上1秒. 实际上是2996秒. --//换一个思路，在3000秒sleep里面，扣除(168-153)/5，以及35-168/5 = 1.4秒。 --//3000-(168-153)/5 -(35-168/5) = 2995.6，有将近2.2秒的误差，应该在其它方面的消耗,在正常范围。 --//trunc(sysdate)+09/24+41/1440+59/86400 == 2023/10/31 09:41:59 == timestamp'2023-10-31 09:41:59' --//trunc(sysdate)+10/24+32/1440+30/86400 == 2023/10/31 10:32:30 == timestamp'2023-10-31 10:32:30' --//总共监测 (19+32)*60+30-59 = 3031秒。实际上等待ITL153回话的事务完成(看后面的解析)，释放对应ITL槽。 --//3000+35-(168-153)/5 = 3032秒，非常接近。 --//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 SYS@book> @wcy trunc(sysdate)+09/24+41/1440+59/86400 trunc(sysdate)+10/24+32/1440+30/86400 "event='enq: TX - allocate ITL entry'" -- Display ASH Wait Chain Signatures script v0.7 by Tanel Poder ( http://blog.tanelpoder.com ) %This SECONDS AAS WAIT_CHAIN FIRST_SEEN LAST_SEEN ------ ---------- ---------- -------------------------------------------------------------------------------- ------------------- ------------------- 83% 2472 .8 -> 196,31,@1=>46,71,@1=>enq: TX - allocate ITL entry -> [idle blocker 1,196,31] 2023-10-31 09:42:45 2023-10-31 10:32:29 2% 47 0 -> 180,21,@1=>46,71,@1=>enq: TX - allocate ITL entry -> [idle blocker 1,180,21] 2023-10-31 09:42:43 2023-10-31 10:08:11 2% 47 0 -> 30,21,@1=>46,71,@1=>enq: TX - allocate ITL entry -> [idle blocker 1,30,21] 2023-10-31 09:42:36 2023-10-31 10:07:36 2% 47 0 -> 207,39,@1=>46,71,@1=>enq: TX - allocate ITL entry -> [idle blocker 1,207,39] 2023-10-31 09:42:44 2023-10-31 10:08:16 2% 47 0 -> 63,27,@1=>46,71,@1=>enq: TX - allocate ITL entry -> [idle blocker 1,63,27] 2023-10-31 09:42:41 2023-10-31 10:08:01 2% 47 0 -> 373,25,@1=>46,71,@1=>enq: TX - allocate ITL entry -> [idle blocker 1,373,25] 2023-10-31 09:42:34 2023-10-31 10:07:26 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 2% 47 0 -> 406,25,@1=>46,71,@1=>enq: TX - allocate ITL entry -> [idle blocker 1,406,25] 2023-10-31 09:42:39 2023-10-31 10:07:51 2% 47 0 -> 130,43,@1=>46,71,@1=>enq: TX - allocate ITL entry -> [idle blocker 1,130,43] 2023-10-31 09:42:37 2023-10-31 10:07:41 2% 47 0 -> 16,33,@1=>46,71,@1=>enq: TX - allocate ITL entry -> [idle blocker 1,16,33] 2023-10-31 09:42:38 2023-10-31 10:07:46 2% 47 0 -> 380,39,@1=>46,71,@1=>enq: TX - allocate ITL entry -> [idle blocker 1,380,39] 2023-10-31 09:42:40 2023-10-31 10:07:56 2% 46 0 -> 84,13,@1=>46,71,@1=>enq: TX - allocate ITL entry -> [idle blocker 1,84,13] 2023-10-31 09:42:35 2023-10-31 10:07:31 2% 46 0 -> 109,25,@1=>46,71,@1=>enq: TX - allocate ITL entry -> [idle blocker 1,109,25] 2023-10-31 09:42:42 2023-10-31 10:08:06 12 rows selected. --//前面11个槽47秒,很容易验证 1+2+4+5*8 = 47. --//阻塞会话的sid是 196 180 30 207 63 373 406 130 16 380 84 109. --//下划线的FIRST_SEEN最小. $ awk '/ITL/{print }' /tmp/itl.txt | head | nl 1 ITL1 96 29 11764 DEDICATED 11797 2 ITL2 56 39 11766 DEDICATED 11795 3 ITL3 38 39 11767 DEDICATED 11796 4 ITL4 80 27 11770 DEDICATED 11800 5 ITL5 129 35 11771 DEDICATED 11808 6 ITL6 117 27 11774 DEDICATED 11803 7 ITL7 235 17 11775 DEDICATED 11811 8 ITL8 178 33 11777 DEDICATED 11818 9 ITL9 160 21 11779 DEDICATED 11817 10 ITL10 148 27 11781 DEDICATED 11815 --//我按照顺序执行的,ITL1的sid=96等于插入1的会话. $ awk '/ITL/{print $2}' /tmp/itl.txt | egrep -n "^196$|^180$|^30$|^207$|^63$|^373$|^406$|^130$|^16$|^380$|^84$|^109$" 142:373 ~~~~~~~~ 143:84 144:30 145:130 146:16 147:406 148:380 149:63 150:109 151:180 152:207 153:196 --//下划线的行相当于使用ITL142的会话.换一个写法可以验证我的判断. $ awk '/ITL/{print $2,$1}' /tmp/itl.txt | egrep -n "^196 |^180 |^30 |^207 |^63 |^373 |^406 |^130 |^16 |^380 |^84 |^109 " 142:373 ITL142 143:84 ITL143 144:30 ITL144 145:130 ITL145 146:16 ITL146 147:406 ITL147 148:380 ITL148 149:63 ITL149 150:109 ITL150 151:180 ITL151 152:207 ITL152 153:196 ITL153 --//前面都能对上.如果你结合前面@wcy的输出,可以推断ITL槽从小到大看看那个槽没有事务就是使用它. --//将@wcy的输出保存为/tmp/itl2.txt $ grep TX /tmp/itl2.txt | sort -k16 | awk '{print $5}' | sed 's/,.*$//' 373 84 30 130 16 406 380 63 109 180 207 196 --//前面说明sort -k16按照FIRST_SEEN的时分秒排序,然后awk '{print $5}'取出阻塞的会话部分,sed 's/,.*$//'删除后面多余的部分从,开始. --//顺序完全跟前面的对上. --//我不知道出现阻塞时,随机选取1段ITL槽尝试还是别的,感觉是随机选择.最大选择12个ITL槽,然后轮询探查. --//或者是与登陆的某个参数有关. 5.转储索引看看: SYS@book> @ seg2 scott.il_01 SEG_MB OWNER SEGMENT_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK ------ ----- ------------ ------------ ------------------- ------ ------ ------ 0 SCOTT IL_01 INDEX USERS 8 4 690 SYS@book> alter system dump datafile 4 block 690; System altered. Block header dump: 0x010002b3 Object id on Block? Y seg/obj: 0x161e0 csc: 0x03.1772299e itc: 169 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 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 0x02 0x007a.00f.00000004 0x00c00f2a.0001.14 --U- 1 fsc 0x0000.177229a5 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 0x03 0x001f.001.00000005 0x00c013e6.0002.38 C--- 0 scn 0x0003.17722733 0x04 0x0035.015.00000004 0x00c00ade.0001.0f C--- 0 scn 0x0003.17722737 0x05 0x0074.019.00000004 0x00c00ec4.0004.2c C--- 0 scn 0x0003.1772273b 0x06 0x0057.008.00000005 0x00c00cf2.0004.17 C--- 0 scn 0x0003.1772273f 0x07 0x000f.002.00000007 0x00c00775.0006.2f C--- 0 scn 0x0003.17722743 0x08 0x000e.000.00000007 0x00c00762.000e.14 C--- 0 scn 0x0003.17722747 0x09 0x0046.00b.00000004 0x00c00bed.0001.12 C--- 0 scn 0x0003.1772274b 0x0a 0x009c.00a.00000004 0x00c01142.0005.12 C--- 0 scn 0x0003.1772274f 0x0b 0x0015.010.00000004 0x00c00859.0001.28 C--- 0 scn 0x0003.17722753 0x0c 0x0053.011.00000004 0x00c00cbc.0001.2c C--- 0 scn 0x0003.17722757 0x0d 0x003b.010.00000004 0x00c00b38.0001.32 C--- 0 scn 0x0003.1772275c 0x0e 0x0048.007.00000004 0x00c00c08.0001.1e C--- 0 scn 0x0003.17722760 0x0f 0x001a.014.00000004 0x00c00922.0007.12 C--- 0 scn 0x0003.17722764 0x10 0x0018.019.00000004 0x00c00909.0001.25 C--- 0 scn 0x0003.17722768 0x11 0x001e.010.00000004 0x00c0096c.0001.0e C--- 0 scn 0x0003.1772276c 0x12 0x003f.004.00000004 0x00c00b79.0001.05 C--- 0 scn 0x0003.17722770 0x13 0x0041.004.00000004 0x00c00b98.0001.25 C--- 0 scn 0x0003.17722774 0x14 0x0030.015.00000004 0x00c00a85.0002.09 C--- 0 scn 0x0003.17722778 0x15 0x0092.01c.00000004 0x00c010a7.0004.26 C--- 0 scn 0x0003.1772277c 0x16 0x0089.009.00000004 0x00c0101c.0001.1d C--- 0 scn 0x0003.17722780 0x17 0x00a0.004.00000004 0x00c0118e.0001.0a C--- 0 scn 0x0003.17722784 0x18 0x0085.005.00000004 0x00c00fda.0001.0c C--- 0 scn 0x0003.17722788 0x19 0x0095.013.00000004 0x00c010d9.0001.1d C--- 0 scn 0x0003.1772278c 0x1a 0x0062.017.00000004 0x00c00daf.0006.11 C--- 0 scn 0x0003.17722790 0x1b 0x000d.008.00000006 0x00c00759.0002.21 C--- 0 scn 0x0003.17722794 0x1c 0x006e.014.00000004 0x00c00e63.0002.22 C--- 0 scn 0x0003.17722798 0x1d 0x0081.005.00000004 0x00c01501.0003.05 C--- 0 scn 0x0003.1772279c 0x1e 0x005b.00c.00000004 0x00c00d38.0001.26 C--- 0 scn 0x0003.177227a1 0x1f 0x0008.013.00000731 0x00c000f2.03d1.12 C--- 0 scn 0x0003.177227a5 0x20 0x005a.007.00000005 0x00c00d24.0008.22 C--- 0 scn 0x0003.177227a9 0x21 0x0037.007.00000005 0x00c00af2.0007.11 C--- 0 scn 0x0003.177227ad 0x22 0x0019.01c.00000004 0x00c01309.0005.19 C--- 0 scn 0x0003.177227b1 0x23 0x0058.015.00000004 0x00c00d0b.0001.0d C--- 0 scn 0x0003.177227b5 0x24 0x0020.017.00000004 0x00c00982.0006.12 C--- 0 scn 0x0003.177227b9 0x25 0x0091.013.00000004 0x00c0109b.0001.2c C--- 0 scn 0x0003.177227bd 0x26 0x0054.016.00000004 0x00c00cc9.0001.08 C--- 0 scn 0x0003.177227c1 0x27 0x006a.006.00000004 0x00c00e2e.0001.2a C--- 0 scn 0x0003.177227c5 0x28 0x0059.01f.00000004 0x00c01422.0008.04 C--- 0 scn 0x0003.177227c9 0x29 0x0028.012.00000004 0x00c00a04.0004.07 C--- 0 scn 0x0003.177227cd 0x2a 0x0011.013.00000006 0x00c0081b.0002.16 C--- 0 scn 0x0003.177227d1 0x2b 0x0038.004.00000005 0x00c00b02.0009.20 C--- 0 scn 0x0003.177227d5 0x2c 0x004f.021.00000004 0x00c00c72.0012.25 C--- 0 scn 0x0003.177227d9 0x2d 0x0039.014.00000005 0x00c00b1a.000d.1d C--- 0 scn 0x0003.177227dd 0x2e 0x0026.008.00000004 0x00c009e9.0001.0f C--- 0 scn 0x0003.177227e1 0x2f 0x0001.004.0000062e 0x00c008e3.0331.15 C--- 0 scn 0x0003.177227e6 0x30 0x0021.01b.00000004 0x00c00992.0008.0f C--- 0 scn 0x0003.177227ea 0x31 0x008d.021.00000004 0x00c003b1.0004.3a C--- 0 scn 0x0003.177227ee 0x32 0x0097.002.00000005 0x00c010f2.000b.2e C--- 0 scn 0x0003.177227f2 0x33 0x000c.00f.00000005 0x00c0074e.0002.07 C--- 0 scn 0x0003.177227f6 0x34 0x0072.00e.00000004 0x00c00ea8.0001.2f C--- 0 scn 0x0003.177227fa 0x35 0x002e.013.00000004 0x00c00a6e.0001.34 C--- 0 scn 0x0003.177227fe 0x36 0x0084.003.00000004 0x00c00fcd.0001.3a C--- 0 scn 0x0003.17722802 0x37 0x0073.003.00000005 0x00c004c7.0004.1c C--- 0 scn 0x0003.17722806 0x38 0x006b.00c.00000004 0x00c0133a.0004.30 C--- 0 scn 0x0003.1772280a 0x39 0x0049.013.00000004 0x00c00c12.0006.0b C--- 0 scn 0x0003.1772280e 0x3a 0x0051.01f.00000004 0x00c00c92.000c.12 C--- 0 scn 0x0003.17722813 0x3b 0x0044.017.00000004 0x00c00bc2.0007.1c C--- 0 scn 0x0003.17722818 0x3c 0x0082.006.00000004 0x00c013fe.0002.2b C--- 0 scn 0x0003.1772281d 0x3d 0x005c.01f.00000004 0x00c00d49.0006.16 C--- 0 scn 0x0003.17722821 0x3e 0x006f.014.00000004 0x00c00439.0002.0a C--- 0 scn 0x0003.17722825 0x3f 0x002d.005.00000005 0x00c0016b.000b.27 C--- 0 scn 0x0003.17722829 0x40 0x007e.008.00000005 0x00c00f63.0010.08 C--- 0 scn 0x0003.1772282d 0x41 0x008b.006.00000005 0x00c01032.0008.12 C--- 0 scn 0x0003.17722831 0x42 0x004d.014.00000004 0x00c00c5d.0001.2e C--- 0 scn 0x0003.17722835 0x43 0x0032.013.00000004 0x00c00aa9.0001.1b C--- 0 scn 0x0003.17722839 0x44 0x0047.000.00000004 0x00c0041c.0002.2a C--- 0 scn 0x0003.1772283d 0x45 0x0098.008.00000005 0x00c01104.0005.23 C--- 0 scn 0x0003.17722841 0x46 0x006d.010.00000004 0x00c00e5e.0006.28 C--- 0 scn 0x0003.17722845 0x47 0x0034.013.00000004 0x00c00acb.0001.23 C--- 0 scn 0x0003.17722849 0x48 0x0016.021.00000004 0x00c00862.0009.13 C--- 0 scn 0x0003.1772284e 0x49 0x0042.005.00000004 0x00c00ba9.0001.03 C--- 0 scn 0x0003.17722852 0x4a 0x0010.018.00000006 0x00c01371.0005.38 C--- 0 scn 0x0003.17722856 0x4b 0x008a.002.00000004 0x00c0102d.0001.31 C--- 0 scn 0x0003.1772285a 0x4c 0x002c.010.00000004 0x00c00a43.0005.10 C--- 0 scn 0x0003.1772285e 0x4d 0x004e.00d.00000004 0x00c00c6c.0001.0a C--- 0 scn 0x0003.17722862 0x4e 0x0099.00b.00000004 0x00c01118.0001.1b C--- 0 scn 0x0003.17722866 0x4f 0x0045.00f.00000004 0x00c00bd2.0005.11 C--- 0 scn 0x0003.1772286b 0x50 0x00a1.012.00000004 0x00c0119e.0004.0a C--- 0 scn 0x0003.1772286f 0x51 0x004a.01b.00000004 0x00c00c23.0008.34 C--- 0 scn 0x0003.17722873 0x52 0x0096.010.00000004 0x00c010ea.0001.11 C--- 0 scn 0x0003.17722877 0x53 0x0070.01f.00000004 0x00c00e82.0007.12 C--- 0 scn 0x0003.1772287b 0x54 0x007b.020.00000004 0x00c00f33.0007.0d C--- 0 scn 0x0003.1772287f 0x55 0x0006.009.000007a7 0x00c005e2.036e.17 C--- 0 scn 0x0003.17722883 0x56 0x0066.001.00000004 0x00c00dea.0001.3b C--- 0 scn 0x0003.17722887 0x57 0x009d.013.00000004 0x00c01152.0008.0f C--- 0 scn 0x0003.1772288b 0x58 0x004b.009.00000004 0x00c00c39.0001.3e C--- 0 scn 0x0003.1772288f 0x59 0x00a8.015.00000004 0x00c01202.0007.10 C--- 0 scn 0x0003.17722893 0x5a 0x0090.007.00000005 0x00c01085.0007.05 C--- 0 scn 0x0003.17722898 0x5b 0x0093.015.00000005 0x00c010b2.000a.18 C--- 0 scn 0x0003.1772289c 0x5c 0x008f.010.00000004 0x00c0107a.0001.1f C--- 0 scn 0x0003.177228a0 0x5d 0x0076.014.00000004 0x00c00ee2.0002.14 C--- 0 scn 0x0003.177228a4 0x5e 0x0071.020.00000004 0x00c01227.0002.1e C--- 0 scn 0x0003.177228a8 0x5f 0x003e.00c.00000005 0x00c00b64.0006.18 C--- 0 scn 0x0003.177228ac 0x60 0x00a4.002.00000004 0x00c011c8.0001.33 C--- 0 scn 0x0003.177228b0 0x61 0x008c.008.00000005 0x00c01046.000c.29 C--- 0 scn 0x0003.177228b4 0x62 0x00a7.00b.00000004 0x00c011f8.0001.26 C--- 0 scn 0x0003.177228b8 0x63 0x005f.00f.00000004 0x00c00d72.0009.13 C--- 0 scn 0x0003.177228bc 0x64 0x0080.003.00000004 0x00c00f8a.0001.0c C--- 0 scn 0x0003.177228c0 0x65 0x005e.005.00000004 0x00c00d68.0001.1a C--- 0 scn 0x0003.177228c4 0x66 0x0079.014.00000004 0x00c00f18.0001.33 C--- 0 scn 0x0003.177228c9 0x67 0x0036.015.00000004 0x00c00aec.0001.0c C--- 0 scn 0x0003.177228cd 0x68 0x006c.015.00000004 0x00c00e46.0002.0c C--- 0 scn 0x0003.177228d1 0x69 0x0014.01b.00000006 0x00c0084f.0002.30 C--- 0 scn 0x0003.177228d5 0x6a 0x0075.015.00000004 0x00c00ede.0001.31 C--- 0 scn 0x0003.177228d9 0x6b 0x0067.006.00000004 0x00c0131d.0002.1e C--- 0 scn 0x0003.177228dd 0x6c 0x0007.007.0000062c 0x00c000e3.02ca.0b C--- 0 scn 0x0003.177228e1 0x6d 0x0052.018.00000004 0x00c00ca4.0002.10 C--- 0 scn 0x0003.177228e6 0x6e 0x005d.006.00000005 0x00c00d52.0005.17 C--- 0 scn 0x0003.177228ea 0x6f 0x0043.00a.00000004 0x00c00bb9.0001.1f C--- 0 scn 0x0003.177228ee 0x70 0x0065.01a.00000004 0x00c00ddc.0008.1e C--- 0 scn 0x0003.177228f2 0x71 0x003d.01b.00000004 0x00c00b54.0002.21 C--- 0 scn 0x0003.177228f6 0x72 0x009a.00e.00000004 0x00c01128.0001.2e C--- 0 scn 0x0003.177228fa 0x73 0x0002.009.00000710 0x00c00094.02ba.1f C--- 0 scn 0x0003.177228fe 0x74 0x0033.006.00000005 0x00c00ab4.0003.17 C--- 0 scn 0x0003.17722902 0x75 0x00a6.008.00000004 0x00c011e3.0002.06 C--- 0 scn 0x0003.17722907 0x76 0x0024.004.00000004 0x00c009cd.0001.1c C--- 0 scn 0x0003.1772290b 0x77 0x007d.01e.00000004 0x00c00f53.0007.1f C--- 0 scn 0x0003.1772290f 0x78 0x007c.003.00000005 0x00c00f46.0002.3c C--- 0 scn 0x0003.17722913 0x79 0x0040.00a.00000004 0x00c00b8b.0001.1c C--- 0 scn 0x0003.17722917 0x7a 0x0068.011.00000004 0x00c013a0.0003.1f C--- 0 scn 0x0003.1772291b 0x7b 0x0013.00e.00000006 0x00c0083a.0002.06 C--- 0 scn 0x0003.1772291f 0x7c 0x0005.009.00000715 0x00c0021c.04b4.3d C--- 0 scn 0x0003.17722923 0x7d 0x0088.012.00000004 0x00c01002.0006.16 C--- 0 scn 0x0003.17722927 0x7e 0x0029.017.00000004 0x00c00a12.0006.1e C--- 0 scn 0x0003.1772292b 0x7f 0x009b.020.00000004 0x00c0113c.0001.23 C--- 0 scn 0x0003.1772292f 0x80 0x007f.00a.00000004 0x00c00f7a.0001.1f C--- 0 scn 0x0003.17722933 0x81 0x000b.004.00000007 0x00c00521.000d.28 C--- 0 scn 0x0003.17722937 0x82 0x0003.004.00000706 0x00c000a5.0433.17 C--- 0 scn 0x0003.1772293b 0x83 0x0009.005.00000ac0 0x00c00103.0401.1f C--- 0 scn 0x0003.1772293f 0x84 0x0060.007.00000004 0x00c00d8a.0001.0e C--- 0 scn 0x0003.17722943 0x85 0x009e.009.00000004 0x00c0116c.0001.09 C--- 0 scn 0x0003.17722948 0x86 0x0094.01e.00000004 0x00c013dd.0004.04 C--- 0 scn 0x0003.1772294c 0x87 0x0064.00a.00000004 0x00c00dc3.0002.33 C--- 0 scn 0x0003.17722950 0x88 0x0086.00c.00000004 0x00c00fe2.0004.13 C--- 0 scn 0x0003.17722954 0x89 0x004c.005.00000004 0x00c00c48.0001.23 C--- 0 scn 0x0003.17722958 0x8a 0x0022.00e.00000004 0x00c012bd.0004.1b C--- 0 scn 0x0003.1772295c 0x8b 0x0069.006.00000004 0x00c00e19.0001.35 C--- 0 scn 0x0003.17722960 0x8c 0x009f.015.00000004 0x00c01172.0007.10 C--- 0 scn 0x0003.17722964 0x8d 0x0027.00a.00000005 0x00c009f9.000c.2c C--- 0 scn 0x0003.17722968 0x8e 0x002b.00c.00000004 0x00c00a3c.0001.19 C--- 0 scn 0x0003.1772296c 0x8f 0x002a.00d.00000004 0x00c00a22.001b.13 C--- 0 scn 0x0003.17722970 --//slot=2752525 = /2^16 %2^16 = 42,13 = 0x2a000d 0x90 0x001d.012.00000004 0x00c00959.0001.14 C--- 0 scn 0x0003.17722974 --//slot=1900562 = /2^16 %2^16 = 29,18 = 0x1d0012 0x91 0x0061.006.00000004 0x00c00d98.0001.2c C--- 0 scn 0x0003.17722978 --//slot=6356998 = /2^16 %2^16 = 97,6 = 0x610006 0x92 0x00a3.00e.00000004 0x00c011b2.0005.14 C--- 0 scn 0x0003.1772297c --//slot=10682382 = /2^16 %2^16 = 163,14 = 0xa3000e 0x93 0x001c.017.00000004 0x00c0094b.0001.2b C--- 0 scn 0x0003.17722980 --//slot=1835031 = /2^16 %2^16 = 28,23 = 0x1c0017 0x94 0x0087.005.00000004 0x00c00ffa.0001.19 C--- 0 scn 0x0003.17722984 --//slot=8847365 = /2^16 %2^16 = 135,5 = 0x870005 0x95 0x0063.003.00000004 0x00c00db8.0001.28 C--- 0 scn 0x0003.17722989 --//slot=6488067 = /2^16 %2^16 = 99,3 = 0x630003 0x96 0x0056.019.00000004 0x00c00108.0002.18 C--- 0 scn 0x0003.1772298d --//slot=5636121 = /2^16 %2^16 = 86,25 = 0x560019 0x97 0x0017.015.00000004 0x00c00878.0001.2f C--- 0 scn 0x0003.17722991 --//slot=1507349 = /2^16 %2^16 = 23,21 = 0x170015 0x98 0x0055.018.00000004 0x00c00cd2.0002.2f C--- 0 scn 0x0003.17722995 --//slot=5570584 = /2^16 %2^16 = 85,24 = 0x550018 0x99 0x0023.005.00000004 0x00c009b8.0001.32 C--- 0 scn 0x0003.17722999 --//slot=2293765 = /2^16 %2^16 = 35,5 = 0x230005 0x9a 0x0031.015.00000004 0x00c00a92.0009.1c C--- 0 scn 0x0003.1772299d --//slot=3211285 = /2^16 %2^16 = 49,21 = 0x310015 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 0x9b 0x0025.011.00000004 0x00c0011d.0002.12 --U- 1 fsc 0x0000.177229a1 0x9c 0x00a9.007.00000004 0x00c0051c.0002.30 --U- 1 fsc 0x0000.177229a9 0x9d 0x0004.007.00000639 0x00c000b3.034d.34 --U- 1 fsc 0x0000.177229ad 0x9e 0x0083.00a.00000004 0x00c00fbd.0001.1a --U- 1 fsc 0x0000.177229b1 0x9f 0x001b.016.00000004 0x00c00932.0006.15 --U- 1 fsc 0x0000.177229b5 0xa0 0x00a2.00a.00000004 0x00c011aa.0001.31 --U- 1 fsc 0x0000.177229b9 0xa1 0x00a5.01c.00000004 0x00c011d2.0009.16 --U- 1 fsc 0x0000.177229bd 0xa2 0x008e.003.00000005 0x00c01062.0007.12 --U- 1 fsc 0x0000.177229c1 0xa3 0x002f.012.00000004 0x00c00a75.0004.26 --U- 1 fsc 0x0000.177229c5 0xa4 0x0012.017.00000006 0x00c0082b.0002.3b --U- 1 fsc 0x0000.177229c9 0xa5 0x000a.01d.000053ea 0x00c00282.0f90.2a --U- 1 fsc 0x0000.177229cd 0xa6 0x0050.016.00000004 0x00c00c83.0012.0a --U- 1 fsc 0x0000.177229d1 0xa7 0x003a.00c.00000004 0x00c00b2a.0001.21 --U- 1 fsc 0x0000.177229d5 0xa8 0x0078.003.00000005 0x00c00f0d.0008.0e --U- 1 fsc 0x0000.177229d9 0xa9 0x003c.00e.00000004 0x00c00b4b.0001.2a --U- 1 fsc 0x0000.177229dd --//注意看下划线最后事务使用该ITL槽完成插入. --//WAIT #140211912589984: nam='enq: TX - allocate ITL entry' ela= 1000882 name|mode=1415053316 usn<<16 | slot=3211285 sequence=4 obj#=90592 tim=1698716566359329 --//3211285 = /2^16 %2^16 (Type | Mode) = 49,21 = 0x310015 --//0x9a = 154,使用154 ITL槽,我前面ITL153,相差1,这个很好理解,实际索引块的第1个itl槽是用来做索引分裂的.dml事务使用从itl=0x02开始. --//还有一个细节，虽然是等待itl=0x9a的事务结束释放，但是实际上该事务使用的ITL槽是0x02。 0x02 0x007a.00f.00000004 0x00c00f2a.0001.14 --U- 1 fsc 0x0000.177229a5 --//你可以从前面@xid的输出确定。 XIDUSN_XIDSLOT_XIDSQN ------------------------------ 122.15.4 --//122 = 0x007a --//也就是oracle在等待itl=0x9a事务槽释放后，还是选择没有使用的scn最小的事务槽，使用它。 $ awk '/ITL/{print $2,$1}' /tmp/itl.txt | egrep -n "^196 |^180 |^30 |^207 |^63 |^373 |^406 |^130 |^16 |^380 |^84 |^109 "| tail -1 153:196 ITL153 --//转储剩余部分. Leaf block dump =============== header address 140323553233420=0x7f9f9f8b4a0c kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 0 kdxconro 169 kdxcofbo 374=0x176 kdxcofeo 2096=0x830 kdxcoavs 1722 kdxlespl 0 kdxlende 0 kdxlenxt 0=0x0 kdxleprv 0=0x0 kdxledsz 6 kdxlebksz 4024 row#0[4013] flag: ------, lock: 0, len=11, data:(6): 01 00 02 af 00 00 col 0; len 2; (2): c1 02 ... row#167[2108] flag: ------, lock: 169, len=12, data:(6): 01 00 02 ae 00 21 col 0; len 3; (3): c2 02 45 row#168[2096] flag: ------, lock: 2, len=12, data:(6): 01 00 02 af 00 21 col 0; len 3; (3): c2 02 46 ----- end of leaf block dump ----- End dump data blocks tsn: 4 file#: 4 minblk 691 maxblk 691 --//通过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 6.总结: --//我重复一年前的测试,主要遇到一些不理解问题.我以为如果增值插入就不会出现索引的enq: TX - allocate ITL entry等待,因为这 --//时索引分裂不是50-50,而是90-10,90-10分裂实际上仅仅插入最大值在另外1块，实际上的情况是索引块使用168个ITL槽后,再有事务 --//出现，而索引块还有许多自由空间情况的情况下(索引键值占用空间很小)，会出现这样的等待事件,换一句话讲在生产系统很难遇到 --//这样的情况,毕竟要占用168个事务槽呢。 --//除非密集插入提交很慢的情况下，并且增序插入，索引键值偏小的情况下，才有可能遇到这样的情况。 --//还有实际上它并没有使用等待的itl槽，而是最后等待它释放后，选择没有使用的scn最小的事务槽. 7.附上wcy.sql脚本。 $ cat wcy.sql @ tpt/ash/ash_wait_chains BLOCKING_SESSION||','||BLOCKING_SESSION_SERIAL#||',@'||BLOCKING_INST_ID||'=>'||session_id||','||SESSION_SERIAL#||',@'||inst_id||'=>'||event "&&3" &&1 &&2

编辑推荐：

返回列表

[20231027]Index ITL Limit 3.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推