Oracle makes a huge disclaimer
that the undocumented initialization parameters are
usually only used in emergencies. However, those who want
to manipulate the internal mechanisms of Oracle to
customize the behavior to their systems find the
undocumented parameters very useful.
Using undocumented parameters without
the consent of Oracle can make your system un-supported
and you may be on your own if you experience data
corruption. Don't say I didn't warn you!
With over 100 undocumented parameters (see listing 1)
it impossible to cover them all in this short article.
Let's look at performance-related parameters first.
For specialized tasks such as database tuning,
it's possible to set the Oracle undocumented ("hidden" parameters). You
should carefully test all undocumented parameters and get permission from Oracle
technical support before using undocumented parameters in production. Here
is a common list of some of the undocumented parameters:
| Also, see these undocumented parameters from this
Oracle IBM terabyte RAM benchmark:
_undo_autotune = false
_imu_pools = 358
_optimizer_cache_stats = false
_optimizer_cost_model = io
_cursor_cache_frame_bind_memory = true
_db_writer_coalesce_area_size = 16777216
_kghdsidx_count = 1
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.
Oracle latch parameters: Whenever index
contention is experienced (as evidenced by process waits),
adjusting the following parameters may be helpful.
- 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 sometimes 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.
- _latch_spin_count - This parameter shows how often a
latch request will be taken.
- _db_block_write_batch - Formerly documented, now
undocumented. It is the number of blocks that the db
writers will write in each batch. It defaults to 512 or
db_files*db_file_simultaneous_writes/2 up to a limit of
one-fourth the value of db_cache_size.
Oracle parallel query parameters: OPQ is an
amazing facility for improving the speed of large-table
full-table scans, and some DBAs are not aware that there
are a dozen hidden parameters that can be changed to
affect the behavior of parallel queries. Earl Shaffer
provides this query
to display the parameters.
Note that this script must be run from the SYS user as
only the SYS user can access the X$ internal tables.
Note: To see then an undocumented
parameter has changed you cheek to see if it has moved into the v$parameter
-- changed undocumented parameters
substr(name, 0,1) ='_';
The most important of these hidden parallel parameters
is the _parallelism_cost_fudge_factor. This parameter
governs the invocation of OPQ by the cost-based SQL
optimizer when parallel_automatic_tuning=true. By
adjusting this parameters you can control the threshold
for invoking parallel queries.
Now let's look at some common DBA undocumented
parameters. The following undocumented parameters are the
most commonly-used in Oracle administration.
Corruption undocumented parameters
parameters allow you to ignore corrupt data blocks when
your database is corrupted. These should only be used in
- _allow_resetlogs_corruption - This parameter may be
the only way to start a db backed-up open without
setting backup on tablespaces, it will result in an
- _corrupted_rollback_segments - The only way to start
up with corrupted public rollback segments. This
undocumented parameter can be used without fear of
- _allow_read_only_corruption - This parameter allows
the database to be opened 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. A database that has been opened in this manner
should not be used in a normal manner, as it will not be
- _corrupt_blocks_on_stuck_recovery ? This parameter
can sometimes be useful for getting a corrupted database
started. However, it probably won't be supported if done
without Oracle's blessing. Immediately export the tables
needed and rebuild the database if used.
SQL Optimizer undocumented parameters:
parameters control the internal behavior of the cost-based
SQL optimizer (CBO).
- _fast_full_scan_enabled - This enables (or disables)
fast full index scans, if only indexes are required to
resolve the queries.
- _always_star_transformation - This parameter helps
to tune data warehouse queries, provided that the
warehouse is designed properly.
- _small_table_threshold - This sets the size
definition of a small table. A small table is
automatically pinned into the buffers when queried.
Defaults to 2 percent in Oracle9i.
Data Buffer behavior parameters:
For the very
brave DBA, you can change the caching and aging rules
within the Oracle db_cache_size and change the way that
Oracle keeps data blocks in RAM memory. While these
parameters are somewhat dangerous, some savvy DBAs have
been able to get more efficient data caching by adjusting
- _db_aging_cool_count - Touch count set when buffer
- _db_aging_freeze_cr - Make CR buffers always be
FALSE too cold to keep in cache
- _db_aging_hot_criteria - Touch count which sends a
buffer to head of replacement list
- _db_aging_stay_count - Touch count set when buffer
moved to head of replacement list
- _db_aging_touch_time - Touch count which sends a
buffer to head of replacement list
- _db_block_cache_clone - Always clone data blocks on
get (for debugging)
- _db_block_cache_map - Map / unmap and track
reference counts on blocks (for debugging)
- _db_block_cache_protect - Protect database blocks
(true only when debugging)
- _db_block_hash_buckets - Number of database block
- _db_block_hi_priority_batch_size - Fraction of
writes for high priority reasons
- _db_block_max_cr_dba - Maximum Allowed Number of CR
buffers per dba
- _db_block_max_scan_cnt - Maximum number of buffers
to inspect when looking for free
- _db_block_med_priority_batch_size - Fraction of
writes for medium priority reasons
A Case study in undocumented parameters
If you have a limited
number of active sessions you may wish to
override the PGA governor that only allows
any single task to consume 5% of the total
PGA. Laurent Schneider notes in Oracle
MOSC that overriding the PGA defaults
made a large batch processes run more than
"I set appropriate values for
pga_aggregate_target and _pga_max_size...
alter system set pga_aggregate_target=6G;
alter system set "_pga_max_size"=2000000000;
...and I gave the query some hints
"NOREWRITE FULL USE_HASH ORDERED".
result, it boosted my query performance from
12 hours to 1.5 hour."
Ah, if only
it were that easy, just change an
undocumented setting and batch jobs
magically run times faster.
Laurent Schneider notes some perils and
reliability issues relating to this
parameter and says "this parameter often
leads to an ORA-4030, even when plenty of
memory available, for some obscure reasons".
abandoned the undocumented approach in favor of parallelizing the batch job, the
promise of eight times faster execution speeds are very tempting. Once you
get permission from Oracle Technical Support to set an undocumented parameter,
they can work with to resolve errors. While they may not address
bugs, they may be able to provide alternatives and workarounds.
Also see these hidden parameter tips:
These Oracle undocumented parameters are especially
useful to the senior Oracle DBA who needs to go beyond the
recommended level of detail and wants to change the
internal behavior of their SGA. The undocumented
parameters are also a lifesaver for performing re-starts
of corrupted databases, but we must always remember that
these parameters are hidden for a reason. They are very
powerful and undocumented, so you should only play with
them if you have a clear understanding about how they
change the internal behavior of Oracle.