Oracle Internals

Oracle 11.1 Supported Optimizer Environment

In Oracle 10.2 there were 25 parameters in the supported optimizer environment; in Oracle 11.1 there are 37 parameters in the supported optimizer environment.

The following table shows the 12 optimizer parameters that were added to the V$SYS_OPTIMIZER_ENV family of views in Oracle 11.1

Note that in Oracle 11.1 there are nine new supported parameters and three new unsupported parameters in V$SYS_OPTIMIZER_ENV

Name

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

_smm_max_size

_smm_min_size

_smm_px_max_size

In addition to the parameters described above, a further 78 parameters are returned by V$SES_OPTIMIZER_ENV for a limited number of sessions. These sessions all appear to be background processes, though not all background processes are affected

Affected background processes in Oracle 11.1 include ARCn, DBRM, FBRA, MMON, MMNL, QMNC and Q000

Note that these background processes are no longer reported in V$SESSION though they are reported in X$KSUSE

The following table shows the 78 additional optimizer parameters that are reported by the V$SES_OPTIMIZER_ENV view for a limited set of background processes

Name

_add_stale_mv_to_dependency_list

_aw_row_source_enabled

_bt_mmv_query_rewrite_enabled

_cpu_to_io

_db_file_optimizer_read_count

_default_non_equality_sel_check

_dm_max_shared_pool_pct

_dml_monitoring_enabled

_enable_dml_lock_escalation

_enable_query_rewrite_on_remote_objs

_enable_row_shipping

_extended_pruning_enabled

_fic_area_size

_full_pwise_join_enabled

_hash_join_enabled

_kdt_buffering

_local_communication_ratio

_nested_loop_fudge

_oneside_colstat_for_equijoins

_optimizer_adjust_for_nulls

_optimizer_aw_join_push_enabled

_optimizer_aw_stats_enabled

_optimizer_block_size

_optimizer_cartesian_enabled

_optimizer_cbqt_factor

_optimizer_extended_stats_usage_control

_optimizer_fkr_index_cost_bias

_optimizer_free_transformation_heap

_optimizer_max_permutations

_optimizer_min_cache_blocks

_optimizer_mjc_enabled

_optimizer_nested_rollup_for_gset

_optimizer_or_expansion

_optimizer_or_expansion_subheap

_optimizer_percent_parallel

_optimizer_reuse_cost_annotations

_optimizer_search_limit

_optimizer_sortmerge_join_enabled

_optimizer_starplan_enabled

_optimizer_use_subheap

_parallel_time_threshold

_parallel_time_unit

_partial_pwise_join_enabled

_pga_max_size

_predicate_elimination_enabled

_project_view_columns

_px_broadcast_fudge_factor

_query_cost_rewrite

_query_mmvrewrite_maxcmaps

_query_mmvrewrite_maxdmaps

_query_mmvrewrite_maxinlists

_query_mmvrewrite_maxintervals

_query_mmvrewrite_maxmergedcmaps

_query_mmvrewrite_maxpreds

_query_mmvrewrite_maxqryinlistvals

_query_mmvrewrite_maxregperm

_query_rewrite_1

_query_rewrite_2

_query_rewrite_drj

_query_rewrite_expression

_query_rewrite_fpc

_query_rewrite_fudge

_query_rewrite_jgmigrate

_query_rewrite_maxdisjunct

_query_rewrite_vop_cleanup

_replace_virtual_columns

_result_cache_auto_size_threshold

_result_cache_auto_time_threshold

_row_shipping_threshold

_simple_view_merging

_slave_mapping_enabled

_smm_auto_cost_enabled

_smm_auto_max_io_size

_smm_auto_min_io_size

_sort_multiblock_read_count

_spr_push_pred_refspr

_virtual_column_overload_allowed

_with_subquery

In Oracle 11.1, V$SQL_OPTIMIZER_ENV reports between 34 and 38 statistics for each child cursor.

The following table shows the five statistics that are optionally reported by V$SQL_OPTIMIZER_ENV

Name

flashback_data_archive_internal_cursor

optimizer_mode_hinted

_smm_max_size

_smm_min_size

_smm_px_max_size