Setting a Session Parameter Overrides OFE

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 11.2.0.4:

alter session set optimizer_features_enable = '11.2.0.4';

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 11.2.0.4. 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 = '11.2.0.4';

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('11.2.0.4') */ ...