【12c新特性】CBO Optimizer优化器新特性列表

【12c新特性】CBO Optimizer新隐藏参数,以下Optimizer优化器新特性列表,通过对比 12c R1 和 11gR2的10053 trace中有影响的优化器参数获得:

 

CLI_internal_cursor
PMO_altidx_rebuild
_adaptive_window_consolidator_enabled
_arch_comp_dbg_scan
_array_cdb_view_enabled
_bloom_filter_size
_bloom_predicate_offload
_bloom_rm_filter
_bloom_sm_enabled
_cell_materialize_all_expressions
_cell_materialize_virtual_columns
_cell_offload_complex_processing
_cell_offload_expressions
_cell_offload_sys_context
_common_data_view_enabled
_dbg_scan
_fast_index_maintenance
_hashops_prefetch_size
_long_varchar_allow_IOT
_multi_commit_global_index_maint
_oltp_comp_dbg_scan
_optimizer_adaptive_plans
_optimizer_ads_max_table_count
_optimizer_ads_time_limit
_optimizer_ads_use_result_cache
_optimizer_ansi_join_lateral_enhance
_optimizer_ansi_rearchitecture
_optimizer_batch_table_access_by_rowid
_optimizer_cluster_by_rowid
_optimizer_cluster_by_rowid_control
_optimizer_cube_join_enabled
_optimizer_dsdir_usage_control
_optimizer_gather_feedback
_optimizer_gather_stats_on_load
_optimizer_generate_transitive_pred
_optimizer_hybrid_fpwj_enabled
_optimizer_multi_table_outerjoin
_optimizer_null_accepting_semijoin
_optimizer_partial_join_eval
_optimizer_performance_feedback
_optimizer_proc_rate_level
_optimizer_proc_rate_source
_optimizer_strans_adaptive_pruning
_optimizer_unnest_scalar_sq
_optimizer_use_gtt_session_stats
_optimizer_use_histograms                        ==>10g已有
_parallel_ctas_enabled
_parallel_fault_tolerance_threshold
_partition_advisor_srs_active
_partition_cdb_view_enabled
_pred_push_cdb_view_enabled
_px_adaptive_dist_method
_px_adaptive_dist_method_threshold
_px_back_to_parallel
_px_cdb_view_enabled
_px_concurrent
_px_cpu_autodop_enabled
_px_cpu_process_bandwidth
_px_filter_parallelized
_px_filter_skew_handling
_px_groupby_pushdown
_px_hybrid_TSM_HWMB_load
_px_join_skew_handling
_px_join_skew_minfreq
_px_join_skew_ratio
_px_load_monitor_threshold
_px_loc_msg_cost
_px_monitor_load
_px_net_msg_cost
_px_object_sampling_enabled
_px_parallelize_expression
_px_partial_rollup_pushdown
_px_pwmr_enabled
_px_replication_enabled
_px_single_server_enabled
_px_tq_rowhvs
_px_wif_dfo_declumping
_px_wif_extend_distribution_keys
_rowsets_cdb_view_enabled
_rowsets_enabled
_rowsets_max_rows
_rowsets_target_maxsize
_smm_max_size_static
_smm_px_max_size_static
_sql_hvshare_threshold
_stat_aggs_one_pass_algorithm
_upddel_dba_hash_mask_bits
_use_hidden_partitions
_zonemap_control
_zonemap_use_enabled
ilm_access_tracking
ilm_dml_timestamp
ilm_filter
optimizer_adaptive_features
optimizer_adaptive_reporting_only
parallel_degree_level
parallel_execution_message_size
parallel_fault_tolerance_enabled
sqlstat_enabled

==============================================================================================》

_adaptive_window_consolidator_enabled TRUE enable/disable adaptive window consolidator PX plan
_arch_comp_dbg_scan 0 archive compression scan debug
_array_cdb_view_enabled TRUE array mode enabled for CDB views
_bloom_filter_size 0 bloom filter vector size (in KB)
_bloom_predicate_offload TRUE enables or disables bloom filter predicate offload to cells
_bloom_rm_filter FALSE remove bloom predicate in favor of zonemap join pruning predicate
_bloom_sm_enabled FALSE enable bloom filter optimization using slave mapping
_cell_materialize_all_expressions FALSE Force materialization of all offloadable expressions on the cells
_cell_materialize_virtual_columns TRUE enable offload of expressions underlying virtual columns to cells
_cell_offload_complex_processing TRUE enable complex SQL processing offload to cells
_cell_offload_expressions TRUE enable offload of expressions to cells
_cell_offload_sys_context TRUE enable offload of SYS_CONTEXT evaluation to cells
_common_data_view_enabled TRUE common objects returned through dictionary views
_dbg_scan 0 generic scan debug
_fast_index_maintenance TRUE fast global index maintenance during PMOPs
_hashops_prefetch_size 4 maximum no of rows whose relevant memory locations are prefetched
_oltp_comp_dbg_scan 0 oltp compression scan debug
_optimizer_adaptive_plans TRUE enable adaptive plans
_optimizer_ads_max_table_count 0 maximum number of tables in a join under ADS
_optimizer_ads_time_limit 0 maximum time limit (seconds) under ADS
_optimizer_ads_use_result_cache TRUE use result cache for ADS queries
_optimizer_ansi_join_lateral_enhance TRUE optimization of left/full ansi-joins and lateral views
_optimizer_ansi_rearchitecture TRUE re-architecture of ANSI left, right, and full outer joins
_optimizer_batch_table_access_by_rowid TRUE enable table access by ROWID IO batching
_optimizer_cluster_by_rowid TRUE enable/disable the cluster by rowid feature
_optimizer_cluster_by_rowid_control 3 internal control for cluster by rowid feature mode
_optimizer_cube_join_enabled TRUE enable cube join
_optimizer_dsdir_usage_control 126 controls optimizer usage of dynamic sampling directives
_optimizer_gather_feedback TRUE optimizer gather feedback
_optimizer_gather_stats_on_load TRUE enable/disable online statistics gathering
_optimizer_generate_transitive_pred TRUE optimizer generate transitive predicates
_optimizer_hybrid_fpwj_enabled TRUE enable hybrid full partition-wise join when TRUE
_optimizer_multi_table_outerjoin TRUE allows multiple tables on the left of outerjoin
_optimizer_null_accepting_semijoin TRUE enables null-accepting semijoin
_optimizer_partial_join_eval TRUE partial join evaluation parameter
_optimizer_performance_feedback OFF controls the performance feedback
_optimizer_proc_rate_level BASIC control the level of processing rates
_optimizer_proc_rate_source DEFAULT control the source of processing rates
_optimizer_strans_adaptive_pruning TRUE allow adaptive pruning of star transformation bitmap trees
_optimizer_unnest_scalar_sq TRUE enables unnesting of of scalar subquery
_optimizer_use_gtt_session_stats TRUE use GTT session private statistics
_optimizer_use_histograms TRUE enable/disable the usage of histograms by the optimizer
_parallel_ctas_enabled TRUE enable/disable parallel CTAS operation
_parallel_fault_tolerance_threshold 3 total number of faults fault-tolerance will handle
_partition_advisor_srs_active TRUE enables sampling based partitioning validation
_partition_cdb_view_enabled TRUE partitioned cdb view evaluation enabled
_pred_push_cdb_view_enabled TRUE predicate pushdown enabled for CDB views
_px_adaptive_dist_method CHOOSE determines the behavior of adaptive distribution methods
_px_adaptive_dist_method_threshold 0 Buffering / decision threshold for adaptive distribution methods
_px_back_to_parallel OFF allow going back to parallel after a serial operation
_px_cdb_view_enabled TRUE parallel cdb view evaluation enabled
_px_concurrent TRUE enables pq with concurrent execution of serial inputs
_px_cpu_autodop_enabled TRUE enables or disables auto dop cpu computation
_px_cpu_process_bandwidth 200 CPU process bandwidth in MB/sec for DOP computation
_px_filter_parallelized TRUE enables or disables correlated filter parallelization
_px_filter_skew_handling TRUE enable correlated filter parallelization to handle skew
_px_groupby_pushdown FORCE perform group-by pushdown for parallel query
_px_hybrid_TSM_HWMB_load TRUE Enable Hybrid Temp Segment Merge/High Water Mark Brokered load method
_px_join_skew_handling TRUE enables skew handling for parallel joins
_px_join_skew_minfreq 30 sets minimum frequency(%) for skewed value for parallel joins
_px_join_skew_ratio 10 sets skew ratio for parallel joins
_px_load_monitor_threshold 10000 threshold for pushing information to load slave workload monitor
_px_loc_msg_cost 1000 CPU cost to send a PX message via shared memory
_px_monitor_load FALSE enable consumer load slave workload monitoring
_px_net_msg_cost 10000 CPU cost to send a PX message over the internconnect
_px_object_sampling_enabled TRUE use base object sampling when possible for range distribution
_px_parallelize_expression TRUE enables or disables expression evaluation parallelization
_px_partial_rollup_pushdown ADAPTIVE perform partial rollup pushdown for parallel execution
_px_pwmr_enabled TRUE parallel partition wise match recognize enabled
_px_replication_enabled TRUE enables or disables replication of small table scans
_px_single_server_enabled TRUE allow single-slave dfo in parallel query
_px_tq_rowhvs TRUE turn on intra-row hash valueing sharing in TQ
_px_wif_dfo_declumping CHOOSE NDV-aware DFO clumping of multiple window sorts
_px_wif_extend_distribution_keys TRUE extend TQ data redistribution keys for window functions
_rowsets_cdb_view_enabled TRUE rowsets enabled for CDB views
_rowsets_enabled TRUE enable/disable rowsets
_rowsets_max_rows 200 maximum number of rows in a rowset
_rowsets_target_maxsize 524288 target size in bytes for space reserved in the frame for a rowset
_smm_max_size_static 92160 static maximum work area size in auto mode (serial)
_smm_px_max_size_static 230400 static maximum work area size in auto mode (global)
_sql_hvshare_threshold 0 threshold to control hash value sharing across operators
_stat_aggs_one_pass_algorithm FALSE enable one pass algorithm for variance-related functions
_upddel_dba_hash_mask_bits 0 controls masking of lower order bits in DBA
_use_hidden_partitions FALSE use hidden partitions
_zonemap_control 0 control different uses/algorithms related to zonemaps
_zonemap_use_enabled TRUE enable the use of zonemaps for IO pruning
optimizer_adaptive_features TRUE controls adaptive features
optimizer_adaptive_reporting_only FALSE use reporting-only mode for adaptive optimizations
parallel_degree_level 100 adjust the computed degree in percentage
parallel_execution_message_size 16384 message buffer size for parallel execution
parallel_fault_tolerance_enabled FALSE enables or disables fault-tolerance for parallel statement
  1. _optimizer_ads_time_limitADS about automatic data sampling