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