Skip to Main Content

Oracle Database Discussions


For appeals, questions and feedback, please email

Huge performance drop after upgrading from 12c o 19c

user8740803Oct 12 2021

Hi all,
we recently moved an application from Oracle to A new database was created and a dump was imported. On the new database a MERGE statement experienced a huge performance drop. During the upgrade the schema has been altered such that the tables involved in the MERGE have some more columns. Hence, the SQL statements are not exactly the same - they have different SQL hashes. However, the execution plans created in the 12c and 9c databases have the exact same plan hash value and appear to be the same.
The MERGE statement is executed in a loop in a package run from the scheduler. The loop is executed approx. 620.000 times. When the MATCH condition is met the MERGE updates exactly one row in the target table. The target table has 23mio rows as well as two other joined tables in the sub-select making up the source have 23mio rows each.
Both databases run on Windows servers having 16GB RAM each. Some parameters are set different like MEMORY_TARGET=12G on 12c while 8G on 19c. The parameters COMPATIBLE and OPTIMIZER_FEATURES_ENABLED differ as well.
Looking at the server I see that on the 19c server all 4 vCPUs are constantly busy while it reads from disk at a sustained rate of approx. 1 GByte/s.
So what is the difference here? What can we do to have the MERGE perform on 19c as fast as it was on 12c? Statistics of all tables involved are up to date. All indexes are valid all constraints are enabled.
Okay! One obvious hint would be to set MEMORY_TARGET on 19c to 12G as it is on 12c. However, I am not sure if this requires an instance restart which would have to be planned.
Disclaimer: I am not the DBA responsible for those databases and I am not the developer of this application and I was not involved in the upgrade. I was just asked to "take a look at it" after things blew up. My access to this database is somewhat limited. I was able to manage for a read-only user having access to some performance views...
Best regards, Ralf

Post Details
Added on Oct 12 2021