[20180613]子游標不共享BIND_EQUIV_FAILURE.txt

--//前幾天在測試函式與標量子查詢中雜湊表的數量時,遇到一個sql語句不能共享的問題,做一個記錄.

1.環境:
SYS@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

grant execute on sys.dbms_lock to scott;

CREATE OR REPLACE FUNCTION sleep1 (seconds IN NUMBER)
RETURN NUMBER
is
d_date date;
BEGIN
select sysdate into d_date from dual;
sys.dbms_lock.sleep(seconds/10);
RETURN seconds;
END;
/

CREATE OR REPLACE FUNCTION sleep (seconds IN NUMBER)
RETURN NUMBER
is
d_date date;
BEGIN
select sysdate into d_date from dual;
--//sys.dbms_lock.sleep(0.01);
RETURN seconds;
END;
/

create table t as select rownum id1,mod(rownum-1,20000)+1 id2 from dual connect by level<=40000;

2.建立測試指令碼:
--//建立指令碼by.txt:
set term off
alter session set statistics_level=all;
variable x number;
exec :x := &&1;
select t.*,(select sleep(id2) from dual) s from t where id2<=:x;
set term on
@ &r/dpc '' ''
quit

--//建立shell指令碼by.sh:
#! /bin/bash
# rm -f ez.txt
for i in $(seq 20000)
do
sqlplus -s -l scott/book @by.txt $i >> ez.txt
done

3.
--//完成過程中,可以發現sql語句select t.*,(select sleep(id2) from dual) s from t where id2<=:x;
--//產生大量子游標.隨著變數x變大.
SCOTT@book> @ &r/share 5sx5b8gvbkp29
SQL_TEXT                       = select t.*,(select sleep(id2) from dual) s from t where id2<=:x
SQL_ID                         = 5sx5b8gvbkp29
ADDRESS                        = 000000007B673FE8
CHILD_ADDRESS                  = 000000007D0E67C0
CHILD_NUMBER                   = 0
LOAD_OPTIMIZER_STATS           = Y
REASON                         = <ChildNode><ChildNumber>0</ChildNumber><ID>40</ID><reason>Bind mismatch(25)</reason><size>0x0</size><details>extended_cursor_sharing</details></ChildNode>
--------------------------------------------------
SQL_TEXT                       = select t.*,(select sleep(id2) from dual) s from t where id2<=:x
SQL_ID                         = 5sx5b8gvbkp29
ADDRESS                        = 000000007B673FE8
CHILD_ADDRESS                  = 000000007DB06200
CHILD_NUMBER                   = 1
BIND_EQUIV_FAILURE             = Y
REASON                         = <ChildNode><ChildNumber>1</ChildNumber><ID>40</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>401504341</init_ranges_in_first_pass></ChildNode>
--------------------------------------------------
SQL_TEXT                       = select t.*,(select sleep(id2) from dual) s from t where id2<=:x
SQL_ID                         = 5sx5b8gvbkp29
ADDRESS                        = 000000007B673FE8
CHILD_ADDRESS                  = 000000007B4D2BC0
CHILD_NUMBER                   = 2
BIND_EQUIV_FAILURE             = Y
REASON                         = <ChildNode><ChildNumber>2</ChildNumber><ID>40</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>1324538965</init_ranges_in_first_pass></ChildNode>
--------------------------------------------------
SQL_TEXT                       = select t.*,(select sleep(id2) from dual) s from t where id2<=:x
SQL_ID                         = 5sx5b8gvbkp29
ADDRESS                        = 000000007B673FE8
CHILD_ADDRESS                  = 000000007D774F18
CHILD_NUMBER                   = 3
BIND_EQUIV_FAILURE             = Y
REASON                         = <ChildNode><ChildNumber>3</ChildNumber><ID>40</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>3389173190</init_ranges_in_first_pass></ChildNode>
--------------------------------------------------
SQL_TEXT                       = select t.*,(select sleep(id2) from dual) s from t where id2<=:x
SQL_ID                         = 5sx5b8gvbkp29
ADDRESS                        = 000000007B673FE8
CHILD_ADDRESS                  = 000000007C35B2F0
CHILD_NUMBER                   = 4
BIND_EQUIV_FAILURE             = Y
REASON                         = <ChildNode><ChildNumber>4</ChildNumber><ID>40</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>3251083087</init_ranges_in_first_pass></ChildNode>
--------------------------------------------------
SQL_TEXT                       = select t.*,(select sleep(id2) from dual) s from t where id2<=:x
SQL_ID                         = 5sx5b8gvbkp29
ADDRESS                        = 000000007B673FE8
CHILD_ADDRESS                  = 000000007DA931D0
CHILD_NUMBER                   = 5
BIND_EQUIV_FAILURE             = Y
REASON                         = <ChildNode><ChildNumber>5</ChildNumber><ID>40</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>1560069912</init_ranges_in_first_pass></ChildNode>
--------------------------------------------------
SQL_TEXT                       = select t.*,(select sleep(id2) from dual) s from t where id2<=:x
SQL_ID                         = 5sx5b8gvbkp29
ADDRESS                        = 000000007B673FE8
CHILD_ADDRESS                  = 000000007C23EDA8
CHILD_NUMBER                   = 6
BIND_EQUIV_FAILURE             = Y
REASON                         = <ChildNode><ChildNumber>6</ChildNumber><ID>40</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>2611100960</init_ranges_in_first_pass></ChildNode>
--------------------------------------------------
SQL_TEXT                       = select t.*,(select sleep(id2) from dual) s from t where id2<=:x
SQL_ID                         = 5sx5b8gvbkp29
ADDRESS                        = 000000007B673FE8
CHILD_ADDRESS                  = 000000007C623EC0
CHILD_NUMBER                   = 7
BIND_EQUIV_FAILURE             = Y
REASON                         =
--------------------------------------------------
PL/SQL procedure successfully completed.
--//以上僅僅一部分.

$ grep SQL_ID ez.txt | uniq -c
    522 SQL_ID 5sx5b8gvbkp29, child number 0
     53 SQL_ID 5sx5b8gvbkp29, child number 1
     58 SQL_ID 5sx5b8gvbkp29, child number 2
     64 SQL_ID 5sx5b8gvbkp29, child number 3
     70 SQL_ID 5sx5b8gvbkp29, child number 4
     77 SQL_ID 5sx5b8gvbkp29, child number 5
     85 SQL_ID 5sx5b8gvbkp29, child number 6
     93 SQL_ID 5sx5b8gvbkp29, child number 7
    103 SQL_ID 5sx5b8gvbkp29, child number 8
    113 SQL_ID 5sx5b8gvbkp29, child number 9
    124 SQL_ID 5sx5b8gvbkp29, child number 10
    137 SQL_ID 5sx5b8gvbkp29, child number 11
    150 SQL_ID 5sx5b8gvbkp29, child number 12
    165 SQL_ID 5sx5b8gvbkp29, child number 13
    182 SQL_ID 5sx5b8gvbkp29, child number 14
    200 SQL_ID 5sx5b8gvbkp29, child number 15
    220 SQL_ID 5sx5b8gvbkp29, child number 16
    242 SQL_ID 5sx5b8gvbkp29, child number 17
    266 SQL_ID 5sx5b8gvbkp29, child number 18
    293 SQL_ID 5sx5b8gvbkp29, child number 19
    322 SQL_ID 5sx5b8gvbkp29, child number 20
    354 SQL_ID 5sx5b8gvbkp29, child number 21
    390 SQL_ID 5sx5b8gvbkp29, child number 22
    429 SQL_ID 5sx5b8gvbkp29, child number 23
    472 SQL_ID 5sx5b8gvbkp29, child number 24
    519 SQL_ID 5sx5b8gvbkp29, child number 25
    571 SQL_ID 5sx5b8gvbkp29, child number 26
    628 SQL_ID 5sx5b8gvbkp29, child number 27
    691 SQL_ID 5sx5b8gvbkp29, child number 28
    760 SQL_ID 5sx5b8gvbkp29, child number 29
    836 SQL_ID 5sx5b8gvbkp29, child number 30
    919 SQL_ID 5sx5b8gvbkp29, child number 31
   1011 SQL_ID 5sx5b8gvbkp29, child number 32
   1112 SQL_ID 5sx5b8gvbkp29, child number 33
   1224 SQL_ID 5sx5b8gvbkp29, child number 34
   1346 SQL_ID 5sx5b8gvbkp29, child number 35
   1481 SQL_ID 5sx5b8gvbkp29, child number 36
   1629 SQL_ID 5sx5b8gvbkp29, child number 37
   1792 SQL_ID 5sx5b8gvbkp29, child number 0
    297 SQL_ID 5sx5b8gvbkp29, child number 38
--//注前面第1列的數字是對於子游標執行的次數.
--//有點奇怪的地方是後面有1段執行的child number 0,共1792次.不知道為什麼.

--//我決定再重複測試看看.修改如下:

#! /bin/bash
# rm -f ez.txt
for i in $(seq 20000)
do
sqlplus -s -l scott/book @by.txt $i >> fz.txt
done

--//輸出檔案換成fz.txt

$ grep SQL_ID fz.txt | uniq -c
    500 SQL_ID 5sx5b8gvbkp29, child number 0
     51 SQL_ID 5sx5b8gvbkp29, child number 1
     56 SQL_ID 5sx5b8gvbkp29, child number 2
     61 SQL_ID 5sx5b8gvbkp29, child number 3
     67 SQL_ID 5sx5b8gvbkp29, child number 4
     74 SQL_ID 5sx5b8gvbkp29, child number 5
     81 SQL_ID 5sx5b8gvbkp29, child number 6
     90 SQL_ID 5sx5b8gvbkp29, child number 7
     99 SQL_ID 5sx5b8gvbkp29, child number 8
    108 SQL_ID 5sx5b8gvbkp29, child number 9
    119 SQL_ID 5sx5b8gvbkp29, child number 10
    131 SQL_ID 5sx5b8gvbkp29, child number 11
    144 SQL_ID 5sx5b8gvbkp29, child number 12
    159 SQL_ID 5sx5b8gvbkp29, child number 13
    175 SQL_ID 5sx5b8gvbkp29, child number 14
    192 SQL_ID 5sx5b8gvbkp29, child number 15
    211 SQL_ID 5sx5b8gvbkp29, child number 16
    232 SQL_ID 5sx5b8gvbkp29, child number 17
    256 SQL_ID 5sx5b8gvbkp29, child number 18
    281 SQL_ID 5sx5b8gvbkp29, child number 19
    309 SQL_ID 5sx5b8gvbkp29, child number 20
    340 SQL_ID 5sx5b8gvbkp29, child number 21
    374 SQL_ID 5sx5b8gvbkp29, child number 22
    412 SQL_ID 5sx5b8gvbkp29, child number 23
    453 SQL_ID 5sx5b8gvbkp29, child number 24
    498 SQL_ID 5sx5b8gvbkp29, child number 25
    548 SQL_ID 5sx5b8gvbkp29, child number 26
    603 SQL_ID 5sx5b8gvbkp29, child number 27
    663 SQL_ID 5sx5b8gvbkp29, child number 28
    729 SQL_ID 5sx5b8gvbkp29, child number 29
    802 SQL_ID 5sx5b8gvbkp29, child number 30
    882 SQL_ID 5sx5b8gvbkp29, child number 31
    971 SQL_ID 5sx5b8gvbkp29, child number 32
   1068 SQL_ID 5sx5b8gvbkp29, child number 0
   1174 SQL_ID 5sx5b8gvbkp29, child number 1
   1292 SQL_ID 5sx5b8gvbkp29, child number 2
   1421 SQL_ID 5sx5b8gvbkp29, child number 3
   1563 SQL_ID 5sx5b8gvbkp29, child number 33
   1719 SQL_ID 5sx5b8gvbkp29, child number 4
   1092 SQL_ID 5sx5b8gvbkp29, child number 5

--//與前面測試不同.不過可以確定是使用id2<=:x 中帶入變數有關.

[20180613]子游標不共享BIND_EQUIV_FAILURE

相關文章