Friday, December 30, 2011

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.

No comments: