[20221101]如何减少BIND_EQUIV_FAILURE引起的子光标.txt

--//生产系统1条语句出现大量BIND_EQUIV_FAILURE引起的子光标,我想尝试通过sql profile或者sql patch来控制减少它.
--//多次尝试都失败,我想既然知道主要有BIND_EQUIV_FAILURE引起的,我必须在测试环境产生它,这样才能找到解决问题的办法.

1.建立测试环境:
SCOTT@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table t as select rownum id , lpad('test',2) vc from dual connect by level <=1e6;
Table created.

SCOTT@book> @ gts t ''
Gather Table Statistics for table t...
exec dbms_stats.gather_table_stats(null, upper('t'), null, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.

$ cat bb1.txt
variable b1 number;
variable b2 number;
exec :b1 := &&1;
exec :b2 := &&2;
select count(*) from t where id between :b1 and :b2;

2.测试:
@ bb1.txt 100 200
@ bb1.txt 100 20000000
@ bb1.txt 100 20000000

SCOTT@book> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
3557268748 a7w0p6ra0g78c            0     105740      1010173228  d4079d0c  2022-10-28 10:21:54    16777216

SCOTT@book> @ gunshare a7w0p6ra0g78c
--- host vim /tmp/unshare.tmp
--- host cat /tmp/unshare.tmp

REASON_NOT_SHARED                CURSORS    SQL_IDS
----------------------------- ---------- ----------
BIND_EQUIV_FAILURE                     1          1
LOAD_OPTIMIZER_STATS                   1          1
2 rows selected.
--//问题已经产生.如何解决呢?

3.分析:
--//很明显产生BIND_EQUIV_FAILURE的主要原因是查询的范围变化,导致在返回值的数量上也跟着发生变化.
--//先尝试:
SYS@book> @ sqlpatch a7w0p6ra0g78c rule

input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12)
drop sql patch ,run  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_a7w0p6ra0g78c');
display sql path message , run @spext a7w0p6ra0g78c

PL/SQL procedure successfully completed.

SYS@book> @ spext a7w0p6ra0g78c
HINT    NAME
------- ------------------------------
rule    sqlpatch_a7w0p6ra0g78c

--//刷新共享池:
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;

@ bb1.txt 100 200
@ bb1.txt 100 20000000
@ bb1.txt 100 20000000

SCOTT@book> @ gunshare a7w0p6ra0g78c
--- host vim /tmp/unshare.tmp
--- host cat /tmp/unshare.tmp

REASON_NOT_SHARED                CURSORS    SQL_IDS
----------------------------- ---------- ----------
BIND_EQUIV_FAILURE                     2          1
LOAD_OPTIMIZER_STATS                   1          1
2 rows selected.

--//依旧出现BIND_EQUIV_FAILURE,无法解决这个问题.

4.为了便于重复测试建立脚本:
$ cat a7w0p6ra0g78c.sql9_0
variable B1 NUMBER
variable B2 NUMBER
begin
:B1 := 100;
:B2 := &&1;
end;
/
set termout off
set sqlblanklines on
alter session set current_schema=SCOTT;
--alter session set statistics_level=all;

select
/*+
rule
OPT_PARAM('_optimizer_extended_cursor_sharing' 'NONE')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'NONE')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
*/
count(*) from t where id between :b1 and :b2;
set termout on
set sqlblanklines off
--@zws '' ''
--@dpc '' ''
@dpc '' outline
rollback;
alter session set current_schema=SYS ;

--//我把可能想到的隐式参数全部加入.执行多次记下sql_id=2sxdnhy32v0qb
@ bb1.txt 100 200
@ bb1.txt 100 20000000
@ bb1.txt 100 20000000
@ bb1.txt 1 2000000000000000000000000
@ bb1.txt 6000 8000

SCOTT@book> @ gunshare 2sxdnhy32v0qb
--- host vim /tmp/unshare.tmp
--- host cat /tmp/unshare.tmp
no rows selected
--//没有出现BIND_EQUIV_FAILURE,现在估计ok了.

SYS@book> @ spsw 2sxdnhy32v0qb 0 a7w0p6ra0g78c 0 '' true
PL/SQL procedure successfully completed.

--//我交换sql profile 后,重启数据库,主要避免一些干扰.

@ bb1.txt 100 200
@ bb1.txt 100 20000000
@ bb1.txt 100 20000000
@ bb1.txt 1 2000000000000000000000000
@ bb1.txt 6000 8000
@ bb1.txt 6000 8000
@ bb1.txt 6000 8000
@ bb1.txt 6000 8000
@ bb1.txt 5000 18000
@ bb1.txt 5000 18000
@ bb1.txt 5000 18000
@ bb1.txt 5000 18000

SCOTT@book> @ gunshare a7w0p6ra0g78c
--- host vim /tmp/unshare.tmp
--- host cat /tmp/unshare.tmp
no rows selected

--//OK,估计这样可以了.