히든 파라미터 확인하는 쿼리
2010.05.12 08:50
원문 : http://www.ischo.net -- 조인상 // 시스템 엔지니어
Writer : http://www.ischo.net -- ischo // System Engineer in Replubic Of Korea
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
본문 : http://www.ischo.net -- 조인상 //시스템 엔지니어
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
COL NAME FORMAT A40
COL VALUE FORMAT A10
COL DESCRIPTION FORMAT A50
SET LINESIZE 150
select
x.ksppinm as Name
,y.ksppstvl as Value
,x.ksppdesc as Description
from
x$ksppi x, x$ksppcv y
where x.indx = y.indx
and substr(x.ksppinm,1,1) = '_'
;
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_trace_files_public FALSE Create publicly accessible trace files
_hang_analysis_num_call_stacks 3 hang analysis num call stacks
_ior_serialize_fault 0 inject fault in the ior serialize code
_inject_startup_fault 0 inject fault in the startup code
_latch_recovery_alignment 998 align latch recovery structures
_spin_count 2000 Amount to spin waiting for a latch
_latch_miss_stat_sid 0 Sid of process for which to collect latch stats
_max_sleep_holding_latch 4 max time to sleep while holding a latch
_max_exponential_sleep 0 max sleep during exponential backoff
_other_wait_threshold 0 threshold wait percentage for event wait class Oth
er
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_other_wait_event_exclusion exclude event names from _other_wait_threshold cal
culations
_use_vector_post TRUE use vector post
_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
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_latch_class_6 latch class 6
_latch_class_7 latch class 7
_latch_classes latch classes override
_ultrafast_latch_statistics TRUE maintain fast-path statistics for ultrafast latche
s
_enable_reliable_latch_waits TRUE Enable reliable latch waits
_session_idle_bit_latches 0 one latch per session or a latch per group of sess
ions
_ksu_diag_kill_time 5 number of seconds ksuitm waits before killing diag
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_ksuitm_dont_kill_dumper FALSE delay inst. termination to allow processes to dump
_disable_image_check FALSE Disable Oracle executable image checking
_num_longop_child_latches 8 number of child latches for long op array
_longops_enabled TRUE longops stats enabled
_test_ksusigskip 5 test the function ksusigskip
_os_sched_high_priority 1 OS high priority level
_disable_kcbhxor_osd FALSE disable kcbh(c)xor OSD functionality
_disable_system_state 4294967294 disable system state dump
_session_wait_history 10 enable session wait history collection
_session_idle_check_interval 60 Resource Manager session idle limit check interval
in seconds
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_pkt_enable FALSE enable progressive kill test
_pkt_start FALSE start progressive kill test instrumention
_pkt_pmon_interval 50 PMON process clean-up interval (cs)
_dead_process_scan_interval 10 PMON dead process scan interval
_collapse_wait_history FALSE collapse wait history
_short_stack_timeout_ms 30000 short stack timeout in ms
_single_process FALSE run without detached processes
_dbg_proc_startup FALSE debug process startup
_static_backgrounds static backgrounds
_number_cached_attributes 10 maximum number of cached attributes per instance
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_oradebug_force FALSE force target processes to execute oradebug command
s?
_ksdxdocmd_default_timeout_ms 30000 default timeout for internal oradebug commands
_watchpoint_on FALSE is the watchpointing feature turned on?
_ksdxw_num_sgw 10 number of watchpoints to be shared by all processe
s
_ksdxw_num_pgw 10 number of watchpoints on a per-process basis
_ksdxw_stack_depth 4 number of PCs to collect in the stack when watchpo
int is hit
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_ksdxw_cini_flg 0 ksdxw context initialization flag
_ksdxw_nbufs 1000 ksdxw number of buffers in buffered mode
_enable_kgh_policy FALSE temporary to disable/enable kgh policy
_NUMA_pool_size Not specif aggregate size in bytes of NUMA pool
ied
_enable_NUMA_optimization TRUE Enable NUMA specific optimizations
_use_ism TRUE Enable Shared Page Tables - ISM
_lock_sga_areas 0 Lock specified areas of the SGA in physical memory
_NUMA_instance_mapping Not specif Set of nodes that this instance should run on
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
ied
_simulator_upper_bound_multiple 2 upper bound multiple of pool size
_simulator_pin_inval_maxcnt 16 maximum count of invalid chunks on pin list
_simulator_lru_rebalance_thresh 10240 LRU list rebalance threshold (count)
_simulator_lru_rebalance_sizthr 2 LRU list rebalance threshold (size)
_simulator_bucket_mindelta 8192 LRU bucket minimum delta
_simulator_lru_scan_count 8 LRU scan count
_simulator_internal_bound 10 simulator internal bound percent
_simulator_reserved_obj_count 1024 simulator reserved object count
_simulator_reserved_heap_count 4096 simulator reserved heap count
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_simulator_sampling_factor 2 sampling factor for the simulator
_realfree_heap_max_size 32768 minimum max total heap size, in Kbytes
_realfree_heap_pagesize_hint 65536 hint for real-free page size in bytes
_realfree_heap_mode 0 mode flags for real-free heap
_use_realfree_heap TRUE use real-free based allocator for PGA memory
_pga_large_extent_size 1048576 PGA large extent size
_uga_cga_large_extent_size 262144 UGA/CGA large extent size
_total_large_extent_memory 0 Total memory for allocating large extents
_use_ism_for_pga TRUE Use ISM for allocating large extents
_private_memory_address Start address of large extent memory segment
_mem_annotation_sh_lev 0 shared memory annotation collection level
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_mem_annotation_pr_lev 0 private memory annotation collection level
_mem_annotation_scale 1 memory annotation pre-allocation scaling
_mem_annotation_store FALSE memory annotation in-memory store
_4031_dump_bitvec 67194879 bitvec to specify dumps prior to 4031 error
_4031_max_dumps 100 Maximum number of 4031 dumps for this process
_4031_dump_interval 300 Dump 4031 error once for each n-second interval
_4031_sga_dump_interval 3600 Dump 4031 SGA heapdump error once for each n-secon
d interval
_4031_sga_max_dumps 10 Maximum number of SGA heapdumps
_mem_std_extent_size 4096 standard extent size for fixed-size-extent heaps
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
__shared_pool_size 251658240 Actual size in bytes of shared pool
__large_pool_size 16777216 Actual size in bytes of large pool
__java_pool_size 16777216 Actual size in bytes of java pool
__streams_pool_size 0 Actual size in bytes of streams pool
_large_pool_min_alloc 16000 minimum allocation size in bytes for the large all
ocation pool
_shared_pool_reserved_pct 5 percentage memory of the shared pool allocated for
the reserved area
_shared_pool_reserved_min_alloc 4400 minimum allocation size in bytes for reserved area
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
of shared pool
_kghdsidx_count 2 max kghdsidx count
_test_param_1 25 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 25 test parmeter 5 - deprecated integer
_test_param_6 0 test parmeter 6 - size (ub8)
_disable_instance_params_check FALSE disable instance type check for ksp
_parameter_table_block_size 2048 parameter table block size
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_ksd_test_param 999 KSD test parmeter
_kse_die_timeout 60000 amount of time a dying process is spared by PMON (
in centi-secs)
_stack_guard_level 0 stack guard level
_kse_pc_table_size 256 kse pc table cache size
_messages 600 message queue resources - dependent on # processes
& # buffers
_enqueue_locks 4170 locks for managed enqueues
_enqueue_resources 1692 resources for enqueues
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_enqueue_hash 705 enqueue hash table length
_enqueue_debug_multi_instance FALSE debug enqueue multi instance
_enqueue_hash_chain_latches 8 enqueue hash chain latches
_ksi_trace KSI trace string of lock type(s)
_trace_processes ALL enable KST tracing in process
_trace_archive FALSE start DIAG process
_trace_events trace events enabled at startup
_trace_buffers ALL:256 trace buffer sizes per process
_trace_flush_processes ALL trace data archived by DIAG for these processes
_trace_file_size 65536 maximum size of trace file (in bytes)
_trace_options text,multi trace data flush options
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
ple
_trace_buffer_wrap_timestamp TRUE enable KST timestamp on trace buffer wrap
_nchar_imp_cnv TRUE NLS allow Implicit Conversion between CHAR and NCH
AR
_disable_file_locks FALSE disable file locks for control, data, redo log fil
es
_ksfd_verify_write FALSE verify asynchronous writes issued through ksfd
_disable_odm FALSE disable odm feature
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_enable_list_io FALSE Enable List I/O
_omf enabled enable/disable OMF
_aiowait_timeouts 100 Number of aiowait timeouts before error is reporte
d
_io_shared_pool_size 4194304 Size of I/O buffer pool from SGA
_db_file_direct_io_count 1048576 Sequential I/O buf size
_ioslave_issue_count 500 IOs issued before completion check
_ioslave_batch_count 1 Per attempt IOs picked
_io_slaves_disabled FALSE Do not use I/O slaves
_lgwr_io_slaves 0 LGWR I/O slaves
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_arch_io_slaves 0 ARCH I/O slaves
_backup_disk_io_slaves 0 BACKUP Disk I/O slaves
_backup_io_pool_size 1048576 memory to reserve from the large pool
_high_server_threshold 0 high server thresholds
_low_server_threshold 0 low server thresholds
_yield_check_interval 100000 interval to check whether actses should yield
_resource_manager_always_on TRUE enable the resource manager always
_first_spare_parameter first spare parameter - integer
_second_spare_parameter second spare parameter - integer
_third_spare_parameter third spare parameter - integer
_fourth_spare_parameter fourth spare parameter - string
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_fifth_spare_parameter fifth spare parameter - string
_sixth_spare_parameter sixth spare parameter - string list
_seventh_spare_parameter seventh spare parameter - string list
_ksxp_send_timeout 300 set timeout for sends queued with the inter-instan
ce IPC
_ksxp_diagmode OFF set to OFF to disable automatic slowsend diagnosti
cs
_skgxp_reaping 1000 tune skgxp OSD reaping limit
_skgxp_udp_hiwat_warn 1000 ach hiwat mark warning interval
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_skgxp_udp_ach_reaping_time 120 time in minutes before idle ach's are reaped
_skgxp_udp_timed_wait_seconds 5 time in seconds before timed wait is invoked
_skgxp_udp_timed_wait_buffering 1024 diagnostic log buffering space (in bytes) for time
d wait (0 means unbufferd
_skgxp_udp_keep_alive_ping_timer_secs 300 connection idle time in seconds before keep alive
is initiated. min: 30 sec max: 1800 sec default: 3
00 sec
_disable_duplex_link FALSE Turn off connection duplexing
_diag_diagnostics TRUE Turn off diag diagnostics
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_disable_interface_checking FALSE disable interface checking at startup
_skgxp_udp_interface_detection_time_secs 60 time in seconds between interface detection checks
_ksxp_reporting_process LMD0 reporting process for KSXP
_ksmg_granule_size 16777216 granule size in bytes
_ksmg_granule_locking_status 1 granule locking status
_ksmg_lock_check_interval timeout action interval in minutes
_ksmg_lock_reacquire_count 5 repeat count for acquisition of locks
_filemap_dir FILEMAP directory
_object_statistics TRUE enable the object level statistics collection
_enable_rlb TRUE enable RLB metrics processing
_disable_health_check FALSE Disable Health Check
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_accept_versions List of parameters for rolling operation
_hang_detection 0 Hang Management detection interval
_diag_daemon TRUE start DIAG daemon
_dump_system_state_scope local scope of sysstate dump during instance termination
_dump_trace_scope global scope of trace dump during a process crash
_dump_interval_limit 120 trace dump time interval limit (in seconds)
_dump_max_limit 5 max number of dump within dump interval
_trace_navigation_scope global enabling trace navigation linking
_max_protocol_support 10000 Max occurrence protocols supported in a process
_lm_lms 0 number of background gcs server processes to start
_lm_dynamic_lms FALSE dynamic lms invocation
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_lm_max_lms 0 max. number of background global cache server proc
esses
_lm_min_lms 0 min. number of background global cache server proc
esses
_lm_activate_lms_threshold 100 threshold value to activate an additional lms
_lm_lmd_waittime 8 default wait time for lmd in centiseconds
_lm_lms_waittime 8 default wait time for lms in centiseconds
_lm_procs 320 number of client processes configured for cluster
database
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_lm_ress 6000 number of resources configured for cluster databas
e
_lm_locks 12000 number of enqueues configured for cluster database
_lm_master_weight 1 master resource weight for this instance
_active_standby_fast_reconfiguration TRUE if TRUE optimize dlm reconfiguration for active/st
andby OPS
_lm_enq_rcfg TRUE if TRUE enables enqueue reconfiguration
_lm_dynamic_remastering FALSE if TRUE enables dynamic remastering
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_lm_xids 352 number of transaction IDs configured for cluster d
atabase
_lm_res_part 128 number of resource partition configured for gcs
_lm_drm_window 0 dynamic remastering bucket window size
_lm_drm_max_requests 100 dynamic remastering maximum affinity requests proc
essed together
_lm_drm_xlatch 0 dynamic remastering forced exclusive latches
_lm_contiguous_res_count 128 number of contiguous blocks that will hash to the
same HV bucket
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_lm_num_pt_buckets 4096 number of buckets in the object affinity hash tabl
e
_lm_num_pt_latches 128 number of latches in the object affinity hash tabl
e
_lm_num_pcmhv_latches 0 number of latches covering the PCM HV buckets in c
gs
_lm_node_join_opt FALSE cluster database node join optimization in reconfi
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
g
_lm_non_fault_tolerant FALSE disable cluster database fault-tolerance mode
_lm_cache_res_cleanup 25 percentage of cached resources should be cleanup
_lm_cache_res_type TMHWDI cache resource: string of lock types(s)
_lm_cache_lvl0_cleanup 0 how often to cleanup level 0 cache res (in sec)
_lm_send_buffers 10000 number of cluster database send buffers
_lm_rcv_buffer_size 32768 the size of receive buffer
_ogms_home GMS home directory
_lm_sync_timeout Synchronization timeout for DLM reconfiguration st
eps
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_lm_ticket_active_sendback Flow control ticket active sendback threshold
_lm_rcfg_timeout 180000 Reconfiguration timeout
_lm_enq_lock_freelist Number of ges enqueue element freelist
_lm_enqueue_freelist 3 Number of enqueue freelist
_lm_dd_interval 60 dd time interval in seconds
_lm_dd_scan_interval 5 dd scan interval in seconds
_dlmtrace Trace string of global enqueue type(s)
_lm_tx_delta 16 TX lock localization delta
_lm_proc_freeze_timeout 300 reconfiguration: process freeze timeout
_lm_validate_resource_type FALSE if TRUE enables resource name validation
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_lm_file_affinity mapping between file id and master instance number
_lm_share_lock_opt FALSE if TRUE enables share lock optimization
_lm_res_hash_bucket 0 number of resource hash buckets
_ges_diagnostics TRUE if TRUE enables GES diagnostics
_fair_remote_cvt FALSE if TRUE enables fair remote convert
_dlm_send_timeout 30000 DLM send timeout value
_ges_dd_debug TRUE if TRUE enables GES deadlock detection debug diagn
ostics
_lm_global_posts TRUE if TRUE deliver global posts to remote nodes
_rcfg_parallel_replay TRUE if TRUE enables parallel replay and cleanup at rec
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
onfiguration
_parallel_replay_msg_limit 4000 Number of messages for each round of parallel repl
ay
_rcfg_parallel_fixwrite TRUE if TRUE enables parallel fixwrite at reconfigurati
on
_parallel_fixwrite_bucket 1000 Number of buckets for each round of fix write
_rcfg_parallel_verify TRUE if TRUE enables parallel verify at reconfiguration
_dump_rcvr_ipc TRUE if TRUE enables IPC dump at instance eviction time
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_ges_health_check 1 if greater than 0 enables GES system health check
_lm_msg_batch_size 0 GES batch message size
_lm_tickets 1000 GES messaging tickets
_lm_msg_cache_thresholds GES message buffer caching threshold
_lm_msg_cleanup_interval 3000 GES message buffer cleanup interval time
_lm_send_mode auto GES send mode
_lm_send_queue_length 5000 GES send queue maximum length
_lm_send_queue_batching TRUE GES send queue message batching
_lm_process_batching TRUE GES implicit process batching for IPC messages
_lm_sq_batch_factor 2 GES send queue minimum batching factor
_lm_sq_batch_type auto GES send queue batching mechanism
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_lm_sq_batch_waittick 3 GES send queue batching waittime in tick
_lm_sendproxy_reserve 25 GES percentage of send proxy reserve of send ticke
ts
_lm_checksum_batch_msg FALSE GES checksum batch messages
_abort_recovery_on_join FALSE if TRUE, abort recovery on join reconfigurations
_send_ast_to_foreground TRUE if TRUE, send ast message to foreground
_reliable_block_sends FALSE if TRUE, block sends across interconnect are relia
ble
_blocks_per_cache_server 128 number of consecutive blocks per global cache serv
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
er
_send_close_with_block TRUE if TRUE, send close with block even with direct se
nds
_gcs_fast_reconfig TRUE if TRUE, enable fast reconfiguration for gcs locks
_cr_grant_global_role TRUE if TRUE, grant lock for CR requests when block is
in global role
_cr_grant_local_role AUTO turn 3-way CR grants off, make it automatic, or tu
rn it on
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_skip_assume_msg TRUE if TRUE, skip assume message for consigns at the m
aster
_gcs_resources number of gcs resources to be allocated
_gcs_latches 0 number of gcs resource hash latches to be allocate
d per LMS process
_gcs_process_in_recovery TRUE if TRUE, process gcs requests during instance reco
very
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_gcs_shadow_locks number of pcm shadow locks to be allocated
_side_channel_batch_size 200 number of messages to batch in a side channel mess
age (DFS)
_side_channel_batch_timeout 6 timeout before shipping out the batched side chann
elmessages
_master_direct_sends 31 direct sends for messages from master (DFS)
_cgs_send_timeout 300 CGS send timeout value
_imr_active TRUE Activate Instance Membership Recovery feature
_imr_max_reconfig_delay 300 Maximum Reconfiguration delay (seconds)
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_imr_splitbrain_res_wait 600 Maximum wait for split-brain resolution (seconds)
_imr_disk_voting_interval 3 Maximum wait for IMR disk voting (seconds)
_cluster_library clss cluster library selection
_cgs_reconfig_timeout 120 CGS reconfiguration timeout interval
_lm_dynamic_load TRUE dynamic load adjustment
_notify_crs FALSE notify cluster ready services of startup and shutd
own
_shared_pool_max_size 0 shared pool maximum size when auto SGA enabled
_streams_pool_max_size 0 streams pool maximum size when auto SGA enabled
_disable_streams_pool_auto_tuning FALSE disable streams pool auto tuning
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_memory_management_tracing 0 trace memory management activity
_memory_sanity_check 0 partial granule sanity check
_init_granule_interval 10 number of granules to process for deferred cache
_memory_broker_stat_interval 30 memory broker statistics gathering interval
_memory_broker_shrink_heaps 15 memory broker allow policy to shrink shared pool
_memory_broker_shrink_java_heaps 900 memory broker allow policy to shrink java pool
_memory_broker_shrink_streams_pool 900 memory broker allow policy to shrink streams pool
_memory_broker_shrink_timeout 60000000 memory broker policy to timeout shrink shared/java
pool
_memory_broker_log_stat_entries 5 memory broker num stat entries
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_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 FALSE disable latch-free SCN writes using 64-bit compare
& swap
_controlfile_enqueue_timeout 900 control file enqueue timeout in seconds
_controlfile_update_check OFF controlfile update sanity check
_controlfile_enqueue_dump FALSE dump the system states after controlfile enqueue t
imeout
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_controlfile_block_size 0 control file block size in bytes
_controlfile_section_init_size control file initial section size
_controlfile_section_max_expand control file max expansion rate
_db_block_buffers 265856 Number of database blocks cached in memory: hidden
parameter
_db_block_cache_protect FALSE protect database blocks (true only when debugging)
_db_block_cache_protect_internal 0 protect database blocks (for strictly internal use
only)
_dbwr_tracing 0 Enable dbwriter tracing
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_disable_multiple_block_sizes FALSE disable multiple block size support (for debugging
)
_db_fast_obj_truncate TRUE enable fast object truncate
_db_fast_obj_ckpt TRUE enable fast object checkpoint
_db_obj_enable_ksr TRUE enable ksr in object checkpoint/reuse
_small_table_threshold 5317 threshold level of table size for direct reads
_db_block_cache_num_umap 0 number of unmapped buffers (for tracking swap call
s on blocks)
_dbwr_async_io TRUE Enable dbwriter asynchronous writes
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_recovery_percentage 50 recovery buffer cache percentage
_db_lost_write_checking FALSE Enable scn based lost write detection mechanism
__db_cache_size 2248146944 Actual size of DEFAULT buffer pool for standard bl
ock size buffers
_db_block_numa 1 Number of NUMA nodes
_db_block_lru_latches 32 number of lru latches
_db_percpu_create_cachesize 2 size of cache created per cpu in deferred cache cr
eate
_db_todefer_cache_create TRUE buffer cache deferred create
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_db_block_known_clean_pct 2 Initial Percentage of buffers to maintain known cl
ean
_db_block_max_scan_pct 40 Percentage of buffers to inspect when looking for
free
_db_large_dirty_queue 25 Number of buffers which force dirty queue to be wr
itten
_db_writer_max_writes 0 Max number of outstanding DB Writer IOs
_db_writer_chunk_writes 0 Number of writes DBWR should wait for
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_db_block_med_priority_batch_size 0 Fraction of writes for medium priority reasons
_db_block_hi_priority_batch_size 0 Fraction of writes for high priority reasons
_db_writer_histogram_statistics FALSE maintain dbwr histogram statistics in x$kcbbhs
_dbwr_scan_interval 300 dbwriter scan interval
_db_writer_flush_imu TRUE If FALSE, DBWR will not downgrade IMU txns for AGI
NG
_db_writer_coalesce_write_limit 131072 Limit on size of coalesced write
_db_writer_coalesce_area_size 1048576 Size of memory allocated to dbwriter for coalescin
g writes
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_selftune_checkpoint_write_pct 3 Percentage of total physical i/os for self-tune ck
pt
_db_writer_verify_writes FALSE Enable lost write detection mechanism
_db_block_prefetch_quota 10 Prefetch quota as a percent of cache size
_db_block_prefetch_limit 0 Prefetch limit in blocks
_db_cache_pre_warm TRUE Buffer Cache Pre-Warm Enabled : hidden parameter
_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_aging_hot_criteria 2 Touch count which sends a buffer to head of replac
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
ement list
_db_aging_stay_count 0 Touch count set when buffer moved to head of repla
cement list
_db_aging_cool_count 1 Touch count set when buffer cooled
_db_aging_touch_time 3 Touch count which sends a buffer to head of replac
ement list
_db_aging_freeze_cr FALSE Make CR buffers always be too cold to keep in cach
e
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_db_block_hash_buckets 1048576 Number of database block hash buckets
_db_block_hash_latches 4096 Number of database block hash latches
_db_handles 1500 System-wide simultaneous buffer operations
_db_handles_cached 5 Buffer handles cached each process
_wait_for_sync TRUE wait for sync on commit MUST BE ALWAYS TRUE
_db_block_cache_clone FALSE Always clone data blocks on get (for debugging)
_db_block_trace_protect FALSE trace buffer protect calls
_db_block_max_cr_dba 6 Maximum Allowed Number of CR buffers per dba
_trace_buffer_flushes FALSE trace buffer flushes if otrace cacheIO event is se
t
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_trace_multi_block_reads FALSE trace multi_block reads if otrace cacheIO event is
set
_trace_cr_buffer_creates FALSE trace cr buffer creates if otrace cacheIO event is
set
_write_clones 3 write clones flag
_trace_buffer_gets FALSE trace kcb buffer gets if otrace cacheIO event is s
et
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_check_block_after_checksum TRUE perform block check after checksum if both are tur
ned on
_trace_pin_time 0 trace how long a current pin is held
_db_fast_obj_check FALSE enable fast object drop sanity check
_db_block_temp_redo FALSE generate redo for temp blocks
_db_block_adjchk_level 1 adjacent cache buffer check level
_db_block_adjcheck TRUE adjacent cache buffer checks - low blkchk overwrit
e parameter
_db_block_check_objtyp TRUE check objd and typ on cache disk read
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_db_block_do_full_mbreads FALSE do full block read even if some blocks are in cach
e
_trace_buffer_wait_timeouts 0 trace buffer busy wait timeouts
_buffer_busy_wait_timeout 100 buffer busy wait time in centiseconds
_db_cache_crx_check FALSE check for costly crx examination functions
_db_hot_block_tracking FALSE track hot blocks for hash latch contention
_db_block_cache_history 0 buffer header tracing (non-zero only when debuggin
g)
_db_block_cache_history_level 2 buffer header tracing level
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_db_block_table_scan_buffer_size 4194304 Size of shared table scan read buffer
_db_obj_ckpt_ordered FALSE fast object checkpoint ordered by lrba
_db_cache_process_cr_pin_max maximum number of cr pins a process may have
_db_cache_advice_sample_factor 4 cache advisory sampling factor
_db_cache_advice_batch_size 128 cache advisory simulation batch size
_db_mttr_advice OFF MTTR advisory
_db_mttr_sim_target MTTR simulation targets
_db_mttr_sample_factor 64 MTTR simulation sampling factor
_db_mttr_partitions 0 number of partitions for MTTR advisory
_db_mttr_sim_trace_size 256 MTTR simulation trace size
_db_mttr_trace_to_alert FALSE dump trace entries to alert file
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_immediate_commit_propagation TRUE if TRUE, propagate commit SCN immediately
_minimum_giga_scn 0 Minimum SCN to start with in 2^30 units
_redo_compatibility_check FALSE general and redo/undo compatibility sanity check
_db_block_check_for_debug FALSE Check more and dump block before image for debuggi
ng
_db_always_check_system_ts TRUE Always perform block check and checksum for System
tablespace
_log_checkpoint_recovery_check 0 # redo blocks to verify after checkpoint
_two_pass TRUE enable two-pass thread recovery
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_recovery_verify_writes FALSE enable thread recovery write verify
_disable_recovery_read_skip FALSE Disable the read optimization during media recover
y
_log_archive_security_enabled TRUE log archive security enabled
_log_archive_avoid_memcpy TRUE log archive avoid memcpy
_log_archive_prot_auto_demote FALSE log archive protection auto demotion
_log_archive_compress_enable FALSE Bypass database rules for enabling archivelog comp
ression
_real_time_apply_arch_delay 0 Archival delay with real time apply
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_real_time_apply_sim 0 Simulation value with real time apply
_log_archive_buffers 10 Number of buffers to allocate for archiving
_log_archive_buffer_size 2048 Size of each archival buffer in log file blocks
_log_archive_callout archival callout
_log_archive_net_timeout 0 maximum network wait time in seconds when SYNC=PAR
ALLEL
_log_archive_delta_sync_wait 0 iterative sleep time in centiseconds seconds when
SYNC=PARALLEL
_max_rfs_connections 0 maximum number of connections used for remote arch
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
ival
_lgwr_max_ns_wt 1 Maximum wait time for lgwr to allow NetServer to p
rogress
_max_arch_lns_wait 120 Maximum wait by ARCH to allow NetServer to complet
e archival
_max_lns_shutdown_archival_time 30 Maximum time spent by LNS to archive last log duri
ng shutdown
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_ta_lns_wait_for_arch_log 20 LNS Wait time for arhcived version of online log
_ns_max_flush_wt 1 Flush wait time for NetServer to flush oustanding
writes
_ns_max_send_delay 15 Data Loss Time Bound for NetServer
_lgwr_ta_sim_err 0 Variable to simulate errors lgwr true async
_lgwr_ns_sim_err 0 Variable to simulate errors lgwrns
_lgwr_ns_nl_min 500 Variable to simulate network latency or buffer thr
eshold
_lgwr_ns_nl_max 1000 Variable to simulate network latency or buffer thr
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
eshold
_log_archive_network_redo_size 10 Log archive network redo buffer size used by ARCH
_lgwr_async_io FALSE LGWR Asynchronous IO enabling boolean flag
_lgwr_async_broadcasts TRUE LGWR Asynchronous Broadcasts enabling boolean flag
_lgwr_delay_write FALSE LGWR write delay for debugging
_log_io_size 0 automatically initiate log write if this many redo
blocks in buffer
_log_switch_timeout 0 Maximum number of seconds redos in the current log
could span
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_log_buffers_debug FALSE debug redo buffers (slows things down)
_log_buffers_corrupt FALSE corrupt redo buffers before write
_log_debug_multi_instance FALSE debug redo multi instance code
_log_simultaneous_copies 16 number of simultaneous copies into redo buffer(# o
f copy latches)
_log_parallelism 1 Number of log buffer strands
_log_parallelism_max 2 Maximum number of log buffer strands
_log_parallelism_dynamic TRUE Enable dynamic strands
_log_private_parallelism FALSE Number of private log buffer strands for zero-copy
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
redo
_log_private_parallelism_mul 10 Active sessions multiplier to deduce number of pri
vate strands
_log_private_mul 5 Private strand multiplier for log space preallocat
ion
_lightweight_hdrs TRUE Lightweight headers for redo
_mirror_redo_buffers FALSE Save buffers for debugging redo corruptions
_disable_logging FALSE Disable logging
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_db_file_exec_read_count 16 multiblock read count for regular clients
_db_file_optimizer_read_count 16 multiblock read count for regular clients
_db_file_noncontig_mblock_read_count 11 number of noncontiguous db blocks to be prefetched
_db_recovery_temporal_file_dest default database recovery temporal file location
_hard_protection FALSE if TRUE enable H.A.R.D specific format changes
_allow_error_simulation FALSE Allow error simulation for testing
_db_file_format_io_buffers 4 Block formatting I/O buf count
_gc_use_cr TRUE if TRUE, allow CR pins on PI and WRITING buffers
_gc_undo_affinity TRUE if TRUE, enable dynamic undo affinity
_gc_affinity_time 10 if non zero, enable dynamic object affinity
_gc_affinity_limit 50 dynamic affinity limit
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_gc_affinity_minimum 4800 dynamic affinity minimum activity per minute
_recovery_asserts FALSE if TRUE, enable expensive integrity checks
_gc_integrity_checks 1 set the integrity check level
_gc_vector_read TRUE if TRUE, vector read current buffers
_gc_element_percent 103 global cache element percent
_gc_latches 8 number of latches per LMS process
_gc_defer_time 3 how long to defer down converts for hot buffers
_kcl_debug TRUE if TRUE, record le history
_kcl_index_split TRUE if TRUE, reject pings on blocks in middle of a spl
it
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_kcl_commit TRUE if TRUE, call kjbcommit
_fairness_threshold 4 number of times to CR serve before downgrading loc
k
_interconnect_checksum TRUE if TRUE, checksum interconnect blocks
_cr_server_log_flush TRUE if TRUE, flush redo log before serving a CR buffer
_ping_level 3 fusion ping level
_async_recovery_reads TRUE if TRUE, issue recovery reads asynchronously
_async_recovery_claims TRUE if TRUE, issue recovery claims asynchronously
_avoid_prepare TRUE if TRUE, do not prepare a buffer when the master i
s local
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_bwr_for_flushed_pi TRUE if TRUE, generate a BWR for a flushed PI
_send_requests_to_pi TRUE if TRUE, try to send CR requests to PI buffers
_kcl_undo_locks 128 number of locks per undo segment
_kcl_undo_grouping 32 grouping for undo block locks
_kcl_conservative_log_flush FALSE if TRUE, conservatively log flush before CR servin
g
_gc_statistics TRUE if TRUE, kcl statistics are maintained
_gc_check_bscn TRUE if TRUE, check for stale blocks
_gc_global_lru AUTO turn global lru off, make it automatic, or turn it
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
on
_gc_dynamic_affinity_locks TRUE if TRUE, get dynamic affinity locks
_gc_undo_affinity_locks TRUE if TRUE, get affinity locks for undo
_gc_coalesce_recovery_reads TRUE if TRUE, coalesce recovery reads
_gc_keep_recovery_buffers TRUE if TRUE, make recovery buffers current
_gc_maximum_bids 0 maximum number of bids which can be prepared
_adaptive_direct_read TRUE Adaptive Direct Read
_db_block_align_direct_read TRUE Align Direct Reads
_disable_incremental_checkpoints FALSE Disable incremental checkpoints for thread recover
y
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_fast_start_instance_recovery_target 0 cluster availability target time in RAC environmen
t
_disable_selftune_checkpointing FALSE Disable self-tune checkpointing
_selftune_checkpointing_lag 300 Self-tune checkpointing lag the tail of the redo l
og
_target_rba_max_lag_percentage 90 target rba max log lag percentage
_no_recovery_through_resetlogs FALSE no recovery through this resetlogs operation
_log_blocks_during_backup TRUE log block images when changed during backup
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_allow_resetlogs_corruption FALSE allow resetlogs even if it will cause corruption
_allow_terminal_recovery_corruption FALSE Finish terminal recovery even if it may cause corr
uption
_allow_read_only_corruption FALSE allow read-only open even if database is corrupt
_serial_recovery FALSE force serial recovery or parallel recovery
_coord_message_buffer 0 parallel recovery coordinator side extra message b
uffer size
_multiple_instance_recovery FALSE use multiple instances for media recovery
_backup_kgc_bufsz 0 specifies buffer size to be used for kgc compressi
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
on
_backup_kgc_type 0 specifies compression type used for kgc compressio
n
_backup_kgc_niters 0 specifies number of iterations used for kgc compre
ssion
_backup_ksfq_bufsz 0 size of the ksfq buffer used for backup/restore
_backup_ksfq_bufcnt 0 number of the ksfq buffers used for backup/restore
_backup_max_gap_size 4294967294 largest gap in an incremental/optimized backup buf
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
fer, in bytes
_unused_block_compression FALSE enable unused block compression
_restore_spfile restore spfile to this location
_dummy_instance FALSE dummy instance started by RMAN
_rman_io_priority 3 priority at which rman backup i/o's are done
_tts_allow_charset_mismatch FALSE allow plugging in a tablespace with an incompatibl
e character set
_xtts_allow_pre10 FALSE allow cross platform for pre10 compatible tablespa
ce
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_xtts_set_platform_info FALSE set cross platform info during file header read
_disable_recoverable_recovery FALSE Disable the new recoverable recovery mechanism
_recoverable_recovery_batch_percent 50 Recoverable recovery batch size (percentage of buf
fer cache)
_disable_incremental_recovery_ckpt FALSE Disable incremental recovery checkpoint mechanism
_incremental_recovery_ckpt_min_batch 30 Minimum number of writes for incremental recovery
ckpt every 3 sec
_media_recovery_read_batch 128 media recovery block read batch
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_media_recovery_reap_time 3 media recovery reap time
_bct_public_dba_buffer_size 0 total size of all public change tracking dba buffe
rs, in bytes
_bct_initial_private_dba_buffer_size 0 initial number of entries in the private change tr
acking dba buffers
_bct_bitmaps_per_file 8 number of bitmaps to store for each datafile
_bct_file_block_size 0 block size of change tracking file, in bytes
_bct_file_extent_size 0 extent size of change tracking file, in bytes
_bct_chunk_size 0 change tracking datafile chunk size, in bytes
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_bct_crash_reserve_size 262144 change tracking reserved crash recovery SGA space,
in bytes
_bct_buffer_allocation_size 2097152 size of one change tracking buffer allocation, in
bytes
_bct_buffer_allocation_max 104857600 maximum size of all change tracking buffer allocat
ions, in bytes
_bct_buffer_allocation_min_extents 1 mininum number of extents to allocate per buffer a
llocation
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_bct_fixtab_file change tracking file for fixed tables
_block_change_tracking TRUE change tracking possible
_log_deletion_policy mandatory archivelog deletion policy for mandatory/all desti
nation
_validate_flashback_database FALSE Scan database to validate result of flashback data
base
_flashback_allow_noarchivelog FALSE Allow enabling flashback on noarchivelog database
_verify_flashback_redo TRUE Verify that the redo logs needed for flashback are
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
available
_flashback_verbose_info FALSE Print verbose information about flashback database
_flashback_logfile_enqueue_timeout 600 flashback logfile enqueue timeout for opens
_flashback_barrier_interval 1800 Flashback barrier interval in seconds
_flashback_standby_barrier_interval 1800 Flashback standby barrier interval in seconds
_flashback_fuzzy_barrier TRUE Use flashback fuzzy barrier
_flashback_log_size 1000 Flashback log size
_flashback_log_min_size 100 Minimum flashback log size
_flashback_copy_latches 10 Number of flashback copy latches
_flashback_n_log_per_thread 128 Desired number of flashback logs per flashback thr
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
ead
_flashback_max_n_log_per_thread 2048 Maximum number of flashback logs per flashback thr
ead
_flashback_generation_buffer_size 33554432 flashback generation buffer size
_flashback_max_log_size 0 Maximum flashback log size in bytes (OS limit)
_flashback_log_io_error_behavior 0 Specify Flashback log I/O error behavior
_flashback_hint_barrier_percent 20 Flashback hint barrier percent
_percent_flashback_buf_partial_full 50 Percent of flashback buffer filled to be considere
d partial full
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_flashback_write_size_qm 4 Desired flashback write size in quarter MB
_tdb_debug_mode 16 set debug mode for testing transportable database
_row_locking always row-locking
_serializable FALSE serializable
_log_committime_block_cleanout FALSE Log commit-time block cleanout
_ktc_latches 0 number of ktc latches
_prescomm FALSE presume commit of IMU transactions
_recursive_imu_transactions FALSE recursive transactions may be IMU
_ktc_debug 0 for ktc debug
_allocate_creation_order FALSE should files be examined in creation order during
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
allocation
_log_space_errors TRUE should we report space errors to alert log
_bump_highwater_mark_count 0 how many blocks should we allocate per free list o
n advancing HWM
_assm_default TRUE ASSM default
_enable_hwm_sync TRUE enable HWM synchronization
_hwm_sync_threshold 10 HWM synchronization threshold in percentage
_enable_tablespace_alerts TRUE enable tablespace alerts
_last_allocation_period 5 period over which an instance can retain an active
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
level1 bitmap
_inst_locking_period 5 period an instance can retain a newly acquired lev
el1 bitmap
_allocation_update_interval 3 interval at which successful search in L1 should b
e updated
_minimum_blocks_to_shrink 0 minimum number freeable blocks for shrink to be pr
esent
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_minimum_extents_to_shrink 1 minimum number freeable extents for shrink to be p
resent
_max_shrink_obj_stats 0 number of segments for which shrink stats will be
maintained
_check_ts_threshold 0 check tablespace thresholds
_ktu_latches 0 number of KTU latches
_rollback_segment_initial 1 starting undo segment number
_rollback_segment_count 0 number of undo segments
_offline_rollback_segments offline undo segment list
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_corrupted_rollback_segments corrupted undo segment list
_transaction_auditing TRUE transaction auditing records generated in the redo
log
_enable_block_level_transaction_recovery TRUE enable block level recovery
_cleanup_rollback_entries 100 no. of undo entries to apply per transaction clean
up
_smu_error_simulation_site 0 site ID of error simulation in KTU code
_smu_error_simulation_type 0 error type for error simulation in KTU code
_collect_undo_stats TRUE Collect Statistics v$undostat
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_smu_timeouts comma-separated *AND double-quoted* list of AUM ti
meouts: mql, tur, sess_exprn, qry_exprn, slot_intv
l
_smu_debug_mode 0 <debug-flag> - set debug event for testing SMU ope
rations
_undo_debug_mode 0 debug flag for undo related operations
_verify_undo_quota FALSE TRUE - verify consistency of undo quota statistics
_discrete_transactions_enabled FALSE enable OLTP mode
_in_memory_undo TRUE Make in memory undo for top level transactions
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_imu_pools 3 in memory undo pools
_row_cr TRUE enable row cr for all sql
_undo_autotune TRUE enable auto tuning of undo_retention
_rollback_stopat 0 stop at -position to step rollback
_smon_internal_errlimit 100 limit of SMON internal errors
_transaction_recovery_servers 0 max number of parallel recovery slaves that may be
used
_parallel_recovery_stopat 32767 stop at -position- to step through SMON
_disable_txn_alert 0 disable txn layer alert
_undo_debug_usage 0 invoke undo usage functions for testing
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_db_change_notification_enable TRUE enable db change notification
_db_row_overlap_checking TRUE row overlap checking override parameter for data/i
ndex blocks
_db_index_block_checking TRUE index block checking override parameter
_alternate_iot_leaf_block_split_points TRUE enable alternate index-organized table leaf-block
split-points
_release_insert_threshold 5 maximum number of unusable blocks to unlink from f
reelist
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_walk_insert_threshold 0 maximum number of unusable blocks to walk across f
reelist
_enable_hash_overflow FALSE TRUE - enable hash cluster overflow based on SIZE
_use_seq_process_cache TRUE whether to use process local seq cache
_reuse_index_loop 5 number of blocks being examine for index block reu
se
_kdbl_enable_post_allocation FALSE allocate dbas after populating data buffers
_ldr_io_size 262144 size of write IOs used during a load operation
_ignore_desc_in_index FALSE ignore DESC in indexes, sort those columns ascendi
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
ng anyhow
_keep_remote_column_size FALSE remote column size does not get modified
_row_cache_cursors 20 number of cached cursors for row cache management
_kgl_multi_instance_lock TRUE whether KGL to support multi-instance locks
_kgl_multi_instance_pin TRUE whether KGL to support multi-instance pins
_kgl_multi_instance_invalidation TRUE whether KGL to support multi-instance invalidation
s
_kgl_latch_count 0 number of library cache latches
_kgl_heap_size 1024 extent size for library cache heap 0
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_kgl_fixed_extents TRUE fixed extent size for library cache memory allocat
ions
_kgl_session_cached_objects 10 maximum length of the KGL object cache lru
_kgl_keep_cache_pct 30 KGL keep cache minimum threshold
_kgl_keep_cache_retain_pct 20 KGL keep cache retain threshold
_kgl_bucket_count 9 index to the bucket count array
_library_cache_advice TRUE whether KGL advice should be turned on
_kglsim_maxmem_percent 5 max percentage of shared pool size to be used for
KGL advice
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_kgl_hash_collision FALSE whether KGL hash collision is possible
_kgl_time_to_wait_for_locks 15 time to wait for locks and pins before timing out
_kgl_large_heap_warning_threshold 2097152 maximum heap size before KGL writes warnings to th
e alert log
_synonym_repoint_tracing FALSE whether to trace metadata comparisons for synonym
repointing
_cursor_cache_frame_bind_memory FALSE frame & bind buffer caching
_kgx_latches 512 # of mutex latches if CAS is not supported.
_kks_use_mutex_pin FALSE Turning on this will make KKS use mutex for cursor
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
pins.
_cursor_stats_bucket 15 Hash table buckets for cursor stats (2^).
_cursor_stats_heap 4 Heaps/latches for cursor stats.
_passwordfile_enqueue_timeout 900 password file enqueue timeout in seconds
_dynamic_rls_policies TRUE rls policies are dynamic
_app_ctx_vers FALSE enable app ctx versioning
_session_context_size 10000 session app context size
_default_encrypt_alg 0 default encryption algorithm
_ols_cleanup_task TRUE Clean up unnecessary entries in OLS sessinfo table
_distributed_recovery_connection_hold_ti 200 number of seconds RECO holds outbound connections
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
me open
__dg_broker_service_names orcl_XPT service names for broker use
_pmon_load_constants 300,192,64 server load balancing constants (S,P,D,I,L,C,M)
,3,10,10,0
,0
_dispatcher_rate_ttl time-to-live for rate statistic (100ths of a secon
d)
_dispatcher_rate_scale scale to display rate statistic (100ths of a secon
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
d)
_kernel_message_network_driver FALSE kernel message network driver
_dedicated_server_poll_count 10 dedicated server poll count
_dedicated_server_post_wait_call FALSE dedicated server post/wait call
_dedicated_server_post_wait FALSE dedicated server post/wait
_session_kept_cursor_pins 0 Number of cursors pins to keep in a session
_dump_qbc_tree 0 dump top level query parse tree to trace
_plsql_dump_buffer_events conditions upon which the PL/SQL circular buffer i
s dumped
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_plsql_anon_block_code_type INTERPRETE PL/SQL anonymous block code-type
D
_plsql_nvl_optimize FALSE PL/SQL NVL optimize
_job_queue_interval 5 Wakeup interval in seconds for job queue co-ordina
tor
_parallel_default_max_instances 1 default maximum number of instances for parallel q
uery
_system_trig_enabled TRUE are system triggers enabled
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_kkfi_trace FALSE trace expression substitution
_adjust_literal_replacement FALSE If TRUE, we will adjust the SQL/PLUS output
_kolfuseslf FALSE allow kolf to use slffopen
_session_cached_instantiations 60 Number of pl/sql instantiations to cache in a sess
ion.
_px_trace none px trace parameter
_xt_trace none external tables trace parameter
_xt_coverage none external tables code coverage parameter
_ku_trace none datapump trace parameter
_optimizer_trace none optimizer trace parameter
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_parallel_server_idle_time 30000 idle time before parallel query server dies (in 1/
100 sec)
_parallel_server_sleep_time 10 sleep time between dequeue timeouts (in 1/100ths)
_px_send_timeout 300 IPC message send timeout value in seconds
_dynamic_stats_threshold 30 delay threshold (in seconds) between sending stati
stics messages
_parallel_fake_class_pct 0 fake db-scheduler percent used for testing
_px_load_publish_interval 200 interval at which LMON will check whether to publi
sh PX load
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_parallel_execution_message_align FALSE Alignment of PX buffers to OS page boundary
_PX_use_large_pool FALSE Use Large Pool as source of PX buffers
_parallel_min_message_pool 0 minimum size of shared pool memory to reserve for
pq servers
_px_buffer_ttl 30 ttl for px mesg buffers in seconds
_tq_dump_period 0 time period for duping of TQ statistics (s)
_affinity_on TRUE enable/disable affinity at run time
_enable_default_affinity 0 to enable default implementation of affinity osds
_dss_cache_flush FALSE enable full cache flush for parallel execution
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_hash_join_enabled TRUE enable/disable hash join
_hash_multiblock_io_count 0 number of blocks hash join will read/write at once
_cursor_db_buffers_pinned 884 additional number of buffers a cursor can pin at o
nce
_old_connect_by_enabled FALSE enable/disable old connect by
_table_lookup_prefetch_size 40 table lookup prefetch vector size
_multi_join_key_table_lookup TRUE TRUE iff multi-join-key table lookup prefetch is e
nabled
_table_lookup_prefetch_thresh 2 table lookup prefetch threshold
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_adaptive_fetch_enabled TRUE enable/disable adaptive fetch in parallel group by
_query_execution_cache_max_size 65536 max size of query execution cache
_fast_dual_enabled TRUE enable/disable fast dual
_newsort_enabled TRUE controls whether new sorts can be used as system s
ort
_newsort_type 0 specifies options for the new sort algorithm
_newsort_ordered_pct 63 controls when new sort avoids sorting ordered inpu
t
_oradbg_pathname path of oradbg script
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_vendor_lib_loc Vendor library search root directory
_disable_sun_rsm TRUE Disable IPC OSD support for Sun RSMAPI
_ipc_test_failover 0 Test transparent cluster network failover
_ipc_test_mult_nets 0 simulate multiple cluster networks
_ipc_fail_network 0 Simulate cluster network failer
_no_objects FALSE no object features are used
_domain_index_batch_size 2000 maximum number of rows from one call to domain ind
ex fetch routine
_domain_index_dml_batch_size 200 maximum number of rows for one call to domain inde
x dml routines
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_nchar_imp_conv TRUE should implicit conversion bewteen clob and nclob
be allowed
_insert_enable_hwm_brokered TRUE during parallel inserts high water marks are broke
red
_all_shared_dblinks treat all dblinks as shared
_close_cached_open_cursors FALSE close cursors cached by PL/SQL at each commit
_disable_savepoint_reset FALSE disable the fix for bug 1402161
_init_sql_file ?/rdbms/ad File containing SQL statements to execute upon dat
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
min/sql.bs abase creation
q
_sort_multiblock_read_count 2 multi-block read count for sort
_shrunk_aggs_enabled TRUE enable use of variable sized buffers for non-disti
nct aggregates
_shrunk_aggs_disable_threshold 60 percentage of exceptions at which to switch to ful
l length aggs
_gby_onekey_enabled TRUE enable use of one comparison of all group by keys
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_sql_connect_capability_table SQL Connect Capability Table (testing only)
_optimizer_mode_force TRUE force setting of optimizer mode for user recursive
SQL also
_explain_rewrite_mode FALSE allow additional messages to be generated during e
xplain rewrite
_query_rewrite_or_error FALSE allow query rewrite, if referenced tables are not
dataless
_sort_elimination_cost_ratio 0 cost ratio for sort eimination under first_rows mo
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
de
_sql_connect_capability_override 0 SQL Connect Capability Table Override
_always_anti_join CHOOSE always use this method for anti-join when possible
_partition_view_enabled TRUE enable/disable partitioned views
_always_star_transformation FALSE always favor use of star transformation
_b_tree_bitmap_plans TRUE enable the use of bitmap plans for tables w. only
B-tree indexes
_column_elimination_off FALSE turn off predicate-only column elimination
_cpu_to_io 0 divisor for converting CPU cost to I/O cost
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_optimizer_extended_cursor_sharing UDO optimizer extended cursor sharing
_optimizer_cost_model CHOOSE optimizer cost model
_optimizer_undo_cost_change 10.2.0.1 optimizer undo cost change
_optimizer_system_stats_usage TRUE system statistics usage
_optimizer_cache_stats FALSE cost with cache statistics
_new_sort_cost_estimate TRUE enables the use of new cost estimate for sort
_complex_view_merging TRUE enable complex view merging
_simple_view_merging TRUE control simple view merging performed by the optim
izer
_unnest_subquery TRUE enables unnesting of correlated subqueries
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_eliminate_common_subexpr TRUE enables elimination of common sub-expressions
_pred_move_around TRUE enables predicate move-around
_convert_set_to_join FALSE enables conversion of set operator to join
_px_minus_intersect TRUE enables pq for minus/interect operators
_remove_aggr_subquery TRUE enables removal of subsumed aggregated subquery
_distinct_view_unnesting FALSE enables unnesting of in subquery into distinct vie
w
_optimizer_push_down_distinct 0 push down distinct from query block to table
_optimizer_cost_based_transformation LINEAR enables cost-based query transformation
_optimizer_squ_bottomup TRUE enables unnesting of subquery in a bottom-up manne
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
r
_optimizer_cbqt_factor 50 cost factor for cost-based query transformation
_push_join_predicate TRUE enable pushing join predicate inside a view
_push_join_union_view TRUE enable pushing join predicate inside a union all v
iew
_push_join_union_view2 TRUE enable pushing join predicate inside a union view
_fast_full_scan_enabled TRUE enable/disable index fast full scan
_optimizer_skip_scan_enabled TRUE enable/disable index skip scan
_optimizer_join_sel_sanity_check TRUE enable/disable sanity check for multi-column join
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
selectivity
_optim_enhance_nnull_detection TRUE TRUE to enable index [fast] full scan more often
_idl_conventional_index_maintenance TRUE enable conventional index maintenance for insert d
irect load
_enable_cscn_caching FALSE enable commit SCN caching for all transactions
_parallel_broadcast_enabled TRUE enable broadcasting of small inputs to hash and so
rt merge joins
_px_broadcast_fudge_factor 100 set the tq broadcasting fudge factor percentage
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_px_kxib_tracing 0 turn on kxib tracing
_px_granule_size 100000 default size of a rowid range granule (in KB)
_px_xtgranule_size 10000 default size of a external table granule (in KB)
_xtbuffer_size 0 buffer size in KB needed for populate/query operat
ion
_px_async_getgranule FALSE asynchronous get granule in the slave
_px_min_granules_per_slave 13 minimum number of rowid range granules to generate
per slave
_px_max_granules_per_slave 100 maximum number of rowid range granules to generate
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
per slave
_px_no_stealing FALSE prevent parallel granule stealing in shared nothin
g environment
_px_no_granule_sort FALSE prevent parallel partition granules to be sorted o
n size
_px_proc_constrain TRUE reduce parallel_max_servers if greater than (proce
sses - fudge)
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_parallel_adaptive_max_users 2 maximum number of users running with default DOP
_parallel_load_balancing TRUE parallel execution load balanced slave allocation
_parallel_load_bal_unit 0 number of threads to allocate per instance
_parallel_slave_acquisition_wait 1 time(in seconds) to wait before retrying slave acq
uisition
_pdml_slaves_diff_part TRUE slaves start on different partition when doing ind
ex maint
_pdml_gim_sampling 5000 control separation of global index maintenance for
PDML
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_pdml_gim_staggered FALSE slaves start on different index when doing index m
aint
_px_dynamic_opt TRUE turn off/on restartable qerpx dynamic optimization
_px_dynamic_sample_size 50 num of samples for restartable qerpx dynamic optim
ization
_px_rownum_pd TRUE turn off/on parallel rownum pushdown optimization
_predicate_elimination_enabled TRUE allow predicate elimination if set to TRUE
_groupby_nopushdown_cut_ratio 3 groupby nopushdown cut ratio
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_groupby_orderby_combine 5000 groupby/orderby don't combine threshold
_temp_tran_block_threshold 100 number of blocks for a dimension before we temp tr
ansform
_temp_tran_cache TRUE determines if temp table is created with cache opt
ion
_ordered_semijoin TRUE enable ordered semi-join subquery
_always_semi_join CHOOSE always use this method for semi-join when possible
_ordered_nested_loop TRUE enable ordered nested loop costing
_nested_loop_fudge 100 nested loop fudge
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_project_view_columns TRUE enable projecting out unreferenced columns of a vi
ew
_no_or_expansion FALSE OR expansion during optimization disabled
_optimizer_max_permutations 2000 optimizer maximum join permutations per query bloc
k
_system_index_caching 0 optimizer percent system index caching
_serial_direct_read FALSE enable direct read in serial
_disable_datalayer_sampling FALSE disable datalayer sampling
_disable_sample_io_optim FALSE disable row sampling IO optimization
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_sample_rows_per_block 4 number of rows per block used for sampling IO opti
mization
_ncmb_readahead_enabled 0 enable multi-block readahead for an index scan
_ncmb_readahead_tracing 0 turn on multi-block readahead tracing
_index_prefetch_factor 100 index prefetching factor
_query_cost_rewrite TRUE perform the cost based rewrite with materialized v
iews
_query_rewrite_2 TRUE perform query rewrite before&after or only after v
iew merging
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_query_rewrite_1 TRUE perform query rewrite before&after or only before
view merging
_query_rewrite_fudge 90 cost based query rewrite with MVs fudge factor
_query_rewrite_expression TRUE rewrite with cannonical form for expressions
_query_rewrite_jgmigrate TRUE mv rewrite with jg migration
_query_rewrite_fpc TRUE mv rewrite fresh partition containment
_query_rewrite_drj TRUE mv rewrite and drop redundant joins
_query_rewrite_maxdisjunct 257 query rewrite max disjuncts
_query_rewrite_vop_cleanup TRUE prune frocol chain before rewrite after view-mergi
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
ng
_mmv_query_rewrite_enabled TRUE allow rewrites with multiple MVs and/or base table
s
_bt_mmv_query_rewrite_enabled TRUE allow rewrites with multiple MVs and base tables
_add_stale_mv_to_dependency_list TRUE add stale mv to dependency list
_full_pwise_join_enabled TRUE enable full partition-wise join when TRUE
_partial_pwise_join_enabled TRUE enable partial partition-wise join when TRUE
_slave_mapping_enabled TRUE enable slave mapping when TRUE
_slave_mapping_group_size 0 force the number of slave group in a slave mapper
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_local_communication_costing_enabled TRUE enable local communication costing when TRUE
_local_communication_ratio 50 set the ratio between global and local communicati
on (0..100)
_parallelism_cost_fudge_factor 350 set the parallelism cost fudge factor
_left_nested_loops_random TRUE enable random distribution method for left of nest
edloops
_improved_row_length_enabled TRUE enable the improvements for computing the average
row length
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_px_index_sampling 200 parallel query sampling for index create (100000 =
100%)
_index_join_enabled TRUE enable the use of index joins
_use_nosegment_indexes FALSE use nosegment indexes in explain plan
_enable_type_dep_selectivity TRUE enable type dependent selectivity estimates
_sqlexec_progression_cost 1000 sql execution progression monitoring cost threshol
d
_improved_outerjoin_card TRUE improved outer-join cardinality calculation
_optimizer_adjust_for_nulls TRUE adjust selectivity for null values
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_optimizer_degree 0 force the optimizer to use the same degree of para
llelism
_optimizer_choose_permutation 0 force the optimizer to use the specified permutati
on
_use_column_stats_for_function TRUE enable the use of column statistics for DDP functi
ons
_subquery_pruning_cost_factor 20 subquery pruning cost factor
_subquery_pruning_reduction 50 subquery pruning reduction factor
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_subquery_pruning_enabled TRUE enable the use of subquery predicates to perform p
runing
_subquery_pruning_mv_enabled FALSE enable the use of subquery predicates with MVs to
perform pruning
_parallel_txn_global FALSE enable parallel_txn hint with updates and deletes
_or_expand_nvl_predicate TRUE enable OR expanded plan for NVL/DECODE predicate
_like_with_bind_as_equality FALSE treat LIKE predicate with bind as an equality pred
icate
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_table_scan_cost_plus_one TRUE bump estimated full table scan and index ffs cost
by one
_cost_equality_semi_join TRUE enables costing of equality semi-join
_default_non_equality_sel_check TRUE sanity check on default selectivity for like/range
predicate
_new_initial_join_orders TRUE enable initial join orders based on new ordering h
euristics
_oneside_colstat_for_equijoins TRUE sanity check on default selectivity for like/range
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
predicate
_column_tracking_level 1 column usage tracking
_optim_peek_user_binds TRUE enable peeking of user binds
_mv_refresh_selections TRUE create materialized views with selections and fast
refresh
_cursor_plan_enabled TRUE enable collection and display of cursor plans
_minimal_stats_aggregation TRUE prohibit stats aggregation at compile/partition ma
intenance time
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_mv_refresh_eut TRUE refresh materialized views using EUT(partition)-ba
sed algorithm
_mav_refresh_consistent_read TRUE refresh materialized views using consistent read s
napshot
_mav_refresh_opt 0 optimizations during refresh of materialized views
_mav_refresh_unionall_tables 3 # tables for union all expansion during materializ
ed view refresh
_mv_refresh_delta_fraction 10 delta mv as fractional percentage of size of mv
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_force_temptables_for_gsets FALSE executes concatenation of rollups using temp table
s
_pga_max_size 209715200 Maximum size of the PGA memory for one process
_smm_auto_min_io_size 56 Minimum IO size (in KB) used by sort/hash-join in
auto mode
_smm_auto_max_io_size 248 Maximum IO size (in KB) used by sort/hash-join in
auto mode
_smm_auto_cost_enabled TRUE if TRUE, use the AUTO size policy cost functions
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_smm_control 0 provides controls on the memory manager
_smm_trace 0 Turn on/off tracing for SQL memory manager
_smm_min_size 828 minimum work area size in auto mode
_smm_max_size 102400 maximum work area size in auto mode (serial)
_smm_px_max_size 414208 maximum work area size in auto mode (global)
_smm_retain_size 0 work area retain size in SGA for shared server ses
sions (0 for AUTO)
_smm_bound 0 overwrites memory manager automatically computed b
ound
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_smm_advice_log_size 0 overwrites default size of the PGA advice workarea
history log
_smm_advice_enabled TRUE if TRUE, enable v$pga_advice
_gs_anti_semi_join_allowed TRUE enable anti/semi join for the GS query
_mv_refresh_use_stats TRUE pass cardinality hints to refresh queries
_optim_new_default_join_sel TRUE improves the way default equijoin selectivity are
computed
_optimizer_dyn_smp_blks 32 number of blocks for optimizer dynamic sampling
_pre_rewrite_push_pred TRUE push predicates into views before rewrite
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_optimizer_new_join_card_computation TRUE compute join cardinality using non-rounded input v
alues
_mav_refresh_double_count_prevented FALSE materialized view MAV refreshes avoid double count
ing
_pct_refresh_double_count_prevented TRUE materialized view PCT refreshes avoid double count
ing
_mv_refresh_new_setup_disabled FALSE materialized view MV refresh new setup disabling
_load_without_compile none Load PL/SQL or Database objects without compilatio
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
n
_precompute_gid_values TRUE precompute gid values and copy them before returni
ng a row
_union_rewrite_for_gs YES_GSET_M expand queries with GSets into UNIONs for rewrite
VS
_nested_mav_fast_oncommit_enabled TRUE nested MAV refresh fast on commit allowed
_generalized_pruning_enabled TRUE controls extensions to partition pruning for gener
al predicates
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_rowsource_execution_statistics FALSE if TRUE, Oracle will collect rowsource statistics
_rowsource_statistics_sampfreq 128 frequency of rowsource statistic sampling (must be
a power of 2)
_bitmap_or_improvement_enabled TRUE controls extensions to partition pruning for gener
al predicates
_intrapart_pdml_enabled TRUE Enable intra-partition updates/deletes
_ctas_hwm_brokered_loads TRUE Enable HWM brokered CTAS
_intrapart_pdml_randomlocal_enabled TRUE Enable intra-partition updates/deletes with random
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
local dist
_optim_adjust_for_part_skews TRUE adjust stats for skews across partitions
_optimizer_compute_index_stats TRUE force index stats collection on index creation/reb
uild
_optimizer_autostats_job TRUE enable/disable auto stats collection job
_optimizer_push_pred_cost_based TRUE use cost-based query transformation for push pred
optimization
_optimizer_filter_pred_pullup TRUE use cost-based flter predicate pull up transformat
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
ion
_force_datefold_trunc FALSE force use of trunc for datefolding rewrite
_array_update_vector_read_enabled FALSE Enable array update vector read
_two_pass_reverse_polish_enabled TRUE uses two-pass reverse polish alg. to generate cano
nical forms
_expand_aggregates TRUE expand aggregates
_dump_common_subexpressions FALSE dump common subexpressions
_spr_use_hash_table FALSE use hash table for spreadsheet
_spr_use_AW_AS TRUE enable AW for hash table in spreadsheet
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_dump_connect_by_loop_data FALSE dump connect by loop error message into trc file
_column_compression_factor 0 Column compression ratio
_dml_monitoring_enabled TRUE enable modification monitoring
_px_slaves_share_cursors 0 slaves share cursors with QC
_fic_algorithm_set automatic Set Frequent Itemset Counting Algorithm
_fic_area_size 131072 size of Frequent Itemset Counting work area
_fic_max_length 20 Frequent Itemset Counting Maximum Itemset Length
_fic_min_bmsize 1024 Frequent Itemset Counting Minimum BITMAP Size
_dtree_area_size 131072 size of Decision Tree Classification work area
_dtree_pruning_enabled TRUE Decision Tree Pruning Enabled
_dtree_compressbmp_enabled TRUE Decision Tree Using Compressed Bitmaps Enabled
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_dtree_binning_enabled TRUE Decision Tree Binning Enabled
_dtree_max_surrogates 1 maximum number of surrogates
_dtree_bintest_id 0 Decision Tree Binning Test ID
_right_outer_hash_enable TRUE Right Outer/Semi/Anti Hash Enabled
_optimizer_mjc_enabled TRUE enable merge join cartesian
_optimizer_sortmerge_join_enabled TRUE enable/disable sort-merge join method
_cursor_bind_capture_area_size 400 maximum size of the cursor bind capture area
_cursor_bind_capture_interval 900 interval (in seconds) between two bind capture for
a cursor
_dump_cursor_heap_sizes FALSE dump comp/exec heap sizes to tryace file
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_projection_pushdown TRUE projection pushdown
_projection_pushdown_debug 0 level for projection pushdown debugging
_px_compilation_debug 0 debug level for parallel compilation
_px_compilation_trace 0 tracing level for parallel compilation
_fic_outofmem_candidates FALSE Frequent Itemset Counting Out Of Memory Candidates
Generation
_print_refresh_schedule false enable dbms_output of materialized view refresh sc
hedule
_optimizer_undo_changes FALSE undo changes to query optimizer
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_optimizer_percent_parallel 101 optimizer percent parallel
_optimizer_search_limit 5 optimizer search limit
_mv_refresh_costing rule refresh decision based on cost or on rules
_mv_refresh_ana 0 what percent to analyze after complete/PCT refresh
_cache_stats_monitor FALSE if TRUE, enable cache stats monitoring
_stn_trace 0 SQL tracing parameter
_sta_control 0 SQL Tuning Advisory control parameter
_ds_iocount_iosize 6553664 Dynamic Sampling Service defaults: #IOs and IO Siz
e
_mv_refsched_timeincr 300000 proportionality constant for dop vs. time in MV re
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
fresh
_spr_push_pred_refspr TRUE push predicates through reference spreadsheet
_optimizer_block_size 8192 standard block size used by optimizer
_spr_max_rules 10000 maximum number of rules in sql spreadsheet
_idxrb_rowincr 100000000 proportionality constant for dop vs. rows in index
rebuild
_hj_bit_filter_threshold 50 hash-join bit filtering threshold (0 always enable
d)
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_optimizer_save_stats TRUE enable/disable saving old versions of optimizer st
ats
_optimizer_cost_filter_pred FALSE enables costing of filter predicates in IO cost m
odel
_optimizer_correct_sq_selectivity TRUE force correct computation of subquery selectivity
_allow_commutativity TRUE allow for commutativity of +, * when comparing exp
ressions
_mv_refresh_rebuild_percentage 10 minimum percentage change required in MV to force
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
an index rebuild
_qa_control 0 Oracle internal parameter to control QA
_qa_lrg_type 0 Oracle internal parameter to specify QA lrg type
_mv_refresh_force_parallel_query 0 force materialized view refreshes to use parallel
query
_enable_fast_ref_after_mv_tbs FALSE enable fast refresh after move tablespace
_optim_dict_stats_at_db_cr_upg TRUE enable/disable dictionary stats gathering at db cr
eate/upgrade
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_optimizer_dim_subq_join_sel TRUE use join selectivity in choosing star transformati
on dimensions
_optimizer_disable_strans_sanity_checks 0 disable star transformation sanity checks
_allow_level_without_connect_by FALSE allow level without connect by
_optimizer_ignore_hints FALSE enables the embedded hints to be ignored
_optimizer_random_plan 0 optimizer seed value for random plans
_optimizer_ceil_cost TRUE CEIL cost in CBO
_delay_index_maintain TRUE delays index maintenance until after MV is refresh
ed
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_query_rewrite_setopgrw_enable TRUE perform general rewrite using set operator summari
es
_cursor_plan_hash_version 1 version of cursor plan hash value
_disable_function_based_index FALSE disable function-based index matching
_optimizer_invalidation_period 18000 time window for invalidation of cursors of analyze
d objects
_px_net_msg_cost 10000 CPU cost to send a PX message over the internconne
ct
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_px_loc_msg_cost 1000 CPU cost to send a PX message via shared memory
_smm_freeable_retain 5120 value in KB of the instance freeable PGA memory to
retain
_cursor_plan_unparse_enabled TRUE enables/disables using unparse to build projection
/predicates
_kill_java_threads_on_eoc FALSE Kill Java threads and do sessionspace migration at
end of call
_optimizer_join_order_control 3 controls the optimizer join order search algorithm
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_px_nss_planb TRUE enables or disables NSS Plan B reparse with outlin
e
_bloom_filter_enabled TRUE enables or disables bloom filter
_bloom_filter_debug 0 debug level for bloom filtering
_enable_refresh_schedule TRUE enable or disable MV refresh scheduling (revert to
9.2 behavior)
_optimizer_cartesian_enabled TRUE optimizer cartesian join enabled
_optimizer_starplan_enabled TRUE optimizer star plan enabled
_optimizer_join_elimination_enabled TRUE optimizer join elimination enabled
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_gby_hash_aggregation_enabled TRUE enable group-by and aggregation using hash scheme
_extended_pruning_enabled TRUE do runtime pruning in iterator if set to TRUE
_sql_model_unfold_forloops RUN_TIME specifies compile-time unfolding of sql model forl
oops
_enable_dml_lock_escalation TRUE enable dml lock escalation against partitioned tab
les if TRUE
_plsql_cache_enable TRUE PL/SQL Function Cache Enabled
_disable_fast_aggregation FALSE fast aggregation
_disable_adaptive_shrunk_aggregation FALSE adaptive shrunk aggregation
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_plsql_minimum_cache_hit_percent 20 plsql minimum cache hit percentage required to kee
p caching active
_plan_outline_data TRUE explain plan outline data enabled
_outline_bitmap_tree TRUE BITMAP_TREE hint enabled in outline
_drop_table_optimization_enabled TRUE reduce SGA memory use during drop of a partitioned
table
_very_large_partitioned_table 1024 very_large_partitioned_table
_drop_table_granule 256 drop_table_granule
_xpl_trace 0 Explain Plan tracing parameter
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_dm_max_shared_pool_pct 1 max percentage of the shared pool to use for a min
ing model
_optimizer_multiple_cenv generate and run plans using several compilation e
nvironments
_kql_subheap_trace 0 tracing level for library cache subheap level pins
_optimizer_cost_hjsmj_multimatch TRUE add cost of generating result set when #rows per k
ey > 1
_optimizer_transitivity_retain TRUE retain equi-join pred upon transitive equality pre
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
d generation
_px_pwg_enabled TRUE parallel partition wise group by enabled
_sql_hash_debug 0 Hash value of the SQL statement to debug
_mv_rolling_inv FALSE create/alter mv uses rolling cursor invalidation i
nstead of immediate
_smm_isort_cap 102400 maximum work area for insertion sort(v1)
_optimizer_cbqt_no_size_restriction TRUE disable cost based transformation query size restr
iction
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_windowfunc_optimization_settings 0 settings for window function optimizations
_truncate_optimization_enabled TRUE do truncate optimization if set to TRUE
_optimizer_enhanced_filter_push TRUE push filters before trying cost-based query transf
ormation
_compilation_call_heap_extent_size 16384 Size of the compilation call heaps extents
_xpl_peeked_binds_log_size 8192 maximum bytes for logging peeked bind values for V
$SQL_PLAN (0 = OFF)
_rowsrc_trace_level 0 Row source tree tracing level
_optimizer_rownum_pred_based_fkr TRUE enable the use of first K rows due to rownum predi
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
cate
_optimizer_better_inlist_costing ALL enable improved costing of index access using in-l
ist(s)
_optimizer_self_induced_cache_cost FALSE account for self-induced caching
_optimizer_min_cache_blocks 10 set minimum cached blocks
_enable_exchange_validation_using_check TRUE use check constraints on the table for validation
_optimizer_or_expansion DEPTH control or expansion approach used
_optimizer_outer_to_anti_enabled TRUE Enable transformation of outer-join to anti-join i
f possible
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_optimizer_order_by_elimination_enabled TRUE Eliminates order bys from views before query trans
formation
_optimizer_star_tran_in_with_clause TRUE enable/disable star transformation in with clause
queries
_selfjoin_mv_duplicates TRUE control rewrite self-join algorithm
_dimension_skip_null TRUE control dimension skip when null feature
_force_rewrite_enable FALSE control new query rewrite features
_optimizer_complex_pred_selectivity TRUE enable selectivity estimation for builtin function
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
s
_build_deferred_mv_skipping_mvlog_update FALSE DEFERRED MV creation skipping MV log setup update
_tsm_connect_string TSM test connect string
_tsm_disable_auto_cleanup 1 Disable TSM auto cleanup actions
_enable_nativenet_tcpip FALSE Enable skgxp driver usage for native net
_aq_tm_scanlimit 0 scan limit for Time Managers to clean up IOT
_bufq_stop_flow_control FALSE Stop enforcing flow control for buffered queues
_bufq_flow_control_thrsh 0 flow control threshold value
_bufq_max_msg_sz 0 maximum size of the message allowed in memory
_olapi_history_retention FALSE enable olapi history retention
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_olapi_session_history 300 enable olapi session history collection
_olapi_session_history_retention FALSE enable olapi session history retention
_olapi_iface_object_history 1000 enable olapi interface object history collection
_olapi_iface_object_history_retention FALSE enable olapi interface object history retention
_olapi_interface_operation_history 1000 enable olapi interface operation history collectio
n
_olapi_iface_operation_history_retention FALSE enable olapi interface operation history retention
_olapi_memory_operation_history 1000 enable olapi memory alloc/free history collection
_olapi_memory_operation_history_retentio FALSE enable olapi memory operation history retention
n
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_olapi_memory_operation_history_pause_at 0 enable olapi memory alloc/free history collection
_seqno pausing
_olap_continuous_trace_file FALSE Specify TRUE to enable continuous OLAP tracing - o
therwise only exceptional events will be logged
_olap_table_function_statistics FALSE Specify TRUE to output OLAP table function timed s
tatistics trace
_olap_parallel_update_threshold 1000 OLAP parallel update threshold in pages
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_olap_parallel_update_small_threshold 1000 OLAP parallel update threshold for number of small
pagespaces
_olap_parallel_update_small_group 400 OLAP parallel update pagespace by group
_olap_aggregate_buffer_size 1048576 OLAP Aggregate max buffer size
_olap_aggregate_min_buffer_size 1024 OLAP Aggregate min buffer size
_olap_aggregate_work_per_thread 1024 OLAP Aggregate max work parents
_olap_aggregate_min_thread_status 64 OLAP Aggregate minimum cardinality of dimensions f
or thread
_olap_aggregate_statlen_thresh 1024 OLAP Aggregate status array usage threshold
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_olap_aggregate_worklist_max 5000 OLAP Aggregate max worklists generated at once
_olap_aggregate_max_thread_tuples 5000 OLAP Aggregate max thread tuples creation
_olap_aggregate_store_probability 100 OLAP Aggregate function storeback probability
_olap_aggregate_function_merge_threshold 529 OLAP Aggregate function merge threshold
_olap_aggregate_function_cache_enabled TRUE OLAP Aggregate function cache enabler
_olap_aggregate_multipath_hier FALSE OLAP Aggregate Multi-path Hierarhies enabled
_olap_aggregate_flags 0 OLAP Aggregate debug flags
_olap_allocate_errorlog_header Dim S OLAP Allocate Errorlog Header format
ource Ba
sis
%-8d %-8s
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
%-8b Descr
iption
-------- -
------- --
------ ---
--------
_olap_allocate_errorlog_format %8p %8y %8 OLAP Allocate Errorlog Format
z %e (%n)
_olap_poutlog_echo_to_eventlog FALSE OLAP POutLog copy output to event log (tracefile)
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_olap_dbgoutfile_echo_to_eventlog FALSE OLAP DbgOutfile copy output to event log (tracefil
e)
_olap_eif_export_lob_size 2147483647 OLAP EIF Export BLOB size
_olap_sort_buffer_size 262144 OLAP Sort Buffer Size
_olap_sesscache_enabled TRUE OLAP Session Cache knob
_olap_object_hash_class 2 OLAP Object Hash Table Class
_olap_dimension_corehash_max 10000 OLAP Dimension In-Core Hash Table Maximum Size
_olap_dimension_corehash_class 1 OLAP Dimension In-Core Hash Table Class
_olap_page_pool_low 262144 OLAP Page Pool Low Watermark
_olap_page_pool_hi 50 OLAP Page Pool High Watermark
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_olap_page_pool_expand_rate 20 OLAP Page Pool Expand Rate
_olap_page_pool_shrink_rate 50 OLAP Page Pool Shrink Rate
_olap_page_pool_hit_target 100 OLAP Page Pool Hit Target
_olap_page_pool_pressure 90 OLAP Page Pool Pressure Threshold
_olap_statbool_threshold 8100 OLAP Status Boolean CBM threshold
_olap_statbool_corebits 20000000 OLAP Status Boolean max incore bits
_olap_lmgen_dim_size 100 Limitmap generator dimension column size
_olap_lmgen_meas_size 1000 Limitmap generator measure column size
_olap_wrap_errors FALSE Wrap error messages to OLAP outfile
_xsolapi_fetch_type PARTIAL OLAP API fetch type
_xsolapi_dimension_group_creation OVERFETCH OLAP API symmetric overfetch
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_xsolapi_sql_auto_measure_hints TRUE OLAP API enable automatic measure hints
_xsolapi_sql_auto_dimension_hints FALSE OLAP API enable automatic dimension hints
_xsolapi_sql_hints OLAP API generic hints
_xsolapi_sql_measure_hints OLAP API measure hints
_xsolapi_sql_dimension_hints OLAP API dimension hints
_xsolapi_sql_top_measure_hints OLAP API top measure hints
_xsolapi_sql_top_dimension_hints OLAP API top dimension hints
_xsolapi_sql_all_non_base_hints OLAP API non-base hints
_xsolapi_sql_all_multi_join_non_base_hin OLAP API multi-join non-base hints
ts
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_xsolapi_densify_cubes TABULAR OLAP API cube densification
_xsolapi_sql_optimize TRUE OLAP API enable optimization
_xsolapi_sql_remove_columns TRUE OLAP API enable remove unused columns optimization
s
_xsolapi_sql_symmetric_predicate TRUE OLAP API enable symmetric predicate for dimension
groups
_xsolapi_sql_use_bind_variables TRUE OLAP API enable bind variables optimization
_xsolapi_sql_prepare_stmt_cache_size 16 OLAP API prepare statement cache size
_xsolapi_sql_result_set_cache_size 32 OLAP API result set cache size
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_xsolapi_debug_output OLAP API debug output disposition
_xsolapi_materialize_sources TRUE OLAP API Enable source materialization
_xsolapi_load_at_process_start NEVER When to load OLAP API library at server process st
art
_xsolapi_auto_materialization_type PRED_AND_R OLAP API behavior for auto materialization
C
_xsolapi_auto_materialization_bound 20 OLAP API lower bound for auto materialization.
_xsolapi_materialization_rowcache_min_ro 1 OLAP API min number of rows required to use rowcac
ws_for_use he in query materialization
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_xsolapi_source_trace FALSE OLAP API output Source definitions to trace file
_asm_disk_repair_time 14400 seconds to wait before dropping a failing disk
_kffmap_hash_size 1024 size of kffmap_hash table
_kffmop_hash_size 2048 size of kffmop_hash table
_asm_ausize 1048576 allocation unit size
_asm_blksize 4096 metadata block size
_asm_acd_chunks 1 initial ACD chunks created
_asm_libraries ufs library search order for discovery
_asm_maxio 1048576 Maximum size of individual I/O request
_asm_allow_only_raw_disks TRUE Discovery only raw devices
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_asmlib_test 0 Osmlib test event
_asm_allow_resilver_corruption FALSE Enable disk resilvering for external redundancy
_asmsid asm ASM instance id
_asm_wait_time 18 Max/imum time to wait before asmb exits
_asm_stripewidth 8 ASM file stripe width
_asm_stripesize 131072 ASM file stripe size
_asm_droptimeout 60 timeout before offlined disks get dropped (in 3s t
icks)
_asm_emulmax 10000 max number of concurrent disks to emulate I/O erro
rs
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_asm_emultimeout 0 timeout before emulation begins (in 3s ticks)
_asm_kfdpevent 0 KFDP event
_alert_expiration 604800 seconds before an alert message is moved to except
ion queue
_alert_message_cleanup 1 Enable Alert Message Cleanup
_alert_message_purge 1 Enable Alert Message Purge
_alert_post_background 1 Enable Background Alert Posting
_swrf_test_action 0 test action parameter for SWRF
_sysaux_test_param 1 test parameter for SYSAUX
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_swrf_mmon_flush TRUE Enable/disable SWRF MMON FLushing
_awr_corrupt_mode FALSE AWR Corrupt Mode
_awr_restrict_mode FALSE AWR Restrict Mode
_swrf_mmon_metrics TRUE Enable/disable SWRF MMON Metrics Collection
_swrf_metric_frequent_mode FALSE Enable/disable SWRF Metric Frequent Mode Collectio
n
_awr_flush_threshold_metrics FALSE Enable/Disable Flushing AWR Workload Metrics
_awr_flush_workload_metrics FALSE Enable/Disable Flushing AWR Workload Metrics
_awr_disabled_flush_tables Disable flushing of specified AWR tables
_swrf_on_disk_enabled TRUE Parameter to enable/disable SWRF
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_swrf_mmon_dbfus TRUE Enable/disable SWRF MMON DB Feature Usage
_awr_mmon_cpuusage TRUE Enable/disable AWR MMON CPU Usage Tracking
_swrf_test_dbfus FALSE Enable/disable DB Feature Usage Testing
_mwin_schedule TRUE Enable/disable Maintenance Window Schedules
_awr_sql_child_limit 200 Setting for AWR SQL Child Limit
_sqltune_category_parsed DEFAULT Parsed category qualifier for applying hintsets
_ash_sampling_interval 1000 Time interval between two successive Active Sessio
n samples in millisecs
_ash_size 1048618 To set the size of the in-memory Active Session Hi
story buffers
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_ash_enable TRUE To enable or disable Active Session sampling and f
lushing
_ash_disk_write_enable TRUE To enable or disable Active Session History flushi
ng
_ash_disk_filter_ratio 10 Ratio of the number of in-memory samples to the nu
mber of samples actually written to disk
_ash_eflush_trigger 66 The percentage above which if the in-memory ASH is
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
full the emergency flusher will be triggered
_ash_sample_all FALSE To enable or disable sampling every connected sess
ion including ones waiting for idle waits
_ash_dummy_test_param 0 Oracle internal dummy ASH parameter used ONLY for
testing!
_threshold_alerts_enable 1 if 1, issue threshold-based alerts
_addm_auto_enable TRUE governs whether ADDM gets run automatically after
every AWR snapshot
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_addm_version_check TRUE governs whether ADDM checks the input AWR snapshot
version
_addm_skiprules comma-separated list of ADDM nodes to skip
1127 rows selected.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
COL NAME FORMAT A40
COL VALUE FORMAT A10
COL DESCRIPTION FORMAT A50
SET LINESIZE 150
select
x.ksppinm as Name
,y.ksppstvl as Value
,x.ksppdesc as Description
from
x$ksppi x, x$ksppcv y
where x.indx = y.indx
and substr(x.ksppinm,1,1) = '_'
;
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_trace_files_public FALSE Create publicly accessible trace files
_hang_analysis_num_call_stacks 3 hang analysis num call stacks
_ior_serialize_fault 0 inject fault in the ior serialize code
_inject_startup_fault 0 inject fault in the startup code
_latch_recovery_alignment 998 align latch recovery structures
_spin_count 2000 Amount to spin waiting for a latch
_latch_miss_stat_sid 0 Sid of process for which to collect latch stats
_max_sleep_holding_latch 4 max time to sleep while holding a latch
_max_exponential_sleep 0 max sleep during exponential backoff
_other_wait_threshold 0 threshold wait percentage for event wait class Oth
er
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_other_wait_event_exclusion exclude event names from _other_wait_threshold cal
culations
_use_vector_post TRUE use vector post
_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
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_latch_class_6 latch class 6
_latch_class_7 latch class 7
_latch_classes latch classes override
_ultrafast_latch_statistics TRUE maintain fast-path statistics for ultrafast latche
s
_enable_reliable_latch_waits TRUE Enable reliable latch waits
_session_idle_bit_latches 0 one latch per session or a latch per group of sess
ions
_ksu_diag_kill_time 5 number of seconds ksuitm waits before killing diag
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_ksuitm_dont_kill_dumper FALSE delay inst. termination to allow processes to dump
_disable_image_check FALSE Disable Oracle executable image checking
_num_longop_child_latches 8 number of child latches for long op array
_longops_enabled TRUE longops stats enabled
_test_ksusigskip 5 test the function ksusigskip
_os_sched_high_priority 1 OS high priority level
_disable_kcbhxor_osd FALSE disable kcbh(c)xor OSD functionality
_disable_system_state 4294967294 disable system state dump
_session_wait_history 10 enable session wait history collection
_session_idle_check_interval 60 Resource Manager session idle limit check interval
in seconds
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_pkt_enable FALSE enable progressive kill test
_pkt_start FALSE start progressive kill test instrumention
_pkt_pmon_interval 50 PMON process clean-up interval (cs)
_dead_process_scan_interval 10 PMON dead process scan interval
_collapse_wait_history FALSE collapse wait history
_short_stack_timeout_ms 30000 short stack timeout in ms
_single_process FALSE run without detached processes
_dbg_proc_startup FALSE debug process startup
_static_backgrounds static backgrounds
_number_cached_attributes 10 maximum number of cached attributes per instance
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_oradebug_force FALSE force target processes to execute oradebug command
s?
_ksdxdocmd_default_timeout_ms 30000 default timeout for internal oradebug commands
_watchpoint_on FALSE is the watchpointing feature turned on?
_ksdxw_num_sgw 10 number of watchpoints to be shared by all processe
s
_ksdxw_num_pgw 10 number of watchpoints on a per-process basis
_ksdxw_stack_depth 4 number of PCs to collect in the stack when watchpo
int is hit
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_ksdxw_cini_flg 0 ksdxw context initialization flag
_ksdxw_nbufs 1000 ksdxw number of buffers in buffered mode
_enable_kgh_policy FALSE temporary to disable/enable kgh policy
_NUMA_pool_size Not specif aggregate size in bytes of NUMA pool
ied
_enable_NUMA_optimization TRUE Enable NUMA specific optimizations
_use_ism TRUE Enable Shared Page Tables - ISM
_lock_sga_areas 0 Lock specified areas of the SGA in physical memory
_NUMA_instance_mapping Not specif Set of nodes that this instance should run on
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
ied
_simulator_upper_bound_multiple 2 upper bound multiple of pool size
_simulator_pin_inval_maxcnt 16 maximum count of invalid chunks on pin list
_simulator_lru_rebalance_thresh 10240 LRU list rebalance threshold (count)
_simulator_lru_rebalance_sizthr 2 LRU list rebalance threshold (size)
_simulator_bucket_mindelta 8192 LRU bucket minimum delta
_simulator_lru_scan_count 8 LRU scan count
_simulator_internal_bound 10 simulator internal bound percent
_simulator_reserved_obj_count 1024 simulator reserved object count
_simulator_reserved_heap_count 4096 simulator reserved heap count
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_simulator_sampling_factor 2 sampling factor for the simulator
_realfree_heap_max_size 32768 minimum max total heap size, in Kbytes
_realfree_heap_pagesize_hint 65536 hint for real-free page size in bytes
_realfree_heap_mode 0 mode flags for real-free heap
_use_realfree_heap TRUE use real-free based allocator for PGA memory
_pga_large_extent_size 1048576 PGA large extent size
_uga_cga_large_extent_size 262144 UGA/CGA large extent size
_total_large_extent_memory 0 Total memory for allocating large extents
_use_ism_for_pga TRUE Use ISM for allocating large extents
_private_memory_address Start address of large extent memory segment
_mem_annotation_sh_lev 0 shared memory annotation collection level
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_mem_annotation_pr_lev 0 private memory annotation collection level
_mem_annotation_scale 1 memory annotation pre-allocation scaling
_mem_annotation_store FALSE memory annotation in-memory store
_4031_dump_bitvec 67194879 bitvec to specify dumps prior to 4031 error
_4031_max_dumps 100 Maximum number of 4031 dumps for this process
_4031_dump_interval 300 Dump 4031 error once for each n-second interval
_4031_sga_dump_interval 3600 Dump 4031 SGA heapdump error once for each n-secon
d interval
_4031_sga_max_dumps 10 Maximum number of SGA heapdumps
_mem_std_extent_size 4096 standard extent size for fixed-size-extent heaps
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
__shared_pool_size 251658240 Actual size in bytes of shared pool
__large_pool_size 16777216 Actual size in bytes of large pool
__java_pool_size 16777216 Actual size in bytes of java pool
__streams_pool_size 0 Actual size in bytes of streams pool
_large_pool_min_alloc 16000 minimum allocation size in bytes for the large all
ocation pool
_shared_pool_reserved_pct 5 percentage memory of the shared pool allocated for
the reserved area
_shared_pool_reserved_min_alloc 4400 minimum allocation size in bytes for reserved area
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
of shared pool
_kghdsidx_count 2 max kghdsidx count
_test_param_1 25 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 25 test parmeter 5 - deprecated integer
_test_param_6 0 test parmeter 6 - size (ub8)
_disable_instance_params_check FALSE disable instance type check for ksp
_parameter_table_block_size 2048 parameter table block size
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_ksd_test_param 999 KSD test parmeter
_kse_die_timeout 60000 amount of time a dying process is spared by PMON (
in centi-secs)
_stack_guard_level 0 stack guard level
_kse_pc_table_size 256 kse pc table cache size
_messages 600 message queue resources - dependent on # processes
& # buffers
_enqueue_locks 4170 locks for managed enqueues
_enqueue_resources 1692 resources for enqueues
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_enqueue_hash 705 enqueue hash table length
_enqueue_debug_multi_instance FALSE debug enqueue multi instance
_enqueue_hash_chain_latches 8 enqueue hash chain latches
_ksi_trace KSI trace string of lock type(s)
_trace_processes ALL enable KST tracing in process
_trace_archive FALSE start DIAG process
_trace_events trace events enabled at startup
_trace_buffers ALL:256 trace buffer sizes per process
_trace_flush_processes ALL trace data archived by DIAG for these processes
_trace_file_size 65536 maximum size of trace file (in bytes)
_trace_options text,multi trace data flush options
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
ple
_trace_buffer_wrap_timestamp TRUE enable KST timestamp on trace buffer wrap
_nchar_imp_cnv TRUE NLS allow Implicit Conversion between CHAR and NCH
AR
_disable_file_locks FALSE disable file locks for control, data, redo log fil
es
_ksfd_verify_write FALSE verify asynchronous writes issued through ksfd
_disable_odm FALSE disable odm feature
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_enable_list_io FALSE Enable List I/O
_omf enabled enable/disable OMF
_aiowait_timeouts 100 Number of aiowait timeouts before error is reporte
d
_io_shared_pool_size 4194304 Size of I/O buffer pool from SGA
_db_file_direct_io_count 1048576 Sequential I/O buf size
_ioslave_issue_count 500 IOs issued before completion check
_ioslave_batch_count 1 Per attempt IOs picked
_io_slaves_disabled FALSE Do not use I/O slaves
_lgwr_io_slaves 0 LGWR I/O slaves
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_arch_io_slaves 0 ARCH I/O slaves
_backup_disk_io_slaves 0 BACKUP Disk I/O slaves
_backup_io_pool_size 1048576 memory to reserve from the large pool
_high_server_threshold 0 high server thresholds
_low_server_threshold 0 low server thresholds
_yield_check_interval 100000 interval to check whether actses should yield
_resource_manager_always_on TRUE enable the resource manager always
_first_spare_parameter first spare parameter - integer
_second_spare_parameter second spare parameter - integer
_third_spare_parameter third spare parameter - integer
_fourth_spare_parameter fourth spare parameter - string
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_fifth_spare_parameter fifth spare parameter - string
_sixth_spare_parameter sixth spare parameter - string list
_seventh_spare_parameter seventh spare parameter - string list
_ksxp_send_timeout 300 set timeout for sends queued with the inter-instan
ce IPC
_ksxp_diagmode OFF set to OFF to disable automatic slowsend diagnosti
cs
_skgxp_reaping 1000 tune skgxp OSD reaping limit
_skgxp_udp_hiwat_warn 1000 ach hiwat mark warning interval
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_skgxp_udp_ach_reaping_time 120 time in minutes before idle ach's are reaped
_skgxp_udp_timed_wait_seconds 5 time in seconds before timed wait is invoked
_skgxp_udp_timed_wait_buffering 1024 diagnostic log buffering space (in bytes) for time
d wait (0 means unbufferd
_skgxp_udp_keep_alive_ping_timer_secs 300 connection idle time in seconds before keep alive
is initiated. min: 30 sec max: 1800 sec default: 3
00 sec
_disable_duplex_link FALSE Turn off connection duplexing
_diag_diagnostics TRUE Turn off diag diagnostics
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_disable_interface_checking FALSE disable interface checking at startup
_skgxp_udp_interface_detection_time_secs 60 time in seconds between interface detection checks
_ksxp_reporting_process LMD0 reporting process for KSXP
_ksmg_granule_size 16777216 granule size in bytes
_ksmg_granule_locking_status 1 granule locking status
_ksmg_lock_check_interval timeout action interval in minutes
_ksmg_lock_reacquire_count 5 repeat count for acquisition of locks
_filemap_dir FILEMAP directory
_object_statistics TRUE enable the object level statistics collection
_enable_rlb TRUE enable RLB metrics processing
_disable_health_check FALSE Disable Health Check
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_accept_versions List of parameters for rolling operation
_hang_detection 0 Hang Management detection interval
_diag_daemon TRUE start DIAG daemon
_dump_system_state_scope local scope of sysstate dump during instance termination
_dump_trace_scope global scope of trace dump during a process crash
_dump_interval_limit 120 trace dump time interval limit (in seconds)
_dump_max_limit 5 max number of dump within dump interval
_trace_navigation_scope global enabling trace navigation linking
_max_protocol_support 10000 Max occurrence protocols supported in a process
_lm_lms 0 number of background gcs server processes to start
_lm_dynamic_lms FALSE dynamic lms invocation
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_lm_max_lms 0 max. number of background global cache server proc
esses
_lm_min_lms 0 min. number of background global cache server proc
esses
_lm_activate_lms_threshold 100 threshold value to activate an additional lms
_lm_lmd_waittime 8 default wait time for lmd in centiseconds
_lm_lms_waittime 8 default wait time for lms in centiseconds
_lm_procs 320 number of client processes configured for cluster
database
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_lm_ress 6000 number of resources configured for cluster databas
e
_lm_locks 12000 number of enqueues configured for cluster database
_lm_master_weight 1 master resource weight for this instance
_active_standby_fast_reconfiguration TRUE if TRUE optimize dlm reconfiguration for active/st
andby OPS
_lm_enq_rcfg TRUE if TRUE enables enqueue reconfiguration
_lm_dynamic_remastering FALSE if TRUE enables dynamic remastering
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_lm_xids 352 number of transaction IDs configured for cluster d
atabase
_lm_res_part 128 number of resource partition configured for gcs
_lm_drm_window 0 dynamic remastering bucket window size
_lm_drm_max_requests 100 dynamic remastering maximum affinity requests proc
essed together
_lm_drm_xlatch 0 dynamic remastering forced exclusive latches
_lm_contiguous_res_count 128 number of contiguous blocks that will hash to the
same HV bucket
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_lm_num_pt_buckets 4096 number of buckets in the object affinity hash tabl
e
_lm_num_pt_latches 128 number of latches in the object affinity hash tabl
e
_lm_num_pcmhv_latches 0 number of latches covering the PCM HV buckets in c
gs
_lm_node_join_opt FALSE cluster database node join optimization in reconfi
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
g
_lm_non_fault_tolerant FALSE disable cluster database fault-tolerance mode
_lm_cache_res_cleanup 25 percentage of cached resources should be cleanup
_lm_cache_res_type TMHWDI cache resource: string of lock types(s)
_lm_cache_lvl0_cleanup 0 how often to cleanup level 0 cache res (in sec)
_lm_send_buffers 10000 number of cluster database send buffers
_lm_rcv_buffer_size 32768 the size of receive buffer
_ogms_home GMS home directory
_lm_sync_timeout Synchronization timeout for DLM reconfiguration st
eps
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_lm_ticket_active_sendback Flow control ticket active sendback threshold
_lm_rcfg_timeout 180000 Reconfiguration timeout
_lm_enq_lock_freelist Number of ges enqueue element freelist
_lm_enqueue_freelist 3 Number of enqueue freelist
_lm_dd_interval 60 dd time interval in seconds
_lm_dd_scan_interval 5 dd scan interval in seconds
_dlmtrace Trace string of global enqueue type(s)
_lm_tx_delta 16 TX lock localization delta
_lm_proc_freeze_timeout 300 reconfiguration: process freeze timeout
_lm_validate_resource_type FALSE if TRUE enables resource name validation
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_lm_file_affinity mapping between file id and master instance number
_lm_share_lock_opt FALSE if TRUE enables share lock optimization
_lm_res_hash_bucket 0 number of resource hash buckets
_ges_diagnostics TRUE if TRUE enables GES diagnostics
_fair_remote_cvt FALSE if TRUE enables fair remote convert
_dlm_send_timeout 30000 DLM send timeout value
_ges_dd_debug TRUE if TRUE enables GES deadlock detection debug diagn
ostics
_lm_global_posts TRUE if TRUE deliver global posts to remote nodes
_rcfg_parallel_replay TRUE if TRUE enables parallel replay and cleanup at rec
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
onfiguration
_parallel_replay_msg_limit 4000 Number of messages for each round of parallel repl
ay
_rcfg_parallel_fixwrite TRUE if TRUE enables parallel fixwrite at reconfigurati
on
_parallel_fixwrite_bucket 1000 Number of buckets for each round of fix write
_rcfg_parallel_verify TRUE if TRUE enables parallel verify at reconfiguration
_dump_rcvr_ipc TRUE if TRUE enables IPC dump at instance eviction time
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_ges_health_check 1 if greater than 0 enables GES system health check
_lm_msg_batch_size 0 GES batch message size
_lm_tickets 1000 GES messaging tickets
_lm_msg_cache_thresholds GES message buffer caching threshold
_lm_msg_cleanup_interval 3000 GES message buffer cleanup interval time
_lm_send_mode auto GES send mode
_lm_send_queue_length 5000 GES send queue maximum length
_lm_send_queue_batching TRUE GES send queue message batching
_lm_process_batching TRUE GES implicit process batching for IPC messages
_lm_sq_batch_factor 2 GES send queue minimum batching factor
_lm_sq_batch_type auto GES send queue batching mechanism
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_lm_sq_batch_waittick 3 GES send queue batching waittime in tick
_lm_sendproxy_reserve 25 GES percentage of send proxy reserve of send ticke
ts
_lm_checksum_batch_msg FALSE GES checksum batch messages
_abort_recovery_on_join FALSE if TRUE, abort recovery on join reconfigurations
_send_ast_to_foreground TRUE if TRUE, send ast message to foreground
_reliable_block_sends FALSE if TRUE, block sends across interconnect are relia
ble
_blocks_per_cache_server 128 number of consecutive blocks per global cache serv
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
er
_send_close_with_block TRUE if TRUE, send close with block even with direct se
nds
_gcs_fast_reconfig TRUE if TRUE, enable fast reconfiguration for gcs locks
_cr_grant_global_role TRUE if TRUE, grant lock for CR requests when block is
in global role
_cr_grant_local_role AUTO turn 3-way CR grants off, make it automatic, or tu
rn it on
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_skip_assume_msg TRUE if TRUE, skip assume message for consigns at the m
aster
_gcs_resources number of gcs resources to be allocated
_gcs_latches 0 number of gcs resource hash latches to be allocate
d per LMS process
_gcs_process_in_recovery TRUE if TRUE, process gcs requests during instance reco
very
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_gcs_shadow_locks number of pcm shadow locks to be allocated
_side_channel_batch_size 200 number of messages to batch in a side channel mess
age (DFS)
_side_channel_batch_timeout 6 timeout before shipping out the batched side chann
elmessages
_master_direct_sends 31 direct sends for messages from master (DFS)
_cgs_send_timeout 300 CGS send timeout value
_imr_active TRUE Activate Instance Membership Recovery feature
_imr_max_reconfig_delay 300 Maximum Reconfiguration delay (seconds)
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_imr_splitbrain_res_wait 600 Maximum wait for split-brain resolution (seconds)
_imr_disk_voting_interval 3 Maximum wait for IMR disk voting (seconds)
_cluster_library clss cluster library selection
_cgs_reconfig_timeout 120 CGS reconfiguration timeout interval
_lm_dynamic_load TRUE dynamic load adjustment
_notify_crs FALSE notify cluster ready services of startup and shutd
own
_shared_pool_max_size 0 shared pool maximum size when auto SGA enabled
_streams_pool_max_size 0 streams pool maximum size when auto SGA enabled
_disable_streams_pool_auto_tuning FALSE disable streams pool auto tuning
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_memory_management_tracing 0 trace memory management activity
_memory_sanity_check 0 partial granule sanity check
_init_granule_interval 10 number of granules to process for deferred cache
_memory_broker_stat_interval 30 memory broker statistics gathering interval
_memory_broker_shrink_heaps 15 memory broker allow policy to shrink shared pool
_memory_broker_shrink_java_heaps 900 memory broker allow policy to shrink java pool
_memory_broker_shrink_streams_pool 900 memory broker allow policy to shrink streams pool
_memory_broker_shrink_timeout 60000000 memory broker policy to timeout shrink shared/java
pool
_memory_broker_log_stat_entries 5 memory broker num stat entries
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_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 FALSE disable latch-free SCN writes using 64-bit compare
& swap
_controlfile_enqueue_timeout 900 control file enqueue timeout in seconds
_controlfile_update_check OFF controlfile update sanity check
_controlfile_enqueue_dump FALSE dump the system states after controlfile enqueue t
imeout
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_controlfile_block_size 0 control file block size in bytes
_controlfile_section_init_size control file initial section size
_controlfile_section_max_expand control file max expansion rate
_db_block_buffers 265856 Number of database blocks cached in memory: hidden
parameter
_db_block_cache_protect FALSE protect database blocks (true only when debugging)
_db_block_cache_protect_internal 0 protect database blocks (for strictly internal use
only)
_dbwr_tracing 0 Enable dbwriter tracing
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_disable_multiple_block_sizes FALSE disable multiple block size support (for debugging
)
_db_fast_obj_truncate TRUE enable fast object truncate
_db_fast_obj_ckpt TRUE enable fast object checkpoint
_db_obj_enable_ksr TRUE enable ksr in object checkpoint/reuse
_small_table_threshold 5317 threshold level of table size for direct reads
_db_block_cache_num_umap 0 number of unmapped buffers (for tracking swap call
s on blocks)
_dbwr_async_io TRUE Enable dbwriter asynchronous writes
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_recovery_percentage 50 recovery buffer cache percentage
_db_lost_write_checking FALSE Enable scn based lost write detection mechanism
__db_cache_size 2248146944 Actual size of DEFAULT buffer pool for standard bl
ock size buffers
_db_block_numa 1 Number of NUMA nodes
_db_block_lru_latches 32 number of lru latches
_db_percpu_create_cachesize 2 size of cache created per cpu in deferred cache cr
eate
_db_todefer_cache_create TRUE buffer cache deferred create
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_db_block_known_clean_pct 2 Initial Percentage of buffers to maintain known cl
ean
_db_block_max_scan_pct 40 Percentage of buffers to inspect when looking for
free
_db_large_dirty_queue 25 Number of buffers which force dirty queue to be wr
itten
_db_writer_max_writes 0 Max number of outstanding DB Writer IOs
_db_writer_chunk_writes 0 Number of writes DBWR should wait for
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_db_block_med_priority_batch_size 0 Fraction of writes for medium priority reasons
_db_block_hi_priority_batch_size 0 Fraction of writes for high priority reasons
_db_writer_histogram_statistics FALSE maintain dbwr histogram statistics in x$kcbbhs
_dbwr_scan_interval 300 dbwriter scan interval
_db_writer_flush_imu TRUE If FALSE, DBWR will not downgrade IMU txns for AGI
NG
_db_writer_coalesce_write_limit 131072 Limit on size of coalesced write
_db_writer_coalesce_area_size 1048576 Size of memory allocated to dbwriter for coalescin
g writes
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_selftune_checkpoint_write_pct 3 Percentage of total physical i/os for self-tune ck
pt
_db_writer_verify_writes FALSE Enable lost write detection mechanism
_db_block_prefetch_quota 10 Prefetch quota as a percent of cache size
_db_block_prefetch_limit 0 Prefetch limit in blocks
_db_cache_pre_warm TRUE Buffer Cache Pre-Warm Enabled : hidden parameter
_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_aging_hot_criteria 2 Touch count which sends a buffer to head of replac
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
ement list
_db_aging_stay_count 0 Touch count set when buffer moved to head of repla
cement list
_db_aging_cool_count 1 Touch count set when buffer cooled
_db_aging_touch_time 3 Touch count which sends a buffer to head of replac
ement list
_db_aging_freeze_cr FALSE Make CR buffers always be too cold to keep in cach
e
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_db_block_hash_buckets 1048576 Number of database block hash buckets
_db_block_hash_latches 4096 Number of database block hash latches
_db_handles 1500 System-wide simultaneous buffer operations
_db_handles_cached 5 Buffer handles cached each process
_wait_for_sync TRUE wait for sync on commit MUST BE ALWAYS TRUE
_db_block_cache_clone FALSE Always clone data blocks on get (for debugging)
_db_block_trace_protect FALSE trace buffer protect calls
_db_block_max_cr_dba 6 Maximum Allowed Number of CR buffers per dba
_trace_buffer_flushes FALSE trace buffer flushes if otrace cacheIO event is se
t
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_trace_multi_block_reads FALSE trace multi_block reads if otrace cacheIO event is
set
_trace_cr_buffer_creates FALSE trace cr buffer creates if otrace cacheIO event is
set
_write_clones 3 write clones flag
_trace_buffer_gets FALSE trace kcb buffer gets if otrace cacheIO event is s
et
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_check_block_after_checksum TRUE perform block check after checksum if both are tur
ned on
_trace_pin_time 0 trace how long a current pin is held
_db_fast_obj_check FALSE enable fast object drop sanity check
_db_block_temp_redo FALSE generate redo for temp blocks
_db_block_adjchk_level 1 adjacent cache buffer check level
_db_block_adjcheck TRUE adjacent cache buffer checks - low blkchk overwrit
e parameter
_db_block_check_objtyp TRUE check objd and typ on cache disk read
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_db_block_do_full_mbreads FALSE do full block read even if some blocks are in cach
e
_trace_buffer_wait_timeouts 0 trace buffer busy wait timeouts
_buffer_busy_wait_timeout 100 buffer busy wait time in centiseconds
_db_cache_crx_check FALSE check for costly crx examination functions
_db_hot_block_tracking FALSE track hot blocks for hash latch contention
_db_block_cache_history 0 buffer header tracing (non-zero only when debuggin
g)
_db_block_cache_history_level 2 buffer header tracing level
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_db_block_table_scan_buffer_size 4194304 Size of shared table scan read buffer
_db_obj_ckpt_ordered FALSE fast object checkpoint ordered by lrba
_db_cache_process_cr_pin_max maximum number of cr pins a process may have
_db_cache_advice_sample_factor 4 cache advisory sampling factor
_db_cache_advice_batch_size 128 cache advisory simulation batch size
_db_mttr_advice OFF MTTR advisory
_db_mttr_sim_target MTTR simulation targets
_db_mttr_sample_factor 64 MTTR simulation sampling factor
_db_mttr_partitions 0 number of partitions for MTTR advisory
_db_mttr_sim_trace_size 256 MTTR simulation trace size
_db_mttr_trace_to_alert FALSE dump trace entries to alert file
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_immediate_commit_propagation TRUE if TRUE, propagate commit SCN immediately
_minimum_giga_scn 0 Minimum SCN to start with in 2^30 units
_redo_compatibility_check FALSE general and redo/undo compatibility sanity check
_db_block_check_for_debug FALSE Check more and dump block before image for debuggi
ng
_db_always_check_system_ts TRUE Always perform block check and checksum for System
tablespace
_log_checkpoint_recovery_check 0 # redo blocks to verify after checkpoint
_two_pass TRUE enable two-pass thread recovery
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_recovery_verify_writes FALSE enable thread recovery write verify
_disable_recovery_read_skip FALSE Disable the read optimization during media recover
y
_log_archive_security_enabled TRUE log archive security enabled
_log_archive_avoid_memcpy TRUE log archive avoid memcpy
_log_archive_prot_auto_demote FALSE log archive protection auto demotion
_log_archive_compress_enable FALSE Bypass database rules for enabling archivelog comp
ression
_real_time_apply_arch_delay 0 Archival delay with real time apply
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_real_time_apply_sim 0 Simulation value with real time apply
_log_archive_buffers 10 Number of buffers to allocate for archiving
_log_archive_buffer_size 2048 Size of each archival buffer in log file blocks
_log_archive_callout archival callout
_log_archive_net_timeout 0 maximum network wait time in seconds when SYNC=PAR
ALLEL
_log_archive_delta_sync_wait 0 iterative sleep time in centiseconds seconds when
SYNC=PARALLEL
_max_rfs_connections 0 maximum number of connections used for remote arch
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
ival
_lgwr_max_ns_wt 1 Maximum wait time for lgwr to allow NetServer to p
rogress
_max_arch_lns_wait 120 Maximum wait by ARCH to allow NetServer to complet
e archival
_max_lns_shutdown_archival_time 30 Maximum time spent by LNS to archive last log duri
ng shutdown
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_ta_lns_wait_for_arch_log 20 LNS Wait time for arhcived version of online log
_ns_max_flush_wt 1 Flush wait time for NetServer to flush oustanding
writes
_ns_max_send_delay 15 Data Loss Time Bound for NetServer
_lgwr_ta_sim_err 0 Variable to simulate errors lgwr true async
_lgwr_ns_sim_err 0 Variable to simulate errors lgwrns
_lgwr_ns_nl_min 500 Variable to simulate network latency or buffer thr
eshold
_lgwr_ns_nl_max 1000 Variable to simulate network latency or buffer thr
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
eshold
_log_archive_network_redo_size 10 Log archive network redo buffer size used by ARCH
_lgwr_async_io FALSE LGWR Asynchronous IO enabling boolean flag
_lgwr_async_broadcasts TRUE LGWR Asynchronous Broadcasts enabling boolean flag
_lgwr_delay_write FALSE LGWR write delay for debugging
_log_io_size 0 automatically initiate log write if this many redo
blocks in buffer
_log_switch_timeout 0 Maximum number of seconds redos in the current log
could span
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_log_buffers_debug FALSE debug redo buffers (slows things down)
_log_buffers_corrupt FALSE corrupt redo buffers before write
_log_debug_multi_instance FALSE debug redo multi instance code
_log_simultaneous_copies 16 number of simultaneous copies into redo buffer(# o
f copy latches)
_log_parallelism 1 Number of log buffer strands
_log_parallelism_max 2 Maximum number of log buffer strands
_log_parallelism_dynamic TRUE Enable dynamic strands
_log_private_parallelism FALSE Number of private log buffer strands for zero-copy
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
redo
_log_private_parallelism_mul 10 Active sessions multiplier to deduce number of pri
vate strands
_log_private_mul 5 Private strand multiplier for log space preallocat
ion
_lightweight_hdrs TRUE Lightweight headers for redo
_mirror_redo_buffers FALSE Save buffers for debugging redo corruptions
_disable_logging FALSE Disable logging
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_db_file_exec_read_count 16 multiblock read count for regular clients
_db_file_optimizer_read_count 16 multiblock read count for regular clients
_db_file_noncontig_mblock_read_count 11 number of noncontiguous db blocks to be prefetched
_db_recovery_temporal_file_dest default database recovery temporal file location
_hard_protection FALSE if TRUE enable H.A.R.D specific format changes
_allow_error_simulation FALSE Allow error simulation for testing
_db_file_format_io_buffers 4 Block formatting I/O buf count
_gc_use_cr TRUE if TRUE, allow CR pins on PI and WRITING buffers
_gc_undo_affinity TRUE if TRUE, enable dynamic undo affinity
_gc_affinity_time 10 if non zero, enable dynamic object affinity
_gc_affinity_limit 50 dynamic affinity limit
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_gc_affinity_minimum 4800 dynamic affinity minimum activity per minute
_recovery_asserts FALSE if TRUE, enable expensive integrity checks
_gc_integrity_checks 1 set the integrity check level
_gc_vector_read TRUE if TRUE, vector read current buffers
_gc_element_percent 103 global cache element percent
_gc_latches 8 number of latches per LMS process
_gc_defer_time 3 how long to defer down converts for hot buffers
_kcl_debug TRUE if TRUE, record le history
_kcl_index_split TRUE if TRUE, reject pings on blocks in middle of a spl
it
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_kcl_commit TRUE if TRUE, call kjbcommit
_fairness_threshold 4 number of times to CR serve before downgrading loc
k
_interconnect_checksum TRUE if TRUE, checksum interconnect blocks
_cr_server_log_flush TRUE if TRUE, flush redo log before serving a CR buffer
_ping_level 3 fusion ping level
_async_recovery_reads TRUE if TRUE, issue recovery reads asynchronously
_async_recovery_claims TRUE if TRUE, issue recovery claims asynchronously
_avoid_prepare TRUE if TRUE, do not prepare a buffer when the master i
s local
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_bwr_for_flushed_pi TRUE if TRUE, generate a BWR for a flushed PI
_send_requests_to_pi TRUE if TRUE, try to send CR requests to PI buffers
_kcl_undo_locks 128 number of locks per undo segment
_kcl_undo_grouping 32 grouping for undo block locks
_kcl_conservative_log_flush FALSE if TRUE, conservatively log flush before CR servin
g
_gc_statistics TRUE if TRUE, kcl statistics are maintained
_gc_check_bscn TRUE if TRUE, check for stale blocks
_gc_global_lru AUTO turn global lru off, make it automatic, or turn it
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
on
_gc_dynamic_affinity_locks TRUE if TRUE, get dynamic affinity locks
_gc_undo_affinity_locks TRUE if TRUE, get affinity locks for undo
_gc_coalesce_recovery_reads TRUE if TRUE, coalesce recovery reads
_gc_keep_recovery_buffers TRUE if TRUE, make recovery buffers current
_gc_maximum_bids 0 maximum number of bids which can be prepared
_adaptive_direct_read TRUE Adaptive Direct Read
_db_block_align_direct_read TRUE Align Direct Reads
_disable_incremental_checkpoints FALSE Disable incremental checkpoints for thread recover
y
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_fast_start_instance_recovery_target 0 cluster availability target time in RAC environmen
t
_disable_selftune_checkpointing FALSE Disable self-tune checkpointing
_selftune_checkpointing_lag 300 Self-tune checkpointing lag the tail of the redo l
og
_target_rba_max_lag_percentage 90 target rba max log lag percentage
_no_recovery_through_resetlogs FALSE no recovery through this resetlogs operation
_log_blocks_during_backup TRUE log block images when changed during backup
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_allow_resetlogs_corruption FALSE allow resetlogs even if it will cause corruption
_allow_terminal_recovery_corruption FALSE Finish terminal recovery even if it may cause corr
uption
_allow_read_only_corruption FALSE allow read-only open even if database is corrupt
_serial_recovery FALSE force serial recovery or parallel recovery
_coord_message_buffer 0 parallel recovery coordinator side extra message b
uffer size
_multiple_instance_recovery FALSE use multiple instances for media recovery
_backup_kgc_bufsz 0 specifies buffer size to be used for kgc compressi
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
on
_backup_kgc_type 0 specifies compression type used for kgc compressio
n
_backup_kgc_niters 0 specifies number of iterations used for kgc compre
ssion
_backup_ksfq_bufsz 0 size of the ksfq buffer used for backup/restore
_backup_ksfq_bufcnt 0 number of the ksfq buffers used for backup/restore
_backup_max_gap_size 4294967294 largest gap in an incremental/optimized backup buf
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
fer, in bytes
_unused_block_compression FALSE enable unused block compression
_restore_spfile restore spfile to this location
_dummy_instance FALSE dummy instance started by RMAN
_rman_io_priority 3 priority at which rman backup i/o's are done
_tts_allow_charset_mismatch FALSE allow plugging in a tablespace with an incompatibl
e character set
_xtts_allow_pre10 FALSE allow cross platform for pre10 compatible tablespa
ce
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_xtts_set_platform_info FALSE set cross platform info during file header read
_disable_recoverable_recovery FALSE Disable the new recoverable recovery mechanism
_recoverable_recovery_batch_percent 50 Recoverable recovery batch size (percentage of buf
fer cache)
_disable_incremental_recovery_ckpt FALSE Disable incremental recovery checkpoint mechanism
_incremental_recovery_ckpt_min_batch 30 Minimum number of writes for incremental recovery
ckpt every 3 sec
_media_recovery_read_batch 128 media recovery block read batch
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_media_recovery_reap_time 3 media recovery reap time
_bct_public_dba_buffer_size 0 total size of all public change tracking dba buffe
rs, in bytes
_bct_initial_private_dba_buffer_size 0 initial number of entries in the private change tr
acking dba buffers
_bct_bitmaps_per_file 8 number of bitmaps to store for each datafile
_bct_file_block_size 0 block size of change tracking file, in bytes
_bct_file_extent_size 0 extent size of change tracking file, in bytes
_bct_chunk_size 0 change tracking datafile chunk size, in bytes
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_bct_crash_reserve_size 262144 change tracking reserved crash recovery SGA space,
in bytes
_bct_buffer_allocation_size 2097152 size of one change tracking buffer allocation, in
bytes
_bct_buffer_allocation_max 104857600 maximum size of all change tracking buffer allocat
ions, in bytes
_bct_buffer_allocation_min_extents 1 mininum number of extents to allocate per buffer a
llocation
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_bct_fixtab_file change tracking file for fixed tables
_block_change_tracking TRUE change tracking possible
_log_deletion_policy mandatory archivelog deletion policy for mandatory/all desti
nation
_validate_flashback_database FALSE Scan database to validate result of flashback data
base
_flashback_allow_noarchivelog FALSE Allow enabling flashback on noarchivelog database
_verify_flashback_redo TRUE Verify that the redo logs needed for flashback are
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
available
_flashback_verbose_info FALSE Print verbose information about flashback database
_flashback_logfile_enqueue_timeout 600 flashback logfile enqueue timeout for opens
_flashback_barrier_interval 1800 Flashback barrier interval in seconds
_flashback_standby_barrier_interval 1800 Flashback standby barrier interval in seconds
_flashback_fuzzy_barrier TRUE Use flashback fuzzy barrier
_flashback_log_size 1000 Flashback log size
_flashback_log_min_size 100 Minimum flashback log size
_flashback_copy_latches 10 Number of flashback copy latches
_flashback_n_log_per_thread 128 Desired number of flashback logs per flashback thr
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
ead
_flashback_max_n_log_per_thread 2048 Maximum number of flashback logs per flashback thr
ead
_flashback_generation_buffer_size 33554432 flashback generation buffer size
_flashback_max_log_size 0 Maximum flashback log size in bytes (OS limit)
_flashback_log_io_error_behavior 0 Specify Flashback log I/O error behavior
_flashback_hint_barrier_percent 20 Flashback hint barrier percent
_percent_flashback_buf_partial_full 50 Percent of flashback buffer filled to be considere
d partial full
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_flashback_write_size_qm 4 Desired flashback write size in quarter MB
_tdb_debug_mode 16 set debug mode for testing transportable database
_row_locking always row-locking
_serializable FALSE serializable
_log_committime_block_cleanout FALSE Log commit-time block cleanout
_ktc_latches 0 number of ktc latches
_prescomm FALSE presume commit of IMU transactions
_recursive_imu_transactions FALSE recursive transactions may be IMU
_ktc_debug 0 for ktc debug
_allocate_creation_order FALSE should files be examined in creation order during
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
allocation
_log_space_errors TRUE should we report space errors to alert log
_bump_highwater_mark_count 0 how many blocks should we allocate per free list o
n advancing HWM
_assm_default TRUE ASSM default
_enable_hwm_sync TRUE enable HWM synchronization
_hwm_sync_threshold 10 HWM synchronization threshold in percentage
_enable_tablespace_alerts TRUE enable tablespace alerts
_last_allocation_period 5 period over which an instance can retain an active
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
level1 bitmap
_inst_locking_period 5 period an instance can retain a newly acquired lev
el1 bitmap
_allocation_update_interval 3 interval at which successful search in L1 should b
e updated
_minimum_blocks_to_shrink 0 minimum number freeable blocks for shrink to be pr
esent
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_minimum_extents_to_shrink 1 minimum number freeable extents for shrink to be p
resent
_max_shrink_obj_stats 0 number of segments for which shrink stats will be
maintained
_check_ts_threshold 0 check tablespace thresholds
_ktu_latches 0 number of KTU latches
_rollback_segment_initial 1 starting undo segment number
_rollback_segment_count 0 number of undo segments
_offline_rollback_segments offline undo segment list
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_corrupted_rollback_segments corrupted undo segment list
_transaction_auditing TRUE transaction auditing records generated in the redo
log
_enable_block_level_transaction_recovery TRUE enable block level recovery
_cleanup_rollback_entries 100 no. of undo entries to apply per transaction clean
up
_smu_error_simulation_site 0 site ID of error simulation in KTU code
_smu_error_simulation_type 0 error type for error simulation in KTU code
_collect_undo_stats TRUE Collect Statistics v$undostat
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_smu_timeouts comma-separated *AND double-quoted* list of AUM ti
meouts: mql, tur, sess_exprn, qry_exprn, slot_intv
l
_smu_debug_mode 0 <debug-flag> - set debug event for testing SMU ope
rations
_undo_debug_mode 0 debug flag for undo related operations
_verify_undo_quota FALSE TRUE - verify consistency of undo quota statistics
_discrete_transactions_enabled FALSE enable OLTP mode
_in_memory_undo TRUE Make in memory undo for top level transactions
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_imu_pools 3 in memory undo pools
_row_cr TRUE enable row cr for all sql
_undo_autotune TRUE enable auto tuning of undo_retention
_rollback_stopat 0 stop at -position to step rollback
_smon_internal_errlimit 100 limit of SMON internal errors
_transaction_recovery_servers 0 max number of parallel recovery slaves that may be
used
_parallel_recovery_stopat 32767 stop at -position- to step through SMON
_disable_txn_alert 0 disable txn layer alert
_undo_debug_usage 0 invoke undo usage functions for testing
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_db_change_notification_enable TRUE enable db change notification
_db_row_overlap_checking TRUE row overlap checking override parameter for data/i
ndex blocks
_db_index_block_checking TRUE index block checking override parameter
_alternate_iot_leaf_block_split_points TRUE enable alternate index-organized table leaf-block
split-points
_release_insert_threshold 5 maximum number of unusable blocks to unlink from f
reelist
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_walk_insert_threshold 0 maximum number of unusable blocks to walk across f
reelist
_enable_hash_overflow FALSE TRUE - enable hash cluster overflow based on SIZE
_use_seq_process_cache TRUE whether to use process local seq cache
_reuse_index_loop 5 number of blocks being examine for index block reu
se
_kdbl_enable_post_allocation FALSE allocate dbas after populating data buffers
_ldr_io_size 262144 size of write IOs used during a load operation
_ignore_desc_in_index FALSE ignore DESC in indexes, sort those columns ascendi
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
ng anyhow
_keep_remote_column_size FALSE remote column size does not get modified
_row_cache_cursors 20 number of cached cursors for row cache management
_kgl_multi_instance_lock TRUE whether KGL to support multi-instance locks
_kgl_multi_instance_pin TRUE whether KGL to support multi-instance pins
_kgl_multi_instance_invalidation TRUE whether KGL to support multi-instance invalidation
s
_kgl_latch_count 0 number of library cache latches
_kgl_heap_size 1024 extent size for library cache heap 0
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_kgl_fixed_extents TRUE fixed extent size for library cache memory allocat
ions
_kgl_session_cached_objects 10 maximum length of the KGL object cache lru
_kgl_keep_cache_pct 30 KGL keep cache minimum threshold
_kgl_keep_cache_retain_pct 20 KGL keep cache retain threshold
_kgl_bucket_count 9 index to the bucket count array
_library_cache_advice TRUE whether KGL advice should be turned on
_kglsim_maxmem_percent 5 max percentage of shared pool size to be used for
KGL advice
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_kgl_hash_collision FALSE whether KGL hash collision is possible
_kgl_time_to_wait_for_locks 15 time to wait for locks and pins before timing out
_kgl_large_heap_warning_threshold 2097152 maximum heap size before KGL writes warnings to th
e alert log
_synonym_repoint_tracing FALSE whether to trace metadata comparisons for synonym
repointing
_cursor_cache_frame_bind_memory FALSE frame & bind buffer caching
_kgx_latches 512 # of mutex latches if CAS is not supported.
_kks_use_mutex_pin FALSE Turning on this will make KKS use mutex for cursor
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
pins.
_cursor_stats_bucket 15 Hash table buckets for cursor stats (2^).
_cursor_stats_heap 4 Heaps/latches for cursor stats.
_passwordfile_enqueue_timeout 900 password file enqueue timeout in seconds
_dynamic_rls_policies TRUE rls policies are dynamic
_app_ctx_vers FALSE enable app ctx versioning
_session_context_size 10000 session app context size
_default_encrypt_alg 0 default encryption algorithm
_ols_cleanup_task TRUE Clean up unnecessary entries in OLS sessinfo table
_distributed_recovery_connection_hold_ti 200 number of seconds RECO holds outbound connections
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
me open
__dg_broker_service_names orcl_XPT service names for broker use
_pmon_load_constants 300,192,64 server load balancing constants (S,P,D,I,L,C,M)
,3,10,10,0
,0
_dispatcher_rate_ttl time-to-live for rate statistic (100ths of a secon
d)
_dispatcher_rate_scale scale to display rate statistic (100ths of a secon
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
d)
_kernel_message_network_driver FALSE kernel message network driver
_dedicated_server_poll_count 10 dedicated server poll count
_dedicated_server_post_wait_call FALSE dedicated server post/wait call
_dedicated_server_post_wait FALSE dedicated server post/wait
_session_kept_cursor_pins 0 Number of cursors pins to keep in a session
_dump_qbc_tree 0 dump top level query parse tree to trace
_plsql_dump_buffer_events conditions upon which the PL/SQL circular buffer i
s dumped
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_plsql_anon_block_code_type INTERPRETE PL/SQL anonymous block code-type
D
_plsql_nvl_optimize FALSE PL/SQL NVL optimize
_job_queue_interval 5 Wakeup interval in seconds for job queue co-ordina
tor
_parallel_default_max_instances 1 default maximum number of instances for parallel q
uery
_system_trig_enabled TRUE are system triggers enabled
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_kkfi_trace FALSE trace expression substitution
_adjust_literal_replacement FALSE If TRUE, we will adjust the SQL/PLUS output
_kolfuseslf FALSE allow kolf to use slffopen
_session_cached_instantiations 60 Number of pl/sql instantiations to cache in a sess
ion.
_px_trace none px trace parameter
_xt_trace none external tables trace parameter
_xt_coverage none external tables code coverage parameter
_ku_trace none datapump trace parameter
_optimizer_trace none optimizer trace parameter
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_parallel_server_idle_time 30000 idle time before parallel query server dies (in 1/
100 sec)
_parallel_server_sleep_time 10 sleep time between dequeue timeouts (in 1/100ths)
_px_send_timeout 300 IPC message send timeout value in seconds
_dynamic_stats_threshold 30 delay threshold (in seconds) between sending stati
stics messages
_parallel_fake_class_pct 0 fake db-scheduler percent used for testing
_px_load_publish_interval 200 interval at which LMON will check whether to publi
sh PX load
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_parallel_execution_message_align FALSE Alignment of PX buffers to OS page boundary
_PX_use_large_pool FALSE Use Large Pool as source of PX buffers
_parallel_min_message_pool 0 minimum size of shared pool memory to reserve for
pq servers
_px_buffer_ttl 30 ttl for px mesg buffers in seconds
_tq_dump_period 0 time period for duping of TQ statistics (s)
_affinity_on TRUE enable/disable affinity at run time
_enable_default_affinity 0 to enable default implementation of affinity osds
_dss_cache_flush FALSE enable full cache flush for parallel execution
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_hash_join_enabled TRUE enable/disable hash join
_hash_multiblock_io_count 0 number of blocks hash join will read/write at once
_cursor_db_buffers_pinned 884 additional number of buffers a cursor can pin at o
nce
_old_connect_by_enabled FALSE enable/disable old connect by
_table_lookup_prefetch_size 40 table lookup prefetch vector size
_multi_join_key_table_lookup TRUE TRUE iff multi-join-key table lookup prefetch is e
nabled
_table_lookup_prefetch_thresh 2 table lookup prefetch threshold
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_adaptive_fetch_enabled TRUE enable/disable adaptive fetch in parallel group by
_query_execution_cache_max_size 65536 max size of query execution cache
_fast_dual_enabled TRUE enable/disable fast dual
_newsort_enabled TRUE controls whether new sorts can be used as system s
ort
_newsort_type 0 specifies options for the new sort algorithm
_newsort_ordered_pct 63 controls when new sort avoids sorting ordered inpu
t
_oradbg_pathname path of oradbg script
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_vendor_lib_loc Vendor library search root directory
_disable_sun_rsm TRUE Disable IPC OSD support for Sun RSMAPI
_ipc_test_failover 0 Test transparent cluster network failover
_ipc_test_mult_nets 0 simulate multiple cluster networks
_ipc_fail_network 0 Simulate cluster network failer
_no_objects FALSE no object features are used
_domain_index_batch_size 2000 maximum number of rows from one call to domain ind
ex fetch routine
_domain_index_dml_batch_size 200 maximum number of rows for one call to domain inde
x dml routines
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_nchar_imp_conv TRUE should implicit conversion bewteen clob and nclob
be allowed
_insert_enable_hwm_brokered TRUE during parallel inserts high water marks are broke
red
_all_shared_dblinks treat all dblinks as shared
_close_cached_open_cursors FALSE close cursors cached by PL/SQL at each commit
_disable_savepoint_reset FALSE disable the fix for bug 1402161
_init_sql_file ?/rdbms/ad File containing SQL statements to execute upon dat
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
min/sql.bs abase creation
q
_sort_multiblock_read_count 2 multi-block read count for sort
_shrunk_aggs_enabled TRUE enable use of variable sized buffers for non-disti
nct aggregates
_shrunk_aggs_disable_threshold 60 percentage of exceptions at which to switch to ful
l length aggs
_gby_onekey_enabled TRUE enable use of one comparison of all group by keys
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_sql_connect_capability_table SQL Connect Capability Table (testing only)
_optimizer_mode_force TRUE force setting of optimizer mode for user recursive
SQL also
_explain_rewrite_mode FALSE allow additional messages to be generated during e
xplain rewrite
_query_rewrite_or_error FALSE allow query rewrite, if referenced tables are not
dataless
_sort_elimination_cost_ratio 0 cost ratio for sort eimination under first_rows mo
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
de
_sql_connect_capability_override 0 SQL Connect Capability Table Override
_always_anti_join CHOOSE always use this method for anti-join when possible
_partition_view_enabled TRUE enable/disable partitioned views
_always_star_transformation FALSE always favor use of star transformation
_b_tree_bitmap_plans TRUE enable the use of bitmap plans for tables w. only
B-tree indexes
_column_elimination_off FALSE turn off predicate-only column elimination
_cpu_to_io 0 divisor for converting CPU cost to I/O cost
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_optimizer_extended_cursor_sharing UDO optimizer extended cursor sharing
_optimizer_cost_model CHOOSE optimizer cost model
_optimizer_undo_cost_change 10.2.0.1 optimizer undo cost change
_optimizer_system_stats_usage TRUE system statistics usage
_optimizer_cache_stats FALSE cost with cache statistics
_new_sort_cost_estimate TRUE enables the use of new cost estimate for sort
_complex_view_merging TRUE enable complex view merging
_simple_view_merging TRUE control simple view merging performed by the optim
izer
_unnest_subquery TRUE enables unnesting of correlated subqueries
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_eliminate_common_subexpr TRUE enables elimination of common sub-expressions
_pred_move_around TRUE enables predicate move-around
_convert_set_to_join FALSE enables conversion of set operator to join
_px_minus_intersect TRUE enables pq for minus/interect operators
_remove_aggr_subquery TRUE enables removal of subsumed aggregated subquery
_distinct_view_unnesting FALSE enables unnesting of in subquery into distinct vie
w
_optimizer_push_down_distinct 0 push down distinct from query block to table
_optimizer_cost_based_transformation LINEAR enables cost-based query transformation
_optimizer_squ_bottomup TRUE enables unnesting of subquery in a bottom-up manne
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
r
_optimizer_cbqt_factor 50 cost factor for cost-based query transformation
_push_join_predicate TRUE enable pushing join predicate inside a view
_push_join_union_view TRUE enable pushing join predicate inside a union all v
iew
_push_join_union_view2 TRUE enable pushing join predicate inside a union view
_fast_full_scan_enabled TRUE enable/disable index fast full scan
_optimizer_skip_scan_enabled TRUE enable/disable index skip scan
_optimizer_join_sel_sanity_check TRUE enable/disable sanity check for multi-column join
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
selectivity
_optim_enhance_nnull_detection TRUE TRUE to enable index [fast] full scan more often
_idl_conventional_index_maintenance TRUE enable conventional index maintenance for insert d
irect load
_enable_cscn_caching FALSE enable commit SCN caching for all transactions
_parallel_broadcast_enabled TRUE enable broadcasting of small inputs to hash and so
rt merge joins
_px_broadcast_fudge_factor 100 set the tq broadcasting fudge factor percentage
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_px_kxib_tracing 0 turn on kxib tracing
_px_granule_size 100000 default size of a rowid range granule (in KB)
_px_xtgranule_size 10000 default size of a external table granule (in KB)
_xtbuffer_size 0 buffer size in KB needed for populate/query operat
ion
_px_async_getgranule FALSE asynchronous get granule in the slave
_px_min_granules_per_slave 13 minimum number of rowid range granules to generate
per slave
_px_max_granules_per_slave 100 maximum number of rowid range granules to generate
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
per slave
_px_no_stealing FALSE prevent parallel granule stealing in shared nothin
g environment
_px_no_granule_sort FALSE prevent parallel partition granules to be sorted o
n size
_px_proc_constrain TRUE reduce parallel_max_servers if greater than (proce
sses - fudge)
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_parallel_adaptive_max_users 2 maximum number of users running with default DOP
_parallel_load_balancing TRUE parallel execution load balanced slave allocation
_parallel_load_bal_unit 0 number of threads to allocate per instance
_parallel_slave_acquisition_wait 1 time(in seconds) to wait before retrying slave acq
uisition
_pdml_slaves_diff_part TRUE slaves start on different partition when doing ind
ex maint
_pdml_gim_sampling 5000 control separation of global index maintenance for
PDML
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_pdml_gim_staggered FALSE slaves start on different index when doing index m
aint
_px_dynamic_opt TRUE turn off/on restartable qerpx dynamic optimization
_px_dynamic_sample_size 50 num of samples for restartable qerpx dynamic optim
ization
_px_rownum_pd TRUE turn off/on parallel rownum pushdown optimization
_predicate_elimination_enabled TRUE allow predicate elimination if set to TRUE
_groupby_nopushdown_cut_ratio 3 groupby nopushdown cut ratio
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_groupby_orderby_combine 5000 groupby/orderby don't combine threshold
_temp_tran_block_threshold 100 number of blocks for a dimension before we temp tr
ansform
_temp_tran_cache TRUE determines if temp table is created with cache opt
ion
_ordered_semijoin TRUE enable ordered semi-join subquery
_always_semi_join CHOOSE always use this method for semi-join when possible
_ordered_nested_loop TRUE enable ordered nested loop costing
_nested_loop_fudge 100 nested loop fudge
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_project_view_columns TRUE enable projecting out unreferenced columns of a vi
ew
_no_or_expansion FALSE OR expansion during optimization disabled
_optimizer_max_permutations 2000 optimizer maximum join permutations per query bloc
k
_system_index_caching 0 optimizer percent system index caching
_serial_direct_read FALSE enable direct read in serial
_disable_datalayer_sampling FALSE disable datalayer sampling
_disable_sample_io_optim FALSE disable row sampling IO optimization
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_sample_rows_per_block 4 number of rows per block used for sampling IO opti
mization
_ncmb_readahead_enabled 0 enable multi-block readahead for an index scan
_ncmb_readahead_tracing 0 turn on multi-block readahead tracing
_index_prefetch_factor 100 index prefetching factor
_query_cost_rewrite TRUE perform the cost based rewrite with materialized v
iews
_query_rewrite_2 TRUE perform query rewrite before&after or only after v
iew merging
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_query_rewrite_1 TRUE perform query rewrite before&after or only before
view merging
_query_rewrite_fudge 90 cost based query rewrite with MVs fudge factor
_query_rewrite_expression TRUE rewrite with cannonical form for expressions
_query_rewrite_jgmigrate TRUE mv rewrite with jg migration
_query_rewrite_fpc TRUE mv rewrite fresh partition containment
_query_rewrite_drj TRUE mv rewrite and drop redundant joins
_query_rewrite_maxdisjunct 257 query rewrite max disjuncts
_query_rewrite_vop_cleanup TRUE prune frocol chain before rewrite after view-mergi
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
ng
_mmv_query_rewrite_enabled TRUE allow rewrites with multiple MVs and/or base table
s
_bt_mmv_query_rewrite_enabled TRUE allow rewrites with multiple MVs and base tables
_add_stale_mv_to_dependency_list TRUE add stale mv to dependency list
_full_pwise_join_enabled TRUE enable full partition-wise join when TRUE
_partial_pwise_join_enabled TRUE enable partial partition-wise join when TRUE
_slave_mapping_enabled TRUE enable slave mapping when TRUE
_slave_mapping_group_size 0 force the number of slave group in a slave mapper
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_local_communication_costing_enabled TRUE enable local communication costing when TRUE
_local_communication_ratio 50 set the ratio between global and local communicati
on (0..100)
_parallelism_cost_fudge_factor 350 set the parallelism cost fudge factor
_left_nested_loops_random TRUE enable random distribution method for left of nest
edloops
_improved_row_length_enabled TRUE enable the improvements for computing the average
row length
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_px_index_sampling 200 parallel query sampling for index create (100000 =
100%)
_index_join_enabled TRUE enable the use of index joins
_use_nosegment_indexes FALSE use nosegment indexes in explain plan
_enable_type_dep_selectivity TRUE enable type dependent selectivity estimates
_sqlexec_progression_cost 1000 sql execution progression monitoring cost threshol
d
_improved_outerjoin_card TRUE improved outer-join cardinality calculation
_optimizer_adjust_for_nulls TRUE adjust selectivity for null values
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_optimizer_degree 0 force the optimizer to use the same degree of para
llelism
_optimizer_choose_permutation 0 force the optimizer to use the specified permutati
on
_use_column_stats_for_function TRUE enable the use of column statistics for DDP functi
ons
_subquery_pruning_cost_factor 20 subquery pruning cost factor
_subquery_pruning_reduction 50 subquery pruning reduction factor
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_subquery_pruning_enabled TRUE enable the use of subquery predicates to perform p
runing
_subquery_pruning_mv_enabled FALSE enable the use of subquery predicates with MVs to
perform pruning
_parallel_txn_global FALSE enable parallel_txn hint with updates and deletes
_or_expand_nvl_predicate TRUE enable OR expanded plan for NVL/DECODE predicate
_like_with_bind_as_equality FALSE treat LIKE predicate with bind as an equality pred
icate
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_table_scan_cost_plus_one TRUE bump estimated full table scan and index ffs cost
by one
_cost_equality_semi_join TRUE enables costing of equality semi-join
_default_non_equality_sel_check TRUE sanity check on default selectivity for like/range
predicate
_new_initial_join_orders TRUE enable initial join orders based on new ordering h
euristics
_oneside_colstat_for_equijoins TRUE sanity check on default selectivity for like/range
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
predicate
_column_tracking_level 1 column usage tracking
_optim_peek_user_binds TRUE enable peeking of user binds
_mv_refresh_selections TRUE create materialized views with selections and fast
refresh
_cursor_plan_enabled TRUE enable collection and display of cursor plans
_minimal_stats_aggregation TRUE prohibit stats aggregation at compile/partition ma
intenance time
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_mv_refresh_eut TRUE refresh materialized views using EUT(partition)-ba
sed algorithm
_mav_refresh_consistent_read TRUE refresh materialized views using consistent read s
napshot
_mav_refresh_opt 0 optimizations during refresh of materialized views
_mav_refresh_unionall_tables 3 # tables for union all expansion during materializ
ed view refresh
_mv_refresh_delta_fraction 10 delta mv as fractional percentage of size of mv
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_force_temptables_for_gsets FALSE executes concatenation of rollups using temp table
s
_pga_max_size 209715200 Maximum size of the PGA memory for one process
_smm_auto_min_io_size 56 Minimum IO size (in KB) used by sort/hash-join in
auto mode
_smm_auto_max_io_size 248 Maximum IO size (in KB) used by sort/hash-join in
auto mode
_smm_auto_cost_enabled TRUE if TRUE, use the AUTO size policy cost functions
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_smm_control 0 provides controls on the memory manager
_smm_trace 0 Turn on/off tracing for SQL memory manager
_smm_min_size 828 minimum work area size in auto mode
_smm_max_size 102400 maximum work area size in auto mode (serial)
_smm_px_max_size 414208 maximum work area size in auto mode (global)
_smm_retain_size 0 work area retain size in SGA for shared server ses
sions (0 for AUTO)
_smm_bound 0 overwrites memory manager automatically computed b
ound
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_smm_advice_log_size 0 overwrites default size of the PGA advice workarea
history log
_smm_advice_enabled TRUE if TRUE, enable v$pga_advice
_gs_anti_semi_join_allowed TRUE enable anti/semi join for the GS query
_mv_refresh_use_stats TRUE pass cardinality hints to refresh queries
_optim_new_default_join_sel TRUE improves the way default equijoin selectivity are
computed
_optimizer_dyn_smp_blks 32 number of blocks for optimizer dynamic sampling
_pre_rewrite_push_pred TRUE push predicates into views before rewrite
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_optimizer_new_join_card_computation TRUE compute join cardinality using non-rounded input v
alues
_mav_refresh_double_count_prevented FALSE materialized view MAV refreshes avoid double count
ing
_pct_refresh_double_count_prevented TRUE materialized view PCT refreshes avoid double count
ing
_mv_refresh_new_setup_disabled FALSE materialized view MV refresh new setup disabling
_load_without_compile none Load PL/SQL or Database objects without compilatio
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
n
_precompute_gid_values TRUE precompute gid values and copy them before returni
ng a row
_union_rewrite_for_gs YES_GSET_M expand queries with GSets into UNIONs for rewrite
VS
_nested_mav_fast_oncommit_enabled TRUE nested MAV refresh fast on commit allowed
_generalized_pruning_enabled TRUE controls extensions to partition pruning for gener
al predicates
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_rowsource_execution_statistics FALSE if TRUE, Oracle will collect rowsource statistics
_rowsource_statistics_sampfreq 128 frequency of rowsource statistic sampling (must be
a power of 2)
_bitmap_or_improvement_enabled TRUE controls extensions to partition pruning for gener
al predicates
_intrapart_pdml_enabled TRUE Enable intra-partition updates/deletes
_ctas_hwm_brokered_loads TRUE Enable HWM brokered CTAS
_intrapart_pdml_randomlocal_enabled TRUE Enable intra-partition updates/deletes with random
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
local dist
_optim_adjust_for_part_skews TRUE adjust stats for skews across partitions
_optimizer_compute_index_stats TRUE force index stats collection on index creation/reb
uild
_optimizer_autostats_job TRUE enable/disable auto stats collection job
_optimizer_push_pred_cost_based TRUE use cost-based query transformation for push pred
optimization
_optimizer_filter_pred_pullup TRUE use cost-based flter predicate pull up transformat
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
ion
_force_datefold_trunc FALSE force use of trunc for datefolding rewrite
_array_update_vector_read_enabled FALSE Enable array update vector read
_two_pass_reverse_polish_enabled TRUE uses two-pass reverse polish alg. to generate cano
nical forms
_expand_aggregates TRUE expand aggregates
_dump_common_subexpressions FALSE dump common subexpressions
_spr_use_hash_table FALSE use hash table for spreadsheet
_spr_use_AW_AS TRUE enable AW for hash table in spreadsheet
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_dump_connect_by_loop_data FALSE dump connect by loop error message into trc file
_column_compression_factor 0 Column compression ratio
_dml_monitoring_enabled TRUE enable modification monitoring
_px_slaves_share_cursors 0 slaves share cursors with QC
_fic_algorithm_set automatic Set Frequent Itemset Counting Algorithm
_fic_area_size 131072 size of Frequent Itemset Counting work area
_fic_max_length 20 Frequent Itemset Counting Maximum Itemset Length
_fic_min_bmsize 1024 Frequent Itemset Counting Minimum BITMAP Size
_dtree_area_size 131072 size of Decision Tree Classification work area
_dtree_pruning_enabled TRUE Decision Tree Pruning Enabled
_dtree_compressbmp_enabled TRUE Decision Tree Using Compressed Bitmaps Enabled
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_dtree_binning_enabled TRUE Decision Tree Binning Enabled
_dtree_max_surrogates 1 maximum number of surrogates
_dtree_bintest_id 0 Decision Tree Binning Test ID
_right_outer_hash_enable TRUE Right Outer/Semi/Anti Hash Enabled
_optimizer_mjc_enabled TRUE enable merge join cartesian
_optimizer_sortmerge_join_enabled TRUE enable/disable sort-merge join method
_cursor_bind_capture_area_size 400 maximum size of the cursor bind capture area
_cursor_bind_capture_interval 900 interval (in seconds) between two bind capture for
a cursor
_dump_cursor_heap_sizes FALSE dump comp/exec heap sizes to tryace file
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_projection_pushdown TRUE projection pushdown
_projection_pushdown_debug 0 level for projection pushdown debugging
_px_compilation_debug 0 debug level for parallel compilation
_px_compilation_trace 0 tracing level for parallel compilation
_fic_outofmem_candidates FALSE Frequent Itemset Counting Out Of Memory Candidates
Generation
_print_refresh_schedule false enable dbms_output of materialized view refresh sc
hedule
_optimizer_undo_changes FALSE undo changes to query optimizer
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_optimizer_percent_parallel 101 optimizer percent parallel
_optimizer_search_limit 5 optimizer search limit
_mv_refresh_costing rule refresh decision based on cost or on rules
_mv_refresh_ana 0 what percent to analyze after complete/PCT refresh
_cache_stats_monitor FALSE if TRUE, enable cache stats monitoring
_stn_trace 0 SQL tracing parameter
_sta_control 0 SQL Tuning Advisory control parameter
_ds_iocount_iosize 6553664 Dynamic Sampling Service defaults: #IOs and IO Siz
e
_mv_refsched_timeincr 300000 proportionality constant for dop vs. time in MV re
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
fresh
_spr_push_pred_refspr TRUE push predicates through reference spreadsheet
_optimizer_block_size 8192 standard block size used by optimizer
_spr_max_rules 10000 maximum number of rules in sql spreadsheet
_idxrb_rowincr 100000000 proportionality constant for dop vs. rows in index
rebuild
_hj_bit_filter_threshold 50 hash-join bit filtering threshold (0 always enable
d)
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_optimizer_save_stats TRUE enable/disable saving old versions of optimizer st
ats
_optimizer_cost_filter_pred FALSE enables costing of filter predicates in IO cost m
odel
_optimizer_correct_sq_selectivity TRUE force correct computation of subquery selectivity
_allow_commutativity TRUE allow for commutativity of +, * when comparing exp
ressions
_mv_refresh_rebuild_percentage 10 minimum percentage change required in MV to force
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
an index rebuild
_qa_control 0 Oracle internal parameter to control QA
_qa_lrg_type 0 Oracle internal parameter to specify QA lrg type
_mv_refresh_force_parallel_query 0 force materialized view refreshes to use parallel
query
_enable_fast_ref_after_mv_tbs FALSE enable fast refresh after move tablespace
_optim_dict_stats_at_db_cr_upg TRUE enable/disable dictionary stats gathering at db cr
eate/upgrade
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_optimizer_dim_subq_join_sel TRUE use join selectivity in choosing star transformati
on dimensions
_optimizer_disable_strans_sanity_checks 0 disable star transformation sanity checks
_allow_level_without_connect_by FALSE allow level without connect by
_optimizer_ignore_hints FALSE enables the embedded hints to be ignored
_optimizer_random_plan 0 optimizer seed value for random plans
_optimizer_ceil_cost TRUE CEIL cost in CBO
_delay_index_maintain TRUE delays index maintenance until after MV is refresh
ed
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_query_rewrite_setopgrw_enable TRUE perform general rewrite using set operator summari
es
_cursor_plan_hash_version 1 version of cursor plan hash value
_disable_function_based_index FALSE disable function-based index matching
_optimizer_invalidation_period 18000 time window for invalidation of cursors of analyze
d objects
_px_net_msg_cost 10000 CPU cost to send a PX message over the internconne
ct
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_px_loc_msg_cost 1000 CPU cost to send a PX message via shared memory
_smm_freeable_retain 5120 value in KB of the instance freeable PGA memory to
retain
_cursor_plan_unparse_enabled TRUE enables/disables using unparse to build projection
/predicates
_kill_java_threads_on_eoc FALSE Kill Java threads and do sessionspace migration at
end of call
_optimizer_join_order_control 3 controls the optimizer join order search algorithm
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_px_nss_planb TRUE enables or disables NSS Plan B reparse with outlin
e
_bloom_filter_enabled TRUE enables or disables bloom filter
_bloom_filter_debug 0 debug level for bloom filtering
_enable_refresh_schedule TRUE enable or disable MV refresh scheduling (revert to
9.2 behavior)
_optimizer_cartesian_enabled TRUE optimizer cartesian join enabled
_optimizer_starplan_enabled TRUE optimizer star plan enabled
_optimizer_join_elimination_enabled TRUE optimizer join elimination enabled
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_gby_hash_aggregation_enabled TRUE enable group-by and aggregation using hash scheme
_extended_pruning_enabled TRUE do runtime pruning in iterator if set to TRUE
_sql_model_unfold_forloops RUN_TIME specifies compile-time unfolding of sql model forl
oops
_enable_dml_lock_escalation TRUE enable dml lock escalation against partitioned tab
les if TRUE
_plsql_cache_enable TRUE PL/SQL Function Cache Enabled
_disable_fast_aggregation FALSE fast aggregation
_disable_adaptive_shrunk_aggregation FALSE adaptive shrunk aggregation
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_plsql_minimum_cache_hit_percent 20 plsql minimum cache hit percentage required to kee
p caching active
_plan_outline_data TRUE explain plan outline data enabled
_outline_bitmap_tree TRUE BITMAP_TREE hint enabled in outline
_drop_table_optimization_enabled TRUE reduce SGA memory use during drop of a partitioned
table
_very_large_partitioned_table 1024 very_large_partitioned_table
_drop_table_granule 256 drop_table_granule
_xpl_trace 0 Explain Plan tracing parameter
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_dm_max_shared_pool_pct 1 max percentage of the shared pool to use for a min
ing model
_optimizer_multiple_cenv generate and run plans using several compilation e
nvironments
_kql_subheap_trace 0 tracing level for library cache subheap level pins
_optimizer_cost_hjsmj_multimatch TRUE add cost of generating result set when #rows per k
ey > 1
_optimizer_transitivity_retain TRUE retain equi-join pred upon transitive equality pre
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
d generation
_px_pwg_enabled TRUE parallel partition wise group by enabled
_sql_hash_debug 0 Hash value of the SQL statement to debug
_mv_rolling_inv FALSE create/alter mv uses rolling cursor invalidation i
nstead of immediate
_smm_isort_cap 102400 maximum work area for insertion sort(v1)
_optimizer_cbqt_no_size_restriction TRUE disable cost based transformation query size restr
iction
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_windowfunc_optimization_settings 0 settings for window function optimizations
_truncate_optimization_enabled TRUE do truncate optimization if set to TRUE
_optimizer_enhanced_filter_push TRUE push filters before trying cost-based query transf
ormation
_compilation_call_heap_extent_size 16384 Size of the compilation call heaps extents
_xpl_peeked_binds_log_size 8192 maximum bytes for logging peeked bind values for V
$SQL_PLAN (0 = OFF)
_rowsrc_trace_level 0 Row source tree tracing level
_optimizer_rownum_pred_based_fkr TRUE enable the use of first K rows due to rownum predi
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
cate
_optimizer_better_inlist_costing ALL enable improved costing of index access using in-l
ist(s)
_optimizer_self_induced_cache_cost FALSE account for self-induced caching
_optimizer_min_cache_blocks 10 set minimum cached blocks
_enable_exchange_validation_using_check TRUE use check constraints on the table for validation
_optimizer_or_expansion DEPTH control or expansion approach used
_optimizer_outer_to_anti_enabled TRUE Enable transformation of outer-join to anti-join i
f possible
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_optimizer_order_by_elimination_enabled TRUE Eliminates order bys from views before query trans
formation
_optimizer_star_tran_in_with_clause TRUE enable/disable star transformation in with clause
queries
_selfjoin_mv_duplicates TRUE control rewrite self-join algorithm
_dimension_skip_null TRUE control dimension skip when null feature
_force_rewrite_enable FALSE control new query rewrite features
_optimizer_complex_pred_selectivity TRUE enable selectivity estimation for builtin function
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
s
_build_deferred_mv_skipping_mvlog_update FALSE DEFERRED MV creation skipping MV log setup update
_tsm_connect_string TSM test connect string
_tsm_disable_auto_cleanup 1 Disable TSM auto cleanup actions
_enable_nativenet_tcpip FALSE Enable skgxp driver usage for native net
_aq_tm_scanlimit 0 scan limit for Time Managers to clean up IOT
_bufq_stop_flow_control FALSE Stop enforcing flow control for buffered queues
_bufq_flow_control_thrsh 0 flow control threshold value
_bufq_max_msg_sz 0 maximum size of the message allowed in memory
_olapi_history_retention FALSE enable olapi history retention
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_olapi_session_history 300 enable olapi session history collection
_olapi_session_history_retention FALSE enable olapi session history retention
_olapi_iface_object_history 1000 enable olapi interface object history collection
_olapi_iface_object_history_retention FALSE enable olapi interface object history retention
_olapi_interface_operation_history 1000 enable olapi interface operation history collectio
n
_olapi_iface_operation_history_retention FALSE enable olapi interface operation history retention
_olapi_memory_operation_history 1000 enable olapi memory alloc/free history collection
_olapi_memory_operation_history_retentio FALSE enable olapi memory operation history retention
n
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_olapi_memory_operation_history_pause_at 0 enable olapi memory alloc/free history collection
_seqno pausing
_olap_continuous_trace_file FALSE Specify TRUE to enable continuous OLAP tracing - o
therwise only exceptional events will be logged
_olap_table_function_statistics FALSE Specify TRUE to output OLAP table function timed s
tatistics trace
_olap_parallel_update_threshold 1000 OLAP parallel update threshold in pages
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_olap_parallel_update_small_threshold 1000 OLAP parallel update threshold for number of small
pagespaces
_olap_parallel_update_small_group 400 OLAP parallel update pagespace by group
_olap_aggregate_buffer_size 1048576 OLAP Aggregate max buffer size
_olap_aggregate_min_buffer_size 1024 OLAP Aggregate min buffer size
_olap_aggregate_work_per_thread 1024 OLAP Aggregate max work parents
_olap_aggregate_min_thread_status 64 OLAP Aggregate minimum cardinality of dimensions f
or thread
_olap_aggregate_statlen_thresh 1024 OLAP Aggregate status array usage threshold
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_olap_aggregate_worklist_max 5000 OLAP Aggregate max worklists generated at once
_olap_aggregate_max_thread_tuples 5000 OLAP Aggregate max thread tuples creation
_olap_aggregate_store_probability 100 OLAP Aggregate function storeback probability
_olap_aggregate_function_merge_threshold 529 OLAP Aggregate function merge threshold
_olap_aggregate_function_cache_enabled TRUE OLAP Aggregate function cache enabler
_olap_aggregate_multipath_hier FALSE OLAP Aggregate Multi-path Hierarhies enabled
_olap_aggregate_flags 0 OLAP Aggregate debug flags
_olap_allocate_errorlog_header Dim S OLAP Allocate Errorlog Header format
ource Ba
sis
%-8d %-8s
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
%-8b Descr
iption
-------- -
------- --
------ ---
--------
_olap_allocate_errorlog_format %8p %8y %8 OLAP Allocate Errorlog Format
z %e (%n)
_olap_poutlog_echo_to_eventlog FALSE OLAP POutLog copy output to event log (tracefile)
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_olap_dbgoutfile_echo_to_eventlog FALSE OLAP DbgOutfile copy output to event log (tracefil
e)
_olap_eif_export_lob_size 2147483647 OLAP EIF Export BLOB size
_olap_sort_buffer_size 262144 OLAP Sort Buffer Size
_olap_sesscache_enabled TRUE OLAP Session Cache knob
_olap_object_hash_class 2 OLAP Object Hash Table Class
_olap_dimension_corehash_max 10000 OLAP Dimension In-Core Hash Table Maximum Size
_olap_dimension_corehash_class 1 OLAP Dimension In-Core Hash Table Class
_olap_page_pool_low 262144 OLAP Page Pool Low Watermark
_olap_page_pool_hi 50 OLAP Page Pool High Watermark
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_olap_page_pool_expand_rate 20 OLAP Page Pool Expand Rate
_olap_page_pool_shrink_rate 50 OLAP Page Pool Shrink Rate
_olap_page_pool_hit_target 100 OLAP Page Pool Hit Target
_olap_page_pool_pressure 90 OLAP Page Pool Pressure Threshold
_olap_statbool_threshold 8100 OLAP Status Boolean CBM threshold
_olap_statbool_corebits 20000000 OLAP Status Boolean max incore bits
_olap_lmgen_dim_size 100 Limitmap generator dimension column size
_olap_lmgen_meas_size 1000 Limitmap generator measure column size
_olap_wrap_errors FALSE Wrap error messages to OLAP outfile
_xsolapi_fetch_type PARTIAL OLAP API fetch type
_xsolapi_dimension_group_creation OVERFETCH OLAP API symmetric overfetch
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_xsolapi_sql_auto_measure_hints TRUE OLAP API enable automatic measure hints
_xsolapi_sql_auto_dimension_hints FALSE OLAP API enable automatic dimension hints
_xsolapi_sql_hints OLAP API generic hints
_xsolapi_sql_measure_hints OLAP API measure hints
_xsolapi_sql_dimension_hints OLAP API dimension hints
_xsolapi_sql_top_measure_hints OLAP API top measure hints
_xsolapi_sql_top_dimension_hints OLAP API top dimension hints
_xsolapi_sql_all_non_base_hints OLAP API non-base hints
_xsolapi_sql_all_multi_join_non_base_hin OLAP API multi-join non-base hints
ts
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_xsolapi_densify_cubes TABULAR OLAP API cube densification
_xsolapi_sql_optimize TRUE OLAP API enable optimization
_xsolapi_sql_remove_columns TRUE OLAP API enable remove unused columns optimization
s
_xsolapi_sql_symmetric_predicate TRUE OLAP API enable symmetric predicate for dimension
groups
_xsolapi_sql_use_bind_variables TRUE OLAP API enable bind variables optimization
_xsolapi_sql_prepare_stmt_cache_size 16 OLAP API prepare statement cache size
_xsolapi_sql_result_set_cache_size 32 OLAP API result set cache size
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_xsolapi_debug_output OLAP API debug output disposition
_xsolapi_materialize_sources TRUE OLAP API Enable source materialization
_xsolapi_load_at_process_start NEVER When to load OLAP API library at server process st
art
_xsolapi_auto_materialization_type PRED_AND_R OLAP API behavior for auto materialization
C
_xsolapi_auto_materialization_bound 20 OLAP API lower bound for auto materialization.
_xsolapi_materialization_rowcache_min_ro 1 OLAP API min number of rows required to use rowcac
ws_for_use he in query materialization
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_xsolapi_source_trace FALSE OLAP API output Source definitions to trace file
_asm_disk_repair_time 14400 seconds to wait before dropping a failing disk
_kffmap_hash_size 1024 size of kffmap_hash table
_kffmop_hash_size 2048 size of kffmop_hash table
_asm_ausize 1048576 allocation unit size
_asm_blksize 4096 metadata block size
_asm_acd_chunks 1 initial ACD chunks created
_asm_libraries ufs library search order for discovery
_asm_maxio 1048576 Maximum size of individual I/O request
_asm_allow_only_raw_disks TRUE Discovery only raw devices
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_asmlib_test 0 Osmlib test event
_asm_allow_resilver_corruption FALSE Enable disk resilvering for external redundancy
_asmsid asm ASM instance id
_asm_wait_time 18 Max/imum time to wait before asmb exits
_asm_stripewidth 8 ASM file stripe width
_asm_stripesize 131072 ASM file stripe size
_asm_droptimeout 60 timeout before offlined disks get dropped (in 3s t
icks)
_asm_emulmax 10000 max number of concurrent disks to emulate I/O erro
rs
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_asm_emultimeout 0 timeout before emulation begins (in 3s ticks)
_asm_kfdpevent 0 KFDP event
_alert_expiration 604800 seconds before an alert message is moved to except
ion queue
_alert_message_cleanup 1 Enable Alert Message Cleanup
_alert_message_purge 1 Enable Alert Message Purge
_alert_post_background 1 Enable Background Alert Posting
_swrf_test_action 0 test action parameter for SWRF
_sysaux_test_param 1 test parameter for SYSAUX
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_swrf_mmon_flush TRUE Enable/disable SWRF MMON FLushing
_awr_corrupt_mode FALSE AWR Corrupt Mode
_awr_restrict_mode FALSE AWR Restrict Mode
_swrf_mmon_metrics TRUE Enable/disable SWRF MMON Metrics Collection
_swrf_metric_frequent_mode FALSE Enable/disable SWRF Metric Frequent Mode Collectio
n
_awr_flush_threshold_metrics FALSE Enable/Disable Flushing AWR Workload Metrics
_awr_flush_workload_metrics FALSE Enable/Disable Flushing AWR Workload Metrics
_awr_disabled_flush_tables Disable flushing of specified AWR tables
_swrf_on_disk_enabled TRUE Parameter to enable/disable SWRF
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_swrf_mmon_dbfus TRUE Enable/disable SWRF MMON DB Feature Usage
_awr_mmon_cpuusage TRUE Enable/disable AWR MMON CPU Usage Tracking
_swrf_test_dbfus FALSE Enable/disable DB Feature Usage Testing
_mwin_schedule TRUE Enable/disable Maintenance Window Schedules
_awr_sql_child_limit 200 Setting for AWR SQL Child Limit
_sqltune_category_parsed DEFAULT Parsed category qualifier for applying hintsets
_ash_sampling_interval 1000 Time interval between two successive Active Sessio
n samples in millisecs
_ash_size 1048618 To set the size of the in-memory Active Session Hi
story buffers
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_ash_enable TRUE To enable or disable Active Session sampling and f
lushing
_ash_disk_write_enable TRUE To enable or disable Active Session History flushi
ng
_ash_disk_filter_ratio 10 Ratio of the number of in-memory samples to the nu
mber of samples actually written to disk
_ash_eflush_trigger 66 The percentage above which if the in-memory ASH is
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
full the emergency flusher will be triggered
_ash_sample_all FALSE To enable or disable sampling every connected sess
ion including ones waiting for idle waits
_ash_dummy_test_param 0 Oracle internal dummy ASH parameter used ONLY for
testing!
_threshold_alerts_enable 1 if 1, issue threshold-based alerts
_addm_auto_enable TRUE governs whether ADDM gets run automatically after
every AWR snapshot
NAME VALUE DESCRIPTION
---------------------------------------- ---------- --------------------------------------------------
_addm_version_check TRUE governs whether ADDM checks the input AWR snapshot
version
_addm_skiprules comma-separated list of ADDM nodes to skip
1127 rows selected.
