Upgrade Oracle 12c : ORA-04031 caused by the KGLH0 area consuming all the memory

[Farid]

Hello,

We upgraded this past week-end our Oracle database from 11.2 to 12.1.0.2 (OS is Windows x64). Since then we are experiencing every day ORA-04031 errors forcing to restart the Productive system in the middle of business hours. It turns out that is running out of memory and crashing because the KGLH0 process is consuming all the memory.

We first checked the parameters against Oracle recommendations as described here

1888485 - Database Parameter for 12.1.0.2

Some paramters need to be adjusted but nothing that would explain why all of a sudden KGLH0 would use up to 1 GB of memory ...

We checked the size of the shared pool area as descibed here :

690241 - The Shared SQL Area in the Shared Pool and large SQL Stmnts

It is not the problem.

image.png

The problem seems to be KGLH0 as described in the illustarted document

There was no sign of KGLH0 overconsumption before the upgrade :

all-good.png

We found that on ORacle Support a similar bug has been recently identified :

Bug 20370037 - Shared Pool from KGLH0 constantly growing causing ORA-04031 and Latch contention (Doc ID 2119923.1) .

The Oracle note describes some changes of parameters that are not always 100% effective, according to the note orApplypatch 20370037

(I am deeply sorry for the big characters, I dont mean to yell, but I could not find where to change the sizes)

But I do not know if that bug has been identified by SAP, and incuded in a SAP bundle patch.

I also opened a SAP incident, but I am not making any progress here.

Thanks and Regards

[fidel_vales · Accepted solution]

Hi,

You have a windows system. For windows there are no "SAP" Bundle Patches. The equivalent is the Windows Bundle and it does not seem that the patch is released on the last one.
Have you set the two parameters mentioned on the MOS note? (one is also recomended by SAP)
Do you get the error after setting them?
You mention that an incident is already opened. Ask the processor to send it to development Support. There is no patch available for your platform and the workaround seems to be to set an underscore parameter not recomended by us, so it is development support the one that has to "approve" it, which I think will do. They can also confirm if the patch will be in the nex Windows Bundle.

[Farid · Accepted solution]

Hello Fidel,

Thanks for your response.

Yes, we have applied the undercore parameters mentionned in the MOS note 2119923.1 this past week-end.

Unfortunalty, the workaround does not seem to be working, we are observing since this morning a gradual increase

of memory usage by the KGLH0 area. We would probably need to restart the Productive Database by the end of the day.

Also we are trying to escalate the sap customer incident to get through the first level of support.

Thanks and Regards

[Farid]

Hello Stefan,

Thank you very much for your detailled answer. I really appreciate that you took the time to respond, your¸

participation on the old SCN already helped me on countless occasions

The workarounds mentioned in MOS ID #2119923.1 didn't work indeed.

I will carefully go though your documents and keep you informed on the results

PS : You're not the only one struggling with the new SCN . You would most certainly be missed by the whole SAP on Oracle community 😞

Best Regards

[stefan_koehler]

Hi Raoul,

i did not want to write anything on the new SCN platform anymore, but your problem is pretty interesting so i want to join in and help.

1) KGLH0 is not a process - it is a heap. KGLH0 (= Kernel General Library Heap 0) stores the environment, statistics and bind variables of a SQL statement (parent and child cursor) and it is associated with the SQL Heap 0.

2) The work-arounds mentioned in MOS ID #2119923.1 are not valid in SAP environment as you do not use statistics feedback, extended cursor sharing or any adaptive features. Unfortunately SAP disables all of it with its parameter recommendations.

So what now? At first you need to identify the allocation reason in heap KGLH0. This can be done with dumping the corresponding cursor and its heaps. Here is an example from one of my talks about this (but you have to dump KGLH0 instead of SQLA) : http://www.soocs.de/public/research/161114_Oracle_12.1.0.2_SGA_heap_dump_detailed_SQL.txt

In addition you can check out my paper which explains it all in a little bit more detail: http://www.soocs.de/public/talk/161115_DOAG2016_Hacking_Oracles_Memory_About_Internals_Troubleshooti...

After you have found the root cause you can dig further and maybe fix it on your own or implement a work around, but this depends on the root cause of course.

Best Regards

Stefan Koehler

Independent Oracle performance consultant and researcher

P.S.: If this is a critical issue and you want to get it troubleshot right away - you can also book my service 😉

[amit2amit2000]

Hi

I have recently upgraded to 12c and facing same issue . We have AIX environment. Is there any solution you got?

[Former Member]

Hi Raoul,

Did you apply latest bundle patch after 12c installation? If yes check whether this bug fix is part of the bundle patch or not.

Regards,

Harish Karra