RE: ORA-04031 - KGLH0 heap
Date: Tue, 19 Apr 2011 22:05:59 +0200
Message-ID: <4814386347E41145AAE79139EAA398981500B50FEB_at_ws03-exch07.iconos.be>
Hi,
Thanks for the feedback.
Removing the cursor_sharing=similar is certainly one of the things I try to do, but as this is set by the third party application (hard coded) I have no way to override this setting.
I checked the v$open_cursor view because of the session_cached_cursor setting, expecting to see high number of session cached cursors. But in reality the maximum number for the type "SESSION CURSOR CACHED" is lower then expected.
When I look at the cursor type "BUNDLE DICTIONARY LOOKUP CACHED", I see that the statements are statements working on the dictionary objects. No surprise so far, but I have the feeling that the number of cursors are very high:
CURSOR_TYPE COUNTED SQL_ID ROW_NUM
--------------------------------- ---------- ------------- ----------
BUNDLE DICTIONARY LOOKUP CACHED 10155 96g93hntrzjtr 1
9313 2q93zsrvbdw48 2
9198 3w4qs0tbpmxr6 3
8604 83taa7kaw59c1 4
8596 6aq34nj2zb2n7 5
8547 7ng34ruy5awxq 6
8520 gx4mv66pvj3xz 7
8513 5n1fs4m2n2y0r 8
8318 53saa2zkr6wc3 9
5608 db78fxqxwxt7r 10
7 rows selected.
sys_at_GLIMS> select sql_fulltext from v$sql where sql_id = '96g93hntrzjtr' and rownum = 1;
SQL_FULLTEXT
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_ size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, a vgcln from hist_head$ where obj#=:1 and intcol#=:2
sys_at_GLIMS> select sql_fulltext from v$sql where sql_id = '2q93zsrvbdw48' and rownum = 1;
SQL_FULLTEXT
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ w here obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
sys_at_GLIMS> select sql_fulltext from v$sql where sql_id = '3w4qs0tbpmxr6' and rownum = 1;
SQL_FULLTEXT
select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 from cdef$ where robj# =:1
Following lists the top 10 statements by sharable mem (in bytes)
USERS VERSION LOADED OPEN KEPT
SQL_ID SHARABLE_MEM OPENING EXECUTIONS COUNT VERSIONS VERSIONS VERSIONS
------------- -------------- ------- ---------- ------- -------- -------- --------
8svsbp6nq4z84 67.291.720 12 8.468 9 1 3 0
3vkxv5qddr6d4 59.624.782 9 7.376 11 1 2 0
1gf8k4690j2p8 57.632.541 10 3.202 11 2 5 0
4y6zhv13yf146 55.324.465 1 2.246 1 1 1 0
0y660dnu3ur0v 32.306.060 2 51.618 3 1 1 0
cuvy3ffvs54af 31.713.850 6 2.384 15 0 4 0
94wkaxvsk74sc 28.744.170 3 13.073 3 0 1 0
3n4q93jqhsgmx 28.188.202 4 33.524 15 0 3 0
4z7w9wz3s13b5 27.424.063 5 4.527 6 0 3 0
cm1nrb4w4dvqc 25.409.437 1 5.170 2 0 1 0
Am I wrong in considering the sharable mem usage as too high? (the statements in the list are coming from the application. They are not like the statements above).
I have just restarted the instance (to free the memory again) and currently the KGLH0 heap is 56 MB. Just before the reboot this was almost 780 MB.
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
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Grzegorz Goryszewski
Sent: dinsdag 19 april 2011 20:09
To: oracle-l_at_freelists.org
Subject: 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 -- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 19 2011 - 15:05:59 CDT