Hidden Parameters

Oracle has several hidden initialisation parameters. These have names which begin with an underscore. You can see them using the following query, which I ran on an Oracle 9 database:

 

SQL> l

  1  select ksppinm

  2  from   SYS.X$KSPPI

  3* where  substr(KSPPINM,1,1) = '_'

SQL> /

 

 

KSPPINM

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

_trace_files_public

_latch_recovery_alignment

_spin_count

_latch_miss_stat_sid

_max_sleep_holding_latch

_max_exponential_sleep

_use_vector_post

_latch_class_0

_latch_class_1

_latch_class_2

_latch_class_3

 

Etc.

 

KSPPINM

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

_xsolapi_sql_use_bind_variables

_xsolapi_sql_prepare_stmt_cache_size

_xsolapi_sql_result_set_cache_size

_xsolapi_debug_output

_xsolapi_cursor_use_row_cache

_xsolapi_cursor_max_rows_to_cache_per_req

_xsolapi_cursor_max_time_for_partial_cache

_xsolapi_source_trace

 

613 rows selected.

 

SQL>

 

What does it mean when we say they are hidden?  We can see the answer by comparison with an ordinary parameter e.g. sql_trace:

 

SQL> l

  1  select name, value, isdefault

  2  from v$parameter

  3* where name = 'sql_trace'

SQL> /

 

NAME       VALUE      ISDEFAULT

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

sql_trace  FALSE      TRUE

 

SQL>

 

In this case, sql_trace is set to its default value of FALSE but it still has an entry in V$PARAMETER. A hidden parameter, on the other hand, does not have an entry in V$PARAMETER:

 

  1  select name, value, isdefault

  2  from v$parameter

  3* where name = '_trace_files_public'

SQL> /

 

no rows selected

 

SQL>

 

... unless it has been set on purpose. The query below was run on a different Oracle 9 database, which had _trace_files_public set in its parameter file:

 

SQL> l

  1  select name, value, isdefault

  2  from v$parameter

  3* where name = '_trace_files_public'

SQL> /

 

NAME                 VALUE      ISDEFAULT

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

_trace_files_public  TRUE       FALSE

 

SQL>

 

Documentation for hidden parameters is not freely available. Oracle will tell you when to implement them, usually when they are replying to a service request. Advice from Oracle to implement a hidden parameter relates to a given database running on a specific Oracle version. I went to an 11g release 2 seminar run by Oracle recently. They discussed upgrade policy there and said that hidden parameters should be removed before an upgrade.