[20171123]Skip Locked and ITL slot 2.txt

[20171123]Skip Locked and ITL slot 2.txt

--//昨天看連結提到Skip Locked and ITL slot相關問題,連結

--//我自己重複測試看看:

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;