Optimizer parameters:
_optimizer_partial_join_eval partial join evaluation parameter _optimizer_unnest_scalar_sq enables unnesting of of scalar subquery _optimizer_ansi_join_lateral_enhance optimization of left/full ansi-joins and lateral views _optimizer_multi_table_outerjoin allows multiple tables on the left of outerjoin _optimizer_null_accepting_semijoin enables null-accepting semijoin _optimizer_ansi_rearchitecture re-architecture of ANSI left, right, and full outer joins _optimizer_cube_join_enabled enable cube join _optimizer_hybrid_fpwj_enabled enable hybrid full partition-wise join when TRUE _px_object_sampling_enabled use base object sampling when possible for range distribution _px_concurrent enables pq with concurrent execution of serial inputs _px_replication_enabled enables or disables replication of small table scans _px_filter_parallelized enables or disables correlated filter parallelization _px_filter_skew_handling enable correlated filter parallelization to handle skew _px_groupby_pushdown perform group-by pushdown for parallel query _px_parallelize_expression enables or disables expression evaluation parallelization _optimizer_gather_stats_on_load enable/disable online statistics gathering _optimizer_batch_table_access_by_rowid enable table access by ROWID IO batching _px_wif_dfo_declumping NDV-aware DFO clumping of multiple window sorts _px_wif_extend_distribution_keys extend TQ data redistribution keys for window functions _px_join_skew_handling enables skew handling for parallel joins _px_adaptive_dist_method determines the behavior of adaptive distribution methods _px_partial_rollup_pushdown perform partial rollup pushdown for parallel execution _optimizer_dsdir_usage_control controls optimizer usage of dynamic sampling directives _px_cpu_autodop_enabled enables or disables auto dop cpu computation _px_single_server_enabled allow single-slave dfo in parallel query _optimizer_use_gtt_session_stats use GTT session private statistics _optimizer_adaptive_plans enable adaptive plans _optimizer_strans_adaptive_pruning allow adaptive pruning of star transformation bitmap trees _optimizer_proc_rate_level control the level of processing rates _adaptive_window_consolidator_enabled enable/disable adaptive window consolidator PX plan _px_cdb_view_enabled parallel cdb view evaluation enabled _partition_cdb_view_enabled partitioned cdb view evaluation enabled _common_data_view_enabled common objects returned through dictionary views _optimizer_cluster_by_rowid enable/disable the cluster by rowid feature _pred_push_cdb_view_enabled predicate pushdown enabled for CDB views _rowsets_cdb_view_enabled rowsets enabled for CDB views _array_cdb_view_enabled array mode enabled for CDB views
Fix controls:
9898249 initialize col stats for olap earlier in compilation 10004943 enable removal of group by in subquery for create table 9554026 store index filter selectivity/cost 9593547 estimate selectivity for unique scan predicates 9833381 rank predicates before costing 10106423 use base NDV for predicate selectivity in new join order 10175079 increment kafcoref to simulate obsolete fropqop list 10236566 do replacement for expressions involving correlated columns 9721228 allow pushing of complex predicates to Exadata cell 9929609 use qksvc to handle descending indexes 10182672 enhance uniquenes detection of a query block 9832338 disallow outer join oper (+) in CONNECT BY and START WITH clause 11668189 parallelize top-level union all if PDDL or PDML 11940126 fixed groupby partition count method 12390139 enhance qsme to handle more cases 11744016 enhance algorithm to detrimine optimizer duplicate insignificanc 10216738 Toggels subquery coalescing for ANY and ALL subqueries 12563419 add cost of scalar subquery into the cost of outer query 12535474 parallelize nested table access through table function 12561635 cap parallelism if order-by cannot go parallel 12569245 enable bloom filter for partition wise joins 12569300 improve bloom filter costing and heuristics 12569316 show broadcast dist bloom filter rowsource in explain plan 12569321 push bloom filter through other bloom filters 12810427 cardinality feedback for join cardinalities 12914055 use ADS for large parallel tables based on the size 12978495 limit the computed DOP with access path constraints 13110511 allow group-by and distinct placement with in-list predicates 13345888 parallel table lookup access by rowid 13396096 allow expression in connecting cond for semi and regular anti jo 12999577 normalize subquery predicate 12954320 cardinality feedback for bind-aware cursors 13036910 use non-sampling-based freq. histograms for join card. estimatio 12648629 allow common sub-expression elemination after typecheck 13704977 fixes names/hints for multiple-inlined WITH subqueries 11843466 do not force serialize px for serial pl/sql 13909909 Cardinality feedback does not require ACS to be enabled 12856200 Allow partial partition-wise join for range sub-partitioning 9852856 Enable CBQT for MV refresh 14033181 correct ndv for non-popular values in join cardinality comp. 13836796 enable CBQT on queries with materialized WITH subqueries 13699643 Use cached evaluation context in kkoecp 13735304 relax restrictions on window function replaces subquery 14464068 filter pull up from UNION ALL view 13448445 enable serial check for IO dop 9114915 Allow predicate compression prior to type check 13109345 Enables cardinality feedback for parallel queries 14605040 Disable cardinality feedback for temp table 14633570 allow non-column correlations in inline-view generation checks 13573073 Resolve conflicting CFB hints
So there are lots of interesting things mentioned, in particular the Fix Control list contains some very interesting changes. I've highlighted those that at first glance looked interesting to me - and some of them, at least according to the description, seem to introduce significant changes to the CBO calculations and transformations. Time to repeat some existing test cases...
No comments:
Post a Comment