Remove Underscore Parameters not only before you upgrade

Underscore 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 12.2.0.1.0. 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 12.1.0.2 to Oracle 12.2.0.1 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:
     
     _ksb_restart_policy_times
     _compression_compatibility
     _gby_vector_aggregation_enabled
     _optimizer_vector_transformation
     _optimizer_mode_force
     _always_anti_join
     _optimizer_null_aware_antijoin
     _optimizer_partial_join_eval
     _partition_view_enabled
     _b_tree_bitmap_plans
     _cpu_to_io
     _optimizer_extended_cursor_sharing
     _optimizer_extended_cursor_sharing_rel
     _optimizer_adaptive_cursor_sharing
     _optimizer_cost_model
     _optimizer_undo_cost_change
     _optimizer_system_stats_usage
     _new_sort_cost_estimate
     _complex_view_merging
     _unnest_subquery
     _optimizer_unnest_scalar_sq
     _eliminate_common_subexpr
     _pred_move_around
     _convert_set_to_join
     _px_ual_serial_input
     _px_minus_intersect
     _remove_aggr_subquery
     _optimizer_push_down_distinct
     _optimizer_cost_based_transformation
     _optimizer_squ_bottomup
     _push_join_predicate
     _push_join_union_view
     _push_join_union_view2
     _fast_full_scan_enabled
     _optimizer_skip_scan_enabled
     _optimizer_join_sel_sanity_check
     _optim_enhance_nnull_detection
     _parallel_broadcast_enabled
     _always_semi_join
     _ordered_nested_loop
     _optimizer_max_permutations
     _nlj_batching_enabled
     _mmv_query_rewrite_enabled
     _local_communication_costing_enabled
     _left_nested_loops_random
     _improved_row_length_enabled
     _index_join_enabled
     _enable_type_dep_selectivity
     (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 11.1.0.7 to 11.2.0.2. 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 12.2.0.1 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 12.2.0.1 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) 🙂

–Mike