Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id CDA7F100314CA4 for ; Wed, 5 May 2021 10:30:42 +0200 (CEST) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id F347C403EC; Wed, 5 May 2021 08:30:38 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id EB785425E2; Wed, 5 May 2021 08:30:38 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1620203438; bh=CKzjYsQGU2Q8pt7Et5De2L5AzjuDnA6lbtxukU3Ucj0=; h=From:Sender:Sender:From; b=Qw4uZXhDwsoKN8JzwsufVZqLnO8iz2ShZ2LJn3VLh6ctz32brtqkmoJk5GMMd7Y+0 HRIVZiLPAx/NRulZCWpBaGZF9QkgWXRJ1xVjtcKh4UhuFEZhFf6VZu1VTNVzhe8MiF DvBlB51mRah3QHsKoASJMwlIjv7uooxmsRb0uxG8= X-Virus-Scanned: by FreeLists at turing2.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id FdkuhCXCuJnC; Wed, 5 May 2021 08:30:38 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 48284425E3; Wed, 5 May 2021 08:30:36 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1620203437; bh=CKzjYsQGU2Q8pt7Et5De2L5AzjuDnA6lbtxukU3Ucj0=; h=From:Sender:Sender:From; b=lAbdMmENyUYFIokhkz7oTtCdTeZhBBAhWCw5oaxJUkgAHQNGNnRuZrFmq7tj1mlXJ ec7i1Vlnqf2WIQDdVGbPa9IsKW7JureiqTVlVLDLnCIy12DVhil4Q33Gi3wxKvfwjr IoQHCe2KNJ/7jwWfo/lhQEnIrfJEidr3CeU8+DJg= Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 05 May 2021 08:30:35 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id EC932425E2 for ; Wed, 5 May 2021 08:30:34 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20161025 header.b=pSpsPoTS; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id xMOABIVfuTnB for ; Wed, 5 May 2021 08:30:34 +0000 (UTC) Received: from mail-ua1-f43.google.com (mail-ua1-f43.google.com [209.85.222.43]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id D3D86425E1 for ; Wed, 5 May 2021 08:30:34 +0000 (UTC) Received: by mail-ua1-f43.google.com with SMTP id 33so316736uaa.7 for ; Wed, 05 May 2021 01:30:34 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=1s+JxgOEHrhvgLFZsLVA0qcOnnRjLvZcUDl1Y+Fx8ds=; b=Q+NAP76isPYBq8Dc1PkGb+3/MNLIrxmRxCI7KywvWy2jAXyjQLAsZe8XF0XB/GxkvW BjJ782D43HTVovhyQLKxkgw5dl6nUF99/miTl9CMfupia7NF6Fi9upZRXmu36BSH8JYC EZqeT+OD8U8K+UgDutycZFyM6kMoKDWVdEbzae5a4/oS69S4U5IyOsTOXBDsGM4IycxK xMALrbd6mkKnU8nhvBX7tyVnFa0mA3fZWet7arz3tWS2ke2/vx9RsDGENhdocq3bxn6q BE5EIBsPYn2zaQr3jsnKmx3zCnCATWfMrP6BeoY7Suiqu9NfTNpDaFPo+T77nTqNfEM1 0s+Q== X-Gm-Message-State: AOAM533XOSfpYHpQSCVdNYc6Waw115QB17etuyYXafe1G8vxK7xwyMv3 Er85BfC2rfYuO55WFNsIKsf05eBHpELgMs+dILndbjrMeAM= X-Google-Smtp-Source: ABdhPJxbjh8afsP+ukwot3R4KCj+ncRahaSV1UjO9lpyhbib5oi/DmTJ7vHrvd5WSf3gRQVPrkkR0lDJQF5Z2FAyCTQ= X-Received: by 2002:ab0:6309:: with SMTP id a9mr10844614uap.75.1620203434392; Wed, 05 May 2021 01:30:34 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Pap Date: Wed, 5 May 2021 14:00:23 +0530 Message-ID: Subject: Re: Shared memory error To: Tanel Poder Cc: Oracle L Content-Type: multipart/alternative; boundary="0000000000002bbec905c1910279" X-archive-position: 79821 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: oracle.developer35@gmail.com Precedence: normal Reply-To: oracle.developer35@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --0000000000002bbec905c1910279 Content-Type: text/plain; charset="UTF-8" Thank you Tanel. We have *_enable_shared_pool_durations* set as TRUE. And we don't see much of the resize operations for the node which came across this error. Oracle suggests to set the minimum value for both shared_pool and db_cache to get rid of this ora-4031. So wondering if this will have a similar impact as setting _enable_shared_pool_durations to FALSE? Another thing we noticed is post this error , we are seeing a lot of queries running slow across databases, and there have been no change in plan but the 'cell single block physical read' has been increased significantly and eventually they are going for UNDO(i.e. High transaction table consistent read undo record applied). Can this be related to the memory error anyway? On Tue, May 4, 2021 at 9:06 PM Tanel Poder wrote: > Since this is 11.2 and if you don't see shared pool shrink/grow operations > in v$sga_resize_ops - and since the failed allocation sizes are pretty > small (256 & 760 bytes, not 10+ kB and not even the standard 4k extent size > for library cache object heaps), I would suspect that you ran out of memory > in one of the shared sub-sub-pools for session-duration allocations (KKSSP > means "Session Pages" where session-connected things like library cache > lock & library cache pins are kept). > > Oracle 12c splits your shared pool subpools into only 2 sub-sub-pools > (durations), but 11.2 splits them to 4. > > What's the* _enable_shared_pool_durations* value in your env? > > Also, you can take a look into number of shared pool sub-pools and their > free memory (although it doesn't show fragmentation info): > > - > https://tanelpoder.com/2009/06/04/ora-04031-errors-and-monitoring-shared-pool-subpool-memory-utilization-with-sgastatxsql/ > > And since an ORA-4031 error should dump some shared pool heap details to a > tracefile, you can run heapdump analyzer on it, to see how much free memory > you had in the sub-sub-heaps at the time: > > - > https://tanelpoder.com/2009/01/02/oracle-memory-troubleshooting-part-1-heapdump-analyzer/ > > On versions below 12c, when having unexplained shared pool memory errors > (ORA-4031s) and you don't want to go deeper with dynamic tracing > > and things like x$ksmlru > , then a > common workaround is to set *_enable_shared_pool_durations = false* (with > the usual comments that you should get some blessing from Oracle support or > by a MOS search for that parameter & documented bugs/issues). > > In past (perhaps back in 9i, 10g days), I sometimes worked around the > unexplained shared pool issues (bugs), by reducing the *_kghdsidx_count* > value (and sometimes setting it to 1), to avoid the complexity (and new > bugs) of the shared pool subpools completely (but on 11.2.0.4 it probably > works ok enough...) > > -- > Tanel Poder > https://tanelpoder.com/events/ > > > On Tue, May 4, 2021 at 9:02 AM Pap wrote: > >> Hello Listers, It's a 4 node RAC database with version 11.2.0.4. It's >> using ASMM. We saw queries failing with ORA-04031 error twice in the past , >> even if the sum of all the components of the shared_pool was around ~15GB >> during the time(with sga_target being set as ~100GB on each node). We are >> not seeing any such spike/variation in overall usage of the shared_pool >> components but still getting this below error intermittently. . >> >> And i remember, we used to see the same error in few other databases in >> the past, but there we used to see the component "KGH-NO ACCESS" of shared >> pool used to grow rapidly chewing up all the sga memory. But in this case >> we are not seeing such symptoms and we seem to have free memory left while >> it's errored out. So wondering if there is any associated bug? >> >> *Parameters from V$parameter:-* >> >> sga_max_size - 120GB >> >> sga_target - 100GB >> >> shared_pool_size - 0 >> >> memory_target - 0 >> >> *Error:* >> >> ORA-04031: unable to allocate 760 bytes of shared memory ("shared >> pool","unknown object","KKSSP^847","kglss") >> >> ORA-04031: unable to allocate 256 bytes of shared memory ("shared >> pool","unknown object","KKSSP^1807","kgllk") >> >> ORA-04031: unable to allocate bytes of shared memory ("","","","") >> >> >> Regards >> >> Pap >> > --0000000000002bbec905c1910279 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you Tanel. We have=C2=A0 _enable_shared_pool_durations=C2=A0set as TRUE. And we don't see= much of the resize operations for the node which came across this error. O= racle suggests to set the minimum value for both shared_pool and db_cache t= o get rid of this ora-4031. So wondering if this will have a similar impact= as setting=C2=A0 _enable_shared_pool_durations to FALSE? Another thing we noticed is post th= is error , we are seeing a lot of queries running slow across databases, an= d there have been no change in plan but the 'cell single block physical= read' has been increased=C2=A0significantly and eventually they are go= ing for UNDO(i.e. High transaction table consistent read undo record applie= d). Can this be related to the memory error anyway?


=

On Tue, May 4, 2021 at 9:06 PM Tanel Poder <tanel@tanelpoder.com> wrote:
Since this is 11.2 and if you don't see shared pool shrink/gro= w operations in v$sga_resize_ops - and since the failed allocation sizes ar= e pretty small (256 & 760 bytes, not 10+ kB and not even the standard 4= k extent size for library cache object heaps), I would suspect that you ran= out of memory in one of the shared sub-sub-pools for session-duration allo= cations (KKSSP means "Session Pages" where session-connected thin= gs like library cache lock & library cache pins are kept).

Oracle 12c splits your shared pool subpools into only 2 sub-sub-pool= s (durations), but 11.2 splits them to 4.=C2=A0

Wh= at's the _enable_shared_pool_durations value in your env?
<= div>
Also, you can take a look into number of shared pool sub= -pools=C2=A0and their free memory (although it doesn't show fragmentati= on info):
And since an ORA-4031 error should dump some sh= ared pool heap details to a tracefile, you can run heapdump analyzer on it,= to see how much free memory you had in the sub-sub-heaps at the time:
On versions below 12c, when having unexplained = shared pool memory errors (ORA-4031s) and you don't want to go deeper w= ith dynamic tracing and things like x$ksmlru, then a common workaround is to set=C2=A0_ena= ble_shared_pool_durations =3D false (with the usual comments that you s= hould get some blessing from Oracle support or by a MOS search for that par= ameter & documented bugs/issues).

In past (per= haps back in 9i, 10g days), I sometimes worked around the unexplained share= d pool issues (bugs), by reducing the=C2=A0_kghdsidx_count value (an= d sometimes setting it to 1), to avoid the complexity (and new bugs) of the= shared pool subpools completely (but on 11.2.0.4 it probably works ok enou= gh...)

--
Tanel Poder


On Tu= e, May 4, 2021 at 9:02 AM Pap <oracle.developer35@gmail.com> wrote:

Hello Listers, It's a 4 node= RAC database with version 11.2.0.4. It's using ASMM. We saw queries f= ailing with ORA-04031 error twice in the past , even if the sum of all the = components of the shared_pool was around ~15GB during the time(with sga_tar= get being set as ~100GB on each node).=C2=A0We are not seeing any such spik= e/variation in overall usage of the shared_pool components but still gettin= g this below error intermittently. .

And i remember, we used to see the same error in few other = databases in the past, but there we used to see the component "KGH-NO = ACCESS" of shared pool used to grow rapidly chewing up all the sga mem= ory. But in this case we are not seeing such symptoms and we seem to have f= ree memory left while it's errored out. So wondering if there is any as= sociated bug?

Para= meters from V$parameter:-

sga_max_size - 120GB

sga_target - 100GB

shared_pool_size - 0

memo= ry_target - 0

Error:

=C2=A0ORA-04031: unable = to allocate 760 bytes of shared memory ("shared pool","unkno= wn object","KKSSP^847","kglss")=C2=A0

=C2=A0ORA-04031: unable to allocat= e 256 bytes of shared memory ("shared pool","unknown object&= quot;,"KKSSP^1807","kgllk")

=C2=A0ORA-04031: unable to allocate bytes of shared memory (&= quot;","","","")


Regards

<= p style=3D"font-family:"Oracle Sans";color:rgb(85,90,98);font-siz= e:14px;word-break:break-word;line-height:inherit;box-sizing:border-box;padd= ing:0px;margin:0px;border:0px;vertical-align:baseline;outline:0px;text-over= flow:ellipsis;white-space:pre-wrap">Pap

--0000000000002bbec905c1910279-- -- http://www.freelists.org/webpage/oracle-l