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 126.96.36.199.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 188.8.131.52 to Oracle 184.108.40.206 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 220.127.116.11 to 18.104.22.168. 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 22.214.171.124 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 126.96.36.199 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) 🙂