Admin/admin

_optimizer hidden parameter 비교(9i-10gR2)

Qhtlr 2010. 3. 5. 12:00

_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.