When Oracle releases a new version or even a patchset, sometimes they change the default value of initialization parameters or add new features and introducing new parameters with them. These changes affect the behavior of many components. In this post I’d like to address how I deal with parameter of features changing optimizer behavior.
I ran into quite a few cases where I upgraded a database and saw strange optimizer behavior. These can be caused by new features, bugs that were introduced or any other change that was made. I developed a “method” to investigate these issues, and I want to show it to you here.
When we upgrade a database or install a patchset, Oracle may (and does) introduce new features and change behavior. When it is a big change, it is published everywhere, people are aware of it and know how to turn it on and off. However, Oracle also changes and adds small things, and these are not published, so it’s much more difficult to understand and debug them. What we should all know, is that they usually add a hidden parameters for these features that allow us to turn them off.
At this point I’ll say that it is NOT recommended to change hidden parameters without Oracle support, so if you decide to do it, do it at your own risk. If you decide to change a hidden parameter, I would recommend documenting it really well. I’ve seen many databases that have hidden parameters set, even though these parameters were set a few versions before because of a specific bug. They survived upgrades simply because nobody had the courage to remove them.
So back to the method. What I want to do is to look for the specific new optimizer feature that causes the problematic execution plan. Since this is not documented anywhere, it’s hard to find it, but I’m counting on the fact that Oracle added a hidden parameter to disable the feature.
I will use the query I showed in this post to get the list of all parameters, including the hidden ones. Seeing so many parameters doesn’t give us anything, right? But if we know that Oracle added a hidden parameter for each feature and that other parameters didn’t change, what we need to do is to find the ones that change in this exact version. For that I will use the OPTIMIZER_FEATURES_ENABLE parameter.
The first step will be to set the OPTIMIZER_FEATURES_ENABLE in the session only to the previous version (before the patch/upgrade) using “alter session set optimizer_features_enable=x.x.x.x” and see if this fixes the issue. If it does, continue with my method. If it doesn’t, we need to think of something else that causes this behavior since it’s probably not an optimizer new feature.
The steps to find the candidate hidden parameters are (script below):
- Create a table containing all parameters and their values after the patch/upgrade
- Change the OPTIMIZER_FEATURES_ENABLE parameter at the session level to the previous version (the one before the patch/upgrade)
- Create another table containing all parameters and their values
- Use a query to find all parameter that have a different value in the two tables
Script:
| create table params_12_1_0_2 | |
| as | |
| select par.ksppinm name, | |
| val.ksppstvl value, | |
| val.ksppstdf def_val | |
| from x$ksppi par, | |
| x$ksppcv val | |
| where par.indx=val.indx; | |
| alter session set optimizer_features_enable='12.1.0.1'; | |
| create table params_12_1_0_1 | |
| as | |
| select par.ksppinm name, | |
| val.ksppstvl value, | |
| val.ksppstdf def_val | |
| from x$ksppi par, | |
| x$ksppcv val | |
| where par.indx=val.indx; | |
| col name format a50 | |
| col value format a20 | |
| set lines 100 | |
| select | |
| p1.name, p1.value, p2.value | |
| from | |
| params_12_1_0_1 p1, | |
| params_12_1_0_2 p2 | |
| where p1.name=p2.name | |
| and p1.value<>p2.value; |
In my example (12.1.0.1 vs. 12.1.0.2) I have 17 differences (note that one of them is obviously the optimizer_features_enable, and there are a bunch for the in-memory option, which makes sense):
NAME VALUE VALUE
-------------------------------------------------- -------------------- -----
_gby_vector_aggregation_enabled FALSE TRUE
_optimizer_vector_transformation FALSE TRUE
optimizer_features_enable 12.1.0.1 12.1.0.2
_optimizer_undo_cost_change 12.1.0.1 12.1.0.2
_optimizer_aggr_groupby_elim FALSE TRUE
_optimizer_cluster_by_rowid_control 3 129
_distinct_agg_optimization_gsets OFF CHOOSE
_optimizer_reduce_groupby_key FALSE TRUE
_optimizer_cluster_by_rowid_batched FALSE TRUE
_optimizer_inmemory_table_expansion FALSE TRUE
_optimizer_inmemory_gen_pushable_preds FALSE TRUE
_optimizer_inmemory_autodop FALSE TRUE
_optimizer_inmemory_access_path FALSE TRUE
_px_external_table_default_stats FALSE TRUE
_optimizer_inmemory_bloom_filter FALSE TRUE
_optimizer_inmemory_cluster_aware_dop FALSE TRUE
_optimizer_inmemory_minmax_pruning FALSE TRUE
17 rows selected.
After we have the list of parameters, we can make a guess which one is relevant to the strange plan and we can test it by using the OPT_PARAM hint, as explained in this post.
Once we’ve found the relevant parameter, we can do several things:
- Contact Oracle Support. This is good anyway since if this is really a bug they can fix it in future patches.
- Fix the specific query with the OPT_PARAM hint
- Change the parameter in the spfile for the entire database (be very careful with that!)
This method helped me many times when I came to an upgraded system with strange optimizer behavior.
I hope it will help you too.
3 thoughts on “Optimizer Changes After Upgrades”