Re: 4031 errors in 19.7

From: Alfredo Abate <alfredo.abate_at_gmail.com>
Date: Tue, 2 Mar 2021 10:56:49 -0600
Message-ID: <CALrB5pogv4fAPnsSobR3KvzqepB_iM8uSdfXd3SqfbgRc8zgOA_at_mail.gmail.com>



+1 on what Tanel said.

For our EBS (12.1.3), while we do use SGA_TARGET, we do set a minimum we like to see for each of the different pools. For example our shared_pool_size is set to 4G. We also set the shared_pool_reserved_size (10% of shared_pool_size). DB version is old (11.2.0.3).

Alfredo

On Mon, Mar 1, 2021 at 4:41 PM Tanel Poder <tanel_at_tanelpoder.com> wrote:

> KGH: NO ACCESS is buffer cache inside the shared pool (yes, it's a thing
> since Oracle 10.2 :-)
>
>
> https://tanelpoder.com/2009/09/09/kgh-no-access-allocations-in-vsgastat-buffer-cache-within-shared-pool/
>
> For some reason the automatic SGA memory manager (SGA_TARGET or
> MEMORY_TARGET) decided that it wanted to use more buffer cache at the
> expense of shared pool, but couldn't completely clean a granule from shared
> pool chunks (some were pinned), so it flushed out what it could and put the
> KGH: NO ACCESS reservations in there, for buffer cache usage.
>
> I've written about
> https://tanelpoder.com/2009/09/13/kgl-simulator-shared-pool-simulator-and-buffer-cache-simulator-what-are-these/
>
> You can set the *shared_pool_size* parameter to a bigger value, so it
> will act as the minimum size for shared pool, even if your SGA is otherwise
> dynamic thanks to ASMM. Btw, a total 3G shared_pool for E-Business Suite
> (and 19c) doesn't seem like that much (of course depends on how many
> concurrent users you have + how many EBS modules are in use too).
>
> --
> Tanel Poder
> https://tanelpoder.com/events
>
>
> On Mon, Mar 1, 2021 at 11:03 AM Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org>
> wrote:
>
>> From the trace file. What could cause the KGH area to be so large?
>>
>> TOP 10 MEMORY USES FOR SGA HEAP
>> ----------------------------------------------
>> "KGH: NO ACCESS 0001" 1195 MB 40%
>> "SQLA 0003" 507 MB 17%
>> "free memory " 356 MB 12%
>> "KGLH0 0003" 270 MB 9%
>> "KGLS 0003" 70 MB 2%
>> "KGLHD 0003" 47 MB 2%
>> "PLMCD 0003" 40 MB 1%
>> "KQR X PO 0003" 38 MB 1%
>> "KGLH0 0001" 34 MB 1%
>> "SO private sga 0001" 32 MB 1%
>> TOTALS ---------------------------------------
>> Total free memory 356 MB
>> Total memory alloc. 2668 MB
>> Grand total 3024 MB
>>
>> -----Original Message-----
>> From: Petr Novak <Petr.Novak_at_trivadis.com>
>> Sent: Monday, March 01, 2021 8:22 AM
>> To: oracle-l_at_freelists.org; Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org>
>> Subject: AW: 4031 errors in 19.7
>>
>> Hallo Jeff,
>>
>> I had similar experience . After upgrade from 12.2 single tenant to 19c
>> multitenant the SQLArea was much smaller for same shared pool size.
>> In AWR the difference between shared pool size in
>> DBA_HIST_MEM_DYNAMIC_COMP - sum( Shared Pool components) from
>> DBA_HIST_SGASTAT increased after upgrade for the DB with 400G sga_target
>> from 6G to 35G. May be some internal memory structures related to
>> multitenant ? For 19c single tenant this difference was almost same as
>> under 12c
>>
>> Best Regards
>> Petr
>>
>>
>>
>> ________________________________________
>> Von: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> im
>> Auftrag von Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org>
>> Gesendet: Montag, 1. März 2021 14:01
>> An: oracle-l_at_freelists.org
>> Betreff: 4031 errors in 19.7
>>
>> Since upgrading our Oracle EBS 12.1.3 IT Test system to 19.7 mutltenant
>> from 11.2.0.4. We have been experiencing shared pool errors. After the last
>> one, we increased sga_target to 1 G larger than we had in 11g. However, we
>> just experienced another 4031 error. The IT database only has the EBS
>> concurrent managers running and very other light activity. The error
>> occurred on Saturday when only thing else running was stats. Does 19c
>> multitenant require a much larger sga_target?
>>
>> Jeffrey Beckstrom
>> Greater Cleveland Regional Transit Authority
>> 1240 W. 6th Street
>> Cleveland, Ohio 44113
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Mar 02 2021 - 17:56:49 CET

Original text of this message