Re: ORA-04031 - KGLH0 heap
Date: Tue, 19 Apr 2011 20:08:53 +0200
Message-ID: <4DADCFB5.3030108_at_interia.pl>
On 2011-04-19 12:26, D'Hooge Freek wrote:
>
> 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
>
Hi,
I think You problem maybe caused by cursor_sharing = similar which is actually deprecated
ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting (Doc ID 1169017.1)
and got a lot of nasty side effects like unsafe literals
Unsafe Literals or Peeked Bind Variables (Doc ID 377847.1)
AFAIK kglh0 is heap where sql text is placed and due Your's DB settings
there maybe a lot of heap 0 :).
Trying to speculate here but maybe
BUNDLE DICTIONARY LOOKUP CACHED and
DICTIONARY LOOKUP CURSOR CACHED
are softer soft parse related (huge cursor cache lookups).
As it's production server I'd wait for advice from support ,
but if You got test environment You can try with
cursor_sharing = FORCE as a nasty workaround .
I'm not sure if it helps but should clear some mess similar is causing .
Hope You'll get more detailed advice from other .
Regards
GregG
Najwiekszy wybor samochodow nowych i uzywanych! Sprawdz >> http://linkint.pl/f2970
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 19 2011 - 13:08:53 CDT