[20231027]Index ITL Limit 3.txt
[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.建立測試指令碼：
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2992412/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20231027]Index ITL Limit 2.txtIndexMIT
- [20220223]Index ITL Limit.txtIndexMIT
- [20231031]Index ITL Limit 4.txtIndexMIT
- [20210316]MSSM表空間塊ITL的LCK 3.txtSSM
- PostgreSQL DBA(119) - pgAdmin(LIMIT：Index Scan vs Bitmap Index Scan)SQLMITIndex
- MySQL 中 一條 order by index limit 語句的分析MySqlIndexMIT
- 隨筆：MySQL:eq_range_index_dive_limit 索引下探介面MySqlIndexMIT索引
- 遭遇ITL死鎖
- linux limit限制LinuxMIT
- 極限limitMIT
- [20210223]bbed itl ktbitflg 2.txt
- [20210604]索引分裂與 itl ktbitflg.txt索引
- enq: TX - allocate ITL entry等待事件分析ENQ事件
- set_time_limitMIT
- Mysql LIMIT的用法MySqlMIT
- 關於enq: TX - allocate ITL entry等待事件ENQ事件
- [20180327]行遷移與ITL浪費.txt
- MySQL中limit的用法MySqlMIT
- DMSQL LIMIT限定條件SQLMIT
- [20190129]塊內重整3.txt
- [201804012]關於hugepages 3.txt
- [20210904]如何實現3.txt
- 【TUNE_ORACLE】PGA_AGGREGATE_LIMIT詳解（二）PGA_AGGREGATE_LIMIT的作用OracleMIT
- Index of /virtualboxIndex
- PostgreSQL：INDEXSQLIndex
- SQL優化之limit 1SQL優化MIT
- Laravel 中 offset，limit 的使用LaravelMIT
- SQL之limit子句的使用SQLMIT
- mysql 使用技巧 分頁limitMySqlMIT
- sql中limit使用方法SQLMIT
- oracle invisible index與unusable index的區別OracleIndex
- [20190125]MSSM表空間塊ITL的LCK.txtSSM
- [20191210]降序索引疑問3.txt索引
- [20220909]AnonHugePages與transparent hugepage 3.txt
- Python, pandas: how to sort dataframe by index// Merge two dataframes by indexPythonIndex
- 【TUNE_ORACLE】PGA_AGGREGATE_LIMIT詳解（三）PGA_AGGREGATE_LIMIT的大小設定OracleMIT
- index.jspIndexJS
- null與indexNullIndex