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!
All Kinds of Exadata Stuff 