Oracle Internals

While upgrading database from version 12.1 to version 19.10 came across set of queries using lateral views (basically VW_LAT** shown in execution plan). Such queries was behaving slow than the response time we got on 12.1 database. Looking further into this we found it was due to a Bug 30786641 which was introduced after the fix to another Bug 23273859. To understand the problem we need to first cover Bug  23273859. Bug 23273859 which was introduced due to wrong result with join predicate pushed and correlated subquery using ANSI syntax. It affected all versions lesser than 12.2 and fix was introduced in database version 12.2.0.1 base releases. This bug introduced another behavior where lateral view shows performance degradation and oracle comes up another bug in the form of 30786641. Bug 30786641 for slow performance of lateral views was fixed with version 19.10 Jan 2021 RU and in 21.1 Base release. In a case where fixed for this bug already available in your binary, it needs to be ac

Recently while upgrading Oracle Database from version 12.1 to 19.10 found there were couple of queries which was running slow post upgrade. Usually these type of behavior is seen while upgrading to newer version and oracle provide various ways to fix them. The difference between good and bad plan was an introduction of  "MERGE JOIN CARTESIAN" (aka MJC) and that's in where regressed query in 19c taking most of the time. MJC sometimes works for queries but in this case it was clearly not helping.  We had following options here:     1. Copy the profile from 12c to 19c database and it worked, but the problem was not just to specific query but with many similar query. This means profile option is not very attractive in this case.     2. We also tried running query with OFE 12.1 to use the same optimizer features as previous version and query behaves well. Though this option can't be placed as we need to use 19c OFE and can be placed only for testing purpose.           alte