ORA-04031 - KGLH0 heap

I'm investigating an ORA-04031 problem and I'm seeking some more information on some of the shared pool heaps.

In the trace file generated by the ORA-04031 error, I see that the KGLH0 heap occupied more then 700MB (with sga_target set to 1500 MB). When I monitor the v$sgastat view I noticed that this heap is gradually growing after a restart of the database.

Am I correct in thinking that this heap is the "kernel generic library heap 0"? What exactly is stored here?

I also noticed in v$sqlarea that several statements are using a large amount of sharable memory (up to 65 MB), without having a high number of loaded / open versions.

In v$open_cursors I see a several thousand (up to 10.000) cursors for the types "BUNDLE DICTIONARY LOOKUP CACHED" and "DICTIONARY LOOKUP CURSOR CACHED", while in other databases these numbers are always less then 100. Metalink and google searches for these cursor types return no hits.

Could the problem be caused by one of these things?

Some background info:

Oracle EE 11.2.0.2 (recently migrated from 10.2.0.5, after which the problems started) Linux 64 bit
Application sets the following session level parameters (don't look at me, it’s a canned application):

  session_cached_cursors   = 2500
  cursor_sharing           = SIMILAR
  optimizer_mode           = RULE

Funny enough, they are using hints in like 90% of their statements, which switches the optimizer back to cost based. Also they are using bind variables in their statements, so don't know why they set cursor_sharing to similar.

A SR has been opened with Oracle and has been idle for a week (with the exception for asking for a RDA). I also asked the client to open a ticket with their application vendor to have the session level parameters to be removed.

Kind regards,

Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Tue Apr 19 2011 - 05:26:05 CDT