Friday, May 19, 2006

Unveil Undocumented Initialization Parameters

Configuring the Oracle database using the initialization parameters is a vital part of database tuning. There are a lot of parameters whose description can be found in the documentation. Here is how to unveil the undocumented ones. Their names always start with an underscore:

select p.ksppinm "Parameter"
, p.ksppdesc "Description"
, c.ksppstvl "Session Value"
, s.ksppstvl "Instance Value"
from x$ksppi p
, x$ksppcv c
, x$ksppsv s
where p.inst_id = userenv('instance')
and p.indx = c.indx
and p.indx = s.indx
and p.ksppinm LIKE '#_%' escape '#'
order by p.ksppinm

These parameters should only be used with care and under instruction of Oracle Support or an experienced DBA.

One useful parameter is _trace_files_public. Trace files can be made publicly accessible in development and test environments.

No comments: