By Franck Pachot
.
Do you know the optimizer_features_enable parameter? What do you think about it? Good or bad to use it?
If I tell you to set optimizer_features_enable parameter=11.2.0.4 when you upgrade to 12c, do you think it’s a very safe decision, or totally insane to upgrade and set behavior to previous version? It’s not an underscore parameter, you are allowed to use it.
Optimizer Features Enable
If you’re a developer, you know what is versioning. Every change (new feature or bug fix) you do to your software makes a new version of some part of the code. Those changes can be deployed individually as patches, grouped in patchset, or combined to build a new release. Which mean that you can compile an executable with exactly the set of features you want.
But you can do more. You can, instead of having different versions of source code, put everything into the same code, add a parameter to be able to enable the new code part or not, and you have a ‘if’ that checks the parameter in order to run the new code or the old one. You can do even more: when you deploy a new release, you set a runtime version parameter that enables all the features you want to deploy into your new release.
I know only one software that do that: the Oracle optimizer. It probably something hard to maintain for developers, but being able to choose at runtime the features you want to use is great and flexible.
Well in Oracle this idea is not only for the optimizer, you can also choose the compatible version for the database: store it compatible with a previous version. But I’m talking about the optimizer here.
Instance, Session, Statement
Being able to run the optimizer as of a previous version can be great, but it goes beyond that. You can change the optimizer_features_enable parameter only for your session if you want, and even only for one statement. For example:
SELECT /*+ optimizer_features_enable('11.2.0.4') */ ...
will optimize the query as it was optimized in the latest 11g patchset even if you are in 12c. And if you can’t change your queries, you can use SQL Patch to do that. Available in every edition.
Want to know which values you can put there?
There’s the quick way:
SQL> alter session set optimizer_features_enable=RBO;
ERROR:
ORA-00096: invalid value RBO for parameter optimizer_features_enable, must be from among 12.1.0.2, 12.1.0.1, 11.2.0.4, 11.2.0.3, 11.2.0.2, 11.2.0.1, 11.1.0.7, 11.1.0.6, 10.2.0.5, 10.2.0.4, 10.2.0.3, 10.2.0.2, 10.2.0.1, 10.1.0.5, 10.1.0.4, 10.1.0.3, 10.1.0, 9.2.0.8, 9.2.0, 9.0.1, 9.0.0, 8.1.7, 8.1.6, 8.1.5, 8.1.4, 8.1.3, 8.1.0, 8.0.7, 8.0.6, 8.0.5, 8.0.4, 8.0.3, 8.0.0
(Sorry for the joke about RBO – put whatever you want)
And the nice way:
SQL> select listagg(value,', ')within group(order by ordinal) from V$PARAMETER_VALID_VALUES where name='optimizer_features_enable';
LISTAGG(VALUE,',')WITHINGROUP(ORDERBYORDINAL)
----------------------------------------------------------------------------------------------------------------------------------------------
8.0.0, 8.0.3, 8.0.4, 8.0.5, 8.0.6, 8.0.7, 8.1.0, 8.1.3, 8.1.4, 8.1.5, 8.1.6, 8.1.7, 9.0.0, 9.0.1, 9.2.0, 9.2.0.8, 10.1.0, 10.1.0.3, 10.1.0.4, 10.1.0.5, 10.2.0.1, 10.2.0.2, 10.2.0.3, 10.2.0.4, 10.2.0.5, 11.1.0.6, 11.1.0.7, 11.2.0.1, 11.2.0.2, 11.2.0.3, 11.2.0.4, 12.1.0.1, 12.1.0.2
Is is bad or Good?
Well, the truth is that I find that very god to be able to do that, but I’m always reluctant to recommend them because I like to learn and use new features, and I find that setting OFE is negative and old-fashioned. Actually, I’ve that double feeling because there are always two different contexts. Actually this is the reason of that blog: clear out that ‘negative’ feeling about OFE.
New project
You are starting a new project, building a new application? Then install the latest database version and use the latest features. There are a lot of new features that will give more performance, more flexibility, more stability to your application, so use them. No OFE setting here except if you find a bug and need to workaround before getting the patch. But even there, you will not set OFE to previous version. You will disable only the feature or fix controls that cause the problem.
Migration
In the opposite, you are only migrating an existing application that has been tuned on a previous version of the database and you don’t have the budget to involve development in new testing and tuning? Then take the safe way. Set Optimizer Features to the previous version and everything will be fine. You build new reports on that application? Then use new features for them. You can set it for the session. Why not having it set in a logon trigger that check the service name?
Basically, OFE helps to:
- Sort out any other migration issue before having to address the execution plan change ones.
- Test new plans in a managed way. Do it when you are available to run non-regression tests. You can even use SQL Performance Analyzer if you have Tuning Pack.
- You can also capture SQL Plan Baselines while OFE is set to old version, and then bring it back to current version. Then you will evolve plans only when there is no regression in response time. SPM is available in Enterprise Edition without any option.
How long?
Of course, it’s not because you can set optimizer to 8.0 version that it’s good to do it in 12c. OFE helps to postpone the upgrade of the optimizer so that you don’t have to test and resolve everything at the same time. But you should keep OFE to previous version only for a few months or year. Donc cumulate the gaps on multiple releases.
Take the occasion of an application release, when lot of non-regression testing will be done anyway, to bring OFE to latest version. Then you will see lot of statements improved (as it’s the goal of most of new features) and a few issues that will have to be addressed. You may have to gather statistics differently, to write some statements differently, to get rid of lot of old profiles and maybe implement a few new ones, etc. And the you will have a optimal application with its latest version running with the latest optimizer improvements.
Maybe you will choose to disable some features. But you probably don’t need to disable all new features. Let’s take an example about SQL Plan Directives that brought a lot of instability in 12c migrations. You have several ways to disable some of their behaviour (see this blog post). Maybe you will do that until 12.2 that will fix a lot of issues for sure. But don’t disable all 12c features. Don’t disable all adaptive features. Adaptive Plans are great and brings stability by avoiding bad plans to run for hours.
Features per version
When I see that an issue is fixed an issue by setting OFE to a previous version, I try to find which feature is responsible for the problem. I’ve a small script that parses a query with OFE set to previous versions and check (from event 10132 trace) the changes on documented and undocumented parameters. Here are those for the latest patchsets.
_bloom_serial_filter = on new in 11.2.0.4 was = off enable serial bloom filter on exadata (QKSFM_EXECUTION - SQL EXECUTION)
_fix_control_key = 1167487983 new in 11.2.0.4 was = -726982239
optimizer_features_enable = 11.2.0.4 new in 11.2.0.4 was = 11.2.0.3 optimizer plan compatibility parameter (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_undo_cost_change = 11.2.0.4 new in 11.2.0.4 was = 11.2.0.3 optimizer undo cost change (QKSFM_CBO - SQL Cost Based Optimization)
> _px_scalable_invdist = false
_px_scalable_invdist = true _optimizer_adaptive_plans = false
_optimizer_adaptive_plans = true < enable adaptive plans (QKSFM_ADAPTIVE_PLAN - Adaptive plans)
_optimizer_ansi_join_lateral_enhance = true new in 12.1.0.1 was = false optimization of left/full ansi-joins and lateral views (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_ansi_rearchitecture = true new in 12.1.0.1 was = false re-architecture of ANSI left, right, and full outer joins (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_batch_table_access_by_rowid = true new in 12.1.0.1 was = false enable table access by ROWID IO batching (QKSFM_ALL - A Universal Feature)
_optimizer_cluster_by_rowid = true new in 12.1.0.1 was = false enable/disable the cluster by rowid feature (QKSFM_CLUSTER_BY_ROWID - Cluster By Rowid Transformation)
_optimizer_cube_join_enabled = true new in 12.1.0.1 was = false enable cube join (QKSFM_JOIN_METHOD - Join methods)
_optimizer_dsdir_usage_control = 126 new in 12.1.0.1 was = 0 controls optimizer usage of dynamic sampling directives (QKSFM_CBO - SQL Cost Based Optimization)
optimizer_features_enable = 12.1.0.1 new in 12.1.0.1 was = 11.2.0.4 optimizer plan compatibility parameter (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_gather_stats_on_load = true new in 12.1.0.1 was = false enable/disable online statistics gathering (QKSFM_STATS - Optimizer statistics)
_optimizer_hybrid_fpwj_enabled = true new in 12.1.0.1 was = false enable hybrid full partition-wise join when TRUE (QKSFM_PQ - Parallel Query)
_optimizer_multi_table_outerjoin = true new in 12.1.0.1 was = false allows multiple tables on the left of outerjoin (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_nlj_hj_adaptive_join = true new in 12.1.0.1 was = false allow adaptive NL Hash joins (QKSFM_ADAPTIVE_PLAN - Adaptive plans)
_optimizer_null_accepting_semijoin = true new in 12.1.0.1 was = false enables null-accepting semijoin (QKSFM_TRANSFORMATION - Query Transformation)
_optimizer_partial_join_eval = true new in 12.1.0.1 was = false partial join evaluation parameter (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_proc_rate_level = basic new in 12.1.0.1 was = off control the level of processing rates (QKSFM_STATS - Optimizer statistics)
_optimizer_strans_adaptive_pruning = true new in 12.1.0.1 was = false allow adaptive pruning of star transformation bitmap trees (QKSFM_STAR_TRANS - Star Transformation)
_optimizer_undo_cost_change = 12.1.0.1 new in 12.1.0.1 was = 11.2.0.4 optimizer undo cost change (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_unnest_scalar_sq = true new in 12.1.0.1 was = false enables unnesting of of scalar subquery (QKSFM_TRANSFORMATION - Query Transformation)
_optimizer_use_gtt_session_stats = true new in 12.1.0.1 was = false use GTT session private statistics (QKSFM_STATS - Optimizer statistics)
_px_adaptive_dist_method = choose new in 12.1.0.1 was = off determines the behavior of adaptive distribution methods (QKSFM_PQ - Parallel Query)
_px_concurrent = true new in 12.1.0.1 was = false enables pq with concurrent execution of serial inputs (QKSFM_PQ - Parallel Query)
_px_cpu_autodop_enabled = true new in 12.1.0.1 was = false enables or disables auto dop cpu computation (QKSFM_PQ - Parallel Query)
_px_filter_parallelized = true new in 12.1.0.1 was = false enables or disables correlated filter parallelization (QKSFM_PQ - Parallel Query)
_px_filter_skew_handling = true new in 12.1.0.1 was = false enable correlated filter parallelization to handle skew (QKSFM_PQ - Parallel Query)
_px_groupby_pushdown = force new in 12.1.0.1 was = choose perform group-by pushdown for parallel query (QKSFM_PQ - Parallel Query)
_px_join_skew_handling = true new in 12.1.0.1 was = false enables skew handling for parallel joins (QKSFM_PQ - Parallel Query)
_px_object_sampling = 1 new in 12.1.0.1 was = 0 parallel query sampling for base objects (100000 = 100%) (QKSFM_PQ - Parallel Query)
_px_object_sampling_enabled = true new in 12.1.0.1 was = false use base object sampling when possible for range distribution (QKSFM_PQ - Parallel Query)
_px_parallelize_expression = true new in 12.1.0.1 was = false enables or disables expression evaluation parallelization (QKSFM_PQ - Parallel Query)
_px_partial_rollup_pushdown = adaptive new in 12.1.0.1 was = off perform partial rollup pushdown for parallel execution (QKSFM_PQ - Parallel Query)
_px_replication_enabled = true new in 12.1.0.1 was = false enables or disables replication of small table scans (QKSFM_PQ - Parallel Query)
_px_single_server_enabled = true new in 12.1.0.1 was = false allow single-slave dfo in parallel query (QKSFM_PQ - Parallel Query)
_px_wif_dfo_declumping = choose new in 12.1.0.1 was = off NDV-aware DFO clumping of multiple window sorts (QKSFM_PQ - Parallel Query)
_px_wif_extend_distribution_keys = true new in 12.1.0.1 was = false extend TQ data redistribution keys for window functions (QKSFM_PQ - Parallel Query)
_distinct_agg_optimization_gsets = choose new in 12.1.0.2 was = off Use Distinct Aggregate Optimization for Grouping Sets (QKSFM_ALL - A Universal Feature)
_fix_control_key = -1261475868 new in 12.1.0.2 was = 890546215
_gby_vector_aggregation_enabled = true new in 12.1.0.2 was = false enable group-by and aggregation using vector scheme (QKSFM_TRANSFORMATION - Query Transformation)
_optimizer_aggr_groupby_elim = true new in 12.1.0.2 was = false group-by and aggregation elimination (QKSFM_TRANSFORMATION - Query Transformation)
_optimizer_cluster_by_rowid_batched = true new in 12.1.0.2 was = false enable/disable the cluster by rowid batching feature (QKSFM_CLUSTER_BY_ROWID - Cluster By Rowid Transformation)
_optimizer_cluster_by_rowid_control = 129 new in 12.1.0.2 was = 3 internal control for cluster by rowid feature mode (QKSFM_CLUSTER_BY_ROWID - Cluster By Rowid Transformation)
optimizer_features_enable = 12.1.0.2 new in 12.1.0.2 was = 12.1.0.1 optimizer plan compatibility parameter (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_inmemory_access_path = true new in 12.1.0.2 was = false optimizer access path costing for in-memory (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_inmemory_autodop = true new in 12.1.0.2 was = false optimizer autoDOP costing for in-memory (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_inmemory_bloom_filter = true new in 12.1.0.2 was = false controls serial bloom filter for in-memory tables (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_inmemory_cluster_aware_dop = true new in 12.1.0.2 was = false Affinitize DOP for inmemory objects (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_inmemory_gen_pushable_preds = true new in 12.1.0.2 was = false optimizer generate pushable predicates for in-memory (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_inmemory_minmax_pruning = true new in 12.1.0.2 was = false controls use of min/max pruning for costing in-memory tables (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_inmemory_table_expansion = true new in 12.1.0.2 was = false optimizer in-memory awareness for table expansion (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_reduce_groupby_key = true new in 12.1.0.2 was = false group-by key reduction (QKSFM_TRANSFORMATION - Query Transformation)
_optimizer_undo_cost_change = 12.1.0.2 new in 12.1.0.2 was = 12.1.0.1 optimizer undo cost change (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_vector_transformation = true new in 12.1.0.2 was = false perform vector transform (QKSFM_VECTOR_AGG - Vector Transformation)
_px_external_table_default_stats = true new in 12.1.0.2 was = false the external table default stats collection enable/disable (QKSFM_PQ - Parallel Query)
_ds_enable_view_sampling = true new in 12.1.0.2.1 was = false Use sampling for views in Dynamic Sampling (QKSFM_DYNAMIC_SAMPLING - Dynamic sampling)
_ds_sampling_method = PROGRESSIVE new in 12.1.0.2.1 was = NO_QUALITY_METRIC Dynamic sampling method used (QKSFM_DYNAMIC_SAMPLING - Dynamic sampling)
_ds_xt_split_count = 1 new in 12.1.0.2.1 was = 0 Dynamic Sampling Service: split count for external tables (QKSFM_DYNAMIC_SAMPLING - Dynamic sampling)
_fix_control_key = 0 new in 12.1.0.2.1 was = -1261475868
_key_vector_create_pushdown_threshold = 20000 new in 12.1.0.2.1 was = 0 minimum grouping keys for key vector create pushdown (QKSFM_VECTOR_AGG - Vector Transformation)
_optimizer_ads_use_partial_results = true new in 12.1.0.2.1 was = false Use partial results of ADS queries (QKSFM_DYNAMIC_SAMPLING - Dynamic sampling)
_optimizer_ads_use_spd_cache = true new in 12.1.0.2.1 was = false use Sql Plan Directives for caching ADS queries (QKSFM_DYNAMIC_SAMPLING - Dynamic sampling)
_optimizer_band_join_aware = true new in 12.1.0.2.1 was = false enable the detection of band join by the optimizer (QKSFM_ALL - A Universal Feature)
_optimizer_bushy_join = on new in 12.1.0.2.1 was = off enables bushy join (QKSFM_BUSHY_JOIN - bushy join)
_optimizer_cbqt_or_expansion = on new in 12.1.0.2.1 was = off enables cost based OR expansion (QKSFM_CBQT_OR_EXPANSION - Cost Based OR Expansion)
_optimizer_eliminate_subquery = true new in 12.1.0.2.1 was = false consider elimination of subquery optimization (QKSFM_ELIMINATE_SQ - eliminate subqueries)
_optimizer_enable_plsql_stats = true new in 12.1.0.2.1 was = false Use statistics of plsql functions (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_enhanced_join_elimination = true new in 12.1.0.2.1 was = false Enhanced(12.2) join elimination (QKSFM_TABLE_ELIM - Table Elimination)
optimizer_features_enable = 12.2.0.1 new in 12.1.0.2.1 was = 12.1.0.2 optimizer plan compatibility parameter (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_inmemory_use_stored_stats = AUTO new in 12.1.0.2.1 was = NEVER optimizer use stored statistics for in-memory tables (QKSFM_ALL - A Universal Feature)
_optimizer_key_vector_pruning_enabled = true new in 12.1.0.2.1 was = false enables or disables key vector partition pruning (QKSFM_VECTOR_AGG - Vector Transformation)
_optimizer_multicol_join_elimination = true new in 12.1.0.2.1 was = false eliminate multi-column key based joins (QKSFM_TABLE_ELIM - Table Elimination)
_optimizer_undo_cost_change = 12.2.0.1 new in 12.1.0.2.1 was = 12.1.0.2 optimizer undo cost change (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_union_all_gsets = true new in 12.1.0.2.1 was = false Use Union All Optimization for Grouping Sets (QKSFM_GROUPING_SET_XFORM - Grouping Set Transformation)
_optimizer_use_table_scanrate = HADOOP_ONLY new in 12.1.0.2.1 was = OFF Use Table Specific Scan Rate (QKSFM_CBO - SQL Cost Based Optimization)
_optimizer_use_xt_rowid = true new in 12.1.0.2.1 was = false Use external table rowid (QKSFM_TRANSFORMATION - Query Transformation)
_optimizer_vector_base_dim_fact_factor = 200 new in 12.1.0.2.1 was = 0 cost based vector transform base dimension to base fact ratio (QKSFM_VECTOR_AGG - Vector Transformation)
_pwise_distinct_enabled = true new in 12.1.0.2.1 was = false enable partition wise distinct (QKSFM_PARTITION - Partition)
_px_dist_agg_partial_rollup_pushdown = adaptive new in 12.1.0.2.1 was = off perform distinct agg partial rollup pushdown for px execution (QKSFM_PQ - Parallel Query)
_px_scalable_invdist_mcol = true new in 12.1.0.2.1 was = false enable/disable px plan for percentile functions on multiple columns (QKSFM_PQ - Parallel Query)
_query_rewrite_use_on_query_computation = true new in 12.1.0.2.1 was = false query rewrite use on query computation (QKSFM_TRANSFORMATION - Query Transformation)
_recursive_with_branch_iterations = 7 new in 12.1.0.2.1 was = 1 Expected number of iterations of the recurive branch of RW/CBY (QKSFM_EXECUTION - SQL EXECUTION)
_recursive_with_parallel = true new in 12.1.0.2.1 was = false Enable/disable parallelization of Recursive With (QKSFM_EXECUTION - SQL EXECUTION)
_sqlexec_hash_based_distagg_ssf_enabled = true new in 12.1.0.2.1 was = false enable hash based distinct aggregation for single set gby queries (QKSFM_EXECUTION - SQL EXECUTION)
_vector_encoding_mode = manual new in 12.1.0.2.1 was = off enable vector encoding(OFF/MANUAL/AUTO) (QKSFM_EXECUTION - SQL EXECUTION)
_xt_sampling_scan_granules = on new in 12.1.0.2.1 was = off Granule Sampling for Block Sampling of External Tables (QKSFM_EXECUTION - SQL EXECUTION)
Conclusion
- It’s not fool to use new version of optimizer for new projects
- It’s not bad to use previous optimizer version if your application has been tuned for that
- It’s good to try to use new optimizer version when testing a new application release
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)