 |
|
Oracle Tips by Burleson |
Disk I/O Parameters
Because Oracle is a database, disk I/O
management is a critical component. The following undocumented
parameters control the internal behavior of the disk I/O subsystem.
For example, whenever you're experiencing
process waits (due to possible index contention), you can try
adjusting the following parameters.
* _db_block_hash_buckets - This parameter
defaults to two times db_cache_size, but it should be the nearest
prime number to the value of two times db_cache_size.
* _db_block_hash_latches - This parameter
defaults to 1,024, but 32,768 is a better value.
* _kgl_latch_count - This parameter's default
is the least prime number greater than or equal to the number of CPUs.
You can often reduce contention by resetting this value to two times
the number of CPUs plus one.
Disk I/O
List of undocumented Oracle
parameters
Parameter
Current Instance
Name
Value Value
---------------------------------------- ----------
--------------------
Parameter
Description
------------------------------------------------------------------------------_arch_io_slaves
0 0
ARCH I/O slaves
_backup_disk_io_slaves
0 0
BACKUP Disk I/O slaves
_check_block_after_checksum
TRUE TRUE
perform block check after checksum if both are turned on
_db_block_cache_clone
FALSE FALSE
Always clone data blocks on get (for debugging)
_db_block_cache_map
0
0
Map / unmap and track reference counts on blocks (for debugging)
_db_block_cache_protect
FALSE FALSE
protect database blocks (true only when debugging)
_db_block_check_for_debug
FALSE FALSE
Check more and dump block before image for debugging
_db_block_granule_interval
10 10
number of lru latches
_db_block_hash_buckets
5981 5981
Number of database block hash buckets
_db_block_hash_latches
1024 1024
Number of database block hash latches
_db_block_hi_priority_batch_size
0 0
Fraction of writes for high priority reasons
_db_block_lru_latches
8 8
number of lru latches
_db_block_max_cr_dba
6 6
Maximum Allowed Number of CR buffers per dba
_db_block_max_dirty_target
0 0
Upper bound on modified buffers/recovery reads
_db_block_max_scan_pct
40 40
Percentage of buffers to inspect when looking for free
_db_block_med_priority_batch_size
0 0
Fraction of writes for medium priority reasons
_db_block_numa
1 1
Number of NUMA nodes
_db_block_prefetch_quota
10 10
Prefetch quota as a percent of cache size
_db_block_trace_protect
FALSE FALSE
trace buffer protect calls
_db_file_noncontig_mblock_read_count
11 11
number of noncontiguous db blocks to be prefetched
_db_writer_chunk_writes
0 0
Number of writes DBWR should wait for
_db_writer_histogram_statistics
FALSE FALSE
maintain dbwr histogram statistics in x$kcbbhs
_db_writer_max_writes
0 0
Max number of outstanding DB Writer IOs
_db_writer_scan_depth_pct
25 25
Percentage of LRU buffers for dbwr to scan when looking for dirt
_dbwr_async_io
TRUE TRUE
Enable dbwriter asynchronous writes
_dbwr_scan_interval
10 10
dbwriter scan interval
_dbwr_tracing
0 0
Enable dbwriter tracing
_disable_multiple_block_sizes
FALSE FALSE
disable multiple block size support (for debugging)
_dyn_sel_est_num_blocks
30 30
number of blocks for dynamic selectivity estimation
_enable_block_level_transaction_recovery
TRUE TRUE
enable block level recovery
_filesystemio_options
asynch asynch
IO operations on filesystem files
_hash_multiblock_io_count
0 0
number of blocks hash join will read/write at once
_io_slaves_disabled
FALSE FALSE
Do not use I/O slaves
_ldr_io_size
262144 262144
size of write IOs used during a load operation
_lgwr_async_io
TRUE TRUE
LGWR Asynchronous IO enabling boolean flag
_lgwr_io_slaves
0 0
LGWR I/O slaves
_lgwr_max_ns_wt
30 30
Maximum wait time for lgwr to allow NetServer to progress
_lgwr_ns_nl_max
1000 1000
Variable to simulate network latency
_lgwr_ns_nl_min
500 500
Variable to simulate network latency
_lgwr_ns_sim_err
0 0
Variable to simulate errors during lgwr-network server testing
_log_blocks_during_backup
TRUE TRUE
log block images when changed during backup
_log_io_size
0 0
automatically initiate log write if this many redo blocks in buf
_reliable_block_sends
FALSE FALSE
if TRUE, block sends across interconnect are reliable
_send_close_with_block
TRUE TRUE
if TRUE, send close with block even with direct sends
_smm_auto_max_io_size
252 252
Maximum IO size (in KB) used by sort/hash-join in auto mode
_smm_auto_min_io_size
60 60
Minimum IO size (in KB) used by sort/hash-join in auto mode
_sort_multiblock_read_count
2 2
multi-block read count for sort
_temp_tran_block_threshold
100 100
number of blocks for a dimension before we temp transform
_trace_multi_block_reads
FALSE FALSE
trace multi_block reads if otrace cacheIO event is set
49 rows
selected.
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.
|
