[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,估計這樣可以了.