[20171123]Skip Locked and ITL slot 2.txt

[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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2147644/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2147644/