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...

One thought on “Did You Know #9 – OPT_PARAM Hint

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s