Re: ORA-04031 - KGLH0 heap
- From: Grzegorz Goryszewski <grzegorzof@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 19 Apr 2011 20:08:53 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
