Undocumented Initialization Parameters
Oracle Tips by Burleson
Almost every DBA knows about Oracle's
documented initialization parameters; most of them are easy to look up
and use. However, not everyone knows about the undocumented
parameters, and few know how or when to use them. There are many
parameters that Oracle will not allow DBAs to use unless specifically
directed to by Oracle support. DBAs should be aware that use of
certain undocumented parameters will result in an unsupported system.
I will attempt to identify where parameters should be used only under
Oracle support’s guidance and where a DBA can safely utilize these
What’s the Difference?
The major difference between the documented
and undocumented parameters is that the undocumented parameters begin
with an underscore character. In many cases, the undocumented
parameters were either documented at one time in previous releases or
will be in future releases. Those parameters that have been, or will
be, documented are usually those that are safe to use. The
undocumented parameters that have never been really documented and the
ones that never will be are those for which it is difficult to
determine their use safety. When in doubt, get guidance from Oracle
support. And always back up the database before using any of the
questionable parameters so that you have a way to restore a
"supported" version on which you can get help.
So Many Parameters, So Little Time
Let’s begin this discussion with what an
expert has to say on the topic. Rich Niemiec of TUSC cites, in Oracle
Performance Tuning Tips and Techniques (Oracle Press, 2000), the
following 13 undocumented parameters as his favorites:
_ALLOW_RESETLOGS_CORRUPTION. May be
only way to start a db backed-up open without setting backup on
tablespaces; will result in unsupported system. See detailed section
on using _allow_resetlogs_corruption which follows..
_CORRUPTED_ROLLBACK_SEGMENTS. Only way to start up with
corrupted public rollback segments. Can be used without fear of
_ALLOW_READ_ONLY_CORRUPTION. Allows you
to open a database even if it has corruption. This should only be used
to export as much data from a corrupted database as is possible before
re-creating a database. You should not use a database for normal use
that has been opened in this manner, as it will not be supported.
_SPIN_COUNT. Sets the number of spins a
process will undergo before trying to get a latch. If CPU is not fully
loaded, a high value may be best; for a fully loaded CPU, a smaller
value may help. Usually defaults to 2000. Can be changed without fear
of desupport. Flips from undocumented to documented depending on
documented, now is undocumented; is the minimum size of entry in
blocks that will be prebuilt for redo log entries; usually set to 30.
_LATCH_SPIN_COUNT. Shows how often a
latch request will be taken
documented, now undocumented; the number of blocks that the db writers
will write in each batch; defaults to 512 or DB_FILES*DB_FILE_SIMULTANEOUS_WRITES/2
up to a limit of one-fourth the value of DB_BLOCK_BUFFERS.
_CPU_COUNT. Flips from undocumented to
documented. Should be set automatically; but on some platforms,
doesn't; set to the number of CPUs. This determines several other
_INIT_SQL_FILE. The initialization
SQL script run by Oracle when a db is created. This should be sql.bsq;
if you change it, you may not be supported.
_TRACE_FILES_PUBLIC. Changes the
privileges on trace files such that everyone can read them. Should be
Okay to change at will.
_FAST_FULL_SCAN_ENABLED. Enables (or
disables) fast full index scans if only indexes are required to
resolve the queries. Change at will.
sometimes get a corrupted db up; you probably won't be supported if
done without Oracle support’s blessing. Immediately export the tables
you need and rebuild the db if used.
_ALWAYS_STAR_TRANSFORMATION. Helps to
tune data warehouse queries if you have a properly designed data
_SMALL_TABLE_THRESHOLD. Sets the size
of table considered a small table. A small table is automatically
pinned into the buffers when queried. Defaults to 2 percent in
Niemiec lists another 13 parameters that bear
_DEBUG_SGA. Has no noticeable effect.
_LOG_BUFFERS_DEBUG. Slows things down.
_REUSE_INDEX_LOOPS. The blocks to examine for index block
_SAVE_ESCALATES. Not sure what it does;
no measurable effects. According to Steve Adams, Oracle may take an
exclusive lock earlier than required to save lock escalations; if this
is set to FALSE, it won’t. Don’t mess with it.
_OPTIMIZER_UNDO_CHANGES. Reverts to
pre-6.0.3 optimizer for IN statements. This one is required in certain
versions of the Oracle Applications. According to K. Gopalakrishnan of
the India Times, this parameter has nothing to do with cost-based
optimization. In Version 6, somewhere around V6.0.36, if he remembers
correctly, Ed Peeler made several changes to the way the optimizer
made choices. Many queries in the existing Oracle Applications at that
time relied in particular on the old way the optimizer worked to be
certain to come up with the intended plan. The code was written prior
to hints, so many tricks were used to influence the optimizer to come
up with a certain plan and were scattered throughout the code. When
the new database release was made, tests showed that many critical
applications processes ran catastrophically slower. Fortunately, the
old optimizer code had not been stripped out yet, and a way to use the
old optimizer code was allowed via the “hidden*” parameter _undo_optimizer_changes
(probably so that Oracle could test the old versus the new optimizer
internally). So Oracle released this parameter to the applications
community. This was long before the cost-based optimizer even existed.
_DSS_CACHE_FLUSH. Enables full cache
flush for parallel; effect is not measurable.
_DB_NO_MOUNT_LOCK. Doesn't get a mount
lock; no noticeable effect.
_AFFINITY. Defaults to TRUE; enables or
disables CPU affinity.
Tried in a corrupt recovery and didn't do anything; no noticeable
additional buffers a cursor can pin; no noticeable effect. The default
value is max(db_block_buffers/processes-2,2). One note from Oracle
development says that playing around with this parameter will almost
always damage performance, so it was made hidden starting in 8.0.3.
writes of blocks when the db is idle; may result in an unusable system
if used. If you get an instance Crash with the following Error
ORA-00600: internal error code, arguments: [kcbbzo_2], , ,
setting this parameter may help.
_DISABLE_LOGGING. Even I won't support
you if you set this one. If this parameter is set to TRUE, redo
records will not be generated, and recovery is not possible if the
instance crashes or is terminated with shutdown abort.
_IO_SLAVES_DISABLED. Disables I/O
slaves, probably not the safest way.
Other parameters, covered in various notes on
Metalink, ORACLE8i Internal Services for Waits, Latches, Locks and
Memory, by Steve Adams (Oracle Press, 1999), and other boards include
_DISTRIBUTED_LOCK_TIMEOUT. Sets the
amount of time a lock used in a distributed transaction will be held,
usually 5. Safe to reset without worry of desupport.
_ROW_CACHE_CURSORS. Maximum number of
cached recursive cursors used for data dictionary cache queries. This
sets the size of the array in the PGA used for the data dictionary
cursors. It takes 28 bytes per cached cursor. Long-running
procedures that do a lot of data dictionary lookups can benefit from
higher values. If the view K$KQDPG shows excessive overflows of the
cache, a small increase may help. This view is for the current process
only. The ORADUBUG or DBMS_SYSTEM.SET_EV can be used to grab a trace
of a different process. In the resulting trace, find the X$KQDPG
structure, count 12 blocks, and use the hexadecimal values to get the
values. Recursive calls can be caused by firing of database triggers,
execution of SQL statements within stored procedures and functions and
anonymous PL/SQL blocks, and enforcement of referential integrity
constraints. Setting this value higher may reduce these recursive
_LOG_BLOCKS_DURING_BACKUP. From Oracle
support, but we still do not recommend the use of this parameter on
production systems. There is a known problem with performance on
systems using certain configurations of Veritas with Solaris 2.6.
Contact Veritas for further information. Use of this parameter will
result in an unsupported system.
Controls the rate at which the scan depth increases if the db writer
is idle; defaults to one-eighth the difference between the upper and
lower bounds of the scan depth.
Controls the rate at which the scan depth decreases of the db writer
is working too hard. The X$KVIT table gives the details of the db
writer; you can find the details using the following SELECT statement,
courtesy of Steve Adams at www.ixora.com.au:
Kvittag IN ('kcbldq', 'kcbsfs') OR
Kvittag LIKE 'kcbsd_'
_DB_LARGE_DIRTY_QUEUE. Defaults to
one-sixteenth of the write batch size limit. Sets the frequency at
which DBWR writes. Should be decreased gradually in one-block
increments until buffer waits are eliminated. The cost of reducing
this parameter is in CPU usage, so shouldn't be changed without good
_DB_BLOCK_MAX_SCAN_CNT. Defaults to
one-fourth of the working set size, rounded down. If this number of
blocks is scanned, then the free buffer request fails. The failure of
a scan increments the dirty buffers inspected parameter in the
V$SYSSTAT view. If there are no free buffer waits, and there are dirty
buffers inspected, then there is a potentially serious problem and the
parameter should be tuned.
_ENQUEUE_HASH_CHAINS. Derived from the
PROCESSES parameter, so is the value for ENQUEUE_RESOURCES, which is
directly affected by _ENQUEUE_HASH_CHAINS; therefore, if you
explicitly set ENQUEUE_RESOURCES, you will need to adjust _ENQUEUE_HASH_CHAINS
to a prime number just less than the value of ENQUEUE_RESOURCES. If
the value of _ENQUEUE_HASH is not set to a prime number, long enqueue
hash chains could develop. Unless you are receiving ORA-00052 or
ORA-00053 errors, this parameter and ENQUEUE_RESOURCES probably don’t
need adjusting. The default value is equal to CPU_COUNT.
_ENQUEUE_LOCKS. Use V$RESOUCE_LIMIT to
see if you need more locks or ENQUEUE_RESOUORCES. A lock takes 60
bytes; a resource, 72 bytes. To increase the enqueue_locks value in
v$resource limit, you have to increase the _enqueue_limit value in
_USE_ISM. Determines if intimate shared
memory is used. On some platforms this can cause problems, and _USE_ISM
should be set to FALSE instead of its default of TRUE.
_DB_BLOCK_HASH_BUCKETS. In releases
prior to 9i, this was set to twice the value of DB_BLOCK_BUFFERS.
Unfortunately, this should be set to a prime number to keep the hash
chains from getting out of hand; therefore, on releases prior to 9i,
resetting this to a prime number near the value of twice the
DB_BLOCK_BUFFERS is a good idea and will not result in loss of
support. According to Steve Adams, _db_block_hash_buckets could be
used to set both the number of hash chains and latches in previous
releases. From 7.1, it was constrained to prime numbers, and used to
default to next_prime(db_block_buffers / 4).
db_block_buffers, and the _db_block_hash_latches
parameter must be used to control the number of hash latches if
necessary. This parameter is constrained to binary powers so that
Oracle can calculate which latch to use with a simple SHIFT operation,
rather than a DIVIDE operation. The default number of hash latches
depends on db_block_buffers. If db_block_buffers is less than 2052
buffers, then the default number of latches is 2 ^ trunc(log(2,
db_block_buffers - 4) - 1).
If db_block_buffers is greater than 131075
buffers, then the default number of latches is 2 ^ trunc(log(2,
db_block_buffers - 4) - 6). If db_block_buffers is between 2052 and
131075 buffers, then there are 1024 latches by default. Sites that
have used _db_block_hash_buckets to combat cache buffer chains latch
contention under previous releases should allow the value to default
when upgrading to Oracle 8i. Remember that contention for these
latches is almost always a symptom of one or more blocks being very
hot due to unresolved application or SQL tuning problems. Adams may be
correct; however, I have seen improvements on 8.1.7 by setting these
values as in previous releases.
_DB_BLOCK_HASH_LATCHES. Usually set to
1024, which is usually too small; set it to near 32K (32768) for
better performance. Up to release 8.0.
_KGL_LATCH_COUNT. Value defaults to 7.
This determines the number of latches that control the shared pool. If
you need a large shared pool, or have a large number of items that are
placed in the shared pool, set this to a larger prime number.
According to Oracle support: In general, on systems that have multiple
CPUs and/or when parsing a lot of SQL with few shared cursors, it is
recommended to set it to 1. On all other systems it must be set to the
default, in which case the latch contention may not be significant
compared to the cost of building a new cursor for each SQL statement.
However, I tend to agree with Steve Adams: The default is the least
prime number greater than or equal to cpu_count. The maximum is 67. It
can safely be increased to combat library cache latch contention, as
long as you stick to prime numbers. That said, it is only effective if
the activity across the existing child library cache latches is evenly
distributed as shown in V$LATCH_CHILDREN.
Here are some undocumented parameters for
maximizing DBWR performance:
* Increase _db_block_write_batch (hidden
parameter in Oracle8, obsolete in Oracle8i).
* Decrease _db_block_max_scan_count, _db_writer_scan_depth,
and _db_writer_scan_depth_increment to decrease the dirty buffer
* Adjust_db_writer_chunk_writes, which
controls the number of writes DBWR should try to group into one batch
* Adjust _db_block_med_priority_batch_size for
* Adjust _db_block_hi_priority_batch_size for
urgent writes such as when LRUW is full or there are no free buffers
or when free buffers are below limit.
Parameters That Must Be Used
Here I attempt to list the specific conditions
when the following undocumented parameters must be changed:
_DB_HANDLES. In versions before
188.8.131.52, if set too high, this may cause ORA-04031 because of bug
_DB_HANDLES_CACHED. Before 184.108.40.206, may
need to be set to 0.
_ELIMINATE_COMMON_SUBEXPR. If left set
to TRUE, may cause some queries using IN clauses to return too many
rows or bad answers; set to FALSE in 220.127.116.11 and 18.104.22.168.
_IGNORE_DESC_IN_INDEX. May have to set
to TRUE on some platforms (such as AIX) in versions 8.1.6 and 8.1.7 if
you get ORA-03113 and ORA-07445 errors when you try different
management or DML operations on descending indexes. On some platforms
may have to use the event-setting “10612 trace name context forever,
level 1” to reset it.
_INIT_SQL_FILE. This may have to be
reset to use 32K block sizes on some platforms, as they will use the
sql.bsq.32K file instead of the sql.bsq file.
_MV_REFRESH_SELECTIONS. Is 8.1.7 only,
and if you have multilevel joins in your materialized view, setting
this to TRUE may allow fast refreshes.
_NEW_INITIAL_JOIN_ORDERS. Set to TRUE
when upgrading to 11.5.4 on 8.1.7.
_OGMS_HOME. Used in Oracle Parallel
Server. Set explicitly or may default to /tmp, which could result in
file loss and inability to start Oracle Parallel Server.
_SQLEXEC_PROGRESSION_COST. For Oracle
Application 11.5.4 in 8.1.7, set to 0.
_UNNEST_SUBQUERY. Where you cannot use
the UNNEST hint, may improve performance.
_USE_ISM. Set to FALSE on Solaris 2.6
or you may have system crashes and poor performance (depending on
Oracle version may be USE_ISM).
perform block check and checksum for SYSTEM tablespace; this defaults
to TRUE. You may need to set this to FALSE after upgrade from a pre-8i
version of Oracle. If you need to set this to FALSE to restart the DB,
immediately export and rebuild the database, as it detected corruption
in the data dictionary (probably in the C_TS# cluster, but that is
_DB_CACHE_ADVICE. Will turn on the buffer
cache sizing advisory if set to ON to help you perform cache sizing.
Code Depot for Full Scripts
||This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.