How to see Oracle Hidden Parameters

================

General

================

Ho to see Oracle Hidden parameters

COL PARAMETER FOR A30

COL "Session Value" FOR A30

COL "Instance Value" FOR A30

SET LINESIZE 140

SET PAGESIZE 100

SELECT PARAM.ksppinm "Parameter",

       SESS.ksppstvl "Session Value",

       INST.ksppstvl "Instance Value"

  FROM x$ksppi PARAM,

       x$ksppcv SESS,

       x$ksppsv INST

WHERE  PARAM.indx = SESS.indx

  AND  PARAM.indx = INST.indx

  AND  PARAM.ksppinm LIKE '/_%' escape '/'

  AND  PARAM.ksppinm LIKE '_optimizer_invalidation_period'

ORDER BY PARAM.ksppinm;

Parameter                      Session Value Instance Value

------------------------------ ------------- --------------

_optimizer_invalidation_period         18000          18000

Oracle hidden parameters

=========================
General
=========================

Oracle has more than 2000 hidden parameters.
There parameters have a '_' prefix.
They cannot be viewed via V$PARAMETER
Instead one can use the SQL below, to query X$KSPPI table.

=========================
Short reference
=========================

oracle@my_server:~>%sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Sun Aug 20 08:52:10 2017

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> SELECT name, value from V$PARAMETER WHERE name = '_disable_streams_pool_auto_tuning';

no rows selected

SELECT KSPPI.ksppinm PARAM ,
       KSPPCV.ksppstvl session_val ,
       KSPPSV.ksppstvl instance_val,
       KSPPI.ksppdesc description
FROM   X$KSPPI KSPPI,
       X$KSPPCV KSPPCV ,
       X$KSPPSV KSPPSV
WHERE  KSPPI.indx = KSPPCV.indx 
  AND  KSPPI.indx = KSPPSV.indx 
  AND  KSPPI.ksppinm LIKE '/_%' escape '/' 
  AND  KSPPI.ksppinm = '_disable_streams_pool_auto_tuning';

PARAM                              SESSION_VAL INSTANCE_VAL DESCRIPTION
---------------------------------- ----------- ------------ --------------------------------

_disable_streams_pool_auto_tuning  FALSE       FALSE        disable streams pool auto tuning

There are more than 2000 hidden parameters.

SQL> SELECT COUNT(*) FROM x$ksppi;

  COUNT(*)

----------

      2049