Donald K. Burleson Oracle Tips

Tuning Oracle with hidden parameters

Oracle has numerous “hidden” parameters that are used to change the internal behavior of Oracle.  As you may know, all hidden parameters begin with an underscore character.

Officially, Oracle states that the hidden parameter should never be changed unless directed by Oracle technical support, but most expert Oracle DBA’s will commonly adjust these parameters to improve performance.

These hidden parameters are used by Oracle to individualize the internal behavior of the mechanisms for SGA memory management, object management, and hundred of other internal mechanisms.

Many savvy Oracle professionals commonly adjust the hidden parameters to improve the overall performance of their systems.  However, because these are “undocumented” parameters, most Oracle professionals rely on publications such as Oracle Internals to get insights into the proper setting for the hidden parameters.

For example, whenever index contention is experienced (as evidenced by process waits), adjusting the following parameters may be helpful.

• _db_block_hash_buckets - Defaults to 2 x db_block_buffers but should be the nearest prime number to the value of 2x db_block_buffers.

• _db_block_hash_latches - Defaults to 1024 but 32768 is a better value.

• _kgl_latch_count - Defaults to zero which is means 1+number of CPUs. Lock contention can often be reduced by re-setting this value to 2*CPUs +1.

It is easy to display hidden initialization parameters.  Here is the one that I use:

Connect system/manager as sysdba;

select

   a.ksppinm                name,

   b.ksppstvl               value,

   b.ksppstdf               default,

   decode

      (a.ksppity, 1,

       'boolean', 2,

       'string', 3,

       'number', 4,

       'file', a.ksppity)   type,

   a.ksppdesc               description

from  

   sys.x$ksppi a,

   sys.x$ksppcv b

where 

   a.indx = b.indx

and 

   a.ksppinm like '\_%' escape '\'

order by

   name;