[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次.不知道为什么.
$ grep SQL_ID ez.txt | uniq -c | cut -c1-8 | xargs | sed "s/ /+/g"|bc -l
20000
--//共执行20000次可以对上.
--//我决定再重复测试看看.修改如下:
#! /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 中带入变量有关.
