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.
WARNING!
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:
_allow_resetlogs_corruption
_column_tracking_level
_db_aging_cool_count
_db_aging_freeze_cr
_db_aging_hot_criteria
_db_aging_stay_count
_db_aging_touch_time
_db_percent_hot_default
_db_percent_hot_keep
_db_percent_hot_recycle
_init_sql_file
_kghdsidx_count
_log_io_size
_realfree_heap_pagesize_hint
_recyclebin
_small_table_threshold
_system_trig_enabled
_trace_files_public
_use_ism
_use_ism_for_pga
_wait_for_sync
|
Also, see these undocumented parameters from this
Oracle IBM terabyte RAM benchmark:
_NUMA_pool_size
= 536870912
_collect_undo_stats=false
_awr_restrict_mode=true
_db_writer_flush_imu=false
_db_cache_pre_warm=FALSE
_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
_ksmg_granule_size=268435456
_two_pass=false
_session_idle_bit_latches=3000 |
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.
- _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 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
table:
-- changed undocumented parameters
list
select
*
from
v$parameter
where
substr(name, 0,1) ='_';
NAME VALUE
----------------------------------- ------------------------------
_parallel_adaptive_max_users 1
_parallel_default_max_instances 1
_parallel_execution_message_align FALSE
_parallel_fake_class_pct 0
_parallel_load_bal_unit 0
_parallel_load_balancing TRUE
_parallel_min_message_pool 64560
_parallel_recovery_stopat 32767
_parallel_server_idle_time 5
_parallel_server_sleep_time 10
_parallel_txn_global FALSE
_parallelism_cost_fudge_factor 350
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
These
parameters allow you to ignore corrupt data blocks when
your database is corrupted. These should only be used in
emergencies.
- _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
unsupported system.
- _corrupted_rollback_segments - The only way to start
up with corrupted public rollback segments. This
undocumented parameter can be used without fear of
invalidating support.
- _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
supported.
- _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:
These
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
these values:
- _db_aging_cool_count - Touch count set when buffer
cooled
- _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
hash buckets
- _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
8x faster:
"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".
As a
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".
While Laurent
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:
-
-
-
-
-
-
-
Conclusion
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.
