How to set fix control ?

 






Bug 31211220 – High version count (cursor leaks) due to bind_equiv_failure (Doc ID 31211220.8)
SQL's Are Not Getting Shared due to BIND_EQUIV_FAILURE in 12.2 (Doc ID 2635456.1)



alter system set "_fix_control"='17443547:ON';
  
  

set linesize 300 pagesize 300
col fix_control_value 	for a18 
col sql_feature 		for a30

select bugno,value,
case
when value=1 then 'fix_control_on'
when value=0 then 'fix_control_off'
end as fix_control_value,
optimizer_feature_enable,
sql_feature,
is_default,
event,
con_id,
description 
from v$system_fix_control 
where bugno=17443547
;



     BUGNO      VALUE FIX_CONTROL_VALUE  OPTIMIZER_FEATURE_ENABLE  SQL_FEATURE                    IS_DEFAULT      EVENT     CON_ID DESCRIPTION
---------- ---------- ------------------ ------------------------- ------------------------------ ---------- ---------- ---------- ----------------------------------------------------------------
  17443547          1 fix_control_on     12.2.0.1                  QKSFM_CURSOR_SHARING_17443547           1          0          1 Adaptive Cursor Sharing for single bind constant expressions



   sqlplus> alter system set "_fix_control"='17443547:OFF';

System altered.

  
  
    BUGNO      VALUE FIX_CONTROL_VALUE  OPTIMIZER_FEATURE_ENABLE  SQL_FEATURE                    IS_DEFAULT      EVENT     CON_ID DESCRIPTION
---------- ---------- ------------------ ------------------------- ------------------------------ ---------- ---------- ---------- ----------------------------------------------------------------
  17443547          0 fix_control_off    12.2.0.1                  QKSFM_CURSOR_SHARING_17443547           0          0          1 Adaptive Cursor Sharing for single bind constant expressions


  ===

  to check fix control on prod and standby
  
col DB_UNIQUE_NAME for a15
select max(SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME')) "DB_UNIQUE_NAME",count(*) "fix_control_on" from v$system_fix_control  where 1=1 and value=1 ;



col DB_UNIQUE_NAME for a15
select max(SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME')) "DB_UNIQUE_NAME",count(*) "fix_control_off" from v$system_fix_control  where 1=1 and value=0 ;



col DB_UNIQUE_NAME for a15
col "v$parameter" for 99999
select max(SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME')) "DB_UNIQUE_NAME",count(*) "v$parameter" from v$parameter ;

  

SET LINESIZE 300
COLUMN name          FORMAT A30
COLUMN current_value FORMAT A30
COLUMN sid           FORMAT A8
COLUMN spfile_value  FORMAT A30
col DB_UNIQUE_NAME for a15
SELECT p.name,
SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME') as DB_UNIQUE_NAME,
       i.instance_name AS sid,
       p.value AS current_value,
       sp.sid,
       sp.value AS spfile_value      
FROM   v$spparameter sp,
       v$parameter p,
       v$instance i
WHERE  1=1
and sp.name   = p.name
--AND    sp.value != p.value
and sp.name like '%fix_control%';