【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 |
Leave a Reply