Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

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.

 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.