Oracle Internals
JulianDyke.com

Welcome

Seminars

Consultancy

Calendar

Presentations

Diagnostics

Internals

References

Acknowledgements

Oracle 11.1 Unsupported Optimizer Environment

In Oracle 10.2 there were 184 parameters in the unsupported optimizer environment; in Oracle 11.1 there are 259 parameters in the unsupported optimizer environment.

The following table shows the 75 optimizer parameters that were added to the X$QKSCESYS family of views in Oracle 11.1

Name
flashback_data_archive_internal_cursor
is_recur_flags
optimizer_capture_sql_plan_baselines
optimizer_use_invisible_indexes
optimizer_use_pending_statistics
optimizer_use_sql_plan_baselines
parallel_degree
parallel_query_default_dop
result_cache_mode
transaction_isolation_level
_aw_row_source_enabled
_bloom_predicate_enabled
_bloom_pruning_enabled
_direct_path_insert_features
_disable_parallel_conventional_load
_enable_pmo_ctas
_enable_query_rewrite_on_remote_objs
_enable_row_shipping
_first_k_rows_dynamic_proration
_fix_control_key
_force_slave_mapping_intra_part_loads
_force_tmp_segment_loads
_globalindex_pnum_filter_enabled
_is_lock_table_for_ddl_wait_lock
_kdt_buffering
_nlj_batching_enabled
_optimizer_adaptive_cursor_sharing
_optimizer_aw_join_push_enabled
_optimizer_aw_stats_enabled
_optimizer_connect_by_combine_sw
_optimizer_connect_by_cost_based
_optimizer_distinct_elimination
_optimizer_enable_density_improvements
_optimizer_enable_extended_stats
_optimizer_extended_cursor_sharing_rel
_optimizer_extended_stats_usage_control
_optimizer_extend_jppd_view_types
_optimizer_fkr_index_cost_bias
_optimizer_free_transformation_heap
_optimizer_group_by_placement
_optimizer_improve_selectivity
_optimizer_multi_level_push_pred
_optimizer_native_full_outer_join
_optimizer_nested_rollup_for_gset
_optimizer_null_aware_antijoin
_optimizer_or_expansion_subheap
_optimizer_reuse_cost_annotations
_optimizer_rownum_bind_default
_optimizer_skip_scan_guess
_optimizer_sortmerge_join_inequality
_optimizer_star_trans_min_cost
_optimizer_star_trans_min_ratio
_optimizer_use_subheap
_parallel_policy
_parallel_time_threshold
_parallel_time_unit
_pivot_implementation_method
_px_minus_intersect
_px_ual_serial_input
_query_mmvrewrite_maxcmaps
_query_mmvrewrite_maxdmaps
_query_mmvrewrite_maxinlists
_query_mmvrewrite_maxintervals
_query_mmvrewrite_maxmergedcmaps
_query_mmvrewrite_maxpreds
_query_mmvrewrite_maxqryinlistvals
_query_mmvrewrite_maxregperm
_replace_virtual_columns
_result_cache_auto_size_threshold
_result_cache_auto_time_threshold
_row_shipping_explain
_row_shipping_threshold
_trace_virtual_columns
_virtual_column_overload_allowed
_with_subquery

Note that although flashback_data_archive_internal_cursor is a supported parameter, it does not always appear in the V$SYS_OPTIMIZER_ENV family of views

Note that although _smm_max_size, _smm_min_size and _smm_px_max_size are unsupported parameters, they do appear in the V$SYS_OPTIMIZER_ENV family of views