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
Yup, 100% agreed! There are so many dbs out there full of underscore params…
When it comes time to run a dbua, all heck breaks lose!
I only use the ones that Peoplesoft directs me to use and in all others only _disk_sector_size_override, which is still needed to run redo logs with a blocksize that matches my file system blocksize of 4K.
And even this one I’m told will not be necessary at some stage post-18c!
Good riddance! I really don’t want to run underscores unless absolutely necessary or mandatory.
Nice view on underscore parameters. Hope everyone follow this. Specifically documenting the reason for setting underscore parameters.
Thanks Samir!
Cheers,
Mike
Why do some undocumented parameters start with two underscores versus one underscore? On my 19c database there are 30 double underscore undocumented parameters but 4934 single underscore undocumented parameters!
Double Underscores are memory snapshots – meaning, when you create a pfile from spfile, double underscores give the current memory sizing.
But you may please check with Oracle Support for a more sophisticated explanation.
Cheers,
Mike
Hi Mike, I have encountered the same problem not knowing what to do with hundreds of underscore parameters during an upgrade. But I see MOS has this doc Doc ID 315631.1
NOTE:
It is never recommended to modify these hidden parameters without the assistance of Oracle Support.
Changing these parameters may lead to serious corruption issues, high performance degradation and other problems in the database.
Hi Anthony,
did somebody set them into your spfile???
I have seen similar cases where people simply set tons of underscores on their default values for no reason.
Cheers,
Mike