Oracle Concepts - Undocumented Initialization Parameters for 8i

Oracle Tips by Burleson Consulting

The Undocumented Initialization Parameters (?_?)

In addition to the Oracle-documented initialization parameters, there are varying numbers of undocumented initialization parameters in every version of Oracle. These undocumented initialization parameters are usually only used in emergencies and should only be used under the direction of a senior DBA or Oracle support. Source 6 shows a script for getting the undocumented initialization parameters out of an Oracle 7.2 instance and then out of a 7.3 or 8.0.2 instance.

SOURCE 6: Scripts for documenting undocumented initialization parameters

(pre-7.3 and post-7.3).

REM Script for getting undocumented init.ora
REM parameters from a 7.2 instance
REM MRA - Revealnet 2/23/97
REM
COLUMN parameter FORMAT a40
COLUMN value FORMAT a30
COLUMN ksppidf HEADING 'Is|Default'
SET FEEDBACK OFF VERIFY OFF PAGES 55
START title80 'Undocumented Init.ora Parameters'
SPOOL rep_out/&db/undoc
SELECT ksppinm "Parameter",
ksppivl "Value",
ksppidf
FROM x$ksppi
WHERE ksppinm LIKE '/_%' escape '/'
/
SPOOL OFF
TTITLE OFF

REM Script for getting undocumented init.ora
REM parameters from a 7.3, 8.0.x, or 8.1.3 instance
REM MRA - Revealnet 4/23/97
REM
COLUMN parameter           FORMAT a37
COLUMN description      FORMAT a30 WORD_WRAPPED
COLUMN "Session Value"      FORMAT a10
COLUMN "Instance Value" FORMAT a10
SET LINES 100
SET PAGES 0
SPOOL undoc.lis
SELECT
     a.ksppinm "Parameter",
     a.ksppdesc "Description",
     b.ksppstvl "Session Value",
     c.ksppstvl "Instance Value"
FROM
     x$ksppi a,
     x$ksppcv b,
     x$ksppsv c
WHERE
     a.indx = b.indx
     AND a.indx = c.indx
     AND a.ksppinm LIKE '/_%' escape '/'
/
SPOOL OFF
SET LINES 80 PAGES 20
CLEAR COLUMNS

The output for an Oracle 7.2 database looks like Listing 13.

LISTING 13: Undocumented initialization parameters for Oracle 7.2 not included in Oracle 7.3 and 8.0.2.

Date: 03/04/97 Page: 1
Time: 03:47 PM Undocumented Init.ora Parameters SYS

ORCNETD1 database

Parameter                                Default                         Is
                                          Value
-----------------------------------------------------------------------------
_latch_spin_count (7.3 ? _spin_count)    100                            TRUE
_trace_instance_termination              FALSE                          TRUE
_wakeup_timeout                          100                            TRUE
_lgwr_async_write                        TRUE                           TRUE

The undocumented parameters for 7.3 are listed in Listing 13. Note that the descriptions for the parameters are available starting with Oracle 7.3. The undocumented parameters new for Oracle8 are shown in Listing 14.

LISTING 14: Undocumented initialization parameters for Oracle 7.3 not included

in 8.0.5.

Parameter Name                  Parameter Description           Default Value
-----------------------------------------------------------------------------
_standby_lock_name_space        lock name space used for
                                generating lock names for
                                standby datab

_enable_dba_locking             enable persistent locking          FALSE

Table 14 shows all of the undocumented initialization parameters for 8i.

Parameter	Value	Description
_NUMA_instance_mapping	Not Specified	Set of nodes that this instance should run on
_NUMA_pool_size	Not Specified	aggregate size in bytes of NUMA pool
_advanced_dss_features	FALSE	enable advanced DSS features
_affinity_on	FALSE	enable/disable affinity at run time
_all_shared_dblinks	NULL	treat all dblinks as shared
_allocate_creation_order	FALSE	should files be examined in creation order during allocation
_allow_read_only_corruption	FALSE	allow read-only open even if database is corrupt
_allow_resetlogs_corruption	FALSE	allow resetlogs even if it will cause corruption
_always_star_transformation	FALSE	always favor use of star transformation
_arch_io_slaves	0	ARCH I/O slaves
_average_dirties_half_life	2097152	Half life in bytes of first dirties running average
_b_tree_bitmap_plans	FALSE	enable the use of bitmap plans for tables w. only B-tree indexes
_backup_disk_io_slaves	0	BACKUP Disk I/O slaves
_backup_io_pool_size	262144	memory to reserve from the large pool
_bump_highwater_mark_count	0	How many blocks should we allocate per free list on advancing HW
_cleanup_rollback_entries	20	no. of undo entries to apply per transaction cleanup
_close_cached_open_cursors	FALSE	close cursors cached by PL/SQL at each commit
_column_elimination_off	FALSE	turn off predicate-only column elimination
_compatible_no_recovery	0.0.0	Database will be compatible unless crash or media recovery is needed
_complex_view_merging	FALSE	enable complex view merging
_controlfile_enqueue_timeout	900	control file enqueue timeout in seconds
_corrupt_blocks_on_stuck_recovery	0	number of times to corrupt a block when media recovery stuck
_corrupted_rollback_segments	NULL	corrupted undo segment list
_cpu_to_io	NULL	multiplier for converting CPU cost to I/O cost
_cr_server	NULL	if TRUE, enable CR server TRUE (DFS)
_cursor_db_buffers_pinned	78	additional number of buffers a cursor can pin at once
_db_aging_cool_count	1	Touch count set when buffer cooled
_db_aging_freeze_cr	2	Make CR buffers always be FALSE too cold to keep in cache
_db_aging_hot_criteria	2	Touch count which sends a buffer to head of replacement list
_db_aging_stay_count	99	Touch count set when buffer moved to head of replacement list
_db_aging_touch_time	3	Touch count which sends a buffer to head of replacement list
_db_block_cache_clone	FALSE	Always clone data blocks on get (for debugging)
_db_block_cache_map	0	Map / unmap and track reference counts on blocks (for debugging)
_db_block_cache_protect	FALSE	protect database blocks (true only when debugging)
_db_block_hash_buckets	2000	Number of database block hash buckets
_db_block_hi_priority_batch_size	0	Fraction of writes for high priority reasons
_db_block_max_cr_dba	10	Maximum Allowed Number of CR buffers per dba
_db_block_max_scan_cnt	0	Maximum number of buffers to inspect when looking for free
_db_block_med_priority_batch_size	0	Fraction of writes for medium priority reasons
_db_handles	700	System-wide simultaneous buffer operations
_db_handles_cached	5	Buffer handles cached each process
_db_large_dirty_queue	0	Number of buffers which force dirty queue to be written
_db_no_mount_lock	FALSE	do not get a mount lock
_db_percent_hot_default	50	Percent of default buffer pool considered hot
_db_percent_hot_keep	0	Percent of keep buffer pool considered hot
_db_percent_hot_recycle	0	Percent of recycle buffer pool considered hot
_db_writer_chunk_writes	0	Number of writes DBWR should wait for
_db_writer_histogram_statistics	FALSE	maintain dbwr histogram statistics in x$kcbbhs
_db_writer_max_writes	0	Max number of outstanding DB Writer Ios
_db_writer_scan_depth	0	Number of LRU buffers for dbwr to scan when looking for dirty blocks
_db_writer_scan_depth_decrement	0	Subtract from dbwr scan depth when dbwr is working too hard
_db_writer_scan_depth_increment	0	Add to dbwr scan depth when dbwr is behind
_dbwr_async_io	TRUE	Enable dbwriter asynchronous writes
_dbwr_tracing	0	Enable dbwriter tracing
_defer_multiple_waiters	TRUE	if TRUE, defer down converts when there were waiters (DFS)
_disable_incremental_checkpoints	FALSE	Disable incremental checkpoints for thread recovery
_disable_latch_free_SCN_writes_via_32cas	FALSE	disable latch-free SCN writes using 32-bit compare & swap
_disable_latch_free_SCN_writes_via_64cas	TRUE	disable latch-free SCN writes using 64-bit compare & swap
_disable_logging	FALSE	Disable logging
_disable_ntlog_events	FALSE	Disable logging to NT event log
_discrete_transactions_enabled	FALSE	enable OLTP mode
_distributed_lock_timeout	60	number of seconds a distributed transaction waits for a lock
_distributed_recovery_connection_hold_time	200	number of seconds RECO holds outbound connections open
_domain_index_batch_size	2000	maximum number of rows from one call to domain index fetch routine
_domain_index_dml_batch_size	200	maximum number of rows for one call to domain index dml routines
_dss_cache_flush	TRUE	enable full cache flush for parallel execution
_dynamic_stats_threshold	30	Delay threshold (in seconds) between sending statistics messages
_enable_NUMA_optimization	TRUE	Enable NUMA specific optimizations
_enable_block_level_transaction_recovery	true	enable block level recovery
_enable_cscn_caching	TRUE	enable commit SCN caching for all transactions
_enable_default_affinity	0	to enable default implementation of affinity osds
_enable_list_io	TRUE	Enable List I/O
_enable_multitable_sampling	FALSE	enable multitable sampling
_enqueue_debug_multi_instance	FALSE	Debug enqueue multi-instance
_enqueue_hash	265	enqueue hash table length
_enqueue_hash_chain_latches	2	enqueue hash chain latches
_enqueue_locks	1479	Locks for managed enqueues
_fairness_threshold	4	number of times to CR serve before downgrading lock (DFS)
_fast_full_scan_enabled	FALSE	enable fast full scan
_foreground_locks	TRUE	if TRUE, foregrounds acquire PCM locks (DFS)
_full_pwise_join_enabled	TRUE	enable full partition-wise join when TRUE
_gc_class_locks	0	set locks for the minor classes (DFS)
_gc_latches	4	number of latches per lock process (DFS)
_gc_lck_procs	1	number of background parallel server lock processes to start
_groupby_nopushdown_cut_ratio	3	groupby nopushdown cut ratio
_high_server_threshold	0	high server thresholds
_idl_conventional_index_maintenance	TRUE	enable conventional index maintenance for insert direct load

This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam. It?s only $19.95 when you buy it directly from the publisher here.

��