 |
|
Oracle Tips by Burleson |
Undocumented Oracle
Parameters
Like all databases, Oracle has some internal
features that they don’t want DBAs to know about. Many of these
features are deliberately left out of the Oracle documentation because
they can have bad side-effects if they are used improperly. Oracle has
numerous "hidden" parameters that you can use to change the internal
behavior of Oracle. As you may know, all hidden parameters begin with
an underscore character.
However, knowledge of the undocumented
features provides a valuable learning tool for the DBA because they
can gain insights into the internal mechanisms that govern the
behavior of critical components of Oracle. These undocumented
parameters cover these functional areas of Oracle:
* Secret enabling parameters
* Parallel parameters
* Materialized view parameters
* SGA and PGA parameters
* Disk I/O parameters
* Data Buffer Pool parameters
* SQL optimizer parameters
Officially, Oracle states that you should
never change the hidden parameters unless directed by Oracle technical
support, but most expert Oracle DBAs commonly adjust these parameters
to improve performance. Oracle uses these hidden parameters to
individualize the internal behavior of the mechanisms for SGA memory
management, object management, and hundreds of other internal
mechanisms.
Many savvy Oracle professionals 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 settings for the hidden parameters.
Remember, Oracle does not publish the source
code for the Oracle database, and they are not going to tell us the
purpose and behavior of the hidden parameters. It is up to the DBA to
evaluate the parameters and determine those that best meet their
current needs.
As we tour the hidden parameters, you are not
expected to memorize each parameter. The goal is for you to review
the hidden parameters to help gain a more detailed understanding of
the internal workings of Oracle. Let’s start by giving you the SQL
query to display the hidden parameters on your server.
The hidden parameter query script
This script that queries v$ksppi will display
all of the hidden parameters in alphabetical order.
column
parm_name heading 'Parameter|Name' format a40
column current_val heading 'Current|Value' format a10
column default_val heading 'Instance|Value' format a20
column descr heading 'Parameter|Description' format a80
--***********************************************************
-- Undocumented Parameters
--***********************************************************
ttitle 'Full|List of undocumented Oracle parameters'
select
ksppinm parm_name,
session_tab.ksppstvl current_val,
instance_tab.ksppstvl default_val,
ksppdesc descr
from
x$ksppi parm_tab,
x$ksppcv session_tab,
x$ksppsv instance_tab
where
parm_tab.indx = session_tab.indx
and
session_tab.indx = instance_tab.indx
and
substr(parm_tab.ksppinm,1,1) = '_'
and
-- remove rows where hidden parm is the same as v$ parm
not exists
(select name from v$parameter
where
name = substr(ksppinm,2,length(ksppinm))
)
order by
ksppinm
;
Let’s start by examining the important
parameters within each classification of hidden parameters.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
