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 BED91100314C89 for ; Wed, 5 May 2021 13:48:03 +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 EC93F40DC7; Wed, 5 May 2021 11:48:01 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id BB96E41DDE; Wed, 5 May 2021 11:48:01 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1620215281; bh=dSItEv5f/Nbzcu51yTyIN2ZxvtoqN3XIQmiwrDGsmsk=; h=From:Sender:Sender:From; b=d13bgj/kRl2pyCuIZE2ngxoIR81bex+NsA7o+eyU/a3KPef97PqllNfIH0IXIbRxP VY6HD5VBEv4Xj8Z0XkO6MgKdBhmacMCqdSodWLACPvxmpYyERw5JMgHhw3nRSFeTpp 8KuY5mUZNIbk0OAE20bzfwvBoIE3pnlTaltk12VM= 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 itKXHGrcUAr7; Wed, 5 May 2021 11:48:01 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 31C894192F; Wed, 5 May 2021 11:47:59 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1620215280; bh=dSItEv5f/Nbzcu51yTyIN2ZxvtoqN3XIQmiwrDGsmsk=; h=From:Sender:Sender:From; b=BZBZjFXRrpW98h+kHw7kyWQSH5m81sIxgm31J+helJDCwipLUhzQpFt6FdO9yuE+T tT18uznjfQQ8e8kf5sLcVaaNUGm953kwcRQ6VU9nDi41E9AgFMeFhd1wXf/7i8XAzv oKEsS2Tg6eRlu+Vwi8IK/QR9ay/WMSDDB7AjMVC0= Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 05 May 2021 11:47:57 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id C36C341929 for ; Wed, 5 May 2021 11:47:57 +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=cqOZ6wnt; 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 eggO2RobuV9d for ; Wed, 5 May 2021 11:47:57 +0000 (UTC) Received: from mail-ua1-f41.google.com (mail-ua1-f41.google.com [209.85.222.41]) (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 B5BE5418DD for ; Wed, 5 May 2021 11:47:57 +0000 (UTC) Received: by mail-ua1-f41.google.com with SMTP id d30so452265uae.13 for ; Wed, 05 May 2021 04:47:57 -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=kAHJ9FSrAJKS6B8UZXUEdUy28hP1dW6K4FN1bXgOaHk=; b=ibGAafdOoOPY5Jus8voQoRW+I/G2Pt1+ZaUrf/qNr9iz41blatFRy/gPzwQLnsbj7I 3PMTzhCvGjBhXwp0EEFGhyMGaUsugGetuxktmv+KFLPd8QO/svz/sLa7jSM6HL6jFBPw B5HF/UbHXkuQIKnqTNLToRt2BB3HGSiL8fXbbD9Ym1gWBPzp1it7tN79OZhLaQ0Efwte AWQuwNLqyL2XJ6Srm2dQ9e8kn9EZoMvIqjqJG9trT0KzK5g+Sn40HNg+7HB3F6aBbSi5 Qcf5xTZg0FXkr8bVVy5fA2IYdCuqTr7OrIZYgpabsvnxgGi7EmBNkJGhZrlBxZwQgxIG KyZA== X-Gm-Message-State: AOAM5321b5s99fuNL/FQOL5LwJMVI0mQDFJKqugofctTsQgI5/Mcp7Un 0RxCNuJYWcZOzUFRKO6Yf6ijKiwS+T880I+ZEYF3P9uo X-Google-Smtp-Source: ABdhPJzsIQ5QB0fv+HC49VrOp7QAiQV/LckfRJXEZ6nzFWS6fP25bdUIKV1LXAlgU2vbhyEjqUse7o+H+X12h7pIQwI= X-Received: by 2002:a9f:35ef:: with SMTP id u44mr6651510uad.90.1620215277265; Wed, 05 May 2021 04:47:57 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Pap Date: Wed, 5 May 2021 17:17:45 +0530 Message-ID: Subject: Re: Shared memory error To: Tanel Poder Cc: Oracle L Content-Type: multipart/alternative; boundary="0000000000000fa5e905c193c409" X-archive-position: 79822 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 --0000000000000fa5e905c193c409 Content-Type: text/plain; charset="UTF-8" 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 >>> >> --0000000000000fa5e905c193c409 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
We have recently created a new UNDO tablespace on RECO tab= lespace because of space crunch on DATA disk group. So basically all the DM= L and everything happens on DATA whereas UNDO sits on RECO, so can it be th= e cause of such issues anyway?

On Wed, May 5, 2021 at 2:00 PM Pap <oracle.developer35@gmail.com&= gt; wrote:
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

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