[20200129]子光标不共享BIND_EQUIV_FAILURE.txt

来源：这里教程网时间：2026-03-03 14:57:21 作者：

[20200129]子光标不共享BIND_EQUIV_FAILURE.txt --//生产系统再次遇到大量BIND_EQUIV_FAILURE原因导致子光标的情况。我看了我以前测试遇到的情况。 --//链接 http://blog.itpub.net/267265/viewspace-2156139/ =>[20180613]子光标不共享BIND_EQUIV_FAILURE。 --//别人曾经给我建议，问题可能出在alter session set statistics_level=all;的设置上，我也重复测试看看。 1.环境: SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID -------------------- ---------- ---------------------------------------------------------------------------- ------ IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 SYS@test> @ hide _cursor_obsolete_threshold NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD -------------------------- ----------------------------------------------- ------------- ------------- ------------ ----- --------- _cursor_obsolete_threshold Number of cursors per parent before obsoletion. TRUE 8192 8192 TRUE FALSE /* 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,1000)+1 id2 from dual connect by level<=2000; SCOTT@test01p> select * from dba_extents where owner=user and segment_name='T' 2 @ prxx ============================== OWNER : SCOTT SEGMENT_NAME : T PARTITION_NAME : SEGMENT_TYPE : TABLE TABLESPACE_NAME : USERS EXTENT_ID : 0 FILE_ID : 11 BLOCK_ID : 176 BYTES : 65536 BLOCKS : 8 RELATIVE_FNO : 11 PL/SQL procedure successfully completed. 2.建立测试脚本: --//建立脚本by.txt,注解alter session set statistics_level=all;: set term off --//alter session set statistics_level=all; variable x number; exec :x := &&1; SElect t.* from t where id2<=:x; set term on @ dpc '' '' quit --//建立shell脚本by.sh: #!/bin/bash # rm -f ez.txt for i in $(seq 1000) do sqlplus -s -l scott/btbtms@test01p @by.txt $i >> ez.txt done --//在家里的笔记本上测试有点慢，减少循环到1000次。 3.测试： --//执行脚本by.sh. $ grep "SQL_ID" ez.txt | uniq -c 1000 SQL_ID ckynkwp4t00rz, child number 0 --//可以发现并没有产生子光标。确实像别人讲的那样。 4.继续测试: --//修改脚本by.txt.取消注解alter session set statistics_level=all;. set term off alter session set statistics_level=all; variable x number; exec :x := &&1; SElect t.* from t where id2<=:x; set term on @ dpc '' '' quit --//刷新共享池3次。 alter session set statistics_level=all; alter session set statistics_level=all; alter session set statistics_level=all; $ mv ez.txt ezold.txt --//执行脚本by.sh $ grep "SQL_ID" ez.txt | uniq -c 500 SQL_ID ckynkwp4t00rz, child number 0 51 SQL_ID ckynkwp4t00rz, child number 1 56 SQL_ID ckynkwp4t00rz, child number 2 61 SQL_ID ckynkwp4t00rz, child number 3 67 SQL_ID ckynkwp4t00rz, child number 4 74 SQL_ID ckynkwp4t00rz, child number 5 81 SQL_ID ckynkwp4t00rz, child number 6 90 SQL_ID ckynkwp4t00rz, child number 7 20 SQL_ID ckynkwp4t00rz, child number 8 --//在执行过程中,可以发现并出现大量子光标. SCOTT@test01p> @ share ckynkwp4t00rz old 15: and q.sql_id like ''&1''', new 15: and q.sql_id like ''ckynkwp4t00rz''', SQL_TEXT = SElect t.* from t where id2<=:x SQL_ID = ckynkwp4t00rz ADDRESS = 000007FF12B9AA10 CHILD_ADDRESS = 000007FF1265EDE8 CHILD_NUMBER = 0 LOAD_OPTIMIZER_STATS = Y REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>39</ID><reason>Bind mismatch(25)</reason><size>0x0</size><details>extended_cursor_sharing</details></ChildNode> -------------------------------------------------- SQL_TEXT = SElect t.* from t where id2<=:x SQL_ID = ckynkwp4t00rz ADDRESS = 000007FF12B9AA10 CHILD_ADDRESS = 000007FF16F0F400 CHILD_NUMBER = 1 REASON = <ChildNode><ChildNumber>1</ChildNumber><ID>39</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>2540213050</init_ranges_in_first_pass></ChildNode> -------------------------------------------------- SQL_TEXT = SElect t.* from t where id2<=:x SQL_ID = ckynkwp4t00rz ADDRESS = 000007FF12B9AA10 CHILD_ADDRESS = 000007FF13C989F8 CHILD_NUMBER = 2 BIND_EQUIV_FAILURE = Y REASON = <ChildNode><ChildNumber>2</ChildNumber><ID>39</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>2841161709</init_ranges_in_first_pass></ChildNode> -------------------------------------------------- ... -------------------------------------------------- SQL_TEXT = SElect t.* from t where id2<=:x SQL_ID = ckynkwp4t00rz ADDRESS = 000007FF12B9AA10 CHILD_ADDRESS = 000007FF13F41A10 CHILD_NUMBER = 8 BIND_EQUIV_FAILURE = Y REASON = -------------------------------------------------- PL/SQL procedure successfully completed. $ grep "SQL_ID" ez.txt | uniq -c | awk '{ sum=sum+$1};END {print sum}' 1000 --//正好1000次。 --//可以看出设置alter session set statistics_level=all;导致出现子光标不能共享，具体原因是什么不清楚。 --//我反复测试多次,结果都是一样。 5.建立直方图呢？ SCOTT@test01p> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1024 ',Cascade => True ,No_Invalidate => false) PL/SQL procedure successfully completed. --//12c 可以支持bucket大于254. SCOTT@test01p> @ tab_lh scott t '' DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER TABLE_NAME COLUMN SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME] IF NOT INPUT COLUMN_NAME ,USE "" . COLUMN_NAME DATA_TYPE DATA_LENGTH N NUM_DISTINCT DENSITY SAMPLE_SIZE TRANS_LOW TRANS_HIGH NUM_NULLS NUM_BUCKETS LAST_ANALYZED HISTOGRAM DATA_DEFAULT ----------- --------- ----------- - ------------ ---------- ----------- --------- ---------- ---------- ----------- ------------------- ---------- ------------------------- ID1 NUMBER 22 Y 2000 .0005 2000 1 2000 0 1024 2020-01-29 19:44:04 HYBRID ID2 NUMBER 22 Y 1000 .00025 2000 1 1000 0 1000 2020-01-29 19:44:04 FREQUENCY --//刷新共享池3次。 alter session set statistics_level=all; alter session set statistics_level=all; alter session set statistics_level=all; --//执行脚本by.txt。 set term off --//alter session set statistics_level=all; variable x number; exec :x := &&1; SElect t.* from t where id2<=:x; set term on @ dpc '' '' quit $ mv ez.txt ez17.txt $ grep "SQL_ID" ez.txt | uniq -c 1000 SQL_ID ckynkwp4t00rz, child number 0 --//没有子光标产生。 SCOTT@test01p> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 254 ',Cascade => True ,No_Invalidate => false) PL/SQL procedure successfully completed. SCOTT@test01p> @ tab_lh scott t '' DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER TABLE_NAME COLUMN SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME] IF NOT INPUT COLUMN_NAME ,USE "" . COLUMN_NAME DATA_TYPE DATA_LENGTH N NUM_DISTINCT DENSITY SAMPLE_SIZE TRANS_LOW TRANS_HIGH NUM_NULLS NUM_BUCKETS LAST_ANALYZED HISTOGRAM DATA_DEFAULT ----------- --------- ----------- - ------------ ---------- ----------- --------- ---------- --------- ----------- ------------------- --------- ------------ ID1 NUMBER 22 Y 2000 .0005 2000 1 2000 0 254 2020-01-29 20:01:43 HYBRID ID2 NUMBER 22 Y 1000 .001 2000 1 1000 0 254 2020-01-29 20:01:43 HYBRID --//刷新共享池3次。 alter session set statistics_level=all; alter session set statistics_level=all; alter session set statistics_level=all; $ mv ez.txt ez18.txt $ grep "SQL_ID" ez.txt | uniq -c 1000 SQL_ID ckynkwp4t00rz, child number 0 --//没有子光标产生。 --//也就是与直方图无关。 6.继续测试： --//取消直方图设置。 SCOTT@test01p> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false) PL/SQL procedure successfully completed. $ cat by.txt set term off alter session set statistics_level=all; variable x number; exec :x := &&1; SElect t.* from t where id1<=:x; set term on @ dpc '' '' quit $ cat by.sh #!/bin/bash # rm -f ez.txt for i in $(seq 2000 ) do sqlplus -s -l scott/btbtms@test01p @by.txt $i >> ez.txt done alter system flush shared_pool; --//注意查新条件是id1<= :x,验证在x=1000后是否出现子光标。 SCOTT@test01p> @ share basmuva6swhg4 SQL_TEXT = SElect t.* from t where id1<=:x SQL_ID = basmuva6swhg4 ADDRESS = 000007FF1314E908 CHILD_ADDRESS = 000007FF13133298 CHILD_NUMBER = 0 LOAD_OPTIMIZER_STATS = Y REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>39</ID><reason>Bind mismatch(25)</reason><size>0x0</size><details>extended_cursor_sharing</details></ChildNode> -------------------------------------------------- SQL_TEXT = SElect t.* from t where id1<=:x SQL_ID = basmuva6swhg4 ADDRESS = 000007FF1314E908 CHILD_ADDRESS = 000007FF13270B40 CHILD_NUMBER = 1 REASON = <ChildNode><ChildNumber>1</ChildNumber><ID>39</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>3229815407</init_ranges_in_first_pass></ChildNode> -------------------------------------------------- SQL_TEXT = SElect t.* from t where id1<=:x SQL_ID = basmuva6swhg4 ADDRESS = 000007FF1314E908 CHILD_ADDRESS = 000007FF1343C4A0 CHILD_NUMBER = 2 BIND_EQUIV_FAILURE = Y REASON = <ChildNode><ChildNumber>2</ChildNumber><ID>39</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>2954937500</init_ranges_in_first_pass></ChildNode> .. PL/SQL procedure successfully completed. $ grep "SQL_ID" ez.txt | uniq -c 1000 SQL_ID basmuva6swhg4, child number 0 101 SQL_ID basmuva6swhg4, child number 1 111 SQL_ID basmuva6swhg4, child number 2 122 SQL_ID basmuva6swhg4, child number 3 134 SQL_ID basmuva6swhg4, child number 4 147 SQL_ID basmuva6swhg4, child number 5 162 SQL_ID basmuva6swhg4, child number 6 178 SQL_ID basmuva6swhg4, child number 7 45 SQL_ID basmuva6swhg4, child number 8 $ grep "SQL_ID" ez.txt | uniq -c | awk 'BEGIN {a=909;} {sum=sum+$1;a=a*1.10;print sum, a }' 1000 999.9 1101 1099.89 1212 1209.88 1334 1330.87 1468 1463.95 1615 1610.35 1777 1771.38 1955 1948.52 2000 2143.37 --//可以看出一个规律返回1000条记录是第1道坎，以后大约按照0.11的比例增加(最后一行测试不足不算)。当然这仅仅是我的猜测。 7.继续测试： --//翻转执行看看，先执行2000： $ cat by.sh #!/bin/bash # rm -f ez.txt for i in $(seq 2000 -1 1 ) do sqlplus -s -l scott/btbtms@test01p @by.txt $i >> ez.txt done $ grep "SQL_ID" ez.txt | uniq -c 2000 SQL_ID basmuva6swhg4, child number 0 --//并没有产生子光标。 --//我现在仅仅估计出现BIND_EQUIV_FAILURE的原因可能是返回记录出现很大变化时就会出现。 [20200129]子光标不共享BIND_EQUIV_FAILURE.txt --//生产系统再次遇到大量BIND_EQUIV_FAILURE原因导致子光标的情况。我看了我以前测试遇到的情况。 --//链接 http://blog.itpub.net/267265/viewspace-2156139/ =>[20180613]子光标不共享BIND_EQUIV_FAILURE。 --//别人曾经给我建议，问题可能出在alter session set statistics_level=all;的设置上，我也重复测试看看。 1.环境: SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID -------------------- ---------- ---------------------------------------------------------------------------- ------ IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 SYS@test> @ hide _cursor_obsolete_threshold NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD -------------------------- ----------------------------------------------- ------------- ------------- ------------ ----- --------- _cursor_obsolete_threshold Number of cursors per parent before obsoletion. TRUE 8192 8192 TRUE FALSE /* 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,1000)+1 id2 from dual connect by level<=2000; SCOTT@test01p> select * from dba_extents where owner=user and segment_name='T' 2 @ prxx ============================== OWNER : SCOTT SEGMENT_NAME : T PARTITION_NAME : SEGMENT_TYPE : TABLE TABLESPACE_NAME : USERS EXTENT_ID : 0 FILE_ID : 11 BLOCK_ID : 176 BYTES : 65536 BLOCKS : 8 RELATIVE_FNO : 11 PL/SQL procedure successfully completed. 2.建立测试脚本: --//建立脚本by.txt,注解alter session set statistics_level=all;: set term off --//alter session set statistics_level=all; variable x number; exec :x := &&1; SElect t.* from t where id2<=:x; set term on @ dpc '' '' quit --//建立shell脚本by.sh: #!/bin/bash # rm -f ez.txt for i in $(seq 1000) do sqlplus -s -l scott/btbtms@test01p @by.txt $i >> ez.txt done --//在家里的笔记本上测试有点慢，减少循环到1000次。 3.测试： --//执行脚本by.sh. $ grep "SQL_ID" ez.txt | uniq -c 1000 SQL_ID ckynkwp4t00rz, child number 0 --//可以发现并没有产生子光标。确实像别人讲的那样。 4.继续测试: --//修改脚本by.txt.取消注解alter session set statistics_level=all;. set term off alter session set statistics_level=all; variable x number; exec :x := &&1; SElect t.* from t where id2<=:x; set term on @ dpc '' '' quit --//刷新共享池3次。 alter session set statistics_level=all; alter session set statistics_level=all; alter session set statistics_level=all; $ mv ez.txt ezold.txt --//执行脚本by.sh $ grep "SQL_ID" ez.txt | uniq -c 500 SQL_ID ckynkwp4t00rz, child number 0 51 SQL_ID ckynkwp4t00rz, child number 1 56 SQL_ID ckynkwp4t00rz, child number 2 61 SQL_ID ckynkwp4t00rz, child number 3 67 SQL_ID ckynkwp4t00rz, child number 4 74 SQL_ID ckynkwp4t00rz, child number 5 81 SQL_ID ckynkwp4t00rz, child number 6 90 SQL_ID ckynkwp4t00rz, child number 7 20 SQL_ID ckynkwp4t00rz, child number 8 --//在执行过程中,可以发现并出现大量子光标. SCOTT@test01p> @ share ckynkwp4t00rz old 15: and q.sql_id like ''&1''', new 15: and q.sql_id like ''ckynkwp4t00rz''', SQL_TEXT = SElect t.* from t where id2<=:x SQL_ID = ckynkwp4t00rz ADDRESS = 000007FF12B9AA10 CHILD_ADDRESS = 000007FF1265EDE8 CHILD_NUMBER = 0 LOAD_OPTIMIZER_STATS = Y REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>39</ID><reason>Bind mismatch(25)</reason><size>0x0</size><details>extended_cursor_sharing</details></ChildNode> -------------------------------------------------- SQL_TEXT = SElect t.* from t where id2<=:x SQL_ID = ckynkwp4t00rz ADDRESS = 000007FF12B9AA10 CHILD_ADDRESS = 000007FF16F0F400 CHILD_NUMBER = 1 REASON = <ChildNode><ChildNumber>1</ChildNumber><ID>39</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>2540213050</init_ranges_in_first_pass></ChildNode> -------------------------------------------------- SQL_TEXT = SElect t.* from t where id2<=:x SQL_ID = ckynkwp4t00rz ADDRESS = 000007FF12B9AA10 CHILD_ADDRESS = 000007FF13C989F8 CHILD_NUMBER = 2 BIND_EQUIV_FAILURE = Y REASON = <ChildNode><ChildNumber>2</ChildNumber><ID>39</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>2841161709</init_ranges_in_first_pass></ChildNode> -------------------------------------------------- ... -------------------------------------------------- SQL_TEXT = SElect t.* from t where id2<=:x SQL_ID = ckynkwp4t00rz ADDRESS = 000007FF12B9AA10 CHILD_ADDRESS = 000007FF13F41A10 CHILD_NUMBER = 8 BIND_EQUIV_FAILURE = Y REASON = -------------------------------------------------- PL/SQL procedure successfully completed. $ grep "SQL_ID" ez.txt | uniq -c | awk '{ sum=sum+$1};END {print sum}' 1000 --//正好1000次。 --//可以看出设置alter session set statistics_level=all;导致出现子光标不能共享，具体原因是什么不清楚。 --//我反复测试多次,结果都是一样。 5.建立直方图呢？ SCOTT@test01p> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1024 ',Cascade => True ,No_Invalidate => false) PL/SQL procedure successfully completed. --//12c 可以支持bucket大于254. SCOTT@test01p> @ tab_lh scott t '' DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER TABLE_NAME COLUMN SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME] IF NOT INPUT COLUMN_NAME ,USE "" . COLUMN_NAME DATA_TYPE DATA_LENGTH N NUM_DISTINCT DENSITY SAMPLE_SIZE TRANS_LOW TRANS_HIGH NUM_NULLS NUM_BUCKETS LAST_ANALYZED HISTOGRAM DATA_DEFAULT ----------- --------- ----------- - ------------ ---------- ----------- --------- ---------- ---------- ----------- ------------------- ---------- ------------------------- ID1 NUMBER 22 Y 2000 .0005 2000 1 2000 0 1024 2020-01-29 19:44:04 HYBRID ID2 NUMBER 22 Y 1000 .00025 2000 1 1000 0 1000 2020-01-29 19:44:04 FREQUENCY --//刷新共享池3次。 alter session set statistics_level=all; alter session set statistics_level=all; alter session set statistics_level=all; --//执行脚本by.txt。 set term off --//alter session set statistics_level=all; variable x number; exec :x := &&1; SElect t.* from t where id2<=:x; set term on @ dpc '' '' quit $ mv ez.txt ez17.txt $ grep "SQL_ID" ez.txt | uniq -c 1000 SQL_ID ckynkwp4t00rz, child number 0 --//没有子光标产生。 SCOTT@test01p> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 254 ',Cascade => True ,No_Invalidate => false) PL/SQL procedure successfully completed. SCOTT@test01p> @ tab_lh scott t '' DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER TABLE_NAME COLUMN SAMPLE : @ TAB_LH TABLE_NAME [COLUMN_NAME] IF NOT INPUT COLUMN_NAME ,USE "" . COLUMN_NAME DATA_TYPE DATA_LENGTH N NUM_DISTINCT DENSITY SAMPLE_SIZE TRANS_LOW TRANS_HIGH NUM_NULLS NUM_BUCKETS LAST_ANALYZED HISTOGRAM DATA_DEFAULT ----------- --------- ----------- - ------------ ---------- ----------- --------- ---------- --------- ----------- ------------------- --------- ------------ ID1 NUMBER 22 Y 2000 .0005 2000 1 2000 0 254 2020-01-29 20:01:43 HYBRID ID2 NUMBER 22 Y 1000 .001 2000 1 1000 0 254 2020-01-29 20:01:43 HYBRID --//刷新共享池3次。 alter session set statistics_level=all; alter session set statistics_level=all; alter session set statistics_level=all; $ mv ez.txt ez18.txt $ grep "SQL_ID" ez.txt | uniq -c 1000 SQL_ID ckynkwp4t00rz, child number 0 --//没有子光标产生。 --//也就是与直方图无关。 6.继续测试： --//取消直方图设置。 SCOTT@test01p> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false) PL/SQL procedure successfully completed. $ cat by.txt set term off alter session set statistics_level=all; variable x number; exec :x := &&1; SElect t.* from t where id1<=:x; set term on @ dpc '' '' quit $ cat by.sh #!/bin/bash # rm -f ez.txt for i in $(seq 2000 ) do sqlplus -s -l scott/btbtms@test01p @by.txt $i >> ez.txt done alter system flush shared_pool; --//注意查新条件是id1<= :x,验证在x=1000后是否出现子光标。 SCOTT@test01p> @ share basmuva6swhg4 SQL_TEXT = SElect t.* from t where id1<=:x SQL_ID = basmuva6swhg4 ADDRESS = 000007FF1314E908 CHILD_ADDRESS = 000007FF13133298 CHILD_NUMBER = 0 LOAD_OPTIMIZER_STATS = Y REASON = <ChildNode><ChildNumber>0</ChildNumber><ID>39</ID><reason>Bind mismatch(25)</reason><size>0x0</size><details>extended_cursor_sharing</details></ChildNode> -------------------------------------------------- SQL_TEXT = SElect t.* from t where id1<=:x SQL_ID = basmuva6swhg4 ADDRESS = 000007FF1314E908 CHILD_ADDRESS = 000007FF13270B40 CHILD_NUMBER = 1 REASON = <ChildNode><ChildNumber>1</ChildNumber><ID>39</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>3229815407</init_ranges_in_first_pass></ChildNode> -------------------------------------------------- SQL_TEXT = SElect t.* from t where id1<=:x SQL_ID = basmuva6swhg4 ADDRESS = 000007FF1314E908 CHILD_ADDRESS = 000007FF1343C4A0 CHILD_NUMBER = 2 BIND_EQUIV_FAILURE = Y REASON = <ChildNode><ChildNumber>2</ChildNumber><ID>39</ID><reason>Bind mismatch(33)</reason><size>1x4</size><init_ranges_in_first_pass>2954937500</init_ranges_in_first_pass></ChildNode> .. PL/SQL procedure successfully completed. $ grep "SQL_ID" ez.txt | uniq -c 1000 SQL_ID basmuva6swhg4, child number 0 101 SQL_ID basmuva6swhg4, child number 1 111 SQL_ID basmuva6swhg4, child number 2 122 SQL_ID basmuva6swhg4, child number 3 134 SQL_ID basmuva6swhg4, child number 4 147 SQL_ID basmuva6swhg4, child number 5 162 SQL_ID basmuva6swhg4, child number 6 178 SQL_ID basmuva6swhg4, child number 7 45 SQL_ID basmuva6swhg4, child number 8 $ grep "SQL_ID" ez.txt | uniq -c | awk 'BEGIN {a=909;} {sum=sum+$1;a=a*1.10;print sum, a }' 1000 999.9 1101 1099.89 1212 1209.88 1334 1330.87 1468 1463.95 1615 1610.35 1777 1771.38 1955 1948.52 2000 2143.37 --//可以看出一个规律返回1000条记录是第1道坎，以后大约按照0.11的比例增加(最后一行测试不足不算)。当然这仅仅是我的猜测。 7.继续测试： --//翻转执行看看，先执行2000： $ cat by.sh #!/bin/bash # rm -f ez.txt for i in $(seq 2000 -1 1 ) do sqlplus -s -l scott/btbtms@test01p @by.txt $i >> ez.txt done $ grep "SQL_ID" ez.txt | uniq -c 2000 SQL_ID basmuva6swhg4, child number 0 --//并没有产生子光标。 --//我现在仅仅估计出现BIND_EQUIV_FAILURE的原因可能是返回记录出现很大变化时就会出现。