ORA-04031 - KGLH0 heap

Hi,

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@xxxxxxxxx
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

��i��0���zX���+��n��{�+i�^

Other related posts: