Did You Know #9 – OPT_PARAM Hint

Over the years I ran into many Oracle bug related to the optimizer (the last one was yesterday with join push predicates). Many times this is related to Optimizer new features or new behavior and can be disabled using a hidden parameter (in the case from yesterday, the “_optimizer_push_pred_cost_based” parameter).Changing a hidden parameter at the instance level (in the pfile or spfile) is quite risky for two reasons:

1. It might change other queries execution plans that worked well until now
2. Usually we don’t document it well and we are afraid to remove it so they will stay forever (had some examples of these as well)

The solution for this might be the “OPT_PARAM” hint, which allows to change a value of any session parameter. Example:

select /*+ opt_param('_optimizer_push_pred_cost_based','FALSE') */ * from my_table...