1. 首页
  2. IT资讯

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

[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 😡 := &&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 😡 := &&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 😡 := &&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 😡 := &&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

–//并没有产生子光标。
–//我现在仅仅估计出现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 😡 := &&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 😡 := &&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 😡 := &&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 😡 := &&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

–//并没有产生子光标。
–//我现在仅仅估计出现BIND_EQUIV_FAILURE的原因可能是返回记录出现很大变化时就会出现。

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

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

联系我们

13687733322

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

邮件:1877088071@qq.com

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

QR code