Oracle hidden optimizer parameters Don Burleson

Note: Oracle hidden parameters are undocumented and unsupported by Oracle.
Make sure that you thoroughly test all hidden parameters before placing them in your production environment.

The Oracle cost-based optimizer is extremely powerful and there are many documented parameters that influence it's behavior.  Also, optimizer statistics (dbms_stats) have a profound impact on Oracle optimizer behavior.

Parameter Name                            Description

----------------------------------------- --------------------------------------------------------------

_always_anti_join                         always use this method for anti-join when possible

_always_semi_join                         always use this method for semi-join when possible

_always_star_transformation               always favor use of star transformation

_cost_equality_semi_join                  enables costing of equality semi-join

_cpu_count                                current number of cpu's for this instance

_cpu_to_io                                divisor for converting CPU cost to I/O cost

_cr_server_log_flush                      if TRUE, flush redo log before serving a CR buffer (DFS)

_cursor_db_buffers_pinned                 additional number of buffers a cursor can pin at once

_cursor_plan_enabled                      enable collection and display of cursor plans

_index_join_enabled                       enable the use of index joins

_index_prefetch_factor                    index prefetching factor

_left_nested_loops_random                 enable random distribution method for left of nestedloops

_optim_adjust_for_part_skews              adjust stats for skews across partitions

_optim_enhance_nnull_detection            TRUE to enable index [fast] full scan more often

_optim_new_default_join_sel               improves the way default equijoin selectivity are computed

_optim_peek_user_binds                    enable peeking of user binds

_optimizer_adjust_for_nulls               adjust selectivity for null values

_optimizer_choose_permutation             force the optimizer to use the specified permutation

_optimizer_cost_model                     optimizer cost model

_optimizer_degree                         force the optimizer to use the same degree of parallelism

_optimizer_dyn_smp_blks                   number of blocks for optimizer dynamic sampling

_optimizer_mode_force                     force setting of optimizer mode for user recursive SQL also

_optimizer_new_join_card_computation      compute join cardinality using non-rounded input values

_optimizer_percent_parallel               optimizer percent parallel

_optimizer_search_limit                   optimizer search limit

_optimizer_system_stats_usage             system statistics usage

_optimizer_undo_changes                   undo changes to query optimizer

_optimizer_undo_cost_change               optimizer undo cost change

_or_expand_nvl_predicate                  enable OR expanded plan for NVL/DECODE predicate

_query_cost_rewrite                       perform the cost based rewrite with materialized views

_query_rewrite_1                          perform query rewrite before&after or only before view merging

_query_rewrite_2                          perform query rewrite before&after or only after view merging

_query_rewrite_drj                        mv rewrite and drop redundant joins

_query_rewrite_expression                 rewrite with cannonical form for expressions

_query_rewrite_fpc                        mv rewrite fresh partition containment

_query_rewrite_fudge                      cost based query rewrite with MVs fudge factor

_query_rewrite_jgmigrate                  mv rewrite with jg migration

_query_rewrite_maxdisjunct                query rewrite max disjuncts

_query_rewrite_or_error                   allow query rewrite, if referenced tables are not dataless

_query_rewrite_vop_cleanup                prune frocol chain before rewrite after view-merging

_ordered_nested_loop                      enable ordered nested loop costing

_ordered_semijoin                         enable ordered semi-join subquery

_union_rewrite_for_gs                     expand queries with GSets into UNIONs for rewrite

_unnest_notexists_sq                      unnest NOT EXISTS subquery with one or more tables if possible

_unnest_subquery                          enables unnesting of correlated subqueries

_nested_loop_fudge                        nested loop fudge

_nested_mav_fast_oncommit_enabled         nested MAV refresh fast on commit allowed

_new_initial_join_orders                  enable initial join orders based on new ordering heuristics

_new_sort_cost_estimate                   enables the use of new cost estimate for sort

_multi_join_key_table_lookup              TRUE iff multi-join-key table lookup prefetch is enabled

_like_with_bind_as_equality               treat LIKE predicate with bind as an equality predicate

_push_join_predicate                      enable pushing join predicate inside a view

_push_join_union_view                     enable pushing join predicate inside a union view

_partial_pwise_join_enabled               enable partial partition-wise join when TRUE

_small_table_threshold                    threshold level of table size for direct reads

We also see these obscure undocumented optimizer parameters in Oracle 10g:

_add_stale_mv_to_dependency_list
_always_anti_join
_always_semi_join
_always_star_transformation
_b_tree_bitmap_plans
_bloom_filter_enabled
_bt_mmv_query_rewrite_enabled
_complex_view_merging
_convert_set_to_join
_cost_equality_semi_join
_cpu_to_io
_db_file_optimizer_read_count
_default_non_equality_sel_check
_dimension_skip_null
_disable_datalayer_sampling
_disable_function_based_index
_distinct_view_unnesting
_dm_max_shared_pool_pct
_dml_monitoring_enabled
_eliminate_common_subexpr
_enable_dml_lock_escalation
_enable_type_dep_selectivity
_extended_pruning_enabled
_fast_full_scan_enabled
_fic_area_size
_force_datefold_trunc
_force_rewrite_enable
_force_temptables_for_gsets
_full_pwise_join_enabled
_gby_
_generalized_pruning_enabled
_gs_anti_semi_join_allowed
_hash_join_enabled
_hash_multiblock_io_count
_improved_outerjoin_card
_improved_row_length_enabled
_index_join_enabled
_left_nested_loops_random
_like_with_bind_as_equality
_local_communication_costing_enabled
_local_communication_ratio
_minimal_stats_aggregation
_mmv_query_rewrite_enabled
_nested_loop_fudge
_new_initial_join_orders
_new_sort_cost_estimate
_no_or_expansion
_oneside_colstat_for_equijoins
_optim_adjust_for_part_skews
_optim_enhance_nnull_detection
_optim_new_default_join_sel
_optim_peek_user_binds
_optimizer_adjust_for_nulls
_optimizer_better_inlist_costing
_optimizer_block_size
_optimizer_cache_stats
_optimizer_cartesian_enabled
_optimizer_cbqt_factor
_optimizer_cbqt_no_size_restriction
_optimizer_complex_pred_selectivity
_optimizer_compute_index_stats
_optimizer_correct_sq_selectivity
_optimizer_cost_based_transformation 
_optimizer_cost_filter_pred
_optimizer_cost_hjsmj_multimatch
_optimizer_cost_model
_optimizer_degree
_optimizer_dim_subq_join_sel
_optimizer_disable_strans_sanity_checks
_optimizer_enhanced_filter_push
_optimizer_extended_cursor_sharing
_optimizer_filter_pred_pullup
_optimizer_ignore_hints
_optimizer_join_elimination_enabled
_optimizer_join_order_control
_optimizer_join_sel_sanity_check
_optimizer_max_permutations
_optimizer_min_cache_blocks
_optimizer_mjc_enabled
_optimizer_mode_force
_optimizer_new_join_card_computation
_optimizer_or_expansion
_optimizer_order_by_elimination_enabled
_optimizer_outer_to_anti_enabled
_optimizer_percent_parallel
_optimizer_push_down_distinct
_optimizer_push_pred_cost_based
_optimizer_random_plan
_optimizer_rownum_pred_based_fkr
_optimizer_search_limit
_optimizer_self_induced_cache_cost
_optimizer_skip_scan_enabled
_optimizer_sortmerge_join_enabled
_optimizer_squ_bottomup
_optimizer_star_tran_in_with_clause
_optimizer_starplan_enabled
_optimizer_system_stats_usage
_optimizer_transitivity_retain
_optimizer_undo_changes
_optimizer_undo_cost_change
_or_expand_nvl_predicate
_ordered_nested_loop
_parallel_broadcast_enabled
_partial_pwise_join_enabled
_partition_view_enabled
_pga_max_size
_pre_rewrite_push_pred
_pred_move_around
_predicate_elimination_enabled
_project_view_columns
_push_join_predicate
_push_join_union_view
_push_join_union_view2
_px_broadcast_fudge_factor
_px_pwg_enabled
_query_cost_rewrite
_query_rewrite_1
_query_rewrite_2
_query_rewrite_drj
_query_rewrite_expression
_query_rewrite_fpc
_query_rewrite_fudge
_query_rewrite_jgmigrate
_query_rewrite_maxdisjunct
_query_rewrite_or_error
_query_rewrite_setopgrw_enable
_query_rewrite_vop_cleanup
_remove_aggr_subquery
_right_outer_hash_enable
_rowsrc_trace_level
_selfjoin_mv_duplicates
_simple_view_merging
_slave_mapping_enabled
_smm_auto_cost_enabled
_smm_auto_max_io_size
_smm_auto_min_io_size
_smm_max_size
_smm_min_size
_smm_px_max_size
_sort_elimination_cost_ratio
_sort_multiblock_read_count
_spr_push_pred_refspr
_sql_model_unfold_forloops
_subquery_pruning_enabled
_subquery_pruning_mv_enabled
_system_index_caching
_table_scan_cost_plus_one
_union_rewrite_for_gs
_unnest_subquery
_update_bji_ipdml_enabled
_use_column_stats_for_function

There are also these undocumented optimizer parameters in Oracle 10g:

_always_star_transformation
_b_tree_bitmap_plans
_complex_view_merging
_default_non_equality_sel_check
_enable_type_dep_selectivity
_fast_full_scan_enabled
_improved_outerjoin_card
_improved_row_length_enabled
_index_join_enabled
_like_with_bind_as_equality
_nested_loop_fudge
_new_initial_join_orders
_no_or_expansion
_oneside_colstat_for_equijoins
_optim_enhance_nnull_detection
_optimizer_adjust_for_nulls
_optimizer_choose_permutation
_optimizer_mode_force
_optimizer_undo_changes
_or_expand_nvl_predicate
_ordered_nested_loop
_push_join_predicate
_push_join_union_view
_query_cost_rewrite
_sort_elimination_cost_ratio
_sortmerge_inequality_join_off
_subquery_pruning_cost_factor
_subquery_pruning_enabled
_subquery_pruning_reduction_factor
_table_scan_cost_plus_one
_unnest_subquery
_use_column_stats_for_function
_use_nosegment_indexes

Here are some hidden optimizer parameters from Oracle 11g release 2:

_optim_adjust_for_part_skews=TRUE
_optim_enhance_nnull_detection=TRUE
_optim_new_default_join_sel=TRUE
_optim_peek_user_binds=TRUE
_optimizer_adaptive_cursor_sharing=TRUE
_optimizer_better_inlist_costing='ALL'
_optimizer_cbqt_no_size_restriction=TRUE
_optimizer_coalesce_subqueries=TRUE
_optimizer_complex_pred_selectivity=TRUE
_optimizer_compute_index_stats=TRUE
_optimizer_connect_by_combine_sw=TRUE
_optimizer_connect_by_cost_based=TRUE
_optimizer_connect_by_elim_dups=TRUE
_optimizer_correct_sq_selectivity=TRUE
_optimizer_cost_based_transformation='LINEAR'
_optimizer_cost_hjsmj_multimatch=TRUE
_optimizer_cost_model='CHOOSE'
_optimizer_dim_subq_join_sel=TRUE
_optimizer_distinct_agg_transform=TRUE
_optimizer_distinct_elimination=TRUE
_optimizer_distinct_placement=TRUE
_optimizer_eliminate_filtering_join=TRUE
_optimizer_enable_density_improvements=TRUE
_optimizer_enable_extended_stats=TRUE
_optimizer_enable_table_lookup_by_nl=TRUE
_optimizer_enhanced_filter_push=TRUE
_optimizer_extend_jppd_view_types=TRUE
_optimizer_extended_cursor_sharing='UDO'
_optimizer_extended_cursor_sharing_rel='SIMPLE'
_optimizer_extended_stats_usage_control=192
_optimizer_false_filter_pred_pullup=TRUE
_optimizer_fast_access_pred_analysis=TRUE
_optimizer_fast_pred_transitivity=TRUE
_optimizer_filter_pred_pullup=TRUE
_optimizer_fkr_index_cost_bias=10
_optimizer_full_outer_join_to_outer=TRUE
_optimizer_group_by_placement=TRUE
_optimizer_improve_selectivity=TRUE
_optimizer_interleave_jppd=TRUE
_optimizer_join_elimination_enabled=TRUE
_optimizer_join_factorization=TRUE
_optimizer_join_order_control=3
_optimizer_join_sel_sanity_check=TRUE
_optimizer_max_permutations=2000
_optimizer_mode_force=TRUE
_optimizer_multi_level_push_pred=TRUE
_optimizer_native_full_outer_join='FORCE'
_optimizer_new_join_card_computation=TRUE
_optimizer_null_aware_antijoin=TRUE
_optimizer_or_expansion='DEPTH'
_optimizer_order_by_elimination_enabled=TRUE
_optimizer_outer_join_to_inner=TRUE
_optimizer_outer_to_anti_enabled=TRUE
_optimizer_push_down_distinct=0
_optimizer_push_pred_cost_based=TRUE
_optimizer_rownum_bind_default=10
_optimizer_rownum_pred_based_fkr=TRUE
_optimizer_skip_scan_enabled=TRUE
_optimizer_sortmerge_join_inequality=TRUE
_optimizer_squ_bottomup=TRUE
_optimizer_star_tran_in_with_clause=TRUE
_optimizer_system_stats_usage=TRUE
_optimizer_table_expansion=TRUE
_optimizer_transitivity_retain=TRUE
_optimizer_try_st_before_jppd=TRUE
_optimizer_undo_cost_change='11.2.0.3'
_optimizer_unnest_corr_set_subq=TRUE
_optimizer_unnest_disjunctive_subq=TRUE
_optimizer_use_cbqt_star_transformation=TRUE
_optimizer_use_feedback=TRUE