Dear Readers,

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: 12.1.0.2
Target DB version: 19.11.0.0.0

In 12c:

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.

Cheers

Regards,
Adityanath