Upgrade catalogue

This is a list of articles I’ve written that pick up some details (usually problems or bugs) when upgrading. Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first but the catalogue is in two sections: Benefits and Bugs and Limitations.

Benefits and Side-effects

• Window Sorts (Nov 2022): A possible performance bug that you might never notice in large window sorts disappears in 21c
• Incomplete hinting(July 2022): Hints may “stop working” on an upgrade as a side effect of of new transformations
• Index statistics (April 2022): Upgrading to 19c the code to gather index stats auto_sampl_size can use the “approximate NDV” mechanism on a 100% sample – better stats, but may take more time.
• Optimizer tweak (July 2021): A little cunning appearing in 19c that eliminates redundant function calls, and gets better cardinality estimates: but that could make execution plans change.
• Index
• Descending Index tweak (Dec 2020): From 18c to 19c – an enhancement/correction to the optimizer code allows it to use descending indexes unhinted in more cases where they are appropriate.
• Name lengths (Dec 2020): 19c allows all sorts of object names to be 128 characters instead of 30. You may need to adjust some of your code (formatting, or log table definitions) to allow for this.
• Direct I/O (Oct 2020): Upgrading from 11g to 12c and above – serial “insert as select” is enhancd to do direct path reads
• Union All Bloom (Sept 2020): Upgrade from 12.2 to 18c: the optimizer can now push a Bloom filter inside a Union ALL view.
  • Serial Bloom Filter (Sep 2020): This may have appeared in 12c, but the note is just an explanation of why a Bloom filter can improve performance even within a single process.
• Min/Max cost error (July 2020): A note describing a bug and workarounds in min/max index scans that means they won’t always be picked when they should be. Not fixed before 19.11
  • Min/Max index scan (May 2018): Enhanced in 12.2 to be usable in more cases than previously, may result in execution plans changing significantly.
• Recursive With (July 2020): A significant change appearing in 12.2 that increased the cost and cardinality estimates for a recursive with query block – resulting in changes in execution plans.
• _cursor_obsolete_threshold (Oct 2019): Upgrades to 12.2 and above increase this parameter enormously. It might cause excess CPU usage on badly coded applications.
• no change update (Sept2019): The upgrade to 12.2 attempted to reduce redundant redo generation, but could increase it in bad cases; a further enhancement in 19.11 addressed this.
• New delete pattern (June 2014): Upgrading from 11g you will find that Oracle can delete through an index fast full scan. This is not always a good idea.

Bugs and Limitations

• Upgrade Surprise (March 2022): Upgrading from 12c to 19c the optimizer will stop unnesting some subqueries based on expressions.
• DCL (lateral decorrelation) (April 2021): You can use lateral views in 12c and above, but Oracle can “decorrelate” them and sometimes gets the wrong results. (On the plus side, subqueries can have correlation predicates more than one step down).
• Pivot cosmetics (Feb 2021): Upgrading from 11g – some reports using pivot may need cosmetic adjustments as a side effect of the legality of longer column names.
• Aggregate Case Bug (Aug 2020): A bug introduced in 12.2, fixed in 21c, but not reported fixed by 19.14, though backports do exist.
• Join Elimination bug (Feb 2020): Upgrading to 12.2, a plan using join elimination could produce wrong results. Fixed in 19.11, bnt still present in some earlier 19c versions.
• Char() problems (Feb 2020): Various wrong results or performance problems with char() – i.e. fixed width – types introduced in 12.2 with possible fixes in later versions.
• Dictionary cache stress (April 2019): A bug introduced in the upgrade from 12.2 to 18c – fixed by 19,3 – does excess gets on the dictionary cache for “describe” and related API calls.
• Importing Statistics (April 2018): If you’re still using exp/imp after upgrading to 12c you should make the change or you may lose hybrid and top-frequency histograms on the import.