Re: ORA-04031 - KGLH0 heap

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-l


Other related posts: