[20171123]Skip Locked and ITL slot 2.txt
--//昨天看链接提到Skip Locked and ITL slot相关问题,链接
http://jonathanlewis.wordpress.com/2010/05/31/skip-locked/
--//我自己重复测试看看:
1.环境:
SCOTT@book> @ &r/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
2.测试建立:
create table t1 (
id number(6),
modded number(6)
)
pctused 99
pctfree 0
;
insert into t1
select
rownum id,
mod(rownum,3) modded
from
all_objects
where
rownum <= 5000
;
commit;
-- gather stats at this point.
This gave me 693 rows in the first block, 2 entries in the ITL, and 10 bytes of free space so that I could not add an
ITL entry to the block.
Run the following from three different sessions – supply 0, 1, and 2 as the input parameter in turn.
--//建立3个session ,分别带入0,1,2:
select id from t1 where modded = &1 and rownum <=100 for update skip locked;
The first two sessions will return 100 rows, the third session will (should) return no rows – even though there are no
locked rows for the given value of modded – but the first hundred rows that will be scanned cannot be locked because an
ITL entry cannot be acquired.
--//前面2个会话返回100行,而第3个会话因为ITL槽耗尽,无法分配ITL槽,查询结果如下:
SCOTT@book> select id from t1 where modded = &1 and rownum <=100 for update skip locked;
Enter value for 1: 2
old 1: select id from t1 where modded = &1 and rownum <=100 for update skip locked
new 1: select id from t1 where modded = 2 and rownum <=100 for update skip locked
no rows selected
Remove the skip locked from the query and repeat the test – the third session will get stuck on "enq: TX – allocate
ITL entry".
--//如果删除for update skip locked;
select id from t1 where modded = &1 and rownum <=100;
--//查询返回100行.
select id from t1 where modded = &1 and rownum <=100 for update;
SCOTT@book> select id from t1 where modded = &1 and rownum <=100 for update;
Enter value for 1: 2
old 1: select id from t1 where modded = &1 and rownum <=100 for update
new 1: select id from t1 where modded = 2 and rownum <=100 for update
--//我的测试挂起!!查看等待事件如下:
SYS@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATUS STATE WAIT_TIME_MICRO SECONDS_IN_WAIT WAIT_CLASS
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- -------- ------------------- --------------- --------------- --------------------
0000000054580004 00000000000A001F 000000000000599C 1415053316 655391 22940 80 59 42 enq: TX - allocate ITL entry ACTIVE WAITING 14933217 15 Configuration
SCOTT@book> select id from t1 where modded = &1 and rownum <=100 for update;
Enter value for 1: 2
old 1: select id from t1 where modded = &1 and rownum <=100 for update
new 1: select id from t1 where modded = 2 and rownum <=100 for update
select id from t1 where modded = 2 and rownum <=100 for update
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SCOTT@book> @ &r/spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
80 59 19582 DEDICATED 19583 30 27 alter system kill session '80,59' immediate;