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 065D01002C1D3F for ; Wed, 5 May 2021 15:13:54 +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 4598440E45; Wed, 5 May 2021 13:13:52 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 37B0241C67; Wed, 5 May 2021 13:13:52 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1620220432; bh=3X1SSRzNU4O5s59LFcG2coTfZ7BfNMb/ucNwE2y8yuY=; h=From:Sender:Sender:From; b=J4Yiy/xSZWpNVyqZTSXCaIMWKy3ZqnEi6aNvSskZgucWVLD7+ObF7QXXSFdFug/JR iZWGCmEqYQs2USuWblDC7lu4MPK4AJNRFnw6JlfOQ7GMrVGH53refMnzOojIBwUhA9 GU7uOgYYeCv6DOddOpSusO0kF3AEtXr4unSxxgFQ= 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 kODMsyBSBr9e; Wed, 5 May 2021 13:13:52 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id D1AF841DA6; Wed, 5 May 2021 13:13:49 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1620220430; bh=3X1SSRzNU4O5s59LFcG2coTfZ7BfNMb/ucNwE2y8yuY=; h=From:Sender:Sender:From; b=Z8yfFtMesuHiVVl6D/eCkria2OhuwWaJ0AhwFXHAf4OvU8LE78eO7hGlGb482iNx0 nznVyft7HD1WNDE/dr0wdKwWXsimpGd9kq0qyNI5QMTfg07lgh03Sp1xfbJnO1YGcM d5QH1ZBWYs59res1tjHMlzatdkOSylFYyI181R9g= Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 05 May 2021 13:13:48 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 5A04541A8F for ; Wed, 5 May 2021 13:13:48 +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=co3Mr+2o; 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 qDK3yxrRve5y for ; Wed, 5 May 2021 13:13:48 +0000 (UTC) Received: from mail-vs1-f50.google.com (mail-vs1-f50.google.com [209.85.217.50]) (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 4DB5D41313 for ; Wed, 5 May 2021 13:13:48 +0000 (UTC) Received: by mail-vs1-f50.google.com with SMTP id r18so1033601vso.12 for ; Wed, 05 May 2021 06:13:48 -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=enlxuadPNXFctb3GPlMR0EK9qdzS53ytP3+6IHEKwZs=; b=JE3eACgXmOZue6zvBs20LOJED+AjyYzjlULFpFSH0Uyfj2Z+VbUjUmcbZ1R+CKeQXx hvVA7WLrdCmdZ+VTc/R3DEE7zS4/W+Uwp80yQ22m93VFjBVETSuF+7S/qX9jrgGVwRya gKe+wXu/2IDHgjuwxS8NmiVpD0pE9HLYoSwmKKe91MNSCo/equrK+HtGy/V/apzXvi7L +kkolmEbQapB8CYFa1K/c7GcA3eYVC7/4Nuze9zTpyhKQMTc6RSt5e+zyVL0M49xnmDl Gq0wxH7yl0hLbIb3cyRhWjVBfdhEeS+p5XsXe0T4uod+Xl9WbTeIS3W+yKnqqj6m7yU+ lo4Q== X-Gm-Message-State: AOAM533y4yCU0fWD6VWjG56Zx/Y9VBCd6M5yoDj/Ta+oR0gw+PdttZIP 9g36yAPeG54e2roWiCAQbymiE5ml+xLHss6Mr47oq9Go X-Google-Smtp-Source: ABdhPJyW4+lg+YBMsqCewg54re0ACXasceSraa9qQjA6tiMNN3SIWB3UfuyRUisqEhUHOGF4u+m4CDuJ1+wGuhMt8qo= X-Received: by 2002:a67:d78b:: with SMTP id q11mr17010859vsj.37.1620220427865; Wed, 05 May 2021 06:13:47 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Pap Date: Wed, 5 May 2021 18:43:36 +0530 Message-ID: Subject: Re: Shared memory error To: Tanel Poder Cc: Oracle L Content-Type: multipart/alternative; boundary="0000000000000f922505c194f721" X-archive-position: 79823 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 --0000000000000f922505c194f721 Content-Type: text/plain; charset="UTF-8" Is there a way(maybe from dba_hist* views) we can fetch the speed at which UNDO read/write is happening now from RECO vs from DATA disk group in the past? On Wed, May 5, 2021 at 5:17 PM Pap wrote: > We have recently created a new UNDO tablespace on RECO tablespace because > of space crunch on DATA disk group. So basically all the DML and everything > happens on DATA whereas UNDO sits on RECO, so can it be the cause of such > issues anyway? > > On Wed, May 5, 2021 at 2:00 PM Pap wrote: > >> 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 >>>> >>> --0000000000000f922505c194f721 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Is there a way(maybe from dba_hist* views) we can fetch th= e speed at which UNDO read/write is happening now from RECO vs from DATA di= sk group in the past?

On Wed, May 5, 2021 at 5:17 PM Pap <oracle.developer35@gmail.com> wrote= :
We have recently created a new UNDO tablespace on RECO tablespace becaus= e of space crunch on DATA disk group. So basically all the DML and everythi= ng happens on DATA whereas UNDO sits on RECO, so can it be the cause of suc= h issues anyway?

On Wed, May 5, 2021 at 2:00 PM Pap <oracle.developer35@gmail.com<= /a>> wrote:
<= div dir=3D"ltr">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 share= d pool shrink/grow operations in v$sga_resize_ops - and since the failed al= location sizes are pretty small (256 & 760 bytes, not 10+ kB and not ev= en the standard 4k extent size for library cache object heaps), I would sus= pect that you ran out of memory in one of the shared sub-sub-pools for sess= ion-duration allocations (KKSSP means "Session Pages" where sessi= on-connected things like library cache lock & library cache pins are ke= pt).

Oracle 12c splits your shared pool subpools into on= ly 2 sub-sub-pools (durations), but 11.2 splits them to 4.=C2=A0
=
What's the _enable_shared_pool_durations value in= your env?

Also, you can take a look into number o= f shared pool sub-pools=C2=A0and their free memory (although it doesn't= show fragmentation info):
And since an ORA-4031 error sh= ould dump some shared pool heap details to a tracefile, you can run heapdum= p analyzer on it, to see how much free memory you had in the sub-sub-heaps = at the time:
On versions below 12c, when ha= ving unexplained shared pool memory errors (ORA-4031s) and you don't wa= nt to go deeper with dynamic tracing and = things like x$ksmlru, then a common workaround is to= set=C2=A0_enable_shared_pool_durations =3D false (with the usual co= mments that you should get some blessing from Oracle support or by a MOS se= arch 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=C2=A0_kghdsidx_co= unt 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 proba= bly works ok enough...)

--
<= /div>

On Tue, May 4, 2021 at 9:02 AM Pap <oracle.developer35@gmail.com&g= t; wrote:

Hello Listers, I= t'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 t= ime(with sga_target being set as ~100GB on each node).=C2=A0We are not seei= ng 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 err= or in few other databases in the past, but there we used to see the compone= nt "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 w= e 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:

=C2=A0OR= A-04031: unable to allocate 760 bytes of shared memory ("shared pool&q= uot;,"unknown object","KKSSP^847","kglss")=C2= =A0

=C2=A0ORA-04031: u= nable to allocate 256 bytes of shared memory ("shared pool","= ;unknown object","KKSSP^1807","kgllk")

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


Regards

Pap

--0000000000000f922505c194f721-- -- http://www.freelists.org/webpage/oracle-l