By Franck Pachot

.
Oracle provides lots or parameters that can control the behavior of the software. The default values are probably the best ones most of the time. Hundreds of parameters are documented and we can set them to customize the Oracle software for our context because default values can’t fit all different database sizes, usage, workload, infrastructure, etc. And in addition to them there are those ‘underscore parameters’ or ‘hidden parameters’ or ‘undocumented parameters’. You should not set them without validation from Oracle Support.
However, several software vendors recommend some underscore parameter settings. Not only ISVs but software provided by Oracle do the same. And Oracle appliances (ODA, Exadata) also set a bunch of underscore parameters. Some people think that it’s bad, and I’ll explain here why I think it is not.

underscore parameters

How many parameters can I set in 12.1.0.2 ?


SQL> select count(*) from v$parameter;
  COUNT(*)
----------
       381

381 ones. Let’s look at the query that is behind the V$PARAMETER view:


SQL> variable c clob
SQL> exec dbms_utility.expand_sql_text(input_sql_text=>'select count(*) from v$parameter',output_sql_text=>:c);
PL/SQL procedure successfully completed.
SQL> print
C
--------------------------------------------------------------------------------
SELECT COUNT(*) "COUNT(*)" FROM  (SELECT "A2"."CON_ID" "CON_ID" FROM  (SELECT "A
4"."INST_ID" "INST_ID","A4"."CON_ID" "CON_ID" FROM SYS."X$KSPPI" "A4",SYS."X$KSP
PCV" "A3" WHERE "A4"."INDX"="A3"."INDX" AND BITAND("A4"."KSPPIFLG",268435456)=0
AND TRANSLATE("A4"."KSPPINM",'_','#') NOT LIKE '##%' AND (TRANSLATE("A4"."KSPPIN
M",'_','#') NOT LIKE '#%' OR "A3"."KSPPSTDF"='FALSE' OR BITAND("A3"."KSPPSTVF",5
)>0)) "A2" WHERE "A2"."INST_ID"=USERENV('INSTANCE')) "A1"

There is a where clause here about the name of the parameter (KSPPINM) which is:


TRANSLATE("A4"."KSPPINM",'_','#') NOT LIKE '#%'

it means that the name do not start with an underscore. It’s replaced by ‘#’ for the ‘like’ command because ‘_’ is a jocker and probably the ‘escape’ option of the ‘like’ clause were not available when the view was defined.
So this is what returns 381 parameters:


SQL> SELECT COUNT(*) "COUNT(*)" FROM  (SELECT "A2"."CON_ID" "CON_ID" FROM  (SELECT "A4"."INST_ID" "INST_ID","A4"."CON_ID" "CON_ID" FROM SYS."X$KSPPI" "A4",SYS."X$KSPPCV" "A3" WHERE "A4"."INDX"="A3"."INDX" AND BITAND("A4"."KSPPIFLG",268435456)=0 AND TRANSLATE("A4"."KSPPINM",'_','#') NOT LIKE '##%' AND (TRANSLATE("A4"."KSPPINM",'_','#') NOT LIKE '#%' OR "A3"."KSPPSTDF"='FALSE' OR BITAND("A3"."KSPPSTVF",5)>0)) "A2" WHERE "A2"."INST_ID"=USERENV('INSTANCE')) "A1"
  2  /
  COUNT(*)
----------
       381

what if I allow the ones starting by underscore?


SQL> c/NOT LIKE '#%'/LIKE '#%'
  1* SELECT COUNT(*) "COUNT(*)" FROM  (SELECT "A2"."CON_ID" "CON_ID" FROM  (SELECT "A4"."INST_ID" "INST_ID","A4"."CON_ID" "CON_ID" FROM SYS."X$KSPPI" "A4",SYS."X$KSPPCV" "A3" WHERE "A4"."INDX"="A3"."INDX" AND BITAND("A4"."KSPPIFLG",268435456)=0 AND TRANSLATE("A4"."KSPPINM",'_','#') NOT LIKE '##%' AND (TRANSLATE("A4"."KSPPINM",'_','#') LIKE '#%' OR "A3"."KSPPSTDF"='FALSE' OR BITAND("A3"."KSPPSTVF",5)>0)) "A2" WHERE "A2"."INST_ID"=USERENV('INSTANCE')) "A1"
SQL> /
  COUNT(*)
----------
      3604

… a lot more.

They are called ‘underscore parameters because they start with an underscore.
From there, there is nothing bad with them. Just a naming convention defined by Oracle. And because of that when you set it you have to enclose it in double quotes, as with any identifier that do not start with alphabetic character. No taboo there.

hidden parameters

The name ‘hidden parameter’ comes from the fact that those underscore parameter are not displayed by V$PARAMETER. But that’s not totally true:


SQL> show parameter histograms

I’ve no parameter in V$PARAMETER with ‘histograms’ in their names.
But I can set it:


SQL> alter session set "_optimizer_use_histograms"=false;
Session altered.

and then it is displayed:


SQL> show parameter histograms
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_optimizer_use_histograms            boolean     FALSE

The V$PARAMETER view is defined to hide them only when they are not set. If its not the case, it’s a bug (3327961)

So if you think that those parameters are bad and forbidden because they are not displayed, then just set them and then they are allowed 😉
I’m just joking here. My goal is to show that the choice to display them or not is not a reason to make them taboo.

undocumented

Here is the point. They are not found in the documentation. The Oracle® Database Reference book describe 291 parameters in the ‘Initialization Parameter Descriptions’ and none of them start with an underscore. Note that 291 is far from 381 which means that some parameters not starting with an underscore and not hidden from V$PARAMETER and still undocumented parameters.

But look at My Oracle Support notes. They are official documentation, isn’t it? And lot of those underscore parameters are documented there: what they do, in which context they can be set, etc.

What I want to say here is that ‘undocumented’ means that, at release time, Oracle decided not to put them into the documentation because they though we should not need to set them. But then, real life starts. We upgrade databases that are from very different environments. We encounter issues that nobody thought about. We encounter bugs. We upgrade application that are bad from the get-go (not using bind variables, parsing as much as executing, defined tables with thousand of columns, etc.) and new features may not be suited for those bad applications. We apply patches, PSUs… Things change and what was decided at release time about documentation may be different one year later.

This is where undocumented parameters become documented. They keep their name (starting with underscore) but are now totally legal for some specific situation. There is no taboo with that. One way to stabilize a new release is to apply the latest PSUs. Another way is to disable the few features that happen to cause an issue in your environment. And when you upgrade to a new release or new patchset, then check them as you probably don’t need them anymore.

documented parameters that set undocumented ones

12c came with lot of new adaptive features in the CBO, and some of them has brought parsing issues (SPD and ADS to name them by their acronyms). A good OLTP application should not spend its time to parse statements, which means no consequence with that. But if you have a bad application that is already parsing a lot, you may encounter issues. Then, what do you prefer?
One possibility is to set optimizer_features_enable=11.2.0.4 so that you disable most of the 12c CBO new features. And you are happy with it because it’s not hidden, not underscore and not undocumented. However, if you look at what it does behind, you will see that it sets nearly 30 underscore parameters. One of them is setting “_optimizer_dsdir_usage_control”=0 and maybe this is the only one that you need.
So in that case, do you prefer to look good and disable all new features? Which means that you disable adaptive plans for example, which is a very nice feature that stabilize your response time.
Or do you accept to set an underscore parameter and then address exactly the problem and only the problem?

I choose the second one. No problem to set a few parameters, whether they start with underscore or not, as long as:

  • They address an issue you encounted. Check with support about that.
  • You document them. the ALTER SYSTEM SET has a COMMENT clause that you can use to describe the reason, the SR, etc
  • You review them before each upgrade. Do you need them anymore?
  • You plan long term solution if the setting is just a workaround for a bad application design

Disclaimer: This is not an encouragement to set a lot of parameters! Default values are good for most of of them. But this advise, in my opinion, is totally independent of the fact they are underscore or not. For both hidden or not, you probably need only a few of them.

conclusion

Staying in old versions is not a way to achieve stability.
If you want a stable database, you should:

  1. Apply PSUs, patch bundles, and upgrade to latest patchset. Because, believe it or not, new releases tend to fix more bugs than bringing new ones.
  2. Test the upgraded database, and fix the few issues that you may encounter: parameters (hidden or not), fix_control, patches,…

One additional note

I said that ‘documentation’ should not be only the one from the Oracle books, but also MOS notes, because the problems and solutions evolves with time. There is also an excellent source of information about bugs encountered at Oracle customers, reasons, workarounds and fixes. And it’s free: Mike Dietrich blog: https://blogs.oracle.com/UPGRADE