[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有點生疏了。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2992413/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20231027]Index ITL Limit 2.txtIndexMIT
- [20231027]Index ITL Limit 3.txtIndexMIT
- [20220223]Index ITL Limit.txtIndexMIT
- [20160729]行連結行遷移與ITL槽4.txt
- PostgreSQL DBA(119) - pgAdmin(LIMIT：Index Scan vs Bitmap Index Scan)SQLMITIndex
- MySQL 中 一條 order by index limit 語句的分析MySqlIndexMIT
- [20231026]enq TX - allocate ITL entry的測試4.txtENQ
- 隨筆：MySQL:eq_range_index_dive_limit 索引下探介面MySqlIndexMIT索引
- ITL
- oracle ITL槽Oracle
- 深入研究ITL阻塞與ITL死鎖 作者 piner
- 遭遇ITL死鎖
- ITL(Interested Transaction List)REST
- ITL的含義
- MySQL LIMIT 如何改寫成Oracle limitMySqlMITOracle
- zt_ITL Cleanout和ITL中的事務狀態分析
- 推薦：深入研究ITL阻塞與ITL死鎖(轉載)
- 表的itl 屬性
- limit優化MIT優化
- RESOURCE_LIMITMIT
- limit active sessionsMITSession
- MYSQL 5.6 5.7處理資料分佈不均的問題(eq_range_index_dive_limit引數)MySqlIndexMIT
- ITL與事務處理
- enq: TX - allocate ITL entryENQ
- TX:ITL LOCK(INITRANS,MAXINTRANS)
- set_time_limitMIT
- Mysql LIMIT的用法MySqlMIT
- linux limit限制LinuxMIT
- v$resource_limitMIT
- day limit in terms of paymentMIT
- ITL和Freelist的區別
- oracle ITL TX MODE 4問題Oracle
- ITL事務槽記載-01
- 從Dump資料塊看ITL
- KEEP INDEX | DROP INDEXIndex
- DMSQL LIMIT限定條件SQLMIT
- MySQL中limit的用法MySqlMIT
- oracle實現"limit"功能OracleMIT