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

[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,估计这样可以了.