 |
|
Oracle Hidden Parameters
Oracle Tips by Burleson Consulting |
Oracle Hidden Parameters
There are also a number of hidden parameters that greatly impact
Oracle performance. For example, a recent Linux world record
benchmark used the following hidden Oracle parameters:
_in_memory_undo=false
_cursor_cache_frame_bind_memory = true
_db_cache_pre_warm = false
_in_memory_undo=false
_check_block_after_checksum = false
_lm_file_affinity
|
|
WARNING!
These are unsupported parameters and they should not be used
unless you have opened an iTar and tested their behavior on
your own database and you are willing to accept full
responsibility for any issues. |
Oracle will not provide support if any of these values are modified,
so DBAs must do careful research in a test database before making
any production database changes.
Hidden Latch Parameters
§
_db_block_hash_buckets
:
Defaults to two times
db_block_buffers
but should be the nearest prime number
to the value of 2 times
db_block_buffers
§
_db_block_hash_latches
:
Defaults to 1024, but 32768 is sometimes a better value.
§
_kgl_latch_count
:
This defaults to zero and lock contention can often be reduced by
resetting 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, this parameter is now undocumented. It is the
number of blocks that the database 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
.
Hidden Parallel Parameters
The most important of the hidden parallel parameters is the
_parallelism_cost_fudge_factor
. This parameter governs
the invocation of Oracle Parallel Query
(OPQ) by the cost-based SQL optimizer when
parallel_automatic_tuning=TRUE. By adjusting this
parameter, the threshold for invoking parallel queries can be
controlled.
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
While the Oracle hidden parameters are wonderful for the high level
DBA, all others need to make sure they thoroughly test any changes
to hidden parameters before using them in the production
environment. The Oracle benchmarks are a great way to see Oracle
hidden parameters in action, as hardware vendors often know secrets
for optimizing their servers. The following section will examine
some Oracle SQL parameters.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
|
