Search
Navigation
OakieTags
Who's online
There are currently 0 users and 28 guests online.
Affiliations
Upgrades
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$qkcesys
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
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
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.
.png){kind=small}
TweetsAbout Jonathan Lewis
Popular content
Today's:
Monthly archive
- May 2008 (4)
- April 2008 (3)
- March 2008 (8)
- February 2008 (2)
- January 2008 (7)
- December 2007 (4)
- November 2007 (6)
- October 2007 (2)
- September 2007 (2)
- April 2007 (6)
- March 2007 (5)
- February 2007 (4)
- January 2007 (7)
- October 2006 (1)
- « first
- ‹ previous
- 1
- 2
- 3
Recent comments
16 weeks 5 days ago
26 weeks 4 days ago
28 weeks 2 days ago
31 weeks 3 days ago
33 weeks 5 days ago
43 weeks 2 days ago
44 weeks 5 days ago
45 weeks 5 days ago
45 weeks 6 days ago
48 weeks 5 days ago