Re: 4031 errors in 19.7
Date: Tue, 2 Mar 2021 11:41:50 -0600
Message-ID: <CALrB5povETkzN5A7JR9-Ed_6ZWSTHtjAFo6ULQddZDvpSGbEQw_at_mail.gmail.com>
You mentioned multitent so assuming you are using a PDB for this EBS database. How do the init parameters for sga_target, share_pool_size, etc look like when you compare CDB and PDB?
MOS doc 2170772.1 might be helpful?
Alfredo
On Tue, Mar 2, 2021 at 11:13 AM Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org> wrote:
> We were using 4G (3G in our 11g EBS databases) because these are TEST
> databases with very light usage. Even 1 G larger than 11g, we had errors.
>
>
>
> *From:* Alfredo Abate <alfredo.abate_at_gmail.com>
> *Sent:* Tuesday, March 02, 2021 11:57 AM
> *To:* tanel_at_tanelpoder.com
> *Cc:* Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org>; Petr Novak <
> Petr.Novak_at_trivadis.com>; oracle-l_at_freelists.org
> *Subject:* Re: 4031 errors in 19.7
>
>
>
> +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/
> <https://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftanelpoder.com%2F2009%2F09%2F09%2Fkgh-no-access-allocations-in-vsgastat-buffer-cache-within-shared-pool%2F&data=04%7C01%7Cjbeckstrom%40gcrta.org%7C5628edebc2314af30ca808d8dd9c339e%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637503010244457316%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=DpK%2FH%2FvqR3RJlGNqg3kbaDyZ%2BH8%2FKwAjlYw063ORZuo%3D&reserved=0>
>
>
>
> 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/
> <https://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftanelpoder.com%2F2009%2F09%2F13%2Fkgl-simulator-shared-pool-simulator-and-buffer-cache-simulator-what-are-these%2F&data=04%7C01%7Cjbeckstrom%40gcrta.org%7C5628edebc2314af30ca808d8dd9c339e%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637503010244467272%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=u%2F9K3Hjhx5MuLUIPlhbtYM1ny8aEGm7tI0zLo67nKbY%3D&reserved=0>
>
>
>
> 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
> <https://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftanelpoder.com%2Fevents&data=04%7C01%7Cjbeckstrom%40gcrta.org%7C5628edebc2314af30ca808d8dd9c339e%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637503010244467272%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=3oOLP%2FPuYTN4tzIxetBk8NtQIsZRVtOwHLDSWH65JK8%3D&reserved=0>
>
>
>
>
>
> 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
> <https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l&data=04%7C01%7Cjbeckstrom%40gcrta.org%7C5628edebc2314af30ca808d8dd9c339e%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637503010244477228%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=LZwpCMgLdcjqkVdAzhEa3oAN440hblZh%2FJEsCXQP6JA%3D&reserved=0>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 02 2021 - 18:41:50 CET