Underscoring the Magical World of Oracle Database’s Hidden Parameters: Part 2

As you might remember from Part 1, I was (and still am) struggling with a strange bug in Oracle Database 18c that sometimes swaps column names (and therefore column contents) generating incorrect results. Oracle Support and Oracle Development have been working on my SR# for weeks (as of this writing) and still haven’t given me a bug number.

An early discovery was that by setting a new 18c hidden parameter

"_optimizer_allow_all_access_paths"=false;

the “column swap” went away, but so did a lot of other optimizer features, causing some existing well-running SELECT statements to choose horrible execution plans; some report queries which ran in seconds previously now might take hours or never finish. So I set out to dig further — were there other hidden parameters that were closer to triggering this bug? And if I found such a parameter, I could use it in the interim and possibly even help Oracle Development figure out the best fix.

Using Mauro Pagano’s excellent Pathfinder tool, I was able to easily flip each hidden parameter back and forth for each run of my problematic query. As usual, I did not expect most of the results I got. Each of these parameters, when set to the values below, allowed the query to run without getting the ORA-00932 error (which was a red herring in this case):

"_b_tree_bitmap_plans" =  FALSE
"_complex_view_merging" =  TRUE
"_optimizer_cost_based_transformation" =  'OFF'
"_optimizer_generate_transitive_pred" =  FALSE
"_optimizer_squ_bottomup" =  FALSE
"_optimizer_use_cbqt_star_transformation" =  FALSE
"_simple_view_merging" =  FALSE
optimizer_dynamic_sampling = 4, 6, 8 10 -- and probably others
All optimizer_features_enable values except for '12.2.0.1' and '18.1.0.1'
optimizer_index_cost_adj = 1000, 10000 -- and probably others
optimizer_mode =  'FIRST_ROWS'
optimizer_mode =  'RULE'
star_transformation_enabled =  'FALSE'
star_transformation_enabled =  'TEMP_DISABLE'
"_fix_control" = '5302124:0'
"_fix_control" = '5884780:0'
"_fix_control" = '7573526:0'
"_fix_control" = '19710102:0'

None of these looked like something I could set system-wide and not have a deleterious effect on a massive number of existing queries. And there doesn’t seem to be much of a pattern or commonality across these parameters. I uploaded these results to my SR# and I patiently wait for a bug fix or targeted workaround. Stay tuned for Part 3 of this story!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s