Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Undocumented Initialization Parameters


Oracle Tips by Burleson
 

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 only under the direction of a senior DBA or Oracle support. Source 2.5 shows a script for getting the undocumented initialization parameters out of a 7.3, 8.0, 8.1 or 9.0 instance. The undocumented parameters for an Oracle (9.0.1) database are shown in Table 2.4.

Source 2.5 Script for getting undocumented parameters.

REM Script for getting undocumented init.ora
REM parameters from a 7.3, 8.0.x,8.1 or 9.0 instance
REM MRA
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

Note that each undocumented parameter in the table begins with an underscore (_) character. Those of you who have been around awhile will also notice that some of these “undocumented” parameters used to be documented. You may have seen some used, such as “_offline_rollback_segments” (whose use I document in Chapter15); others you will never use or see used. The point is, you should be aware that there are more parameters than those listed in a user’s manual, and that you may need to prompt Oracle support if you see one (for example, “_corrupted_rollback_segments”) that may just be helpful in a sticky situation.

TABLE 2.4   Undocumented Initialization Parameters for Oracle 9i

Parameter

Description

Instance

Session

_NUMA_instance_mapping

Set of nodes that this instance should run on.

Not specified

Not specified         

_NUMA_pool_size

Aggregate size in bytes of NUMA pool.

Not specified

Not specified         

_PX_use_large_pool

Use Large Pool as source of PX buffers.

FALSE

FALSE

_abort_recovery_on_join

If TRUE, abort recovery on join reconfigurations.

FALSE

FALSE              

_active_standby_fast_reconfiguration

If TRUE, optimize dlm reconfiguration for active/standby OPS.

TRUE

TRUE               

_adaptive_direct_read*

Adaptive Direct Read.

 TRUE

TRUE               

_adaptive_fetch_enabled*

Enable/disable adaptive fetch in parallel "group by" operations.

TRUE

TRUE               

_advanced_dss_features

Enable advanced dss features; enable/disable affinity at runtime.

FALSE

FALSE              

_affinity_on

Enable/disable affinity at runtime.

TRUE

TRUE

_all_shared_dblinks

Treat all dblinks as shared.

NULL

NULL

_allocate_creation_order

Should files be examined in creation order during allocation?

FALSE

FALSE

_allocation_update_interval*

Interval at which successful search in L1 should be updated.

3

3

_allow_error_simulation

Allow error simulation for testing.

FALSE

FALSE

_allow_read_only_corruption

Allow read-only open even if database is corrupt.

FALSE

FALSE

_allow_resetlogs_corruption

Allow resetlogs even if it will cause corruption.

FALSE

FALSE

_always_anti_join*

Always use this method for anti-join when possible.

CHOOSE

CHOOSE

_always_semi_join*

 

Always use this method for semi-join when possible.

CHOOSE

CHOOSE

_always_star_transformation

Always favor use of star transformation.

FALSE

FALSE

_aq_tm_scanlimit

Scan limit for Time Managers to clean up IOT.

0

0

_arch_io_slaves

ARCH I/O slaves.

0

0

_async_bsp

If TRUE, BSP flushes log asynchronously (DFS).

TRUE

TRUE

_async_recovery_claims*

If TRUE, issue recovery claims a asynchronously

TRUE

TRUE

_async_recovery_reads*

If TRUE, issue recovery reads asynchronously.

TRUE

TRUE

_avoid_prepare*

If TRUE, do not prepare a buffer when the master is local.

TRUE

TRUE

_b_tree_bitmap_plans

Enable the use of bitmap plans only for tables with B-tree indexes.

TRUE

TRUE

_backup_disk_io_slaves

Backup disk I/O slaves.

0

0

_backup_io_pool_size

Memory to reserve from the large pool.

1048576

1048576

_bsp_log_flush

If TRUE, flush redo log before serving a CR buffer (DFS).

TRUE

TRUE

_bump_highwater_mark_count

How many blocks should we allocate per freelist on advancing HW?

0

0

_cgs_send_timeout*

CGS send timeout.

300

300

_check_block_after_checksum

Perform block check after checksum if both are turned on.

TRUE

TRUE

_cleanup_rollback_entries

Number of undo entries to apply per transaction cleanup.

100

100

_close_cached_open_cursors

Close cursors cached by PL/SQL at each commit.

FALSE

FALSE

_collect_undo_stats*

Collect statistics v$undostat.

TRUE

TRUE

_column_elimination_off

Turn off predicate-only column elimination.

FALSE

FALSE

_column_tracking_level*

Column usage tracking.

1

1

_compatible_no_recovery

Database will be compatible unless crash or media recovery is needed.

0.0.0

0.0.0              

_complex_view_merging

Enable complex view merging.

TRUE

TRUE

_controlfile_enqueue_timeout

Controlfile enqueue timeout in seconds.

900

900

_corrupted_rollback_segments

Corrupted undo segment list.

NULL

NULL

_cost_equality_semi_join*

Enables costing of equality semi-join.

TRUE

TRUE

_cpu_count*

Current number of CPUs for this instance.

0

0

_cpu_to_io

Divisor for converting CPU cost to I/O cost.

0

0

_cr_grant_global_role*

If TRUE, grant lock for CR requests when block is in global role.

TRUE

TRUE

_cr_grant_local_role*

If TRUE, grant lock for CR using three-way ping when block in local role.

FALSE

FALSE

_cursor_db_buffers_pinned

Additional number of buffers a cursor can pin at once. 

51

51 

_cursor_plan_enabled*

Enable collection and display of cursor plans.

TRUE

TRUE

_db_aging_cool_count

Touch count set when buffer cooled.

1

1

_db_aging_freeze_cr

Make CR buffers always be too cold to keep in cache.

FALSE

FALSE

_db_aging_hot_criteria

Touch count, which sends a buffer to head of replacement list.

2

2

_db_aging_stay_count

Touch count set when buffer moved to head of replacement list.

0

0

_db_aging_touch_time

Touch count, which sends a buffer to head of replacement list.

3

3

_db_always_check_system_ts

Always perform block check and checksum for system tablespace.

TRUE

TRUE

db_block_buffers*

 

Number of database blocks cached in memory: hidden  parameter.

8024

8024

_db_block_cache_clone

Always clone data blocks on get (for debugging).

FALSE

FALSE

_db_block_cache_map

Map/unmap and track reference counts on blocks (for debugging).

0

0

_db_block_cache_protect

Protect database blocks (true only when debugging).

FALSE

FALSE

_db_block_check_for_debug

Check more and dump block before image for debugging.

FALSE

FALSE

_db_block_granule_interval*

Number of LRU latches.

10

10

_db_block_hash_buckets

Number of database block hash buckets (should be prime).

16057

16057 

_db_block_hash_latches

Number of database block hash latches.

1024

1024

_db_block_hi_priority_batch_size

Fraction of writes for high-priority reasons.

0

0

_db_block_lru_latches*

Number of LRU latches.

8

8

_db_block_max_cr_dba

Maximum allowed number of CR buffers per DBA.

6

6

_db_block_max_dirty_target*

Upper bound on modified buffers/recovery reads.

0

0

_db_block_max_scan_pct*

Percentage of buffers to inspect when looking for free buffers.

40

40

_db_block_med_priority_batch_size

Fraction of writes for medium-priority reasons.

0

0

_db_block_numa

Number of NUMA nodes.

1

1

_db_block_prefetch_quota

Prefetch quota as a percent of cache size.

10

10

_db_block_trace_protect*

Trace buffer protect calls.

FALSE

FALSE

_db_file_direct_io_count*

Sequential I/O buffer size.

1048576

1048576

_db_file_noncontig_mblock_read_count

Number of noncontiguous db blocks to be prefetched.

11

11

_db_handles

Systemwide simultaneous buffer operations.

750

750

_db_handles_cached

Buffer handles cached each process.

5

5

_db_large_dirty_queue

Number of buffers that force dirty queue to be written.

25

25

_db_no_mount_lock

Do not get a mount lock.       

FALSE

FALSE

_db_percent_hot_default

Percent of default buffer pool considered hot.

50

50

_db_percent_hot_keep

Percent of keep buffer pool considered hot.

0

0

_db_percent_hot_recycle

Percent of recycle buffer pool considered hot.

0

0

_db_writer_chunk_writes

Number of writes DBWR should wait for.

0

0

_db_writer_histogram_statistics

Maintain dbwr histogram statistics in x$kcbbhs.

FALSE

FALSE

_db_writer_max_writes

Max number of outstanding DB writer I/Os.

0

0

_db_writer_scan_depth_pct*

Percentage of LRU buffers for dbwr to scan when looking for dirty buffers.

25

25

_dbwr_async_io

Enable dbwriter asynchronous writes.

TRUE

TRUE

_dbwr_scan_interval

Dbwriter scan interval.

10

10

_dbwr_tracing

Enable dbwriter tracing.

0

0

_default_non_equality_sel_check

Sanity check on default selectivity for like/range predicate.

TRUE

TRUE

_defer_multiple_waiters

If TRUE, defer down converts when there are waiters (DFS).

TRUE

TRUE

_diag_daemon*

Start DIAG daemon.

TRUE

TRUE

_disable_file_locks

Disable file locks for control, data, redo log files.

FALSE

FALSE

_disable_incremental_checkpoints

Disable incremental checkpoints for thread recovery.

FALSE

FALSE

_disable_latch_free_SCN_writes_via_32cas

Disable latch-free SCN writes using 32-bit compare and swap.

FALSE

FALSE

_disable_latch_free_SCN_writes_via_64cas

Disable latch-free SCN writes using 64-bit compare and swap.

FALSE

FALSE

_disable_logging

Disable logging.

FALSE

FALSE

_disable_multiple_block_sizes*

Disable multiple blocksize support (for debugging).

FALSE

FALSE

_disable_sun_rsm*

Disable IPC OSD support for Sun RSMAPI.

TRUE

TRUE

_discrete_transactions_enabled

Enable OLTP mode.

FALSE

FALSE

_dispatcher_rate_scale*

Scale to display rate statistic (100ths of a second).

NULL

NULL

_dispatcher_rate_ttl*

Time-to-live for rate statistic (100ths of a second).

NULL

NULL

_distributed_lock_timeout

Number of seconds a distributed transaction waits for a lock.

60

60

_distributed_recovery_connection_hold_time

Number of seconds RECO holds outbound connections open.

200

200

_dlmtrace*

Trace string of lock types(s).

NULL

NULL

_domain_index_batch_size

Maximum number of rows from one call to domain index fetch routines.

2000

2000

_domain_index_dml_batch_size

Maximum number of rows for one call to domain index DML routines.

200

200

_dss_cache_flush

Enable full cache flush for parallel execution.

FALSE

FALSE

 

 

 

 

_dump_MTTR_to_trace

Dump high-availability MTTR information to CKPT trace file.

FALSE

FALSE

_dyn_sel_est_num_blocks*

Number of blocks for dynamic selectivity estimation.

30

30

_dyn_sel_est_on*

Dynamic selectivity estimation on

FALSE

FALSE

_dynamic_stats_threshold

Delay threshold (in seconds) between sending statistics messages.

30

30

_eliminate_common_subexpr

Enables elimination of common subexpressions.

TRUE

TRUE

_enable_NUMA_optimization

Enable NUMA-specific optimizations.

TRUE

TRUE

_enable_block_level_transaction_recovery

Enable block-level recovery.

TRUE 

TRUE

_enable_cscn_caching

Enable commit SCN caching for all transactions.

TRUE

TRUE

 _enable_default_affinity

 To enable default implementation of affinity OSDS.

0

0

 _enable_kgh_policy*

Temporary to disable/enable KGH policy.

FALSE

FALSE

 _enable_list_io

Enable list I/O.

FALSE

FALSE

_enable_multitable_sampling

Enable multitable sampling.

FALSE

FALSE

_enable_type_dep_selectivity

Enable type-dependent selectivity estimates.

TRUE

TRUE

_enqueue_debug_multi_instance

Debug enqueue multi-instance.

FALSE

FALSE

_enqueue_hash

Enqueue hash table length.

375

375

_enqueue_hash_chain_latches

Enqueue hash chain latches.

1

1

 

 

 

 

 _enqueue_locks

 Locks for managed enqueues.

2230

2230

_explain_rewrite_mode*

Allow additional messages to be generated during explain.

FALSE

FALSE

_fairness_pi*

If TRUE, allow spontaneous PI buffers.

TRUE

TRUE

_fairness_threshold

Number of times to CR serve before downgrading lock (DFS).

4

4

_fast_full_scan_enabled

Enable/disable index fast full scan.

TRUE

TRUE

_fifth_spare_parameter

Fifth spare parameter: string.                               

NULL

NULL

_filesystemio_options

I/O operations on filesystem files.

asynch             

asynch    

_first_spare_parameter

 First spare parameter: integer.

NULL

NULL

_force_temptables_for_gsets*

Executes concatenation of rollups using temp tables.

FALSE

FALSE

_fourth_spare_parameter

Fourth spare parameter: string.

NULL

NULL

_full_pwise_join_enabled

Enable full partition-wise join when TRUE.

TRUE

TRUE

_gc_bsp_procs

Number of buffer server processes to start (DFS).

0

0

 _gc_defer_time

How long to defer down converts for hot buffers (DFS).

3

3

_gc_latches

 Number of latches per lock process (DFS).

2

2

 _gc_lck_procs

 Number of background parallel server lock processes to start.

0

0

 

 

 

 

_gc_releasable_locks*

Number of global cache locks (DFS).

0

0

_groupby_nopushdown_cut_ratio

Groupby nopushdown cut ratio.

3

3

_groupby_orderby_combine

Groupby/orderby don't combine threshold.

5000

5000

_gs_anti_semi_join_allowed*

Enable anti-/semi-join for the GS query.

TRUE

TRUE

_hash_multiblock_io_count*

Number of blocks hash join will read/write at once.

0

0

 _high_server_threshold

High server thresholds.

0

0

_idl_conventional_index_maintenance

Enable conventional index maintenance for insert direct.

TRUE

TRUE

_ignore_desc_in_index

Ignore DESC in indexes; sort those columns ascending anyhow.

FALSE

FALSE

_improved_outerjoin_card

Improved outer-join cardinality calculation.

TRUE

TRUE

_improved_row_length_enabled

Enable the improvements for computing the average row length.

TRUE

TRUE

_imr_active*

Activate Instance Membership Recovery feature.

TRUE

TRUE

_imr_max_reconfig_delay*              

Maximum reconfiguration delay (seconds).

300

300

_index_join_enabled

Enable the use of index joins. 

TRUE

TRUE

_index_prefetch_factor

Index prefetching factor. 

100

100

_init_sql_file

File containing SQL statements to execute upon database creation.

?/rdbms/ad min/sql.bsq

?/rdbms/ad min/sql.bsq

_insert_enable_hwm_brokered*

During parallel inserts high-water marks are brokered.

TRUE

TRUE

_inst_locking_period*

Period an instance can retain a newly acquired level 1 bitmap.

5

5      

_interconnect_checksum

If TRUE, checksum interconnect blocks (DFS).

TRUE

TRUE

_io_slaves_disabled

Do not use I/O slaves.

FALSE

FALSE

_ioslave_batch_count

Per attempt I/Os picked.

1

1

 _ioslave_issue_count

I/Os issued before completion check.

500

500

_ipc_fail_network

Simulate cluster network failure.

0

0

 _ipc_test_failover

Test transparent cluster network failover.

0

0

 _ipc_test_mult_nets

Simulate multiple cluster networks.

0

0

_job_queue_interval*

Wakeup interval in seconds for job queue coordinator.

5

5

 _kcl_debug

If TRUE, record lock escalation history (DFS).

TRUE

TRUE

_kcl_dispatch*

If TRUE, dispatch requests to bsp and lck (DFS).

FALSE

FALSE

 _kcl_name_table_latches*

Number of name table latches (DFS).

8

8

 _kcl_partition*

 If TRUE, dynamically remaster partitioned files (DFS).

FALSE

FALSE

 _kcl_use_cr*

If TRUE, use fusion CR buffers (DFS).

TRUE

TRUE

_kdbl_enable_post_allocation*

Allocate dbas after populating data buffers.

FALSE

FALSE

_keep_recovery_buffers*

If TRUE, make recovery buffers current.

TRUE

TRUE

_keep_remote_column_size*

Remote column size does not get modified.

FALSE

FALSE

_kghdsidx_count*

Max kghdsidx count.

1

1

 _kgl_bucket_count

Index to the bucket count array.

9

9

 _kgl_latch_count

Number of library cache latches.

0

0

 _kgl_multi_instance_invalidation

Whether KGL to support multi-instance invalidations.

TRUE

TRUE

_kgl_multi_instance_lock

Whether KGL to support multi-instance locks.

TRUE

TRUE

_kgl_multi_instance_pin

Whether KGL to support multi-instance pins.

TRUE

TRUE

_kkfi_trace

Trace expression substitution.

FALSE

FALSE

 _ksi_trace

KSI trace string of lock  type(s).

NULL

NULL

 _ksmg_granule_size*

Granule size in bytes.

16777216

16777216

 _ksu_diag_kill_time*

Number of seconds ksuitm waits before killing diag.

5

5

 _large_pool_min_alloc

Minimum allocation size in bytes for the large allocation pool.

16K

16K

 _last_allocation_period*

Period over which an instance can retain an active level 1 bitmap 

5

5

_latch_miss_stat_sid

SID of process for which to collect latch stats.

 0

0

_latch_recovery_alignment

Align latch recovery structures.

254

254

_ldr_io_size*

Size of write I/Os used during a load operation.

262144

262144

_left_nested_loops_random

Enable random distribution method for left of nested loops.

TRUE

TRUE

_lgwr_async_io

LGWR Asynchronous I/O enabling Boolean flag.

TRUE

TRUE

_lgwr_io_slaves

LGWR I/O slaves.

0

0

_lgwr_max_ns_wt*

Maximum wait time for LGWR to allow NetServer to progress.

30

30

_lgwr_ns_nl_max*

Variable to simulate network latency.

1000

1000

_lgwr_ns_nl_min*

Variable to simulate network latency.

500

500

_lgwr_ns_sim_err*

Variable to simulate errors during LGWR-network server testing.

0

0

_like_with_bind_as_equality

Treat LIKE predicates with bind variables as an equality predicate.

FALSE

FALSE

_lm_activate_lms_threshold*

Threshold value to activate an additional LMS.

100

100

_lm_cache_res_cleanup

Percentage of cached resources that should be cleaned up.

25

25

_lm_dd_interval

DD time interval in seconds.

60

60

 

 

 

 

_lm_direct_sends

Processes that will do direct lock manager sends.

all

all

_lm_dispatch_nonpcm*

Dispatching non-PCM messages to LMS.  

FALSE

FALSE

_lm_dispatch_pcm*

Dispatching PCM messages to LMS.

FALSE

FALSE

_lm_dynamic_lms*

Dynamic LMS invocation.

FALSE

FALSE

_lm_dynamic_remastering*

If TRUE, enables dynamic remastering.

TRUE

TRUE

_lm_lms*

Number of background lock manager server processes to start.

0

0

_lm_locks*

Number of locks configured for the lock manager.

 12000

1200

_lm_master_weight*

 Master resource weight for this instance.

1

1

_lm_max_lms*

Max. number of background lock manager server processes.

0

0

_lm_min_lms*

Min. number of background lock manager server processes.

0

0

_lm_msg_batch_size*

GES batch message size.

2048

2048

_lm_node_join_opt*

Lock manager node join optimization in reconfig.

FALSE

FALSE

_lm_non_fault_tolerant

Disable lock manager fault-tolerance mode.

FALSE

FALSE

_lm_proc_freeze_timeout*

Reconfiguration: process freeze timeout.

600

600

_lm_procs

Number of client processes configured for the lock manager.

127

127

_lm_rcfg_timeout*

Reconfiguration timeout.

180000

180000

_lm_rcv_buffer_size

The size of receive buffer.

32768

32768

_lm_res_part*

Number of resource partition configured for the lock manager.

1289

1289

_lm_ress*

Number of resources configured for the lock manager.

6000

6000

_lm_send_buffers

Number of lock manager send buffers.

10000

10000

_lm_send_queue_length*

KJCT send queue maximum length.

5000

5000

_lm_sync_timeout

Synchronization timeout for DLM reconfiguration steps.

NULL

NULL

_lm_ticket_active_sendback

Flow control ticket active sendback threshold.

NULL

NULL 

_lm_tickets*

GES messaging tickets.

1000

1000

_lm_tx_delta*

TX lock localization delta.

16

16

_lm_validate_resource_type*

If TRUE, enables resource name validation.

FALSE

FALSE

_lm_xids

Number of transaction IDs configured for the lock manager.

139

139

_local_communication_costing_enabled*

Enable local communication costing when TRUE.

TRUE

TRUE

_local_communication_ratio*

Set the ratio between global and local communication (0…100).

50

50

_lock_sga_areas

Lock-specified areas of the SGA in physical memory.

0

0

_log_archive_buffer_size

Size of each archival buffer in log file blocks.

2048

2048

_log_archive_buffers

Number of buffers to allocate for archiving.

4

4

_log_blocks_during_backup

Log block images when changed during backup.

TRUE

TRUE

_log_buffers_corrupt

Corrupt redo buffers before write.

FALSE

FALSE

_log_buffers_debug

Debug redo buffers (slows things down).

FALSE

FALSE

_log_checkpoint_recovery_check

Number of redo blocks to verify after checkpoint.

0

0

_log_debug_multi_instance

Debug redo multi-instance code FALSE.

FALSE

 

_log_io_size

Automatically initiate log write if this many redo blocks in buffer.

0

0

_log_simultaneous_copies

Number of simultaneous copies into redo buffer (number of copy latches)

2

2

_log_space_errors

Should we report space errors to alert log?

TRUE

TRUE

_log_switch_timeout*

Maximum number of seconds redos in the current log could span.

0

0

_low_server_threshold

Low server thresholds.

0

0

_master_direct_sends*

Direct sends for messages from master (DFS).

31

31

_mav_refresh_consistent_read*

Refresh materialized views using consistent read snapshot.

TRUE

TRUE

_mav_refresh_double_count_prevented*

Materialized view MAV refreshes avoid double counting.

FALSE

FALSE

_mav_refresh_opt*

Optimizations during refresh of materialized views.

0

0

_mav_refresh_unionall_tables*

Number of tables for unionall expansion during materialized view refresh. @@@Au: Check sense. To us it makes sense@@@

3

3

_max_exponential_sleep

Max sleep during exponential backoff.

0

0

_max_sleep_holding_latch

Max time to sleep while holding a latch.

4

4

_messages

Message queue resources: dependent on number of processes and buffers

300

300

_minimal_stats_aggregation

Prohibit stats aggregation at compile/partition maintenance time.

TRUE

TRUE

_minimum_giga_scn

Minimum SCN to start with in 2^30 units.

0

0

_multi_join_key_table_lookup*

TRUE if multijoin-key table lookup prefetch is enabled.

TRUE

TRUE

_mv_refresh_delta_fraction*

Delta MV as fractional percentage of size of MV.

10

10

_mv_refresh_eut*

Refresh materialized views using EUT (partition)-based algorithm.

TRUE

TRUE

_mv_refresh_new_setup_disabled*

Materialized view MV refresh new setup disabling.

FALSE

FALSE

_mv_refresh_selections

Create materialized views with selections and fast refresh.

TRUE

TRUE

_mv_refresh_use_stats*

Pass cardinality hints to refresh queries.

TRUE

TRUE

_nchar_imp_cnv*

NLS allow implicit conversion between CHAR and NCHAR.

TRUE

TRUE

_ncmb_readahead_enabled

Enable multiblock read-ahead for an index scan.

0

0

_ncmb_readahead_tracing

Turn on multiblock read-ahead tracing.

0

0

_nested_loop_fudge

Nested loop fudge.

100

100

_new_initial_join_orders

Enable initial join orders based on new ordering heuristics.

TRUE

TRUE

_new_sort_cost_estimate*

Enables the use of new cost estimate for sort.

TRUE

TRUE

_no_objects

No object features are used.

FALSE

FALSE

_no_or_expansion

OR expansion during optimization disabled.

FALSE

FALSE

_ns_max_flush_wt*

Flush wait time for NetServer to flush outstanding writes.

30

30

_number_cached_attributes

Maximum number of cached attributes per instance.

10

10

_offline_rollback_segments

Offline undo segment list.

NULL

NULL

_ogms_home

GMS home directory.

NULL

NULL

_old_connect_by_enabled

Enable/disable old connect-by behavior.@@@Au: By what?@@@

FALSE

FALSE

_oneside_colstat_for_equijoins

Sanity check on default selectivity for like/range predicate.

TRUE

TRUE

_open_files_limit

Limit on number of files opened by I/O subsystem.

4294967294

4294967294

_optim_enhance_nnull_detection

TRUE, to enable index [fast] full scan more often.

TRUE

TRUE

_optim_new_default_join_sel*

Improves the way default equijoin selectivity are computed.

TRUE

TRUE

_optim_peek_user_binds*

Enable peeking of user binds.

TRUE

TRUE

_optimizer_adjust_for_nulls

Adjust selectivity for null values.

TRUE

TRUE

_optimizer_choose_permutation

Force the optimizer to use the specified permutation.

0

0

_optimizer_cost_model*

Optimizer cost model.

CHOOSE

CHOOSE

_optimizer_degree

Force the optimizer to use the same degree of parallelism.

0

0

_optimizer_mode_force

Force setting of optimizer mode for user-recursive SQL also.

TRUE

TRUE

_optimizer_new_join_card_computation*

Compute join cardinality using nonrounded input values.

TRUE

TRUE

_optimizer_new_mbio*

Enables the use of new costing I/O with MBIO.

0

0

_optimizer_percent_parallel*

Optimizer percent parallel.

101

101

_optimizer_search_limit

Optimizer search limit.

5

5

_optimizer_undo_changes

Undo changes to query optimizer.

FALSE

FALSE

_or_expand_nvl_predicate

Enable OR expanded plan for NVL/DECODE predicate.

TRUE

TRUE

_oracle_trace_events

Oracle TRACE event flags.

NULL

NULL

_oracle_trace_facility_version

Oracle TRACE facility version.

NULL

NULL

_ordered_nested_loop

Enable ordered nested loop costing.

TRUE

TRUE

_ordered_semijoin

Enable ordered semi-join subquery.

TRUE

TRUE

_parallel_adaptive_max_users

Maximum number of users running with default DOP.

1

1                                     

_parallel_default_max_instances

Default maximum number of instances for parallel query.

1

1                  

_parallel_execution_message_align

Alignment of PX buffers to OS page boundary.

FALSE

FALSE

_parallel_fake_class_pct

Fake db-scheduler percent used for testing.

0

0

_parallel_load_bal_unit

Number of threads to allocate per instance.

0

0

_parallel_load_balancing

Parallel execution load-balanced slave allocation.

TRUE

TRUE

_parallel_min_message_pool

Minimum size of shared pool memory to reserve for PQ servers

64440

64440

_parallel_recovery_stopat

Stop at position to step through SMON. @@@Au: Check—what do hyphens around “position” indicate?Got me, oracle put it there@@@

32767

32767

_parallel_server_idle_time

Idle time before parallel query server dies.

5

5

_parallel_server_sleep_time

Sleep time between dequeue timeouts (in 1/100ths).

10

10

_parallel_txn_global

Enable parallel_txn hint with updates and deletes.

FALSE

FALSE

_parallelism_cost_fudge_factor

Set the parallelism cost fudge factor.

350

350

_partial_pwise_join_enabled

Enable partial partition-wise join when TRUE.

TRUE

TRUE

_passwordfile_enqueue_timeout

Password file enqueue timeout in seconds.

900

900

_pcm_fast_reconfig*

If TRUE, enable fast reconfiguration for PCM locks.

TRUE

TRUE

_pcm_latches*

Number of PCM resource hash latches to be allocated.

128

128

_pcm_resources*

Number of PCM resources to be allocated.

NULL

NULL                                

_pcm_shadow_locks*

Number of PCM shadow locks to be allocated.

NULL

NULL

_pct_refresh_double_count_prevented*

Materialized view PCT refreshes avoid double counting.

TRUE

TRUE

_pdml_gim_sampling

Control separation of global index maintenance for PDML.

5000

5000

_pdml_gim_staggered

Slaves start on different index when doing index maintenance.

FALSE

FALSE

_pdml_slaves_diff_part

Slaves start on different partition when doing index maintenance.

TRUE

TRUE

_pga_max_size*

Maximum size of the PGA memory for one process.

209715200

209715200

_ping_level*

Fusion ping level (DFS).

3

3

_plsql_dump_buffer_events

Conditions upon which the PL/SQL circular buffer is dumped.

NULL

NULL

_plsql_load_without_compile

PL/SQL load without compilation flag.

FALSE

FALSE

_pmon_load_constants*

Server load-balancing constants (S, P, D, I, L, C, M).

300,192,64 300,192,64,3,10,10,0

300,192,64,3,10,10,0         

_pre_rewrite_push_pred*

Push predicates into views before rewrite.

FALSE

FALSE

_pred_move_around*

Enables predicate move-around. 

TRUE

TRUE

_predicate_elimination_Enabled

Allow predicate elimination if set to true. @@@Au: If set to what?@@@

TRUE

TRUE

_project_view_columns

Enable projecting out unreferenced columns of a view

TRUE

TRUE

_push_join_predicate

Enable pushing join predicate inside a view

TRUE

TRUE

_push_join_union_view

Enable pushing join predicate inside a UNION view.

TRUE

TRUE

_px_async_getgranule

Asynchronous get granule in the slave.

FALSE

FALSE

_px_broadcast_fudge_factor

Set the TQ broadcasting fudge factor percentage.

100

100

_px_granule_size

Default size of a rowid range granule (in KB).

100000

100000             

_px_index_sampling

Parallel query sampling for index create (100000 = 100%).

200

200

_px_kxib_tracing

Turn on kxib tracing.

0

0

_px_load_publish_interval

Interval at which LMON will check whether to publish PX load.

200

200

_px_max_granules_per_slave

Maximum number of rowid range granules to generate per slave.

100

100

_px_min_granules_per_slave

Minimum number of rowid range granules to generate per slave.

13

13

_px_no_stealing

Prevent parallel granule stealing in shared nothing environment.

FALSE

FALSE

_px_trace*

PX trace parameter.

none

none

_query_cost_rewrite

Perform the cost-based rewrite with materialized views.

TRUE

TRUE

_query_rewrite_1*

Perform query rewrite before and after or only before view merging.

TRUE

TRUE

_query_rewrite_2

Perform query rewrite before and after or only after view merging.

TRUE

TRUE

_query_rewrite_drj*

MV rewrite, and drop redundant joins.

TRUE

TRUE

_query_rewrite_expression

Rewrite with canonical form for expressions.

TRUE

TRUE

_query_rewrite_fpc*

MV rewrite fresh partition containment.

TRUE

TRUE

_query_rewrite_fudge

Cost-based query rewrite with MV fudge factor.

90

90

_query_rewrite_jgmigrate*

MV rewrite with jg migration.

TRUE

TRUE

_query_rewrite_or_error*

Allow query rewrite, if referenced tables are not dataless.

FALSE

FALSE

_query_rewrite_vop_cleanup

Prune frocol chain before rewrite after view merging.

TRUE

TRUE

_realfree_heap_free_threshold*

Threshold for performing real-free, in Kbytes.

64

64

_realfree_heap_max_size*

Minimum max total heap size, in Kbytes.

32768

32768

_recovery_claim_batch_size*

Number of messages to batch in a recovery claim message (DFS).

10

10                 

_recovery_percentage*

Recovery buffer cache percentage

80

80                 

_release_insert_threshold

Maximum number of unusable blocks to unlink from freelist.

5

5                  

_reliable_block_sends*

If TRUE, block sends across interconnect are reliable.

FALSE

FALSE

_reuse_index_loop

Number of blocks being examined for index block reuse.

5

5

_rollback_segment_count

Number of undo segments.

0

0

_rollback_segment_initial

Starting undo segment number.  

1

1

_row_cache_buffer_size

Size of row cache circular buffer.

200

200

_row_cache_cursors

Number of cached cursors for row cache management.

10

10

_row_cache_instance_locks

Number of row cache instance locks.

100

100 

_scn_scheme

SCN scheme.

NULL

NULL

_second_spare_parameter*

Second spare parameter: integer.

NULL

NULL

_send_close_with_block*

If TRUE, send close with block, even with direct sends.

TRUE

TRUE

_serial_direct_read

Enable direct read in serial

FALSE

FALSE

_session_idle_bit_latches

One latch per session or a latch per group of sessions.

0

0

_seventh_spare_parameter*

Seventh spare parameter: string list.

NULL

NULL

_shared_pool_reserved_min_alloc

Minimum allocation size in bytes for reserved area of shared pool.

4400

4400

_shared_pool_reserved_pct*

Percentage memory of the shared pool allocated for the reserved section.

5

5

_shrunk_aggs_disable_threshold*

Percentage of exceptions at which to switch to full-length aggregates.

60

60

_shrunk_aggs_enabled*

Enable use of variable-sized buffers for nondistinct aggregates.

TRUE

TRUE

_side_channel_batch_size*

Number of messages to batch in a side channel message (DFS).

100

100

_single_process

Run without detached processes.

FALSE

FALSE

 _sixth_spare_parameter*

Sixth spare parameter: string list.

NULL

NULL

 _skip_assume_msg*

If TRUE, skip assume message for consigns at the master.

TRUE

TRUE

 _slave_mapping_enabled*

Enable slave mapping when TRUE.

TRUE

TRUE

 _slave_mapping_group_size*

Force the number of slave group in a slave mapper.

0

0

 _small_table_threshold

Threshold level of table size for direct reads.

160

160

 _smm_auto_cost_enabled*

If TRUE, use the AUTO size policy cost functions.

FALSE

FALSE

 _smm_auto_max_io_size*

Maximum I/O size (in KB) used by sort/hash-join in auto mode.

248

248

 _smm_auto_min_io_size*

Minimum I/O size (in KB) used by sort/hash-join in auto mode.

56

56

 _smm_bound*

Overwrites memory manager automatically computed bound.

0

0

 _smm_control*

Provides controls on the memory manager.

0

0

 _smm_max_size*

Maximum work area size in auto mode (serial).

0

0

 _smm_min_size*

Minimum work area size in auto mode.

128

128

 _smm_px_max_size*

Maximum work area size in auto mode (global).

0

0

 _smm_trace*

Turn on/off tracing for SQL memory manager.

0

0

 _smon_internal_errlimit*

Limit of SMON internal errors.

100

100

 _smu_debug_mode*

<debug-flag> Set debug event for testing SMU operations.

0

0

 _smu_error_simulation_site*

Site ID of error simulation in KTU code.

0

0

 _smu_error_simulation_type*

Error type for error simulation in KTU code.

0

0

 _sort_elimination_cost_ratio

Cost ratio for sort elimination under first_rows mode.

0

0

 _sort_multiblock_read_count*

Multiblock read count for sort.

2

2

 _sort_space_for_write_buffers

Tenths of sort_area_size devoted to direct write buffers.

1

1

_sortmerge_inequality_join_off

Turns off sort-merge join on inequality.

FALSE

FALSE

_spin_count

Amount to spin waiting for a latch.

1

1

_sql_connect_capability_override

SQL Connect Capability Table override.

0

0

 _sql_connect_capability_table

SQL Connect Capability Table (testing only).

NULL

NULL

 _sqlexec_progression_cost

SQL execution progression monitoring cost threshold.

1000

1000

_subquery_pruning_cost_factor

Subquery pruning cost factor.

20

20

 _subquery_pruning_enabled

Enable the use of subquery predicates to perform pruning.

TRUE

TRUE

_subquery_pruning_mv_enabled*

Enable the use of subquery predicates with MVs to perform  pruning.

TRUE

TRUE

 _subquery_pruning_reduction

Subquery pruning reduction factor.

50

50

 _system_index_caching*

Optimizer percent system index caching.

0

0

 _system_trig_enabled

Are system triggers enabled?   

TRUE

TRUE

 _table_lookup_prefetch_size*

Table lookup prefetch vector size.

40

40

 _table_scan_cost_plus_one

Bump estimated full table scan cost by one.

FALSE

FALSE

 _temp_tran_block_threshold

Number of blocks for a dimension before we temp transform.

100

100

 _temp_tran_cache

Determines if temp table is created with cache option.

TRUE

TRUE

 _test_param_1

Test parmeter 1: integer.

25

25

 _test_param_2

Test parameter 2:  string.

NULL

NULL

 _test_param_3

Test parameter 3:  string.

NULL

NULL

 _test_param_4*

Test parameter 4: string list.

NULL

NULL

 _test_param_5*

Test parmeter 5: deprecated integer.

25

25

 _test_param_6*

Test parmeter 6:  size (ub8).

0

0

 _third_spare_parameter

Third spare parameter:  integer.

NULL

NULL

 _tq_dump_period

Time period for duping of TQ statistics (s).

0

0

 _trace_archive*

Start DIAG process.

FALSE

FALSE

 _trace_buffer_flushes

Trace buffer flushes if otrace cacheIO event is set.

FALSE

FALSE

 _trace_buffer_gets

 Trace kcb buffer gets if otrace cacheIO event is set.

FALSE

FALSE

 _trace_buffers*

Trace buffer sizes per process

ALL:256

ALL:256            

_trace_cr_buffer_creates

Trace cr buffer creates if otrace cacheIO event is set.

FALSE

FALSE

 _trace_events

Trace events enabled at startup.

NULL

NULL

 _trace_file_size

Maximum size of trace file (in number of trace records).

65536

65536

 _trace_files_public

Create publicly accessible trace files.

FALSE

FALSE

 _trace_flush_processes*

Trace data archived by DIAG for these processes.

ALL

ALL

 _trace_instance_termination

Trace instance termination actions.

FALSE

FALSE

 _trace_multi_block_reads

Trace multi_block reads if otrace cacheIO event is set.

FALSE

FALSE

 _trace_options*

 Trace data flush options.

text,multiple

text,multiple

 _trace_processes*

Enable KST tracing in process.

ALL

ALL

 _transaction_recovery_servers

Max number of parallel recovery slaves that may be used.

0

0

 _tts_allow_nchar_mismatch

Allow plugging in a tablespace with a different national character set.

 FALSE

 FALSE

 _two_pass*

Enable two-pass thread recovery.

TRUE

TRUE

 _unnest_notexists_sq*

Unnest NOT EXISTS subquery with one or more tables if possible.

SINGLE

SINGLE

 _unnest_subquery

Enables unnesting of correlated subqueries.

TRUE

TRUE

 _use_column_stats_for_function

Enable the use of column statistics for DDP functions.

TRUE

TRUE

 _use_ism

Enable shared page tables – ISM.

TRUE

TRUE

 _use_new_explain_plan*

If TRUE, use the AUTO size policy cost functions.

FALSE

FALSE

 _use_nosegment_indexes

Use nosegment indexes in explain plan.

FALSE

FALSE

 _use_realfree_heap*

Use real free-based allocator for PGA memory.

FALSE

FALSE

 _use_vector_post

Use vector post.

TRUE

TRUE

 _verify_undo_quota*

If TRUE; verify consistency of undo quota statistics.

FALSE

FALSE

 _wait_for_sync

Wait for sync on commit MUST ALWAYS BE TRUE.

TRUE

TRUE

 _walk_insert_threshold

Maximum number of unusable blocks to walk across freelist.

0

0

 _write_clones

Write clones flag.

3

3

_yield_check_interval

Interval to check whether actses should yield.

100000

 

100000

 

* Indicates parameter is Oracle only.


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter