データベースについて調べてみた

データベースについて調べたことのメモ。Oracle要素多めになる予定。

Oracle19cと23cの隠しパラメータの差分について調べてみた ~その2~

前回に続き、Oracle Database19cと23cの隠しパラメータを比較した結果の纏め。今回はデフォルト値や設定範囲(ALTER SESSION/SYSTEM可否)、名称変更されたと思われる隠しパラメータを記載する。

※文字数が多いためPCでの閲覧を推奨

 

Oracle19cと23cの隠しパラメータを比較した結果として、以下5種類について記載する。

  1. 23cでは廃止されているパラメータ
  2. 19cと23cでデフォルト値が異なるパラメータ  ← 今回はココカラ
  3. 19cと23cで設定可能範囲が変更されているパラメータ
  4. 19cと23cでパラメータ名が変わった様に見えるパラメータ  ← 今回はココマデ
  5. 19c→23cで新規登場したパラメータ

 

2.デフォルト値が変わった隠しパラメータ

19cと23cでデフォルトが変更となった隠しパラメータは下記の123個。

パラメータ名 デフォルト値(19c) デフォルト値(23c) パラメータ概要
_active_instance_count   1024 number of active instances in the cluster database
_aq_lb_cycle 120 30 Time(seconds) between consecutive AQ load balancing efforts
_aq_pt_shrink_frequency 1450 60 PT shrink window Size
_ash_compression_enable TRUE FALSE To enable or disable string compression in ASH
_asm_compatibility 11.2.0.2 18 default ASM compatibility level
_asm_enable_batch_scrub FALSE TRUE Allow scrubbing verification to run in batch mode
_asm_enable_lostwrite_scrub FALSE TRUE Allow lost write detection in scrubbing
_asm_min_compatibility 11.2.0.2 18 default mininum ASM compatibility level
_asm_read_cancel AUTO DYNAMIC Read cancel timeout in milliseconds
_awr_warehouse_enabled FALSE TRUE Enable/Disable AWR warehouse functionality
_bct_buffer_allocation_max 104857600 1073741824 maximum size of all change tracking buffer allocations, in bytes
_bct_health_check_interval 60 0 CTWR health check interval (seconds), zero to disable
_bct_mrp_timeout 600 5 CTWR MRP wait timeout (seconds), zero to wait forever
_bloom_pushing_total_max 262144 20971520 bloom filter combined pushing size upper bound (in KB)
_bsln_adaptive_thresholds_enabled TRUE FALSE Adaptive Thresholds Enabled
_cdb_special_old_xplan TRUE FALSE display old-style plan for CDB special fixed table
_cgs_combine_css_events 7 31 CGS registration flags for CSS event combining
_cgs_publish_netinfo_collect_event_chm rcfg-half-timeout,rcfg-timeout,rcfg-done,idleconn-half-timeout,idleconn-timeout-imr,idleconn-cln rcfg-half-timeout,rcfg-timeout,rcfg-done,idleconn-half-timeout,idleconn-timeout-imr,idleconn-cln,rcfg-begin,rcfg-pngchkdone,rcfg-memchg enable cgs publish collect_netinfo event to event stream for CHM
_check_block_after_checksum TRUE FALSE check block before write irrespective of db_block_checking
_controlfile_split_brain_check TRUE FALSE Check for a split-brain in distributed lock manager domain
_cp_num_hash_latches 1   connection pool number of hash latches
_cpu_util_adj_force 0 1 force cpu util adjustment
_cross_con_collection FALSE TRUE enable cross container collection
_cursor_reload_failure_threshold 0 20 Number of failed reloads before marking cursor unusable
_db_flash_cache_max_read_retry 3 10 Flash cache max read retry
_db_minimum_auxsize_percent 10 0 min percent of aux buffers to be maintained before using aux2
_db_num_evict_waitevents 64 16384 number of evict wait events
_db_xmem_hot_switch_criteria 4 195 Xmem/Pmem buffer touch count which makes this buffer candidate of switching to DRAM buffer
_dbfs_symlink_path_prefix FALSE TRUE disallow symbolic link creation in dbfs outside path_prefix
_dbrm_dynamic_threshold 989922280 17302504 DBRM dynamic threshold setting
_dbrm_runchk 32769000 32798000 DBRM kskrunstatechk thresholds
_disable_oradebug_commands none restricted disable execution of certain categories of oradebug commands
_dlm_stats_collect_mode 6 14 DLM statistics collection mode
_dm_max_shared_pool_pct 1 5 max percentage of the shared pool to use for a mining model
_dump_trace_scope globa global scope of trace dump during a process crash
_enable_dbwr_auto_tracing 0 1 enable dbwriter auto-tracing
_enable_nvm_dispatcher FALSE 0 Enable NVM Dispatcher and/or DMPROC
_enable_pluggable_database FALSE TRUE Enable Pluggable Database
_enable_replay_upgrade_diag FALSE TRUE Enable diagnostics for Replay Upgrade
_enable_securefile_flashback_opt FALSE TRUE Enable securefile flashback optimization
_enable_tcpinfo_statistics 111 238 Enable TCP Info Statistics
_federation_max_root_clones 5 4000 Maximum number of Root Clones per Root
_fg_fast_sync_spin_usecs 300 100 DAX log file sync spin time
_gc_max_downcvt 2048 0 maximum downconverts to process at one time
_gc_save_cleanout TRUE FALSE if TRUE, save cleanout to apply later
_gc_spin_time 16 0 rdma spin time
_gcr_cpu_min_hard_limit 2560 0 hard limit for min free CPU to flag high CPU
_gcr_enable_new_drm_check FALSE TRUE if FALSE, revert to old drm load metric
_gcs_partial_open_mode 0 1 partial open cache fusion service in reconfiguration
_gcs_reserved_resources 400 64 allocate the number of reserved resources in reconfiguration
_ges_direct_free_res_type ARAH ARAHHV string of resource types(s) to directly free to the freelist
_ges_gather_res_reuse_stats FALSE TRUE if TRUE, gather resource reuse statistics
_ges_resource_memory_opt 4 36 enable different level of ges res memory optimization
_global_hang_analysis_interval_secs 10 5 the interval at which global hang analysis is run
_hang_allow_resolution_on_single_nonrac FALSE DBRM Blocker Resolver allow resolution on single non-RAC instances
_high_priority_processes LMS*|LM1*|LM2*|LM3*|LM4*|LM5*|LM6*|LM7*|LM8*|LM9* NONE High Priority Process Name Mask
_highest_priority_processes VKTM VKTM|LGWR|LMS*|LM1*|LM2*|LM3*|LM4*|LM5*|LM6*|LM7*|LM8*|LM9*|RS0*|RS1*|RS2* Highest Priority Process Name Mask
_highthreshold_undoretention 4294967294 31536000 high threshold undo_retention in seconds
_inmemory_dynamic_scan_disable_threshold 10 20 Inmemory dynamic scan disable threshold
_inmemory_force_fs_tbs   SYSAUX in-memory faststart force tablespace
_inmemory_format_compatible 19.0.0 21.0.0.0.0 In-memory format compatibility parameter
_inmemory_hwm_expansion 0 1 If 0, the highwatermark CU is dropped when expanded
_inmemory_imcu_source_maxbytes 536870912 0 IMCU maximum source size in bytes
_inmemory_memprot TRUE FALSE enable or disable memory protection for in-memory
_inmemory_private_journal_numbkts 512 2048 Number of priv jrnl ht bkts
_inmemory_private_journal_numgran 255 32 Number of granules per HT node
_inmemory_transaction_options 2806 10998 in-memory transaction performance options
_instance_recovery_bloom_filter_fprate 0 0.01 Allowable false positive percentage (0.001% to 100.00%)
_instance_recovery_bloom_filter_size 83886080 10485760 Bloom filter size (in bytes) used during claim phase
_ka_locks_per_sector 4 12 locks per sector in kernel accelerator
_kdli_allow_corrupt TRUE FALSE allow corrupt filesystem_logging data blocks during read/write
_kdli_dbc   none override db_block_checking setting for securefiles
_kdli_force_storage   none force storage settings for all lobs
_kdli_inode_preference   data inline inode evolution preference (data, headless, lhb)
_kdli_preallocation_mode   length preallocation mode for lob growth
_kdli_readahead_strategy   contig shared/cached IO readahead strategy
_kdli_sio_fileopen   none shared IO fileopen mode: datasync vs nodatasync vs async
_kdli_sio_strategy   extent shared IO strategy: block vs. extent
_key_vector_max_size 0 524288 maximum key vector size (in KB)
_kffmop_chunks 42 128 number of chunks of kffmop's
_ksmd_protect_mode   off KSMD protect mode for catching stale access
_ksws_java_patching 999 0 java patching mode
_ksxp_reporting_process   LMD0 reporting process for KSXP
_kttext_warning 5 1285 tablespace pre-extension and shrink warning threshold in percentage
_lm_cache_res_type TMHWHVDI TMHWDI cache resource: string of lock types(s)
_lm_comm_tkts_calc_period_length 1000 100000 Weighted average calculation interval length (us)
_lm_drm_duration_limit   300 set drm object duration limit (secs time/object size)
_lm_drm_duration_limit_type 2 1 drm object time limit type (time/size)
_lm_drm_filter_history_window   150 drm filter history window
_lm_drm_filters 3 7 enable drm filters
_lm_drm_max_banned_objs 235 10 maximum number of objects not allowed to do drm
_lm_hash_control 1 5 bit field controlling the hashing behavior of the lock manager
_lms_rollbacks 1000 5 Maximum number of CR rollbacks per block under LMS server
_max_incident_file_size NONE 320M Maximum size (in KB, MB, GB, Blocks) of incident dump file
_max_services 8200 25000 maximum number of database services
_min_lwt_lt 24 10 minimum low threshold for LWTs
_minmax_spacebg_slaves 8 2 min-max space management background slaves
_nls_binary FALSE TRUE force binary collation
_NUMA_instance_mapping   Not specified Set of nodes that this instance should run on
_nvm_dispatchers 3 1 number of NVM Dispatcher Slaves
_optimizer_gather_stats_on_conventional_dml TRUE FALSE optimizer online stats gathering for conventional DML
_optimizer_key_vector_payload_dim_aggs FALSE TRUE enables or disables dimension payloading of aggregates in VT
_optimizer_undo_cost_change 19.1.0 23.1.0 optimizer undo cost change
_optimizer_use_stats_on_conventional_dml TRUE FALSE use optimizer statistics gathered for conventional DML
_parallel_log_write_cleanup FALSE TRUE Perform (null) log write cleanup in parallel
_pga_limit_time_until_killed 15 30 seconds to wait before killing session over limit
_rdbms_compatibility 10.1 12.2 default RDBMS compatibility level
_reconfiguration_opt_level 109 493 reconfiguration optimization level
_redo_transport_min_kbytes_sec 10 0 redo transport minimum KB/s
_result_cache_block_size 1024 4096 result cache block size
_result_cache_timeout 10 1 maximum time (sec) a session waits for a result
_rm_exadata_pdb_cpu_cnt_mult 2 1 Multiplication factor for PDB cpu count
_rm_superlong_threshold 0 300000 DBRM kskrunstatechk superlong threshold
_session_prediction_failover_buffer 0 1 session prediction failover buffer
_shared_pool_reserved_pct 5 7 percentage memory of the shared pool allocated for the reserved area
_size_of_log_table 30 100 modifying this hidden parameter, will modify the sizeof the v$gcr_log table
_spadr YES NO _SPADR
_sqlexec_hash_based_distagg_enabled FALSE TRUE enable hash based distinct aggregation for gby queries
_sqlmon_max_planlines 300 1000 Number of plan lines beyond which a plan cannot be monitored
_standby_implicit_rcv_timeout 1 3 minutes to wait for redo during standby implicit recovery
_tsenc_obfuscate_key both none Encryption key obfuscation in memory
_uts_trace_disk_threshold 0 1073741824 Trace disk threshold parameter
_uts_trace_segments 5 4 Maximum number of trace segments

 

 

3.設定可能範囲に変更があったパラメータ

ALTER SESSIONやALTER SYSTEMでの変更可否に関して差分のあった隠しパラメータは以下の17個。大半は23cでは(動的)変更可能になったものが多い。

パラメータ名 23cでの変更内容 パラメータ概要
_auto_manage_exadata_disks SESSION/SYSTEM単位で変更可になった Automate Exadata disk management
_cdb_port SYSTEM単位で変更可になった Port number for CDB
_column_level_stats SESSION単位で変更不可になった Turn column statistics collection on or off
_enable_tcpinfo_statistics SESSION/SYSTEM単位で変更可になった Enable TCP Info Statistics
_kgl_time_to_wait_for_locks SESSION/SYSTEM単位で変更可になった time to wait for locks and pins before timing out
_kse_snap_ring_size SYSTEM単位で変更不可になった ring buffer to debug internal error 17090
_lm_tickets SYSTEM単位で変更可になった GES messaging tickets
_asm_rebalance_space_errors SESSION単位で変更可になった number of out of space errors allowed before aborting rebalance
_auto_manage_exadata_disks SESSION/SYSTEM単位で変更可になった Automate Exadata disk management
_cdb_port SYSTEM単位で変更可になった Port number for CDB Servlet
_cleanup_rollback_entries SYSTEM単位で変更可になった no. of undo entries to apply per transaction cleanup
_column_level_stats SESSION単位で変更不可になった Turn column statistics collection on or off
_disable_inheritpriv_grant_public SESSION単位で変更可になった Disable inherit privilege grant to PUBLIC for newly created users
_dupt_noupdate SESSION単位で変更可になった disable duplicated table updates
_enable_tcpinfo_statistics SESSION/SYSTEM単位で変更可になった Enable TCP Info Statistics
_kdfip_debug SYSTEM単位で変更可になった memopt w debug
_kgl_time_to_wait_for_locks SESSION/SYSTEM単位で変更可になった time to wait for locks and pins before timing out
_kse_snap_ring_size SYSTEM単位で変更不可になった ring buffer to debug internal error 17090
_lm_lazy_domain_timeout SYSTEM単位で変更可になった lazy domain timeout in seconds
_lm_sq_batch_waittick SYSTEM単位で変更可になった GES send queue batching waittime in tick
_lm_tickets SYSTEM単位で変更可になった GES messaging tickets
_securefiles_breakreten_retry SESSION単位で変更可になった segment retry before dishonoring retention
_target_log_write_size SYSTEM単位で変更可になった Write size (in blocks) for overlapped redo writes
_tsenc_tracing SESSION単位で変更可になった Enable TS encryption tracing

 

 

4.隠しパラメータ名に変更があったパラメータ

実は他にもあるかもしれないが、デフォルト値や設定範囲などは同じなのに、パラメータ名の一部だけが変更された様に見えるパラメータが2つあったので、記載しておく。

パラメータ名(19c) パラメータ名(23c) パラメータ概要(19c) パラメータ概要(23c)
_connect_string_params_after_logon_triggers _connect_string_settings_after_logon_triggers set connect string session parameters after logon triggers set connect string session settings after logon triggers
_connect_string_params_unalterable _connect_string_settings_unalterable make connect string session parameters unalterable make connect string session settings unalterable

 

 

新規登場した隠しパラメータは次回に。

 

前回の記事:Oracle19cと23cの隠しパラメータの差分について調べてみた ~その1~