Check hidden parameter value in Oracle

Hidden parameter in oracle is started with first character “_” underscore . These parameter are set according to system configuration during setup the oracle database. Oracle define the hidden parameter at instance and db level. Oracle do not recommend to changes this hidden parameter.

Check the value of the hidden parameter

col Name for a25
col Value for a10
col Description for a30
select ksppinm "Name",ksppstvl "Value",ksppdesc "Description" from x$ksppi a, x$ksppsv b where a.indx=b.indx and ksppinm = '_optimizer_use_feedback';


List all hidden parameter with value and description

col name for a25
col value for a5
col deflt for a20
col type for a10
col description for a30
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 '\_%' escape '\' order by name


Alter or Change the Value of Hidden parameter

alter system set "_cursor_obsolete_threshold"=1024 scope=spfile;
alter system set "_optimizer_use_feedback"=false scope=both;


Check the default parameter

select name, value, isdefault from v$parameter where name like '%optimizer%' order by name;