Hidden parameters - Oracle Tidbits

Upgrade brought an issue with new optimizer features. On analysis an optimizer feature had to be turned off. While I save that experience for another day in here I wanted to share about how the particular feature a hidden parameter setting had to be changed.

To get its current value from "v$parameter"
SQL> select name, value from v$parameter where name like '/_fix%' escape '/';
no rows selected
For finding the hidden parameter the following Query should help.  I was searching for "_fix_control"

set escape '\'
select a.ksppinm name,b.ksppstvl value, b.ksppstdf deflt,
decode (a.ksppity, 1,'boolean',
     2,'string',
     3,'number',
     4,'file', a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like '\_fix\_contr%'
order by
name;

NAME                 VALUE      DEFLT     TYPE                                     DESCRIPTION
-------------------- ---------- --------- ---------------------------------------- --------------------------
_fix_control                    TRUE      string                                   bug fix control parameter


We have the option of changing it session or system level. For changing any hidden parameters it is required to check with oracle support. As an example its being changed at system level here.

SQL> alter system set "_fix_control"='8893626:OFF' scope=both;
System altered.
NAME                 VALUE      DEFLT     TYPE                                     DESCRIPTION
-------------------- ---------- --------- ---------------------------------------- ------------------------
_fix_control         8893626:OFF TRUE      string                                   bug fix control parameter