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

 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
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 










Oracle hidden parameters

Don Burleson

Note:  Oracle hidden parameters are undocumented and unsupported by Oracle. 

WARNING! Using undocumented parameters without the consent of Oracle can make your system un-supported and you may be on your own if you experience data corruption. Don't say I didn't warn you!

Also see these hidden parameter tips:

Make sure that you thoroughly test all hidden parameters before placing 
them in your production environment.
Parameter Name                            Description
----------------------------------------- ----------------------------------------------------------------
_abort_recovery_on_join                   if TRUE, abort recovery on join reconfigurations
_active_standby_fast_reconfiguration      if TRUE optimize dlm reconfiguration for active/standby OPS
_adaptive_direct_read                     Adaptive Direct Read
_adaptive_fetch_enabled                   enable/disable adaptive fetch in parallel group by
_affinity_on                              enable/disable affinity at run time
_all_shared_dblinks                       treat all dblinks as shared
_allocate_creation_order                  should files be examined in creation order during allocation
_allocation_update_interval               interval at which successful search in L1 should be updated
_allow_error_simulation                   Allow error simulation for testing
_allow_read_only_corruption               allow read-only open even if database is corrupt
_allow_resetlogs_corruption               allow resetlogs even if it will cause corruption
_allow_terminal_recovery_corruption       Finish terminal recovery even if it may cause corruption
_always_anti_join                         always use this method for anti-join when possible
_always_semi_join                         always use this method for semi-join when possible
_always_star_transformation               always favor use of star transformation
_app_ctx_vers                             enable app ctx versioning
_aq_tm_scanlimit                          scan limit for Time Managers to clean up IOT
_arch_io_slaves                           ARCH I/O slaves
_async_recovery_claims                    if TRUE, issue recovery claims asynchronously (DFS)
_async_recovery_reads                     if TRUE, issue recovery reads asynchronously (DFS)
_attach_count_slack                       unauth user processes slack
_avoid_prepare                            if TRUE, do not prepare a buffer when the master is local (DFS)
_b_tree_bitmap_plans                      enable the use of bitmap plans for tables w. only B-tree indexes
_backup_disk_io_slaves                    BACKUP Disk I/O slaves
_backup_io_pool_size                      memory to reserve from the large pool
_bitmap_or_improvement_enabled            controls extensions to partition pruning for general predicates
_bump_highwater_mark_count                how many blocks should we allocate per free list on advancing HW
_bwr_for_flushed_pi                       if TRUE, generate a BWR for a flushed PI (DFS)
_cgs_send_timeout                         CGS send timeout value
_check_block_after_checksum               perform block check after checksum if both are turned on
_cleanup_rollback_entries                 no. of undo entries to apply per transaction cleanup
_close_cached_open_cursors                close cursors cached by PL/SQL at each commit
_collect_undo_stats                       Collect Statistics v$undostat
_column_elimination_off                   turn off predicate-only column elimination
_column_tracking_level                    column usage tracking
_compatible_no_recovery                   Database will be compatible unless crash or media recovery is ne
_complex_view_merging                     enable complex view merging
_controlfile_enqueue_timeout              control file enqueue timeout in seconds
_corrupted_rollback_segments              corrupted undo segment list
_cost_equality_semi_join                  enables costing of equality semi-join
_cpu_count                                current number of cpu's for this instance
_cpu_to_io                                divisor for converting CPU cost to I/O cost
_cr_grant_global_role                     if TRUE, grant lock for CR requests when block is in global role
_cr_grant_local_role                      if TRUE, grant lock for CR using 3way ping when block in local r
_cr_server_log_flush                      if TRUE, flush redo log before serving a CR buffer (DFS)
_cursor_db_buffers_pinned                 additional number of buffers a cursor can pin at once
_cursor_plan_enabled                      enable collection and display of cursor plans
_db_aging_cool_count                      Touch count set when buffer cooled
_db_aging_freeze_cr                       Make CR buffers always be too cold to keep in cache
_db_aging_hot_criteria                    Touch count which sends a buffer to head of replacement list
_db_aging_stay_count                      Touch count set when buffer moved to head of replacement list
_db_aging_touch_time                      Touch count which sends a buffer to head of replacement list
_db_always_check_system_ts                Always perform block check and checksum for System tablespace
_db_block_buffers                         Number of database blocks cached in memory: hidden parameter
_db_block_cache_clone                     Always clone data blocks on get (for debugging)
_db_block_cache_num_umap                  number of unmapped buffers (for tracking swap calls on blocks)
_db_block_cache_protect                   protect database blocks (true only when debugging)
_db_block_check_for_debug                 Check more and dump block before image for debugging
_db_block_granule_interval                number of granules to process
_db_block_hash_buckets                    Number of database block hash buckets
_db_block_hash_latches                    Number of database block hash latches
_db_block_hi_priority_batch_size          Fraction of writes for high priority reasons
_db_block_lru_latches                     number of lru latches
_db_block_max_cr_dba                      Maximum Allowed Number of CR buffers per dba
_db_block_max_dirty_target                Upper bound on modified buffers/recovery reads
_db_block_max_scan_pct                    Percentage of buffers to inspect when looking for free
_db_block_med_priority_batch_size         Fraction of writes for medium priority reasons
_db_block_numa                            Number of NUMA nodes
_db_block_prefetch_quota                  Prefetch quota as a percent of cache size
_db_block_trace_protect                   trace buffer protect calls
_db_cache_advice_batch_size               cache advisory simulation batch size
_db_cache_advice_sample_factor            cache advisory sampling factor
_db_file_direct_io_count                  Sequential I/O buf size
_db_file_noncontig_mblock_read_count      number of noncontiguous db blocks to be prefetched
_db_handles                               System-wide simultaneous buffer operations
_db_handles_cached                        Buffer handles cached each process
_db_large_dirty_queue                     Number of buffers which force dirty queue to be written
_db_mttr_advice                           MTTR advisory
_db_mttr_partitions                       number of partitions for MTTR advisory
_db_mttr_sample_factor                    MTTR simulation sampling factor
_db_mttr_sim_target                       MTTR simulation targets
_db_mttr_sim_trace_size                   MTTR simulation trace size
_db_mttr_trace_to_alert                   dump trace entries to alert file
_db_percent_hot_default                   Percent of default buffer pool considered hot
_db_percent_hot_keep                      Percent of keep buffer pool considered hot
_db_percent_hot_recycle                   Percent of recycle buffer pool considered hot
_db_writer_chunk_writes                   Number of writes DBWR should wait for
_db_writer_histogram_statistics           maintain dbwr histogram statistics in x$kcbbhs
_db_writer_max_writes                     Max number of outstanding DB Writer IOs
_db_writer_scan_depth_pct                 Percentage of LRU buffers for dbwr to scan when looking for dirt
_dbg_proc_startup                         debug process startup
_dbwr_async_io                            Enable dbwriter asynchronous writes
_dbwr_scan_interval                       dbwriter scan interval
_dbwr_tracing                             Enable dbwriter tracing
_default_non_equality_sel_check           sanity check on default selectivity for like/range predicate
_defer_multiple_waiters                   if TRUE, defer down converts when there were waiters (DFS)
_diag_daemon                              start DIAG daemon
_disable_file_locks                       disable file locks for control, data, redo log files
_disable_incremental_checkpoints          Disable incremental checkpoints for thread recovery
_disable_kcbhxor_osd                      disable kcbh(c)xor OSD functionality
_disable_latch_free_SCN_writes_via_32cas  disable latch-free SCN writes using 32-bit compare & swap
_disable_latch_free_SCN_writes_via_64cas  disable latch-free SCN writes using 64-bit compare & swap
_disable_logging                          Disable logging
_disable_multiple_block_sizes             disable multiple block size support (for debugging)
_disable_odm                              disable odm feature
_disable_recoverable_recovery             Disable the new recoverable recovery mechanism
_disable_savepoint_reset                  disable the fix for bug 1402161
_disable_sun_rsm                          Disable IPC OSD support for Sun RSMAPI
_disable_system_state                     disable system state dump
_discrete_transactions_enabled            enable OLTP mode
_dispatcher_rate_scale                    scale to display rate statistic (100ths of a second)
_dispatcher_rate_ttl                      time-to-live for rate statistic (100ths of a second)
_distributed_recovery_connection_hold_tim number of seconds RECO holds outbound connections open
_dlmtrace                                 Trace string of lock types(s)
_domain_index_batch_size                  maximum number of rows from one call to domain index fetch routi
_domain_index_dml_batch_size              maximum number of rows for one call to domain index dml routines
_dss_cache_flush                          enable full cache flush for parallel execution
_dump_interval_limit                      trace dump time interval limit (in seconds)
_dump_max_limit                           max number of dump within dump interval
_dump_MTTR_to_trace                       Dump High Availability MTTR infromation to CKPT trace file
_dump_system_state_scope                  scope of sysstate dump during instance termination
_dump_trace_scope                         scope of trace dump during a process crash
_dynamic_rls_policies                     rls policies are dynamic
_dynamic_stats_threshold                  delay threshold (in seconds) between sending statistics messages
_eliminate_common_subexpr                 enables elimination of common sub-expressions
_enable_block_level_transaction_recovery  enable block level recovery
_enable_cscn_caching                      enable commit SCN caching for all transactions
_enable_default_affinity                  to enable default implementation of affinity osds
_enable_kgh_policy                        temporary to disable/enable kgh policy
_enable_list_io                           Enable List I/O
_enable_multitable_sampling               enable multitable sampling
_enable_NUMA_optimization                 Enable NUMA specific optimizations
_enable_type_dep_selectivity              enable type dependent selectivity estimates
_enqueue_debug_multi_instance             debug enqueue multi instance
_enqueue_hash                             enqueue hash table length
_enqueue_hash_chain_latches               enqueue hash chain latches
_enqueue_locks                            locks for managed enqueues
_explain_rewrite_mode                     allow additional messages to be generated during explain rewrite
_fairness_threshold                       number of times to CR serve before downgrading lock (DFS)
_fast_full_scan_enabled                   enable/disable index fast full scan
_fifth_spare_parameter                    fifth spare parameter - string
_first_spare_parameter                    first spare parameter - integer
_force_datefold_trunc                     force use of trunc for datefolding rewrite
_force_temptables_for_gsets               executes concatenation of rollups using temp tables
_fourth_spare_parameter                   fourth spare parameter - string
_full_pwise_join_enabled                  enable full partition-wise join when TRUE
_gby_onekey_enabled                       enable use of one comparison of all group by keys
_gc_affinity_limit                        dynamic affinity limit (DFS)
_gc_affinity_time                         how often in minutes to check affinity (DFS)
_gc_defer_time                            how long to defer down converts for hot buffers (DFS)
_gc_integrity_checks                      if TRUE, enable expensive integrity checks (DFS)
_gc_latches                               number of latches per LMS process (DFS)
_gcs_fast_reconfig                        if TRUE, enable fast reconfiguration for gcs locks
_gcs_latches                              number of gcs resource hash latches to be allocated
_gcs_resources                            number of gcs resources to be allocated
_gcs_shadow_locks                         number of gcs shadow locks to be allocated
_generalized_pruning_enabled              controls extensions to partition pruning for general predicates
_groupby_nopushdown_cut_ratio             groupby nopushdown cut ratio
_groupby_orderby_combine                  groupby/orderby don't combine threshold
_gs_anti_semi_join_allowed                enable anti/semi join for the GS query
_hard_protection                          if TRUE enable H.A.R.D specific format changes
_hash_multiblock_io_count                 number of blocks hash join will read/write at once
_high_server_threshold                    high server thresholds
_idl_conventional_index_maintenance       enable conventional index maintenance for insert direct load
_ignore_desc_in_index                     ignore DESC in indexes, sort those columns ascending anyhow
_improved_outerjoin_card                  improved outer-join cardinality calculation
_improved_row_length_enabled              enable the improvements for computing the average row length
_imr_active                               Activate Instance Membership Recovery feature
_imr_max_reconfig_delay                   Maximum Reconfiguration delay (seconds)
_imr_splitbrain_res_wait                  Maximum wait for split-brain resolution (seconds)
_index_join_enabled                       enable the use of index joins
_index_prefetch_factor                    index prefetching factor
_init_sql_file                            File containing SQL statements to execute upon database creation
_initial_file_size                        Initial size used for file creation when file size not known
_insert_enable_hwm_brokered               during parallel inserts high water marks are brokered
_inst_locking_period                      period an instance can retain a newly acquired level1 bitmap
_instance_type                            type of instance to be executed
_interconnect_checksum                    if TRUE, checksum interconnect blocks (DFS)
_intrapart_pdml_enabled                   Enable intra-partition updates/deletes
_intrapart_pdml_randomlocal_enabled       Enable intra-partition updates/deletes with random local dist
_io_slaves_disabled                       Do not use I/O slaves
_ioslave_batch_count                      Per attempt IOs picked
_ioslave_issue_count                      IOs issued before completion check
_ipc_fail_network                         Simulate cluster network failer
_ipc_test_failover                        Test transparent cluster network failover
_ipc_test_mult_nets                       simulate multiple cluster networks
_job_queue_interval                       Wakeup interval in seconds for job queue co-ordinator
_kcl_commit                               if TRUE, call kjbcommit (DFS)
_kcl_conservative_log_flush               if TRUE, conservatively log flush before CR serving (DFS)
_kcl_debug                                if TRUE, record le history (DFS)
_kcl_index_split                          if TRUE, reject pings on blocks in middle of a split (DFS)
_kcl_name_table_latches                   number of name table latches (DFS)
_kcl_recovery_read_batch                  recovery read batch size (DFS)
_kcl_undo_grouping                        grouping for undo block locks (DFS)
_kcl_undo_locks                           number of locks per undo segment (DFS)
_kcl_use_cr                               if TRUE, use Fusion CR buffers (DFS)
_kdbl_enable_post_allocation              allocate dbas after populating data buffers
_keep_recovery_buffers                    if TRUE, make recovery buffers current (DFS)
_keep_remote_column_size                  remote column size does not get modified
_kghdsidx_count                           max kghdsidx count
_kgl_bucket_count                         index to the bucket count array
_kgl_latch_count                          number of library cache latches
_kgl_multi_instance_invalidation          whether KGL to support multi-instance invalidations
_kgl_multi_instance_lock                  whether KGL to support multi-instance locks
_kgl_multi_instance_pin                   whether KGL to support multi-instance pins
_kglsim_maxmem_percent                    max percentage of shared pool size to be used for KGL advice
_kkfi_trace                               trace expression substitution
_kolfuseslf                               allow kolf to use slffopen
_ksdxw_cini_flg                           ksdxw context initialization flag
_ksdxw_nbufs                              ksdxw number of buffers in buffered mode
_ksdxw_num_pgw                            number of watchpoints on a per-process basis
_ksdxw_num_sgw                            number of watchpoints to be shared by all processes
_ksdxw_stack_depth                        number of PCs to collect in the stack when watchpoint is hit
_ksi_trace                                KSI trace string of lock type(s)
_ksmg_granule_locking_status              granule locking status
_ksmg_granule_size                        granule size in bytes
_ksu_diag_kill_time                       number of seconds ksuitm waits before killing diag
_ktc_latches                              number of ktc latches
_ktu_latches                              number of KTU latches
_large_pool_min_alloc                     minimum allocation size in bytes for the large allocation pool
_last_allocation_period                   period over which an instance can retain an active level1 bitmap
_latch_class_0                            latch class 0
_latch_class_1                            latch class 1
_latch_class_2                            latch class 2
_latch_class_3                            latch class 3
_latch_class_4                            latch class 4
_latch_class_5                            latch class 5
_latch_class_6                            latch class 6
_latch_class_7                            latch class 7
_latch_classes                            latch classes override
_latch_miss_stat_sid                      Sid of process for which to collect latch stats
_latch_recovery_alignment                 align latch recovery structures
_ldr_io_size                              size of write IOs used during a load operation
_left_nested_loops_random                 enable random distribution method for left of nestedloops
_lgwr_async_io                            LGWR Asynchronous IO enabling boolean flag
_lgwr_delay_write                         LGWR write delay for debugging
_lgwr_io_slaves                           LGWR I/O slaves
_lgwr_max_ns_wt                           Maximum wait time for lgwr to allow NetServer to progress
_lgwr_ns_nl_max                           Variable to simulate network latency
_lgwr_ns_nl_min                           Variable to simulate network latency
_lgwr_ns_sim_err                          Variable to simulate errors lgwrns
_library_cache_advice                     whether KGL advice should be turned on
_like_with_bind_as_equality               treat LIKE predicate with bind as an equality predicate
_lm_activate_lms_threshold                threshold value to activate an additional lms
_lm_cache_lvl0_cleanup                    how often to cleanup level 0 cache res (in sec)
_lm_cache_res_cleanup                     percentage of cached resources should be cleanup
_lm_cache_res_type                        cache resource: string of lock types(s)
_lm_dd_interval                           dd time interval in seconds
_lm_direct_sends                          Processes which will do direct sends
_lm_drm_window                            dynamic remastering bucket window size
_lm_dynamic_lms                           dynamic lms invocation
_lm_dynamic_load                          dynamic load adjustment
_lm_dynamic_remastering                   if TRUE enables dynamic remastering
_lm_enq_lock_freelist                     Number of ges enqueue element freelist
_lm_enq_rcfg                              if TRUE enables enqueue reconfiguration
_lm_enqeue_freelist                       Number of enqueue freelist
_lm_file_affinity                         mapping between file id and master instance number
_lm_lmd_waittime                          default wait time for lmd
_lm_lms                                   number of background global cache server processes to start
_lm_lms_waittime                          default wait time for lms
_lm_locks                                 number of enqueues configured for cluster database
_lm_master_weight                         master resource weight for this instance
_lm_max_lms                               max. number of background global cache server processes
_lm_min_lms                               min. number of background global cache server processes
_lm_msg_batch_size                        GES batch message size
_lm_msg_cache_thresholds                  GES message buffer caching threshold
_lm_msg_cleanup_interval                  GES message buffer cleanup interval time
_lm_node_join_opt                         cluster database node join optimization in reconfig
_lm_non_fault_tolerant                    disable cluster database fault-tolerance mode
_lm_num_pcmhv_latches                     number of latches covering the PCM HV buckets in cgs
_lm_proc_freeze_timeout                   reconfiguration: process freeze timeout
_lm_process_batching                      GES implicit process batching for IPC messages
_lm_procs                                 number of client processes configured for cluster database
_lm_rcfg_timeout                          Reconfiguration timeout
_lm_rcv_buffer_size                       the size of receive buffer
_lm_res_hash_bucket                       number of resource hash buckets
_lm_res_part                              number of resource partition configured for gcs
_lm_ress                                  number of resources configured for cluster database
_lm_send_buffers                          number of cluster database send buffers
_lm_send_queue_batching                   GES send queue message batching
_lm_send_queue_length                     GES send queue maximum length
_lm_share_lock_opt                        if TRUE enables share lock optimization
_lm_sq_batch_factor                       GES send queue minimum batching factor
_lm_sync_timeout                          Synchronization timeout for DLM reconfiguration steps
_lm_ticket_active_sendback                Flow control ticket active sendback threshold
_lm_tickets                               GES messaging tickets
_lm_tx_delta                              TX lock localization delta
_lm_validate_resource_type                if TRUE enables resource name validation
_lm_xids                                  number of transaction IDs configured for cluster database
_load_without_compile                     Load PL/SQL or Database objects without compilation
_local_communication_costing_enabled      enable local communication costing when TRUE
_local_communication_ratio                set the ratio between global and local communication (0..100)
_lock_sga_areas                           Lock specified areas of the SGA in physical memory
_log_archive_buffer_size                  Size of each archival buffer in log file blocks
_log_archive_buffers                      Number of buffers to allocate for archiving
_log_archive_callout                      archival callout
_log_archive_delta_sync_wait              iterative sleep time in centiseconds seconds when SYNC=PARALLEL
_log_archive_net_timeout                  maximum network wait time in seconds when SYNC=PARALLEL
_log_blocks_during_backup                 log block images when changed during backup
_log_buffers_corrupt                      corrupt redo buffers before write
_log_buffers_debug                        debug redo buffers (slows things down)
_log_checkpoint_recovery_check            # redo blocks to verify after checkpoint
_log_committime_block_cleanout            Log commit-time block cleanout
_log_debug_multi_instance                 debug redo multi instance code
_log_io_size                              automatically initiate log write if this many redo blocks in buf
_log_simultaneous_copies                  number of simultaneous copies into redo buffer(# of copy latches
_log_space_errors                         should we report space errors to alert log
_log_switch_timeout                       Maximum number of seconds redos in the current log could span
_low_server_threshold                     low server thresholds
_master_direct_sends                      direct sends for messages from master (DFS)
_mav_refresh_consistent_read              refresh materialized views using consistent read snapshot
_mav_refresh_double_count_prevented       materialized view MAV refreshes avoid double counting
_mav_refresh_opt                          optimizations during refresh of materialized views
_mav_refresh_unionall_tables              # tables for union all expansion during materialized view refres
_max_exponential_sleep                    max sleep during exponential backoff
_max_sleep_holding_latch                  max time to sleep while holding a latch
_messages                                 message queue resources - dependent on # processes & # buffers
_minimal_stats_aggregation                prohibit stats aggregation at compile/partition maintenance time
_minimum_giga_scn                         Minimum SCN to start with in 2^30 units
_multi_join_key_table_lookup              TRUE iff multi-join-key table lookup prefetch is enabled
_mv_refresh_delta_fraction                delta mv as fractional percentage of size of mv
_mv_refresh_eut                           refresh materialized views using EUT(partition)-based algorithm
_mv_refresh_new_setup_disabled            materialized view MV refresh new setup disabling
_mv_refresh_selections                    create materialized views with selections and fast refresh
_mv_refresh_use_stats                     pass cardinality hints to refresh queries
_nchar_imp_cnv                            NLS allow Implicit Conversion between CHAR and NCHAR
_ncmb_readahead_enabled                   enable multi-block readahead for an index scan
_ncmb_readahead_tracing                   turn on multi-block readahead tracing
_nested_loop_fudge                        nested loop fudge
_nested_mav_fast_oncommit_enabled         nested MAV refresh fast on commit allowed
_new_initial_join_orders                  enable initial join orders based on new ordering heuristics
_new_sort_cost_estimate                   enables the use of new cost estimate for sort
_no_objects                               no object features are used
_no_or_expansion                          OR expansion during optimization disabled
_ns_max_flush_wt                          Flush wait time for NetServer to flush oustanding writes
_num_longop_child_latches                 number of child latches for long op array
_NUMA_instance_mapping                    Set of nodes that this instance should run on
_NUMA_pool_size                           aggregate size in bytes of NUMA pool
_number_cached_attributes                 maximum number of cached attributes per instance
_object_statistics                        enable the object level statistics collection
_offline_rollback_segments                offline undo segment list
_ogms_home                                GMS home directory
_olap_aggregate_buffer_size               OLAP Aggregate max buffer size
_olap_aggregate_child_fragment_size       OLAP Aggregate child fragment size
_olap_aggregate_child_max_size            OLAP Aggregate child list max size
_olap_aggregate_flags                     OLAP Aggregate debug flags
_olap_aggregate_function_cache_enabled    OLAP Aggregate function cache enabler
_olap_aggregate_function_merge_threshold  OLAP Aggregate function merge threshold
_olap_aggregate_max_thread_tuples         OLAP Aggregate max thread tuples creation
_olap_aggregate_min_buffer_size           OLAP Aggregate min buffer size
_olap_aggregate_min_thread_status         OLAP Aggregate minimum cardinality of dimensions for thread
_olap_aggregate_multipath_hier            OLAP Aggregate Multi-path Hierarhies enabled
_olap_aggregate_statlen_thresh            OLAP Aggregate status array usage threshold
_olap_aggregate_store_probability         OLAP Aggregate function storeback probability
_olap_aggregate_work_per_thread           OLAP Aggregate max work parents
_olap_aggregate_worklist_max              OLAP Aggregate max worklists generated at once
_olap_allocate_errorlog_format            OLAP Allocate Errorlog Format
_olap_allocate_errorlog_header            OLAP Allocate Errorlog Header format
_olap_continuous_trace_file               Specify TRUE to enable continuous OLAP tracing - otherwise only
_olap_dimsave_restore_cache_values        OLAP Dimsave restores cached dimension values
_olap_eif_export_lob_size                 OLAP EIF Export BLOB size
_olap_parallel_update_threshold           OLAP parallel update threshold in pages
_olap_poutlog_echo_to_eventlog            OLAP POutLog copy output to event log (tracefile)
_olap_sort_buffer_size                    OLAP Sort Buffer Size
_old_connect_by_enabled                   enable/disable old connect by
_omf                                      enable/disable OMF
_oneside_colstat_for_equijoins            sanity check on default selectivity for like/range predicate
_open_files_limit                         Limit on number of files opened by I/O subsystem
_optim_adjust_for_part_skews              adjust stats for skews across partitions
_optim_enhance_nnull_detection            TRUE to enable index [fast] full scan more often
_optim_new_default_join_sel               improves the way default equijoin selectivity are computed
_optim_peek_user_binds                    enable peeking of user binds
_optimizer_adjust_for_nulls               adjust selectivity for null values
_optimizer_choose_permutation             force the optimizer to use the specified permutation
_optimizer_cost_model                     optimizer cost model
_optimizer_degree                         force the optimizer to use the same degree of parallelism
_optimizer_dyn_smp_blks                   number of blocks for optimizer dynamic sampling
_optimizer_mode_force                     force setting of optimizer mode for user recursive SQL also
_optimizer_new_join_card_computation      compute join cardinality using non-rounded input values
_optimizer_percent_parallel               optimizer percent parallel
_optimizer_search_limit                   optimizer search limit
_optimizer_system_stats_usage             system statistics usage
_optimizer_undo_changes                   undo changes to query optimizer
_optimizer_undo_cost_change               optimizer undo cost change
_or_expand_nvl_predicate                  enable OR expanded plan for NVL/DECODE predicate
_ordered_nested_loop                      enable ordered nested loop costing
_ordered_semijoin                         enable ordered semi-join subquery
_parallel_adaptive_max_users              maximum number of users running with default DOP
_parallel_broadcast_enabled               enable broadcasting of small inputs to hash and sort merge joins
_parallel_default_max_instances           default maximum number of instances for parallel query
_parallel_execution_message_align         Alignment of PX buffers to OS page boundary
_parallel_fake_class_pct                  fake db-scheduler percent used for testing
_parallel_load_bal_unit                   number of threads to allocate per instance
_parallel_load_balancing                  parallel execution load balanced slave allocation
_parallel_min_message_pool                minimum size of shared pool memory to reserve for pq servers
_parallel_recovery_stopat                 stop at -position- to step through SMON
_parallel_server_idle_time                idle time before parallel query server dies
_parallel_server_sleep_time               sleep time between dequeue timeouts (in 1/100ths)
_parallel_txn_global                      enable parallel_txn hint with updates and deletes
_parallelism_cost_fudge_factor            set the parallelism cost fudge factor
_partial_pwise_join_enabled               enable partial partition-wise join when TRUE
_passwordfile_enqueue_timeout             password file enqueue timeout in seconds
_pct_refresh_double_count_prevented       materialized view PCT refreshes avoid double counting
_pdml_gim_sampling                        control separation of global index maintenance for PDML
_pdml_gim_staggered                       slaves start on different index when doing index maint
_pdml_slaves_diff_part                    slaves start on different partition when doing index maint
_pga_max_size                             Maximum size of the PGA memory for one process
_ping_level                               fusion ping level (DFS)
_plsql_dump_buffer_events                 conditions upon which the PL/SQL circular buffer is dumped
_pmon_load_constants                      server load balancing constants (S,P,D,I,L,C,M)
_pre_rewrite_push_pred                    push predicates into views before rewrite
_precompute_gid_values                    precompute gid values and copy them before returning a row
_pred_move_around                         enables predicate move-around
_predicate_elimination_enabled            allow predicate elimination if set to TRUE
_project_view_columns                     enable projecting out unreferenced columns of a view
_push_join_predicate                      enable pushing join predicate inside a view
_push_join_union_view                     enable pushing join predicate inside a union view
_px_async_getgranule                      asynchronous get granule in the slave
_px_broadcast_fudge_factor                set the tq broadcasting fudge factor percentage
_px_dynamic_opt                           turn off/on restartable qerpx dynamic optimization
_px_dynamic_sample_size                   num of samples for restartable qerpx dynamic optimization
_px_granule_size                          default size of a rowid range granule (in KB)
_px_index_sampling                        parallel query sampling for index create (100000 = 100%)
_px_kxib_tracing                          turn on kxib tracing
_px_load_publish_interval                 interval at which LMON will check whether to publish PX load
_px_max_granules_per_slave                maximum number of rowid range granules to generate per slave
_px_min_granules_per_slave                minimum number of rowid range granules to generate per slave
_px_no_stealing                           prevent parallel granule stealing in shared nothing environment
_px_trace                                 px trace parameter
_PX_use_large_pool                        Use Large Pool as source of PX buffers
_query_cost_rewrite                       perform the cost based rewrite with materialized views
_query_rewrite_1                          perform query rewrite before&after or only before view merging
_query_rewrite_2                          perform query rewrite before&after or only after view merging
_query_rewrite_drj                        mv rewrite and drop redundant joins
_query_rewrite_expression                 rewrite with cannonical form for expressions
_query_rewrite_fpc                        mv rewrite fresh partition containment
_query_rewrite_fudge                      cost based query rewrite with MVs fudge factor
_query_rewrite_jgmigrate                  mv rewrite with jg migration
_query_rewrite_maxdisjunct                query rewrite max disjuncts
_query_rewrite_or_error                   allow query rewrite, if referenced tables are not dataless
_query_rewrite_vop_cleanup                prune frocol chain before rewrite after view-merging
_realfree_heap_free_threshold             threshold for performing real-free, in Kbytes
_realfree_heap_max_size                   minimum max total heap size, in Kbytes
_realfree_heap_mode                       mode flags for real-free heap
_recoverable_recovery_batch_percent       Recoverable recovery batch size (percentage of buffer cache)
_recovery_asserts                         if TRUE, enable expensive recovery sanity checks (DFS)
_recovery_claim_batch_size                number of messages to batch in a recovery claim message (DFS)
_recovery_percentage                      recovery buffer cache percentage
_release_insert_threshold                 maximum number of unusable blocks to unlink from freelist
_reliable_block_sends                     if TRUE, block sends across interconnect are reliable
_reuse_index_loop                         number of blocks being examine for index block reuse
_rollback_segment_count                   number of undo segments
_rollback_segment_initial                 starting undo segment number
_row_cache_cursors                        number of cached cursors for row cache management
_row_cr                                   enable row cr for all sql
_rowsource_execution_statistics           if TRUE, Oracle will collect rowsource level statistics
_scn_scheme                               SCN scheme
_second_spare_parameter                   second spare parameter - integer
_send_ast_to_foreground                   if TRUE, send ast message to foreground
_send_close_with_block                    if TRUE, send close with block even with direct sends
_send_requests_to_PI                      if TRUE, try to send CR requests to PI buffers (DFS)
_serial_direct_read                       enable direct read in serial
_session_idle_bit_latches                 one latch per session or a latch per group of sessions
_seventh_spare_parameter                  seventh spare parameter - string list
_shared_pool_reserved_min_alloc           minimum allocation size in bytes for reserved area of shared poo
_shared_pool_reserved_pct                 percentage memory of the shared pool allocated for the reserved
_shrunk_aggs_disable_threshold            percentage of exceptions at which to switch to full length aggs
_shrunk_aggs_enabled                      enable use of variable sized buffers for non-distinct aggregates
_side_channel_batch_size                  number of messages to batch in a side channel message (DFS)
_side_channel_batch_timeout               timeout before shipping out all the batched side channel message
_single_process                           run without detached processes
_sixth_spare_parameter                    sixth spare parameter - string list
_skip_assume_msg                          if TRUE, skip assume message for consigns at the master
_slave_mapping_enabled                    enable slave mapping when TRUE
_slave_mapping_group_size                 force the number of slave group in a slave mapper
_small_table_threshold                    threshold level of table size for direct reads
_smm_advice_enabled                       if TRUE, enable v$pga_advice
_smm_advice_log_size                      overwrites default size of the PGA advice workarea history log
_smm_auto_cost_enabled                    if TRUE, use the AUTO size policy cost functions
_smm_auto_max_io_size                     Maximum IO size (in KB) used by sort/hash-join in auto mode
_smm_auto_min_io_size                     Minimum IO size (in KB) used by sort/hash-join in auto mode
_smm_bound                                overwrites memory manager automatically computed bound
_smm_control                              provides controls on the memory manager
_smm_max_size                             maximum work area size in auto mode (serial)
_smm_min_size                             minimum work area size in auto mode
_smm_px_max_size                          maximum work area size in auto mode (global)
_smm_trace                                Turn on/off tracing for SQL memory manager
_smon_consume_post                        consume any extra posts after completion of transaction recovery
_smon_internal_errlimit                   limit of SMON internal errors
_smu_debug_mode                           <debug-flag> - set debug event for testing SMU operations
_smu_error_simulation_site                site ID of error simulation in KTU code
_smu_error_simulation_type                error type for error simulation in KTU code
_sort_elimination_cost_ratio              cost ratio for sort eimination under first_rows mode
_sort_multiblock_read_count               multi-block read count for sort
_sort_space_for_write_buffers             tenths of sort_area_size devoted to direct write buffers
_sortmerge_inequality_join_off            turns off sort-merge join on inequality
_spin_count                               Amount to spin waiting for a latch
_sql_connect_capability_override          SQL Connect Capability Table Override
_sql_connect_capability_table             SQL Connect Capability Table (testing only)
_sqlexec_progression_cost                 sql execution progression monitoring cost threshold
_subquery_pruning_cost_factor             subquery pruning cost factor
_subquery_pruning_enabled                 enable the use of subquery predicates to perform pruning
_subquery_pruning_mv_enabled              enable the use of subquery predicates with MVs to perform prunin
_subquery_pruning_reduction               subquery pruning reduction factor
_system_index_caching                     optimizer percent system index caching
_system_trig_enabled                      are system triggers enabled
_table_lookup_prefetch_size               table lookup prefetch vector size
_table_lookup_prefetch_thresh             table lookup prefetch threshold
_table_scan_cost_plus_one                 bump estimated full table scan and index ffs cost by one
_temp_tran_block_threshold                number of blocks for a dimension before we temp transform
_temp_tran_cache                          determines if temp table is created with cache option
_test_ksusigskip                          test the function ksusigskip
_test_param_1                             test parmeter 1 - integer
_test_param_2                             test parameter 2 - string
_test_param_3                             test parameter 3 - string
_test_param_4                             test parameter 4 - string list
_test_param_5                             test parmeter 5 - deprecated integer
_test_param_6                             test parmeter 6 - size (ub8)
_third_spare_parameter                    third spare parameter - integer
_tq_dump_period                           time period for duping of TQ statistics (s)
_trace_archive                            start DIAG process
_trace_buffer_flushes                     trace buffer flushes if otrace cacheIO event is set
_trace_buffer_gets                        trace kcb buffer gets if otrace cacheIO event is set
_trace_buffers                            trace buffer sizes per process
_trace_cr_buffer_creates                  trace cr buffer creates if otrace cacheIO event is set
_trace_events                             trace events enabled at startup
_trace_file_size                          maximum size of trace file (in number of trace records)
_trace_files_public                       Create publicly accessible trace files
_trace_flush_processes                    trace data archived by DIAG for these processes
_trace_instance_termination               trace instance termination actions
_trace_multi_block_reads                  trace multi_block reads if otrace cacheIO event is set
_trace_options                            trace data flush options
_trace_pin_time                           trace how long a current pin is held
_trace_processes                          enable KST tracing in process
_transaction_recovery_servers             max number of parallel recovery slaves that may be used
_tts_allow_nchar_mismatch                 allow plugging in a tablespace with a different national charact
_two_pass                                 enable two-pass thread recovery
_two_pass_reverse_polish_enabled          uses two-pass reverse polish alg. to generate canonical forms
_union_rewrite_for_gs                     expand queries with GSets into UNIONs for rewrite
_unnest_notexists_sq                      unnest NOT EXISTS subquery with one or more tables if possible
_unnest_subquery                          enables unnesting of correlated subqueries
_use_column_stats_for_function            enable the use of column statistics for DDP functions
_use_ism                                  Enable Shared Page Tables - ISM
_use_new_explain_plan                     if TRUE, use the AUTO size policy cost functions
_use_nosegment_indexes                    use nosegment indexes in explain plan
_use_realfree_heap                        use real-free based allocator for PGA memory
_use_seq_process_cache                    whether to use process local seq cache
_use_vector_post                          use vector post
_verify_undo_quota                        TRUE - verify consistency of undo quota statistics
_wait_for_sync                            wait for sync on commit MUST BE ALWAYS TRUE
_walk_insert_threshold                    maximum number of unusable blocks to walk across freelist
_watchpoint_on                            is the watchpointing feature turned on?
_write_clones                             write clones flag
_yield_check_interval                     interval to check whether actses should yield




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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational