前回に続き、Oracle Database19cと23cの隠しパラメータを比較した結果の纏め。今回はデフォルト値や設定範囲(ALTER SESSION/SYSTEM可否)、名称変更されたと思われる隠しパラメータを記載する。
※文字数が多いためPCでの閲覧を推奨
Oracle19cと23cの隠しパラメータを比較した結果として、以下5種類について記載する。
- 23cでは廃止されているパラメータ
- 19cと23cでデフォルト値が異なるパラメータ ← 今回はココカラ
- 19cと23cで設定可能範囲が変更されているパラメータ
- 19cと23cでパラメータ名が変わった様に見えるパラメータ ← 今回はココマデ
- 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 |
新規登場した隠しパラメータは次回に。