Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Thursday, June 23, 2011

Oracle hidden parameters

Oracle database has some parameters related to the database configuration, some of them which are hidden and some of them are unhidden.

To list the unhidden parameters you can connect to the database "as sysdba" and directly query the "v$parameter" view as below.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release Production on Thu Jun 23 08:41:55 2011

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

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

SQL> column name format a30
SQL> column value format a50 
SQL> column description format a50
SQL> set lines 132
SQL> set pause on
SQL> select name, value, description from v$parameter order by name;

NAME                           VALUE                                              DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
O7_DICTIONARY_ACCESSIBILITY    FALSE                                              Version 7 Dictionary Accessibility Support
active_instance_count                                                             number of active instances in the cluster database
aq_tm_processes                0                                                  number of AQ Time Managers to start
archive_lag_target             0                                                  Maximum number of seconds of redos the standby cou
                                                                                  ld lose

asm_diskgroups                                                                    disk groups to mount automatically
asm_diskstring                                                                    disk set locations for discovery
asm_power_limit                1                                                  number of parallel relocations for disk rebalancin

asm_preferred_read_failure_gro                                                    preferred read failure groups


To list the hidden parameters you need to use an SQL like below, by switching commented where conditions, you can query different set of parameters hidden or unhidden. The following SQL only brings the hidden parameter called "_disable_logging" as an example.

SQL> SELECT x.ksppinm name,
       y.ksppstvl VALUE,
       x.ksppdesc description
  FROM x$ksppi x,
       x$ksppcv y
 WHERE x.inst_id = userenv('Instance')
   AND y.inst_id = userenv('Instance')
   AND x.indx = y.indx
--   AND x.ksppinm LIKE '%pga%'; --list hidden and unhidden all parameters like "pga"
   AND x.ksppinm LIKE '\_disable\_logging%' escape '\' --list hidden parameters like "_disable_logging"
--   AND x.ksppinm NOT LIKE '\_%' escape '\' --list all unhidden parameters
--   AND x.ksppinm LIKE '\_%' escape '\' --list all hidden parameters
    order by 1;  2    3    4    5    6    7    8    9   10   11   12   13  

NAME                           VALUE                                              DESCRIPTION
------------------------------ -------------------------------------------------- --------------------------------------------------
_disable_logging               FALSE                                              Disable logging

All the Oracle hidden parameters start with an underscore (_). They are not documented parameters by Oracle. Oracle does not advise customers to change them without the knowledge of Oracle Support.

For example, if you bump into an Oracle database bug and there is no published One-off patch to solve this bug yet, Oracle support can advise you to change an hidden Oracle parameter as a workaround until the patch will be released for this bug. Otherwise do not try to play with them since that can result to unexpected results in your databases.

No comments: