初期化パラメーター optimizer_features_enable の変化による影響

初期化パラメーター optimizer_features_enable

　Oracle Database の初期化パラメーターのひとつに optimizer_features_enable があります。このパラメーターの用途は マニュアル では以下のように説明されています。

OPTIMIZER_FEATURES_ENABLEは、Oracleのリリース番号に基づいて一連のオプティマイザ機能を使用可能にするためのアンブレラ・パラメータとして機能します。

　「アンブレラ・パラメータ」とは何ぞや？という疑問はありますが、パラメーターに指定されたバージョン番号に応じてオプティマイザ関連の機能を制御するものだとわかります。
　このパラメーターに指定できる値は Oracle Database 23ai では以下の通りです。デフォルト値は「23.1.0」で、データベースのバージョンと同じ値になっています。

OPTIMIZER_FEATURES_ENABLE = { 8.0.0 | 8.0.3 | 8.0.4 | 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 | 8.1.5 | 8.1.6 | 8.1.7 | 9.0.0 | 9.0.1 | 9.2.0 | 9.2.0.8 | 10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.1.0.5 | 10.2.0.1 | 10.2.0.2 | 10.2.0.3 | 10.2.0.4 | 10.2.0.5 | 11.1.0.6 | 11.1.0.7 | 11.2.0.1 | 11.2.0.2 | 11.2.0.3 | 11.2.0.4 | 12.1.0.1 | 12.1.0.2 | 12.2.0.1 | 18.1.0 | 19.1.0 | 21.1.0 | 23.1.0 }

初期化パラメーターの変更

　初期化パラメーター optimizer_features_enable の値をデフォルトの「23.1.0」から最も過去のバージョンである「8.0.0」に変更します。

初期化パラメーターの変更

SQL> SHOW PARAMETER optimizer_features_enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      23.1.0

SQL> ALTER SYSTEM SET optimizer_features_enable='8.0.0';

システムが変更されました。

SQL> SHOW PARAMETER optimizer_features_enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      8.0.0

初期化パラメーターの変化

　初期化パラメーター optimizer_features_enable を「23.1.0」から「8.0.0」に変更することにより、他の初期化パラメーターがどのように変化するかを確認しました。以下の初期化パラメーターが自動的に変化することがわかりました。ダイナミック・サンプリングが停止され、クエリー・リライトが行われない等に変化することがわかります。

初期化パラメーター	23.1.0	8.0.0	備考
optimizer_dynamic_sampling	2	0	ダイナミック・サンプリング
optimizer_mode	ALL_ROWS	CHOOSE	最適な実行計画の選定モード
optimizer_use_sql_quarantine	TRUE	FALSE	SQL隔離設定
query_rewrite_enabled	TRUE	FALSE	クエリー・リライト
skip_unusable_indexes	TRUE	FALSE	使用不可のインデックス

ただし、上記の初期化パラメーターがデフォルト値から変更されている場合は手動で変更した値が優先されるようです。以下の例では初期化パラメーター optimizer_mode を変更後に optimizer_features_enable を変更していますが、optimizer_mode は変化していません。

optimizer_mode を変更後に optimizer_features_enable を変更

SQL> SHOW PARAMETER optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

SQL> ALTER SYSTEM SET optimizer_mode='FIRST_ROWS_1';

システムが変更されました。

SQL> SHOW PARAMETER optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      FIRST_ROWS_1

SQL> SHOW PARAMETER optimizer_features_enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      23.1.0

SQL> ALTER SYSTEM SET optimizer_features_enable='8.0.0';

システムが変更されました。

SQL> SHOW PARAMETER optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      FIRST_ROWS_1

隠しパラメーターの変化

　自動的に変更されるのは通常の初期化パラメーターだけでなく、隠しパラメーターも含まれます。初期化パラメーター optimizer_features_enable の変更により隠しパラメーターがどのように変化するかを確認しました。例えば隠しパラメーター "_fast_full_scan_enabled" が TRUE から FALSE に変化します。これにより実行計画 FAST FULL SCAN が利用されなくなります。

　実行計画（23.1.0）

SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT COUNT(col1) FROM data1;

実行計画
----------------------------------------------------------
Plan hash value: 4109052708

--------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |          |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_DATA1 | 91996 |    42   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
  - dynamic statistics used: dynamic sampling (level=2)

　デフォルト状態では実行計画で INDEX FAST FULL SCAN が実行されていることがわかります。統計情報が無いためダイナミックサンプリングが行われたというログも出力されています。

　実行計画（8.0.0）

SQL> ALTER SYSTEM SET optimizer_features_enable='8.0.0';

システムが変更されました。

SQL> SELECT COUNT(col1) FROM data1;

実行計画
----------------------------------------------------------
Plan hash value: 1629554946

------------------------------------
| Id  | Operation          | Name  |
------------------------------------
|   0 | SELECT STATEMENT   |       |
|   1 |  SORT AGGREGATE    |       |
|   2 |   TABLE ACCESS FULL| DATA1 |
------------------------------------

Note
-----
   - rule based optimizer used (consider using cbo)

　実行計画として TABLE ACCESS FULL が利用されていることがわかります。初期化パラメーター optimizer_mode の変更により、rule based optimizer (RBO) が利用されていることも出力されています。

初期化パラメーター optimizer_features_enable をデフォルト値から「8.0.0」に変更することで、全体では以下の 251 個の隠しパラメーターが変化することが確認できました。オプティマイザの機能やパラレル・クエリーに関するパラメーターが変更されています。

初期化パラメーター	23.1.0	8.0.0	備考
_adaptive_window_consolidator_enabled	TRUE	FALSE
_aggregation_optimization_settings	0	32
_always_anti_join	CHOOSE	OFF
_always_semi_join	CHOOSE	OFF
_and_pruning_enabled	TRUE	FALSE
_ansi_join_mv_rewrite_enabled	TRUE	FALSE
_b_tree_bitmap_plans	TRUE	FALSE
_bloom_filter_enabled	TRUE	FALSE
_bloom_filter_ratio	35	30
_bloom_filter_setops_enabled	TRUE	FALSE
_bloom_folding_enabled	TRUE	FALSE
_bloom_pruning_enabled	TRUE	FALSE
_bloom_pruning_setops_enabled	TRUE	FALSE
_bloom_serial_filter	ON	OFF
_cell_offload_grand_total	TRUE	FALSE
_cell_offload_vector_groupby_fact_key	TRUE	FALSE
_cell_offload_vector_groupby_withnojoin	TRUE	FALSE
_complex_view_merging	TRUE	FALSE
_connect_by_use_union_all	TRUE	OLD_PLAN_MODE
_cost_equality_semi_join	TRUE	FALSE
_cpu_to_io	0	100
_dimension_skip_null	TRUE	FALSE
_distinct_agg_optimization_gsets	CHOOSE	OFF
_ds_enable_view_sampling	TRUE	FALSE
_ds_sampling_method	PROGRESSIVE	NO_QUALITY_METRIC
_ds_xt_split_count	1	0
_eliminate_common_subexpr	TRUE	FALSE
_enable_type_dep_selectivity	TRUE	FALSE
_fast_full_scan_enabled	TRUE	FALSE
_first_k_rows_dynamic_proration	TRUE	FALSE
_gby_hash_aggregation_enabled	TRUE	FALSE
_gby_vector_aggregation_enabled	TRUE	FALSE
_generalized_pruning_enabled	TRUE	FALSE
_globalindex_pnum_filter_enabled	TRUE	FALSE
_gs_anti_semi_join_allowed	TRUE	FALSE
_hcs_enable_pred_push	TRUE	FALSE
_improved_outerjoin_card	TRUE	FALSE
_improved_row_length_enabled	TRUE	FALSE
_index_join_enabled	TRUE	FALSE
_inmemory_gd_sym_dict_flags	1	0
_inmemory_hpk4sql_flags	0	1
_inmemory_parallel_jghj_enabled	TRUE	FALSE
_json_qryovergen_rewrite	TRUE	FALSE
_key_vector_create_pushdown_threshold	20000	0
_key_vector_double_enabled	TRUE	FALSE
_key_vector_join_pushdown_enabled	TRUE	FALSE
_key_vector_timestamp_enabled	TRUE	FALSE
_left_nested_loops_random	TRUE	FALSE
_local_communication_costing_enabled	TRUE	FALSE
_minimal_stats_aggregation	TRUE	FALSE
_mmv_query_rewrite_enabled	TRUE	FALSE
_mv_access_compute_fresh_data	ON	OFF
_new_initial_join_orders	TRUE	FALSE
_new_sort_cost_estimate	TRUE	FALSE
_nlj_batching_enabled	1	0
_optim_adjust_for_part_skews	TRUE	FALSE
_optim_enhance_nnull_detection	TRUE	FALSE
_optim_new_default_join_sel	TRUE	FALSE
_optim_peek_user_binds	TRUE	FALSE
_optimizer_adaptive_cursor_sharing	TRUE	FALSE
_optimizer_ads_use_partial_results	TRUE	FALSE
_optimizer_ads_use_spd_cache	TRUE	FALSE
_optimizer_aggr_groupby_elim	TRUE	FALSE
_optimizer_ansi_join_lateral_enhance	TRUE	FALSE
_optimizer_ansi_rearchitecture	TRUE	FALSE
_optimizer_band_join_aware	TRUE	FALSE
_optimizer_batch_table_access_by_rowid	TRUE	FALSE
_optimizer_better_inlist_costing	ALL	OFF
_optimizer_cbqt_no_size_restriction	TRUE	FALSE
_optimizer_cbqt_or_expansion	ON	OFF
_optimizer_cluster_by_rowid_batched	TRUE	FALSE
_optimizer_cluster_by_rowid_control	129	3
_optimizer_cluster_by_rowid	TRUE	FALSE
_optimizer_coalesce_subqueries	TRUE	FALSE
_optimizer_complex_pred_selectivity	TRUE	FALSE
_optimizer_compute_index_stats	TRUE	FALSE
_optimizer_connect_by_combine_sw	TRUE	FALSE
_optimizer_connect_by_cost_based	TRUE	FALSE
_optimizer_connect_by_elim_dups	TRUE	FALSE
_optimizer_control_shard_qry_processing	65280	65337
_optimizer_correct_sq_selectivity	TRUE	FALSE
_optimizer_cost_based_transformation	LINEAR	OFF
_optimizer_cost_hjsmj_multimatch	TRUE	FALSE
_optimizer_cost_model	CHOOSE	IO
_optimizer_cube_join_enabled	TRUE	FALSE
_optimizer_dim_subq_join_sel	TRUE	FALSE
_optimizer_distinct_agg_transform	TRUE	FALSE
_optimizer_distinct_elimination	TRUE	FALSE
_optimizer_distinct_placement	TRUE	FALSE
_optimizer_eliminate_filtering_join	TRUE	FALSE
_optimizer_eliminate_subquery	TRUE	FALSE
_optimizer_enable_density_improvements	TRUE	FALSE
_optimizer_enable_extended_stats	TRUE	FALSE
_optimizer_enable_plsql_stats	TRUE	FALSE
_optimizer_enable_table_lookup_by_nl	TRUE	FALSE
_optimizer_enhanced_filter_push	TRUE	FALSE
_optimizer_enhanced_join_elimination	TRUE	FALSE
_optimizer_exists_to_any_rewrite	TRUE	FALSE
_optimizer_extend_jppd_view_types	TRUE	FALSE
_optimizer_extended_cursor_sharing_rel	SIMPLE	NONE
_optimizer_extended_cursor_sharing	UDO	NONE
_optimizer_extended_stats_usage_control	192	255
_optimizer_false_filter_pred_pullup	TRUE	FALSE
_optimizer_fast_access_pred_analysis	TRUE	FALSE
_optimizer_fast_pred_transitivity	TRUE	FALSE
_optimizer_filter_pred_pullup	TRUE	FALSE
_optimizer_fkr_index_cost_bias	10	2
_optimizer_full_outer_join_to_outer	TRUE	FALSE
_optimizer_gather_feedback	TRUE	FALSE
_optimizer_gather_stats_on_load_index	TRUE	FALSE
_optimizer_gather_stats_on_load	TRUE	FALSE
_optimizer_group_by_placement	TRUE	FALSE
_optimizer_hybrid_fpwj_enabled	TRUE	FALSE
_optimizer_improve_selectivity	TRUE	FALSE
_optimizer_inmemory_access_path	TRUE	FALSE
_optimizer_inmemory_autodop	TRUE	FALSE
_optimizer_inmemory_bloom_filter	TRUE	FALSE
_optimizer_inmemory_cluster_aware_dop	TRUE	FALSE
_optimizer_inmemory_gen_pushable_preds	TRUE	FALSE
_optimizer_inmemory_minmax_pruning	TRUE	FALSE
_optimizer_inmemory_table_expansion	TRUE	FALSE
_optimizer_inmemory_use_stored_stats	AUTO	NEVER
_optimizer_interleave_jppd	TRUE	FALSE
_optimizer_interleave_or_expansion	TRUE	FALSE
_optimizer_join_elimination_enabled	TRUE	FALSE
_optimizer_join_factorization	TRUE	FALSE
_optimizer_join_order_control	3	0
_optimizer_join_sel_sanity_check	TRUE	FALSE
_optimizer_key_vector_payload_dim_aggs	TRUE	FALSE
_optimizer_key_vector_payload	TRUE	FALSE
_optimizer_key_vector_pruning_enabled	TRUE	FALSE
_optimizer_max_permutations	300	80000
_optimizer_mode_force	TRUE	FALSE
_optimizer_multi_level_push_pred	TRUE	FALSE
_optimizer_multi_table_outerjoin	TRUE	FALSE
_optimizer_multicol_join_elimination	TRUE	FALSE
_optimizer_native_full_outer_join	FORCE	OFF
_optimizer_new_join_card_computation	TRUE	FALSE
_optimizer_nlj_hj_adaptive_join	TRUE	FALSE
_optimizer_null_accepting_semijoin	TRUE	FALSE
_optimizer_null_aware_antijoin	TRUE	FALSE
_optimizer_or_expansion	DEPTH	BREADTH
_optimizer_order_by_elimination_enabled	TRUE	FALSE
_optimizer_outer_to_anti_enabled	TRUE	FALSE
_optimizer_partial_join_eval	TRUE	FALSE
_optimizer_proc_rate_level	BASIC	OFF
_optimizer_push_gby_into_union_all	TRUE	FALSE
_optimizer_push_pred_cost_based	TRUE	FALSE
_optimizer_reduce_groupby_key	TRUE	FALSE
_optimizer_rownum_bind_default	10	0
_optimizer_rownum_pred_based_fkr	TRUE	FALSE
_optimizer_skip_scan_enabled	TRUE	FALSE
_optimizer_sortmerge_join_inequality	TRUE	FALSE
_optimizer_squ_bottomup	TRUE	FALSE
_optimizer_star_tran_in_with_clause	TRUE	FALSE
_optimizer_strans_adaptive_pruning	TRUE	FALSE
_optimizer_subsume_vw_sq	ON	OFF
_optimizer_system_stats_usage	TRUE	FALSE
_optimizer_table_expansion	TRUE	FALSE
_optimizer_transitivity_retain	TRUE	FALSE
_optimizer_try_st_before_jppd	TRUE	FALSE
_optimizer_undo_cost_change	23.1.0	8.0.0
_optimizer_union_all_gsets	TRUE	FALSE
_optimizer_unnest_corr_set_subq	TRUE	FALSE
_optimizer_unnest_disjunctive_subq	TRUE	FALSE
_optimizer_unnest_scalar_sq	TRUE	FALSE
_optimizer_use_auto_indexes	AUTO	OFF
_optimizer_use_cbqt_star_transformation	TRUE	FALSE
_optimizer_use_feedback	TRUE	FALSE
_optimizer_use_gtt_session_stats	TRUE	FALSE
_optimizer_use_table_scanrate	HADOOP_ONLY	OFF
_optimizer_use_xt_rowid	TRUE	FALSE
_optimizer_vector_base_dim_fact_factor	200	0
_optimizer_vector_index	ON	OFF
_optimizer_vector_transformation	TRUE	FALSE
_optimizer_wc_filter_pushdown	TRUE	FALSE
_or_expand_nvl_predicate	TRUE	FALSE
_ordered_nested_loop	TRUE	FALSE
_parallel_broadcast_enabled	TRUE	FALSE
_partition_view_enabled	TRUE	FALSE
_pre_rewrite_push_pred	TRUE	FALSE
_pred_move_around	TRUE	FALSE
_push_join_predicate	TRUE	FALSE
_push_join_union_view2	TRUE	FALSE
_push_join_union_view	TRUE	FALSE
_pwise_distinct_enabled	TRUE	FALSE
_px_adaptive_dist_method	CHOOSE	OFF
_px_adaptive_dist_nij_enabled	ON	OFF
_px_concurrent	TRUE	FALSE
_px_cpu_autodop_enabled	TRUE	FALSE
_px_dist_agg_partial_rollup_pushdown	ADAPTIVE	OFF
_px_dynamic_granules_adjust	10	0
_px_dynamic_granules	TRUE	FALSE
_px_extended_join_skew_handling	TRUE	FALSE
_px_external_table_default_stats	TRUE	FALSE
_px_filter_parallelized	TRUE	FALSE
_px_filter_skew_handling	TRUE	FALSE
_px_groupby_pushdown	FORCE	CHOOSE
_px_hybrid_partition_execution_enabled	TRUE	FALSE
_px_hybrid_partition_skew_threshold	10	255
_px_join_skew_handling	TRUE	FALSE
_px_join_skew_null_handling	TRUE	FALSE
_px_join_skew_sampling_time_limit	50	0
_px_join_skew_use_histogram	TRUE	FALSE
_px_minus_intersect	TRUE	FALSE
_px_nlj_bcast_rr_threshold	10	65535
_px_object_sampling_enabled	TRUE	FALSE
_px_parallelize_expression	TRUE	FALSE
_px_parallelize_non_native_datatype	TRUE	FALSE
_px_partial_rollup_pushdown	ADAPTIVE	OFF
_px_partition_load_skew_handling	ON	OFF
_px_partition_load_skew_threshold	3	0
_px_partition_scan_enabled	TRUE	FALSE
_px_partition_skew_threshold	80	0
_px_pwg_enabled	TRUE	FALSE
_px_pwise_wif_enabled	TRUE	FALSE
_px_replication_enabled	TRUE	FALSE
_px_scalable_gby_invdist	TRUE	FALSE
_px_scalable_invdist_mcol	TRUE	FALSE
_px_scalable_invdist	TRUE	FALSE
_px_single_server_enabled	TRUE	FALSE
_px_ual_serial_input	TRUE	FALSE
_px_wif_dfo_declumping	CHOOSE	OFF
_px_wif_extend_distribution_keys	TRUE	FALSE
_px_window_skew_handling	TRUE	FALSE
_query_rewrite_setopgrw_enable	TRUE	FALSE
_query_rewrite_use_on_query_computation	TRUE	FALSE
_recursive_with_branch_iterations	7	1
_recursive_with_parallel	TRUE	FALSE
_remove_aggr_subquery	TRUE	FALSE
_replace_virtual_columns	TRUE	FALSE
_right_outer_hash_enable	TRUE	FALSE
_selfjoin_mv_duplicates	TRUE	FALSE
_slave_mapping_skew_handling	TRUE	FALSE
_sql_model_unfold_forloops	RUN_TIME	COMPILE_TIME
_sqlexec_hash_based_distagg_enabled	TRUE	FALSE
_sqlexec_hash_based_distagg_ser_civ_enabled	TRUE	FALSE
_sqlexec_hash_based_distagg_ssf_enabled	TRUE	FALSE
_sqlexec_hash_based_set_operation_enabled	TRUE	FALSE
_sqlexec_hash_rollup_enabled	TRUE	FALSE
_sqlexec_pwiseops_with_binds_enabled	TRUE	FALSE
_sqlexec_pwiseops_with_sqlfuncs_enabled	TRUE	FALSE
_sqlexec_reorder_wif_enabled	TRUE	FALSE
_sqlexec_window_function_settings	63	0
_subquery_pruning_enabled	TRUE	FALSE
_table_scan_cost_plus_one	TRUE	FALSE
_union_rewrite_for_gs	YES_GSET_MVS	OFF
_unnest_subquery	TRUE	FALSE
_use_column_stats_for_function	TRUE	FALSE
_vector_encoding_mode	MANUAL	OFF
_xt_sampling_scan_granules	ON	OFF

その他の機能

　初期化パラメーター optimizer_features_enable は名前の通り、オプティマイザの動作をバージョンに合わせて変化させるものです。このためオプティマイザに関係ない機能は制約を受けません。例えば Tuned Undo Retension は Oracle Database 12c の新機能ですが、optimizer_features_enable を変更しても関連する初期化パラメーターに変化はありません。

初期化パラメーター	23.1.0	8.0.0	備考
_undo_autotune	TRUE	TRUE	Tuned Undo Retension 機能
_undo_block_compression	TRUE	TRUE
_undo_debug_mode	0	0
_undo_debug_usage	0	0
_undo_ofd_enabled	TRUE	TRUE
_undo_ofd_excess	10	10
_undo_ofd_reserve	30	30
_undo_tbs_slave_percent	0	0
_undotbs_regular_tables	FALSE	FALSE
_undotbs_stepdown_pcent	75	75

Author: Noriyoshi Shinoda / Date: September 2, 2025