This was first published on https://blog.dbi-services.com/the-taboo-of-underscore-parameters (2016-02-21)
Republishing here for new followers. The content is related to the the versions available at the publication date
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.
How many parameters can I set in 12.1.0.2 ?
SQL> select count(*) from v$parameter; COUNT(*) ---------- 381381 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(*) ---------- 381what 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.
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 histogramsI’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 FALSEThe 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.
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.
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:
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.
Staying in old versions is not a way to achieve stability. If you want a stable database, you should:
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
Thanks for making a clear statement on parameters. Yes it is true: there are many parameters. Setting hidden or underscore parameters to values and consider a problem being solved once and forever is practised in many systems. My recommendation: whenever a major change (platform, application, Oracle release) is done – rethink the need using the additional parameters. Probe the experiences made with in depth tests.
Thanks Franck for those insights, much appreciated the post. Foued
Hi Franck , Thank you for sharing ! Just a question SEC_CASE_SENSITIVE_LOGON parameter is deprecated and unsupported in 12c…what does it mean exactly ? It is setted at FALSE in my 12c database …is it still supported ? Cheers. Kais
Hi Kais, sorry I see that I forgot to reply here. The parameter is deprecated, because case insensitive passwords are considered weak. But where did you see it is unsupported?
Well, there are over 3K undocumented parameters.
If you set the wrong one and is not in the supported documentation list, you can get in trouble, specially if you have a massive corruption in your production system.
My previous boss set one (with Oracle support! ) but forgot to tell us when he left, two months down the line and we had a massive corruption in our scanning system… not fun for it had corrupted all backups. The extraction of data was quite something.
oh he had left a few weeks earlier.
Play at home !
Hi Jorge, Thanks for your feedback. Yes some settings can have huge bad consequences. I was writing that with the idea of disabling features when they have issues, temporarily, until issue is fixed. There are parameters that can be set to allow corruption, to be used only when there is no way to open the database safely. Then the first thing to do is export your data to put them in a new, clean, database. By curiosity, can you mention the parameter (or send it to me franck.pachot[at]dbi-services.com if you don’t want to mention it publicly)? Regards, Franck.