Oracle Scratchpad

February 20, 2012

Upgrades

Filed under: CBO,Oracle — Jonathan Lewis @ 5:53 pm BST Feb 20,2012

A couple of weeks ago I posted a reference list of links to the bug fix notes for several of the most recent versions of Oracle - and several of the links recorded a surprisingly large number of clicks very rapidly, especially the 11.2.0.3 link. As a follow-up on the difficulties of upgrading, then, and with an insight into the number of enhancements and fixes to the optimizer that take place I decided to take a look at recent developments in the “fix control” list, and the “optimizer environment” parameters. Here’s a breakdown of the number of entries in recent versions of Oracle.

v$sys_optimizer_env / x$qkscesys

Version       v$ count       x$ count
10.2.0.3            25            194
10.2.0.5            25            207
11.1.0.7            41            270
11.2.0.3            49            321
12.1.0.1            55            408          

v$system_fix_control / x$qksbgsys

Version          count
10.2.0.3            38
10.2.0.5           243
11.1.0.7           229
11.2.0.3           652
12.1.0.1           881

It’s interesting to note the difference between the number of parameters in the v$ and x$ versions of the optimizer environment – and when you see all the hidden parameters in the x$ it may give you some idea of how many things might explain a strange change in execution path on an upgrade. Just for reference, here’s a simple query, with output, from 11.2.0.3 (the commented column doesn’t exist in 10g):

set linesize 180
set trimspool on
set pagesize 60

column name		format a40
column feature		format a24
column value		format a15
column def_value	format a15

break on feature skip 1

select
--	FID_QKSCESYROW          feature,	-- vc64
	PNAME_QKSCESYROW	name,		-- vc40
	PVALUE_QKSCESYROW	value,		-- vc25
	DEFPVALUE_QKSCESYROW	def_value,	-- vc25
	KSPNUM_QKSCESYROW	parameter,	-- number
	PNUM_QKSCESYROW		opt_param,	-- number
	FLAGS_QKSCESYROW	flags		-- number
from
	X$QKSCESYS
order by
--	feature,
	name
;

FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_ALL                _add_stale_mv_to_dependency_list         true            true                  2009        140          6
                         _bloom_filter_enabled                    true            true                  2195        160          7
                         _bloom_folding_enabled                   true            true                  2200        265          7
                         _bloom_minmax_enabled                    true            true                  2387        314          6
                         _bloom_pruning_enabled                   true            true                  2256        211          7
                         _db_file_optimizer_read_count            8               8                     1075         22          6
                         _direct_path_insert_features             0               0                     2298        226          6
                         _disable_parallel_conventional_load      false           false                 2142        202          6
                         _dm_max_shared_pool_pct                  1               1                     2224        163          6
                         _dml_monitoring_enabled                  true            true                  2114        117          6
                         _enable_dml_lock_escalation              false           false                 2213        159          6
                         _enable_pmo_ctas                         0               0                    65535        232         14
                         _enable_row_shipping                     true            true                  2268        215          6
                         _enable_type_dep_selectivity             true            true                  2023         84          7
                         _fic_area_size                           131072          131072                2117        133          6
                         _first_k_rows_dynamic_proration          true            true                  2266        207          7
                         _fix_control_key                         0               0                    65535        191         14
                         _force_datefold_trunc                    false           false                 2104        111          6
                         _force_slave_mapping_intra_part_loads    false           false                 2092        192          6
                         _force_temptables_for_gsets              false           false                 2051        100          6
                         _force_tmp_segment_loads                 false           false                 2091        193          6
                         _full_pwise_join_enabled                 true            true                  2011         79          6
                         _globalindex_pnum_filter_enabled         true            true                  2211        189          7
                         _gs_anti_semi_join_allowed               true            true                  2068        103          7
                         _hash_multiblock_io_count                0               0                     1794         21          6
                         _improved_row_length_enabled             true            true                  2019         82          7
                         _is_lock_table_for_ddl_wait_lock         0               0                    65535        236         14
                         _kdt_buffering                           true            true                  2141        206          6
                         _left_nested_loops_random                true            true                  2018         81          7
                         _local_communication_costing_enabled     true            true                  2015        122          7
                         _local_communication_ratio               50              50                    2016        123          6
                         _minimal_stats_aggregation               true            true                  2045         99          7
                         _mv_generalized_oj_refresh_opt           true            true                  2333        266          6
                         _nlj_batching_enabled                    1               1                     1991        255          7
                         _optim_new_default_join_sel              true            true                  2073        104          7
                         _optimizer_adaptive_cursor_sharing       true            true                  1908        225          7
                         _optimizer_aw_join_push_enabled          true            true                  2285        230          6
                         _optimizer_aw_stats_enabled              true            true                  2267        210          6
                         _optimizer_block_size                    8192            8192                  2163         19          6
                         _optimizer_cache_stats                   false           false                 1912        130          6
                         _optimizer_connect_by_combine_sw         true            true                  2101        231          7
                         _optimizer_connect_by_elim_dups          true            true                  2102        283          7
                         _optimizer_cost_hjsmj_multimatch         true            true                  2227        164          7
                         _optimizer_enable_extended_stats         true            true                  2297        235          7
                         _optimizer_extended_cursor_sharing_rel   simple          simple                1907        224          7
                         _optimizer_fast_access_pred_analysis     true            true                  2326        278          7
                         _optimizer_fast_pred_transitivity        true            true                  2325        277          7
                         _optimizer_ignore_hints                  false           false                 2180        146          6
                         _optimizer_min_cache_blocks              10              10                    2244        178          6
                         _optimizer_mode_force                    true            true                  1891         43          7
                         _optimizer_percent_parallel              101             101                   2147         40          6
                         _optimizer_random_plan                   0               0                     2181        147          6
                         _optimizer_rownum_pred_based_fkr         true            true                  2241        175          7
                         _optimizer_skip_scan_enabled             true            true                  1934        134          7
                         _optimizer_skip_scan_guess               false           false                 2269        214          6
                         _optimizer_sortmerge_join_inequality     true            true                  2250        208          7
                         _optimizer_squ_bottomup                  true            true                  1928        132          7

FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_ALL                _optimizer_system_stats_usage            true            true                  1911        113          7
                         _optimizer_transitivity_retain           true            true                  2228        165          7
                         _parallel_broadcast_enabled              true            true                  1939         62          7
                         _partial_pwise_join_enabled              true            true                  2012         80          6
                         _partition_view_enabled                  true            true                  1900         44          7
                         _pga_max_size                            204800 KB       204800 KB             2054         25          6
                         _project_view_columns                    true            true                  1979        121          6
                         _px_broadcast_fudge_factor               100             100                   1940         63          6
                         _px_minus_intersect                      true            true                  1923        190          7
                         _px_numa_support_enabled                 true            true                  2397        323          6
                         _px_pwg_enabled                          true            true                  2230        166          7
                         _px_ual_serial_input                     true            true                  1922        213          7
                         _replace_virtual_columns                 true            true                  2139        204          7
                         _result_cache_auto_size_threshold        100             100                   1759        252          6
                         _result_cache_auto_time_threshold        1000            1000                  1760        253          6
                         _right_outer_hash_enable                 true            true                  2126        128          7
                         _row_shipping_explain                    false           false                 2264        217          6
                         _row_shipping_threshold                  80              80                    2263        216          6
                         _slave_mapping_enabled                   true            true                  2013        125          6
                         _smm_auto_cost_enabled                   true            true                  2058        102          6
                         _smm_auto_max_io_size                    248 KB          248 KB                2057         29          6
                         _smm_auto_min_io_size                    56 KB           56 KB                 2056         28          6
                         _smm_max_size                            52428 KB        52428 KB              2062         31          6
                         _smm_min_size                            262 KB          262 KB                2061         30          6
                         _smm_px_max_size                         131072 KB       131072 KB             2063         32          6
                         _sort_multiblock_read_count              2               2                     1863         20          6
                         _spr_push_pred_refspr                    true            true                  2162        129          6
                         _sql_compatibility                       0               0                     2370        305          6
                         _sql_model_unfold_forloops               run_time        run_time              2212        158          7
                         _suppress_scn_chk_for_cqn                nosuppress_1466 nosuppress_1466      65535        296         14
                         _table_scan_cost_plus_one                true            true                  2036         93          7
                         _trace_virtual_columns                   false           false                 2138        203          6
                         _update_bji_ipdml_enabled                0               0                    65535        161         14
                         _virtual_column_overload_allowed         true            true                  2140        205          6
                         _with_subquery                           OPTIMIZER       OPTIMIZER             2305        242          6
                         active_instance_count                    1               1                    65535         12          2
                         bitmap_merge_area_size                   1048576         1048576               1753         15          2
                         cpu_count                                2               2                    65535         11          2
                         db_file_multiblock_read_count            128             128                   1073        264          2
                         dst_upgrade_insert_conv                  true            true                  2346        286          2
                         flashback_table_rpi                      non_fbt         non_fbt              65535        169         10
                         hash_area_size                           131072          131072                1793         14          2
                         iot_internal_cursor                      0               0                    65535        293         10
                         is_recur_flags                           0               0                    65535        257          2
                         kkb_drop_empty_segments                  0               0                    65535        310         10
                         only_move_row                            0               0                    65535        315         10
                         optimizer_mode                           all_rows        all_rows              1890         38          3
                         optimizer_secure_view_merging            true            true                  2229        167          2
                         parallel_ddl_mode                        enabled         enabled              65535         37          2
                         parallel_dml_mode                        disabled        disabled             65535         36          2
                         parallel_query_mode                      enabled         enabled              65535         35          2
                         pga_aggregate_target                     262144 KB       262144 KB             2052         24          2
                         result_cache_mode                        MANUAL          MANUAL                1758        212          2
                         sort_area_retained_size                  0               0                     1862         17          2
                         sort_area_size                           65536           65536                 1861         16          2
                         total_cpu_count                          2               2                    65535        275          2
                         total_processor_group_count              1               1                    65535        324          2

FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_ALL                transaction_isolation_level              read_commited   read_commited        65535        218          2
                         workarea_size_policy                     auto            auto                  2055        101          2

QKSFM_AQ                 advanced_queuing_internal_cursor         0               0                    65535        287         10

QKSFM_CBO                _and_pruning_enabled                     true            true                  2365        300          7
                         _b_tree_bitmap_plans                     true            true                  1902         49          7
                         _cpu_to_io                               0               0                     1905         33          7
                         _default_non_equality_sel_check          true            true                  2038         95          6
                         _disable_datalayer_sampling              false           false                 1986         69          6
                         _disable_function_based_index            false           false                 2186        150          6
                         _extended_pruning_enabled                true            true                  2210        154          6
                         _fast_full_scan_enabled                  true            true                  1933         60          7
                         _gby_hash_aggregation_enabled            true            true                  2209        188          7
                         _generalized_pruning_enabled             true            true                  2085        109          7
                         _improved_outerjoin_card                 true            true                  2025         85          7
                         _index_join_enabled                      true            true                  2021         83          7
                         _like_with_bind_as_equality              false           false                 2035         92          6
                         _nested_loop_fudge                       100             100                   1978        120          6
                         _new_initial_join_orders                 true            true                  2039         96          7
                         _new_sort_cost_estimate                  true            true                  1913         52          7
                         _oneside_colstat_for_equijoins           true            true                  2040         97          6
                         _optim_adjust_for_part_skews             true            true                  2094        110          7
                         _optim_enhance_nnull_detection           true            true                  1936         61          7
                         _optim_peek_user_binds                   true            true                  2042         98          7
                         _optimizer_adjust_for_nulls              true            true                  2026         86          6
                         _optimizer_better_inlist_costing         all             all                   2242        176          7
                         _optimizer_cartesian_enabled             true            true                  2206        152          6
                         _optimizer_complex_pred_selectivity      true            true                  2254        186          7
                         _optimizer_compute_index_stats           true            true                  2095        144          7
                         _optimizer_connect_by_cb_whr_only        false           false                 2359        295          6
                         _optimizer_connect_by_cost_based         true            true                  2100        187          7
                         _optimizer_correct_sq_selectivity        true            true                  2169        149          7
                         _optimizer_cost_filter_pred              false           false                 2168        135          6
                         _optimizer_cost_model                    choose          choose                1909         51          7
                         _optimizer_enable_density_improvements   true            true                  2279        229          7
                         _optimizer_extended_cursor_sharing       udo             udo                   1906        162          7
                         _optimizer_extended_stats_usage_control  192             192                   2323        260          7
                         _optimizer_fkr_index_cost_bias           10              10                    2308        243          7
                         _optimizer_improve_selectivity           true            true                  2284        227          7
                         _optimizer_join_order_control            3               3                     2193        151          7
                         _optimizer_join_sel_sanity_check         true            true                  1935        137          7
                         _optimizer_max_permutations              2000            2000                  1981         23          7
                         _optimizer_mjc_enabled                   true            true                  2127        127          6
                         _optimizer_new_join_card_computation     true            true                  2077        107          7
                         _optimizer_or_expansion_subheap          true            true                  2302        249          6
                         _optimizer_rownum_bind_default           10              10                    2280        222          7
                         _optimizer_search_limit                  5               5                     2148         10          6
                         _optimizer_self_induced_cache_cost       false           false                 2243        177          6
                         _optimizer_sortmerge_join_enabled        true            true                  2128        136          6
                         _optimizer_star_trans_min_cost           0               0                     2303        240          6
                         _optimizer_star_trans_min_ratio          0               0                     2304        241          6
                         _optimizer_undo_changes                  false           false                 2146        118          6
                         _optimizer_undo_cost_change              11.2.0.3        11.2.0.3              1910         34          7
                         _optimizer_use_feedback                  true            true                  2371        306          7
                         _optimizer_use_subheap                   true            true                  2301        244          6
                         _ordered_nested_loop                     true            true                  1977         64          7

FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_CBO                _sort_elimination_cost_ratio             0               0                     1894         18          6
                         _system_index_caching                    0               0                     1984         68          6
                         _use_column_stats_for_function           true            true                  2028         88          7
                         cursor_sharing                           exact           exact                 1755         48          2
                         optimizer_capture_sql_plan_baselines     false           false                 2288        238          2
                         optimizer_dynamic_sampling               2               2                     2075        105          3
                         optimizer_features_enable                11.2.0.3        11.2.0.3              1858          9          2
                         optimizer_features_hinted                0.0.0           0.0.0                65535          1         10
                         optimizer_index_caching                  0               0                     1983         67          2
                         optimizer_index_cost_adj                 100             100                   1982         66          2
                         optimizer_mode_hinted                    false           false                65535          0         10
                         optimizer_use_pending_statistics         false           false                 2283        228          2
                         optimizer_use_sql_plan_baselines         true            true                  2289        239          2
                         parallel_ddl_forced_degree               0               0                    65535          5         10
                         parallel_ddl_forced_instances            0               0                    65535          6         10
                         parallel_dml_forced_dop                  0               0                    65535          4         10
                         parallel_execution_enabled               true            true                 65535          2          2
                         parallel_query_forced_dop                0               0                    65535          3         10
                         parallel_threads_per_cpu                 2               2                     1954         13          2
                         skip_unusable_indexes                    true            true                  2149        114          3
                         sqlstat_enabled                          false           false                65535         39         10
                         statistics_level                         typical         typical               2105        112          2

QKSFM_COMPILATION        _allow_level_without_connect_by          false           false                 2179        319          6
                         _connect_by_use_union_all                true            true                  2103        285          7

QKSFM_CVM                _complex_view_merging                    true            true                  1914         53          7

QKSFM_DML                _dml_frequency_tracking                  false           false                 2353        308          6

QKSFM_EXECUTION          _aggregation_optimization_settings       0               0                     2343        282          7
                         _deferred_constant_folding_mode          DEFAULT         DEFAULT               1867        301          6
                         _rdbms_internal_fplib_enabled            false           false                 1869        263          6
                         _rowsrc_trace_level                      0               0                     2240        173          6
                         cell_offload_compaction                  ADAPTIVE        ADAPTIVE              1879        267          2
                         cell_offload_plan_display                AUTO            AUTO                  1880        268          2
                         cell_offload_processing                  true            true                  1876        262          2

QKSFM_FBA                flashback_data_archive_internal_cursor   0               0                    65535        259         10

QKSFM_INDEX              optimizer_use_invisible_indexes          false           false                 2322        258          2

QKSFM_JPPD               _bloom_predicate_enabled                 true            true                  2198        234          6
                         _optimizer_extend_jppd_view_types        true            true                  2098        157          7
                         _optimizer_interleave_jppd               true            true                  2307        309          7
                         _optimizer_multi_level_push_pred         true            true                  2272        220          7

QKSFM_OR_EXPAND          _optimizer_or_expansion                  depth           depth                 2246        179          7

QKSFM_PARTITION          deferred_segment_creation                true            true                  2275        317          2
                         partition_pruning_internal_cursor        0               0                    65535        303         10

QKSFM_PQ                 _bloom_predicate_pushdown_to_storage     true            true                  2199        269          6
                         _bloom_pushing_max                       512             512                   2203        271          6
                         _bloom_vector_elements                   0               0                     2197        270          6
                         _optimizer_degree                        0               0                     2027         87          6
                         _optimizer_instance_count                0               0                     2331        294          6

FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_PQ                 _parallel_cluster_cache_policy           adaptive        adaptive              1957        291          6
                         _parallel_scalability                    50              50                    2314        292          6
                         _parallel_syspls_obey_force              true            true                  2315        261          6
                         _parallel_time_unit                      10              10                    2311        248          6
                         _px_partition_scan_enabled               true            true                  2377        311          7
                         _px_partition_scan_threshold             64              64                    2378        312          6
                         parallel_autodop                         0               0                    65535        289          2
                         parallel_ddldml                          0               0                    65535        290          2
                         parallel_degree                          0               0                    65535        246          2
                         parallel_degree_limit                    65535           65535                 2312        272          2
                         parallel_degree_policy                   manual          manual                1945        245          2
                         parallel_force_local                     false           false                 2313        273          2
                         parallel_hinted                          none            none                 65535        304         10
                         parallel_max_degree                      4               4                    65535        274          2
                         parallel_min_time_threshold              10              10                    2310        247          2
                         parallel_query_default_dop               0               0                    65535        256          2

QKSFM_STAR_TRANS         _optimizer_star_tran_in_with_clause      true            true                  2249        185          7
                         _optimizer_starplan_enabled              true            true                  2207        153          6
                         _optimizer_try_st_before_jppd            true            true                  2373        307          7
                         _optimizer_use_cbqt_star_transformation  true            true                  2362        298          7
                         star_transformation_enabled              false           false                 1903         50          2

QKSFM_TRANSFORMATION     _always_anti_join                        choose          choose                1898         41          7
                         _always_semi_join                        choose          choose                1976         42          7
                         _always_star_transformation              false           false                 1901         45          6
                         _bt_mmv_query_rewrite_enabled            true            true                  2008        139          6
                         _convert_set_to_join                     false           false                 1920         57          7
                         _cost_equality_semi_join                 true            true                  2037         94          7
                         _dimension_skip_null                     true            true                  2252        183          7
                         _distinct_view_unnesting                 false           false                 1925        141          6
                         _eliminate_common_subexpr                true            true                  1918         55          7
                         _enable_query_rewrite_on_remote_objs     true            true                  2281        223          6
                         _force_rewrite_enable                    false           false                 2253        184          6
                         _hash_join_enabled                       true            true                  1792         47          6
                         _max_rwgs_groupings                      8192            8192                  2010        320          6
                         _mmv_query_rewrite_enabled               true            true                  2007        138          7
                         _no_or_expansion                         false           false                 1980         65          6
                         _optimizer_cbqt_factor                   50              50                    1929        131          6
                         _optimizer_cbqt_no_size_restriction      true            true                  2234        170          7
                         _optimizer_coalesce_subqueries           true            true                  2278        276          7
                         _optimizer_cost_based_transformation     linear          linear                1927        126          7
                         _optimizer_dim_subq_join_sel             true            true                  2177        142          7
                         _optimizer_disable_strans_sanity_checks  0               0                     2178        143          6
                         _optimizer_distinct_agg_transform        true            true                  2342        281          7
                         _optimizer_distinct_elimination          true            true                  2270        219          7
                         _optimizer_distinct_placement            true            true                  2277        302          7
                         _optimizer_eliminate_filtering_join      true            true                  2345        284          7
                         _optimizer_enable_table_lookup_by_nl     true            true                  2388        316          7
                         _optimizer_enhanced_filter_push          true            true                  2237        171          7
                         _optimizer_false_filter_pred_pullup      true            true                  2382        313          7
                         _optimizer_filter_pred_pullup            true            true                  2099        172          7
                         _optimizer_filter_pushdown               true            true                  2393        318          6
                         _optimizer_free_transformation_heap      true            true                  2299        250          6
                         _optimizer_full_outer_join_to_outer      true            true                  2396        322          7
                         _optimizer_group_by_placement            true            true                  2276        221          7
                         _optimizer_join_elimination_enabled      true            true                  2208        168          7

FEATURE                  NAME                                     VALUE           DEF_VALUE        PARAMETER  OPT_PARAM      FLAGS
------------------------ ---------------------------------------- --------------- --------------- ---------- ---------- ----------
QKSFM_TRANSFORMATION     _optimizer_join_factorization            true            true                  2360        297          7
                         _optimizer_native_full_outer_join        force           force                 2296        233          7
                         _optimizer_nested_rollup_for_gset        100             100                   2316        254          6
                         _optimizer_null_aware_antijoin           true            true                  1899        156          7
                         _optimizer_order_by_elimination_enabled  true            true                  2248        180          7
                         _optimizer_outer_join_to_inner           true            true                  2395        321          7
                         _optimizer_outer_to_anti_enabled         true            true                  2247        181          7
                         _optimizer_push_down_distinct            0               0                     1926        116          7
                         _optimizer_push_pred_cost_based          true            true                  2097        155          7
                         _optimizer_reuse_cost_annotations        true            true                  2306        251          6
                         _optimizer_table_expansion               true            true                  2364        299          7
                         _optimizer_unnest_all_subqueries         true            true                  1917        288          6
                         _optimizer_unnest_corr_set_subq          true            true                  2341        280          7
                         _optimizer_unnest_disjunctive_subq       true            true                  2340        279          7
                         _or_expand_nvl_predicate                 true            true                  2034         91          7
                         _pivot_implementation_method             choose          choose                2300        237          7
                         _pre_rewrite_push_pred                   true            true                  2076        106          7
                         _pred_move_around                        true            true                  1919         56          7
                         _predicate_elimination_enabled           true            true                  1970        119          6
                         _push_join_predicate                     true            true                  1930         58          7
                         _push_join_union_view                    true            true                  1931         59          7
                         _push_join_union_view2                   true            true                  1932        145          7
                         _query_cost_rewrite                      true            true                  1997         72          6
                         _query_mmvrewrite_maxcmaps               20              20                    2261        198          6
                         _query_mmvrewrite_maxdmaps               10              10                    2260        197          6
                         _query_mmvrewrite_maxinlists             5               5                     2259        196          6
                         _query_mmvrewrite_maxintervals           5               5                     2258        195          6
                         _query_mmvrewrite_maxpreds               10              10                    2257        194          6
                         _query_mmvrewrite_maxqryinlistvals       500             500                   2265        201          6
                         _query_mmvrewrite_maxregperm             512             512                   2262        199          6
                         _query_rewrite_1                         true            true                  1999         74          6
                         _query_rewrite_2                         true            true                  1998         73          6
                         _query_rewrite_drj                       true            true                  2004         78          6
                         _query_rewrite_expression                true            true                  2001         75          6
                         _query_rewrite_fpc                       true            true                  2003         77          6
                         _query_rewrite_fudge                     90              90                    2000          8          6
                         _query_rewrite_jgmigrate                 true            true                  2002         76          6
                         _query_rewrite_maxdisjunct               257             257                   2005         27          6
                         _query_rewrite_or_error                  false           false                 1893         46          6
                         _query_rewrite_setopgrw_enable           true            true                  2184        148          7
                         _query_rewrite_vop_cleanup               true            true                  2006        124          6
                         _remove_aggr_subquery                    true            true                  1924        115          7
                         _selfjoin_mv_duplicates                  true            true                  2251        182          7
                         _simple_view_merging                     true            true                  1915        174          6
                         _subquery_pruning_enabled                true            true                  2031         89          7
                         _subquery_pruning_mv_enabled             false           false                 2032         90          7
                         _union_rewrite_for_gs                    yes_gset_mvs    yes_gset_mvs          2083        108          7
                         _unnest_subquery                         true            true                  1916         54          7
                         query_rewrite_enabled                    true            true                  1995         70          3
                         query_rewrite_integrity                  enforced        enforced              1996         71          2

321 rows selected.

There are a few parameters in there which look a little unexpected – why, for example, should deferred_segment_creation affect the optimizer ? One day I might get around to checking how many of these parameters do result in new child cursors being generated if you change them with an alter session or alter system – but it’s not a job I’m going to do in a hurry.

7 Comments »

  1. For ease of use of V$SESSION_FIX_CONTROL I’ve wrote a simple script called bug.sql. Each time I see something unusual related to, say, join elimination, I run @bug elimination and it gives clue what’s next. Or to report what’s new: @bug 11.2.0.3

    Comment by Timur Akhmadeev — February 21, 2012 @ 9:36 am BST Feb 21,2012 | Reply

  2. Here are statistics for my Oracle 11.2.0.2.2:

    v$sys_optimizer_env/X$QKSCESYS
    Version v$ count x$ count
    11.2.0.2(psu2) 49 313

    v$system_fix_control / x$qksbgsys
    version count
    11.2.0.2(psu2) 551

    Comment by laimis (lnd) — February 21, 2012 @ 1:53 pm BST Feb 21,2012 | Reply

  3. Surprisingly honest naming convention for _nested_loop_fudge and _query_rewrite_fudge :)

    Comment by Kevin Angus — February 23, 2012 @ 8:34 am BST Feb 23,2012 | Reply

  4. [...] Jonathan Lewis is taking a look at recent developments in the “fix control” list, and the “optimizer environment” parameters. [...]

    Pingback by Log Buffer #260, A Carnival of the Vanities for DBAs | The Pythian Blog — February 24, 2012 @ 6:01 am BST Feb 24,2012 | Reply

  5. That’s a post of real interest. We are in progress of migrating into 11R2.

    11.2.0.3 bug list (Doc ID 1348336.1) consists largely of optimizer related bugs. Some of them sound scary:

    “13514126 Wrong results due to missing single column range predicate caused by fix to bug 8275054″

    Generally, it looks like large number of optimizer improvements resulted into equally large number of serious (wrong results or query crash) bugs.

    Comment by laimis (lnd) — February 24, 2012 @ 9:18 am BST Feb 24,2012 | Reply

  6. [...] 11.2.0.1). This setting acts as an umbrella for all the parameters controlling the optimizer (see this recent post by Jonathan Lewis for more information) including the various bug fix controls that can be seen [...]

    Pingback by Dynamic Sampling (II) – Controlling The Activity – Part 1 – All Things Oracle — March 8, 2012 @ 12:20 pm BST Mar 8,2012 | Reply

  7. […] version of the same thing – download and install the product, then take a dump of v$system_fix_control – that may also give you some insight into anomalies (that are not necessarily declared as […]

    Pingback by 12c Debug | Oracle Scratchpad — June 28, 2013 @ 8:48 am BST Jun 28,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,453 other followers