Remove Underscore Parameters not only before you upgrade

Remove Underscore Parameters not only before you upgradeUnderscore parameters are a wonderful thing in Oracle. They are treated like a secret. But you can view them of course. Everybody can. Make a guess how many underscore parameters exist in Oracle You’ll find the answer at the end of this blog post. But why should you remove underscore parameters not only before you upgrade?

I’d like to explain below why underscores, especially too many of them, may cause a lot of trouble. Therefore, housekeeping is very important.

Remove Underscore Parameters not only before you upgrade

This is from a recent upgrade from Oracle to Oracle of a 1.8 TB database I got involved with. The preupgrade.jar (see MOS Note:884522.1 for always the newest drop) reported the following (excerpt):

   + Review and remove any unnecessary HIDDEN/UNDERSCORE parameters.  
     The database contains the following initialization parameters whose name
     begins with an underscore:
     (list truncated)
     Remove hidden parameters before database upgrade unless your application
     vendors and/or Oracle Support state differently.  Changes will need to
     be made in the spfile.

Wow! This is a VERY long list of underscore parameters. In fact you see a (truncated!!!) list of 48 underscore parameters.

Keep underscores only if …

Of course there are situations when you have to keep certain underscore parameters. First of all, if your application vendor dictates them, you better follow this guidance. The Oracle Ebusiness Suite Interoperability notes are such examples. And other software vendors do the same.

There may be also the case that you’ve hit an issue. And the patch is either not available or you can’t apply it. An underscore parameter may be the remedy to it.

Or you read about a hint out there saying “this one is a must”. But never trust a stranger. Always make sure that you fully understand why you’ve set an underscore. But document, why you’ve set it. And when. This will make your life much easier.

Remove underscores if …

But in all other cases you should get rid of them as soon as possible. If you don’t know why the underscore is set, do a quick search on MOS (and maybe another search engine) to double-check. If you are in doubt, remove it. Oh, I see some readers no questioning my state of health. But honestly, underscore parameters are often meant to turn off or tweak certain tiny things in the Oracle code. But do you take the screw driver and switch something in the engine of your car without knowing exactly what you do? Changing the car vendor’s default may cause trouble unless you know exactly what you do.

Furthermore, outdated and not useful underscores not only prevent the usage of improved functionality. They may cause also trouble when you upgrade. A long time ago one of our most internal systems at Oracle got upgraded from to The upgrade took 6 hours. Once we removed all the underscores nobody had proof for their purpose the upgrade duration dropped to 1.5 hours.

In addition, you make it very hard, maybe even impossible, for Oracle Support to reproduce anything.

Housekeeping is important

Housekeeping in important. Do it on a regular basis. For underscore parameters my rules are:

  • Remove underscores you have no proof for their purpose
  • Remove underscores you do not understand unless Oracle Support or your App vendor told you to set them
  • Document why and when you set an underscore
  • When you patch, verify the underscore(s) – in case the issue is now fixed, remove it
  • When you upgrade, remove all underscores except those:
    • The application vendor told you to have them in the new release
    • An issue is still present in the new release
  • Consider also to remove all underscores during upgrade – you may set one or the other later on again
  • Keep in mind that DBUA will remove underscores generally unless you tell it to keep them
  • Every 6 months check and verify your underscore set if

The above example …

Coming back to the example above, some of these underscores came in in Oracle 9i. When I spot “_always_anti_join” or “_always_semi_join” then my alarm clock rings. These parameters were used to fix open issues with the fairly new Cost Based Optimizer model. But they were not needed in Oracle 10g onward anymore.

Don’t keep underscores for nostalgic reasons. Don’t!!!

I’d guess, that not even the best optimizer guys I know can completely foresee the effect you created with such an enormous list as the one above.

How many underscore parameters does Oracle has?

I did ask you this question in the first paragraph. Did you make a guess? Thanks for reading till the end.

A plain vanilla unpatched Oracle database has 4614 underscore parameters. That’s a lot. Especially when you compare it to 428 non-underscore parameters.

Make you you life much easier and set as just a small parameter set. The less parameters – non-underscores and underscores – you have, the easier your maintenance will be. Trust us (at least a bit) 🙂



Share this: