Insights into Statistics, Query Optimization and the Oracle Optimizer

  • 12c |
    July 19, 2016

Setting a Session Parameter Overrides OFE

Nigel Bayliss
Product Manager

I received an email recently that demonstrated something the author considered strange when the init.ora parameter optimizer_feature_enable (OFE) is set in a database session. I thought I'd mention it here because the behavior he spotted is expected, but I don't think that it is entirely obvious.

Let's assume that you're logged into Oracle Database 12c. Now check the value of a hidden parameter applicable to this database version:

select ksppinm name,
       ksppstvl value
from   sys.x$ksppi x,
       sys.x$ksppcv y
where (x.indx = y.indx)
and   ksppinm = '_optimizer_aggr_groupby_elim';
NAME                            VALUE
=============================   =====
_optimizer_aggr_groupby_elim    TRUE

Next, set OFE to

alter session set optimizer_features_enable = '';

And you will then see this:

NAME                             VALUE
 =============================   =====
_optimizer_aggr_groupby_elim     FALSE

That's not surprising - we have turned off an Oracle Database 12c feature by setting OFE down to You can probably guess that the use of hidden parameters is not something I normally recommend, but there are circumstances where you might want to set them. Now, check this out:

alter session set optimizer_features_enable = '';
NAME                            VALUE
=============================   =====
_optimizer_aggr_groupby_elim    TRUE

This time we see "TRUE", and it's because we do not cascade OFE to a parameter that has been changed in the session.

Hints are different because they override session settings. The group-by and aggregation elimination will not be available to the query in this example:

alter session set "_optimizer_aggr_groupby_elim"=true;
SELECT /*+ optimizer_features_enable('') */ ...

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Integrated Cloud Applications & Platform Services