_optimizer hidden parameter 비교(9.2.0.8 - 10.2.0.4)
session value = 10.2.0.4
instance value = 9.2.0.8
PARAMETER DESCRIPTION SESSION_VALUE INSTANCE_VALUE
---------------------------------------- ----------------------------------------------------------------- --------------- ------------
_db_file_optimizer_read_count multiblock read count for regular clients 16 16
_optimizer_adjust_for_nulls adjust selectivity for null values TRUE TRUE
_optimizer_autostats_job enable/disable auto stats collection job TRUE TRUE
_optimizer_better_inlist_costing enable improved costing of index access using in-list(s) ALL OFF #
_optimizer_block_size standard block size used by optimizer 8192 8192
_optimizer_cache_stats cost with cache statistics FALSE FALSE
_optimizer_cartesian_enabled optimizer cartesian join enabled TRUE TRUE
_optimizer_cbqt_factor cost factor for cost-based query transformation 50 50
_optimizer_cbqt_no_size_restriction disable cost based transformation query size restriction TRUE FALSE
_optimizer_ceil_cost CEIL cost in CBO TRUE TRUE
_optimizer_choose_permutation force the optimizer to use the specified permutation 0 0
_optimizer_complex_pred_selectivity enable selectivity estimation for builtin functions TRUE FALSE
_optimizer_compute_index_stats force index stats collection on index creation/rebuild TRUE FALSE
_optimizer_connect_by_combine_sw combine no filtering connect by and start with TRUE FALSE
_optimizer_connect_by_cost_based use cost-based transformation for connect by TRUE FALSE
_optimizer_correct_sq_selectivity force correct computation of subquery selectivity TRUE FALSE
_optimizer_cost_based_transformation enables cost-based query transformation LINEAR OFF #
_optimizer_cost_filter_pred enables costing of filter predicates in IO cost model FALSE FALSE
_optimizer_cost_hjsmj_multimatch add cost of generating result set when #rows per key > 1 TRUE FALSE
_optimizer_cost_model optimizer cost model CHOOSE CHOOSE
_optimizer_degree force the optimizer to use the same degree of parallelism 0 0
_optimizer_dim_subq_join_sel use join selectivity in choosing star transformation dimensions TRUE FALSE
_optimizer_disable_strans_sanity_checks disable star transformation sanity checks 0 0
_optimizer_dyn_smp_blks number of blocks for optimizer dynamic sampling 32 32
_optimizer_enable_density_improvements use improved density computation for selectivity estimation FALSE FALSE
_optimizer_enhanced_filter_push push filters before trying cost-based query transformation TRUE FALSE #
_optimizer_extended_cursor_sharing optimizer extended cursor sharing UDO NONE #
_optimizer_filter_pred_pullup use cost-based flter predicate pull up transformation TRUE FALSE #
_optimizer_fkr_index_cost_bias Optimizer index bias over FTS/IFFS under first K rows mode 10 2 #
_optimizer_ignore_hints enables the embedded hints to be ignored FALSE FALSE
_optimizer_invalidation_period time window for invalidation of cursors of analyzed objects 18000 18000
_optimizer_join_elimination_enabled optimizer join elimination enabled TRUE FALSE
_optimizer_join_order_control controls the optimizer join order search algorithm 3 0 #
_optimizer_join_sel_sanity_check enable/disable sanity check for multi-column join selectivity TRUE FALSE
_optimizer_max_permutations optimizer maximum join permutations per query block 2000 2000
_optimizer_min_cache_blocks set minimum cached blocks 10 10
_optimizer_mjc_enabled enable merge join cartesian TRUE TRUE
_optimizer_mode_force force setting of optimizer mode for user recursive SQL also TRUE TRUE
_optimizer_multiple_cenv generate and run plans using several compilation environments
_optimizer_native_full_outer_join execute full outer join using native implementaion off off
_optimizer_new_join_card_computation compute join cardinality using non-rounded input values TRUE TRUE
_optimizer_or_expansion control or expansion approach used DEPTH BREADTH #
_optimizer_or_expansion_subheap Use subheap for optimizer or-expansion TRUE TRUE
_optimizer_order_by_elimination_enabled Eliminates order bys from views before query transformation TRUE FALSE #
_optimizer_outer_to_anti_enabled Enable transformation of outer-join to anti-join if possible TRUE FALSE #
_optimizer_percent_parallel optimizer percent parallel 101 101
_optimizer_push_down_distinct push down distinct from query block to table 0 0
_optimizer_push_pred_cost_based use cost-based query transformation for push pred optimization TRUE FALSE #
_optimizer_random_plan optimizer seed value for random plans 0 0
_optimizer_rownum_bind_default Default value to use for rownum bind 10 0 #
_optimizer_rownum_pred_based_fkr enable the use of first K rows due to rownum predicate TRUE FALSE #
_optimizer_save_stats enable/disable saving old versions of optimizer stats TRUE TRUE
_optimizer_search_limit optimizer search limit 5 5
_optimizer_self_induced_cache_cost account for self-induced caching FALSE FALSE
_optimizer_skip_scan_enabled enable/disable index skip scan TRUE TRUE
_optimizer_skip_scan_guess consider index skip scan for predicates with guessed selectivity FALSE FALSE
_optimizer_sortmerge_join_enabled enable/disable sort-merge join method TRUE TRUE
_optimizer_sortmerge_join_inequality enable/disable sort-merge join using inequality predicates TRUE TRUE
_optimizer_squ_bottomup enables unnesting of subquery in a bottom-up manner TRUE FALSE #
_optimizer_star_tran_in_with_clause enable/disable star transformation in with clause queries TRUE FALSE #
_optimizer_star_trans_min_cost optimizer star transformation minimum cost 0 0
_optimizer_star_trans_min_ratio optimizer star transformation minimum ratio 0 0
_optimizer_starplan_enabled optimizer star plan enabled TRUE TRUE
_optimizer_system_stats_usage system statistics usage TRUE TRUE
_optimizer_trace optimizer trace parameter none none
_optimizer_transitivity_retain retain equi-join pred upon transitive equality pred generation TRUE FALSE
_optimizer_undo_changes undo changes to query optimizer FALSE FALSE
_optimizer_undo_cost_change optimizer undo cost change 10.2.0.4 9.2.0.8 #
_optimizer_use_histograms Controls whether to use histograms TRUE TRUE
_optimizer_use_subheap Enables physical optimizer subheap TRUE TRUE
70 rows selected.