Top 60 Oracle Blogs

Recent comments

12c New Optimizer Features

Besides the officially available information about new optimizer features in 12c it is always a good idea to have a look at the internal optimizer parameters that show what features are enabled when running with OPTIMIZER_FEATURES_ENABLE = Here is the list of internal optimizer parameters and fix controls that are different between and

Optimizer parameters:

#eeeeee; border: 1px dashed rgb(204, 204, 204); overflow: auto;">_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:

#eeeeee; border: 1px dashed rgb(204, 204, 204); overflow: auto;"> 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...