[20150224]ITL不足的測試例子.txt
[20150224]ITL不足的測試例子.txt
--前幾天看Apress.Oracle.Database.Transactions.and.Locking.Revealed.1484207610.pdf,發現P39頁的測試例子,感覺不對,重複做
--一次。
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t
( x int primary key,
y varchar2(4000)
)
insert into t (x,y) select rownum, rpad('*',148,'*') from dual connect by level <= 46;
commit ;
select length(y),
dbms_rowid.rowid_block_number(rowid) blk,
count(*), min(x), max(x)
from t
group by length(y), dbms_rowid.rowid_block_number(rowid);
LENGTH(Y) BLK COUNT(*) MIN(X) MAX(X)
---------- ---------- ---------- ---------- ----------
148 163 46 1 46
--可以發現記錄在同一塊中。
SCOTT@test> SCOTT@test> select rowid from t where rownum=1;
ROWID
------------------
AABJJ4AAEAAAACjAAA
SCOTT@test> @lookup_rowid AABJJ4AAEAAAACjAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
299640 4 163 0 4,163 alter system dump datafile 4 block 163 ;
--建立測試過程:
create or replace procedure do_update( p_n in number )
as
pragma autonomous_transaction;
l_rec t%rowtype;
resource_busy exception;
pragma exception_init( resource_busy, -54 );
begin
select * into l_rec from t where x = p_n for update NOWAIT;
do_update( p_n+1 );
commit;
exception
when resource_busy
then
dbms_output.put_line( 'locked out trying to select row ' || p_n );
commit;
when no_data_found
then
dbms_output.put_line( 'we finished - no problems' );
commit;
end;
/
--說明這個是一個遞迴的過程,每次呼叫後都會執行1次commit。
SCOTT@test> set serveroutput on
SCOTT@test> exec do_update(1);
locked out trying to select row 38
PL/SQL procedure successfully completed.
--可以發現僅僅執行到38行,停止。
SCOTT@test> alter system checkpoint ;
System altered.
SCOTT@test> alter system dump datafile 4 block 163 ;
System altered.
Block header dump: 0x010000a3
Object id on Block? Y
seg/obj: 0x49278 csc: 0x02.cb66236e itc: 37 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10000a0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0011.009.000002e0 0x00c001e2.0171.07 --U- 1 fsc 0x0000.cb662399
0x02 0x001e.00b.00000019 0x00c015dd.001e.0b --U- 1 fsc 0x0000.cb66239a
0x03 0x0018.004.0000012f 0x00c0cc01.003f.12 --U- 1 fsc 0x0000.cb662398
0x04 0x001d.016.00000017 0x00c00113.002f.01 --U- 1 fsc 0x0000.cb662397
0x05 0x0016.010.00000179 0x00c0117a.006a.07 --U- 1 fsc 0x0000.cb662396
0x06 0x0008.005.000073be 0x00c006ea.211e.06 --U- 1 fsc 0x0000.cb662395
0x07 0x0022.01d.00000016 0x00c0017a.0023.08 --U- 1 fsc 0x0000.cb662394
0x08 0x001b.004.00000015 0x00c000eb.001c.01 --U- 1 fsc 0x0000.cb662393
0x09 0x002b.00c.00000016 0x00c003ab.0023.09 --U- 1 fsc 0x0000.cb662392
0x0a 0x0020.00e.00000016 0x00c00386.0024.06 --U- 1 fsc 0x0000.cb662391
0x0b 0x0003.01e.000074c3 0x00c06abe.2464.22 --U- 1 fsc 0x0000.cb662390
0x0c 0x0029.006.00000018 0x00c0022a.002f.07 --U- 1 fsc 0x0000.cb66238f
0x0d 0x0023.004.00000016 0x00c0018e.001b.3b --U- 1 fsc 0x0000.cb66238e
0x0e 0x0024.014.00000016 0x00c01cb5.0023.01 --U- 1 fsc 0x0000.cb66238d
0x0f 0x0027.01d.00000016 0x00c0020a.002b.08 --U- 1 fsc 0x0000.cb66238c
0x10 0x0001.016.000060a4 0x00c009e7.215d.0a --U- 1 fsc 0x0000.cb66238b
0x11 0x000d.012.0000069a 0x00c0062b.01fd.21 --U- 1 fsc 0x0000.cb66238a
0x12 0x0028.01b.00000014 0x00c00223.001c.02 --U- 1 fsc 0x0000.cb662389
0x13 0x0012.01c.000002f1 0x00c001f2.01b9.06 --U- 1 fsc 0x0000.cb662388
0x14 0x0009.012.000078a4 0x00c050b8.2301.2c --U- 1 fsc 0x0000.cb662387
0x15 0x002e.019.00000014 0x00c0027a.0010.0c --U- 1 fsc 0x0000.cb662386
0x16 0x0025.012.00000014 0x00c00791.001b.17 --U- 1 fsc 0x0000.cb662385
0x17 0x0005.01d.0000b8dd 0x00c04d09.2e54.05 --U- 1 fsc 0x0000.cb662384
0x18 0x000f.003.0000079f 0x00c02662.0242.2c --U- 1 fsc 0x0000.cb662383
0x19 0x0006.003.00008375 0x00c063a5.247f.08 --U- 1 fsc 0x0000.cb662382
0x1a 0x002d.013.00000015 0x00c0025b.001b.01 --U- 1 fsc 0x0000.cb662381
0x1b 0x0004.01f.0000771b 0x00c04c90.23d8.28 --U- 1 fsc 0x0000.cb662380
0x1c 0x0014.018.0000033e 0x00c00213.0246.15 --U- 1 fsc 0x0000.cb66237f
0x1d 0x0010.008.000005dc 0x00c02e1f.01ce.0d --U- 1 fsc 0x0000.cb66237e
0x1e 0x000c.00d.00002fd3 0x00c005b6.0aee.24 --U- 1 fsc 0x0000.cb66237d
0x1f 0x0017.01f.0000014a 0x00c028ae.0054.19 --U- 1 fsc 0x0000.cb66237c
0x20 0x0019.01d.00000015 0x00c000bf.002f.0c --U- 1 fsc 0x0000.cb66237b
0x21 0x002c.011.00000016 0x00c00252.0023.0b --U- 1 fsc 0x0000.cb66237a
0x22 0x000e.00d.00000608 0x00c00496.0211.01 --U- 1 fsc 0x0000.cb662379
0x23 0x0013.01e.0000030b 0x00c00202.0167.0b --U- 1 fsc 0x0000.cb662378
0x24 0x001c.016.00000015 0x00c0010b.0021.01 --U- 1 fsc 0x0000.cb662376
0x25 0x0021.014.00000015 0x00c020c3.0022.06 --U- 1 fsc 0x0000.cb662375
bdba: 0x010000a3
--可以發現生成37個itl槽,塊中剩餘空間已經不足了,無法在繼續操作,建立itl槽。
--但是後面提到如果插入的資料是:
-- truncate table t;
insert into t (x,y) select rownum, rpad('*',147,'*') from dual connect by level <= 46;
commit ;
-- 書中提示會成功,感覺不對,這樣僅僅節約46個位元組,一個itl槽佔用24個位元組,最多僅僅在容納2個itl,也就是39個itl槽。
-- 接著測試:
SCOTT@test> set serveroutput on
SCOTT@test> exec do_update(1);
locked out trying to select row 40
PL/SQL procedure successfully completed.
--可以發現結果與書中的不同,我在10g下也做了測試,結果是一樣的。要想全部執行成功。y長度少1個位元組,多2個itl槽。
--這樣算下來,至少:46-39=7 , 7/2=3.5 , 147-4=143.僅僅這樣可以實現:
SCOTT@test> truncate table t;
Table truncated.
SCOTT@test> insert into t (x,y) select rownum, rpad('*',143,'*') from dual connect by level <= 46;
46 rows created.
SCOTT@test> commit;
Commit complete.
SCOTT@test> exec do_update(1);
we finished - no problems
PL/SQL procedure successfully completed.
--如果欄位y的長度是144,有1條無法處理。
SCOTT@test> truncate table t;
Table truncated.
SCOTT@test> insert into t (x,y) select rownum, rpad('*',144,'*') from dual connect by level <= 46;
46 rows created.
SCOTT@test> commit;
Commit complete.
SCOTT@test> exec do_update(1);
locked out trying to select row 46
PL/SQL procedure successfully completed.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-1440805/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- dockerfile測試例子Docker
- [20231026]enq TX - allocate ITL entry的測試4.txtENQ
- IOS測試用例例子iOS
- 一個測試陣列用法的例子陣列
- 事務提交時itl上flag標記U測試!
- 軟體測試文件有用,但永遠不足
- [20120224]itl的問題.txt
- 演算法測試例子特殊輸入形式演算法
- [20120601]ITL的問題補充.txt
- Oracle進行模擬測試資料的一個例子Oracle
- [20170221]nocache工具的小測試.txt
- ITL
- 不足400元 A/I入門級處理器對比測試
- [20210604]索引分裂與 itl ktbitflg.txt索引
- [20231027]Index ITL Limit 2.txtIndexMIT
- [20231027]Index ITL Limit 3.txtIndexMIT
- [20231031]Index ITL Limit 4.txtIndexMIT
- [20180327]行遷移與ITL浪費.txt
- [20171123]Skip Locked and ITL slot 2.txt
- ITL的含義
- hadoop權威指南上 天氣例子測試執行Hadoop
- [20170708]tmux script例子.txtUX
- [20170912]sql injection例子.txtSQL
- [20170224]nocache工具的小測試2.txt
- [20210223]bbed itl ktbitflg 2.txt
- [20220223]Index ITL Limit.txtIndexMIT
- 軟體測試技術乾貨丨舉個例子告訴你,未開發完成的介面該如何測試
- [20210316]MSSM表空間塊ITL的LCK 3.txtSSM
- [20190125]MSSM表空間塊ITL的LCK.txtSSM
- [20190124]系統表空間塊ITL的LCK.txt
- 測試測試測試測試測試測試
- [20170203]dg磁碟空間不足的處理.txt
- 有誰測試過Jbuilder7中EJB例子中ESiteWL程式???UI
- 表的itl 屬性
- zt_ITL Cleanout和ITL中的事務狀態分析
- [20160919]sql注入例子.txtSQL
- [20160726]行連結行遷移與ITL槽.txt
- [20190416]exclusive latch測試指令碼.txt指令碼