1. 首页
  2. IT资讯

[20180613]子光标不共享BIND_EQUIV_FAILURE

[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 😡 := &&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>1×4</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>1×4</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>1×4</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>1×4</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>1×4</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>1×4</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 中带入变量有关.

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

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/182830.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code