Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

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;

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.