[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>1×4</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>1×4</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>1×4</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>1×4</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
–//并没有产生子光标。