Over the last couple of weeks we have been working at the upgrade of an AIX based database. This database now has been running on 11.2.0.4 for many years. Because of application restrictions any higher database version was not possible until now.
Some specs:
Server is an LPAR on a IBM PSeries P9
It has 8 logical cores and 48Gb of memory.
All storage is SSD or better.
All file systems are mounted read/write, without CIO
The upgraded 19c database is a Single Instance database, no container/multitenant.
The challenge here is that we see a performance degradation of approx. 2 to 3 times after upgrade to 19c
There where a huge update statement runs 18 minutes on 11.2, the same statement takes 37 to 45 minutes on 19c!
The statement updates some 65 million rows in a table, using a where clause on an non-indexed column.
What we're clearly observing from AWR report is the fact that on 11.2 there are no significant wait seen for the inserts on the MVlog, as if it is something implicit.
On 19c the inserts into the MVlog are much more substantial in wait time, I/O usage etc..
The above has been written on April 18th 2023.
It is now mid-June and after weeks of investigating and trying to get Oracle Support in line with our findings, we still did not find a solution.
Update:
It is October 2023 and Oracle Support has filed a bug-request.
Now we wait for the Development team to research the matter
