Forum Stats

  • 3,768,181 Users
  • 2,252,755 Discussions
  • 7,874,485 Comments

Discussions

Huge performance drop after upgrading from 12c o 19c

user8740803
user8740803 Member Posts: 10 Red Ribbon

Hi all,

we recently moved an application from Oracle 12.1.0.2.0 to 19.10.0.0. 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

Answers

  • user8740803
    user8740803 Member Posts: 10 Red Ribbon

    Hi all,

    for those of you looking into this having the same problem here is what happened to us:

    We finally opened a Service Request with Oracle Support and after some digging around it turned out that we hit

    BUG 32385068 - 19.9 SDO_TXN_JOURNAL_GTT INSERT PERFORMANCE SIGNIFICANTLY WORSE THAN 12.2

    A fix for that bug should be included in 19.13 Windows DB patch bundle due for end of November. Let's hope the bug fix will solve the problem.

    Best regards, Ralf