Trust you all are doing well.
In this post I would be discussing about an issue, we observed during RAT replay test after upgrading our database on 19c. There was considerable increase in wait time for the event “latch: shared pool” post DB was upgraded to 19c, which was not the case with 12c.
Original DB version: 220.127.116.11
Target DB version: 18.104.22.168.0
Post DB upgrade to 19c:
Ofcourse issue was with 19c as we did same RAT replay on 12c which worked without any issues. Interestingly I found lot many customers are observing this behavior post 19c upgrades.
One more observation was related increased wait times for event ==> “library cache: mutex X”. This was resolved by applying patch for a Bug 32356628 – Significant increase in library cache: mutex x wait time after upgrading database to 19c (Doc ID 32356628.8)
We got a recommendation of increasing shared_pool value to fix this from Oracle. But it didn’t help.
ASH views also shown max wait time with no sql_id, so I was assuming there must be some bug in 19C causing this. After having a look in latch statistics miss resource, I found a entry with label “unknown latch: kghfrunp: alloc: wait” with maximum sleep counts. I found some MOS documents which confirmed this was related to shared pool.
After digging more in MOS I found matching internal bug 33225584 for which patch is already available AIM:ORA-32701: POSSIBLE HANGS UP TO HANG ID=114 DETECTED (ROOT:NOT IN WAIT / IMMEDIATE:LATCH: SHARED POOL / TYPE:HANG) – KGHFRUNP
After patch application, next RAT replay shown significant improvement in for wait event : latch: shared pool. Issue resolved 🙂
Though this both bugs are not mentioned in Oracle Database 19c Important Recommended One-off Patches (Doc ID 555.1), I would strongly recommend you to refer it before you upgrade any databases to 19c.
Hope u will find this post very useful.