RE: 4031 errors in 19.7
Date: Tue, 2 Mar 2021 18:05:36 +0000
Message-ID: <DM6PR09MB46777EE00BE93EC52C4DD9BEDF999_at_DM6PR09MB4677.namprd09.prod.outlook.com>
PDB and CDB were set identicall.
Oracle support advised me to increase sga_target by 1 G and unset memory parameters for the PDB.
From: Alfredo Abate <alfredo.abate_at_gmail.com>
Sent: Tuesday, March 02, 2021 12:42 PM
To: Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org>
Cc: tanel_at_tanelpoder.com; Petr Novak <Petr.Novak_at_trivadis.com>; oracle-l_at_freelists.org
Subject: Re: 4031 errors in 19.7
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<mailto: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<mailto:alfredo.abate_at_gmail.com>>
Sent: Tuesday, March 02, 2021 11:57 AM
To: tanel_at_tanelpoder.com<mailto:tanel_at_tanelpoder.com>
Cc: Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org<mailto:jbeckstrom_at_gcrta.org>>; Petr Novak <Petr.Novak_at_trivadis.com<mailto:Petr.Novak_at_trivadis.com>>; oracle-l_at_freelists.org<mailto: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<mailto: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%7C44a709705b3b418bb47108d8dda27dbe%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637503037264804086%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=XRbZfTSBfkjr3523fJ3p2AH%2Fu3HIU4ezYNwxeS1TB1s%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%7C44a709705b3b418bb47108d8dda27dbe%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637503037264814047%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=bJM4j6Hth2KgkSs5wtO1xxyGB5419QXrMeSizQsOQJM%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%7C44a709705b3b418bb47108d8dda27dbe%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637503037264814047%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=j1aF39heEkf7PhRKDZvEX93o1AgCk8yBfPg27GTgmlA%3D&reserved=0> On Mon, Mar 1, 2021 at 11:03 AM Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org<mailto: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 ----------------------------------------------Received on Tue Mar 02 2021 - 19:05:36 CET
"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<mailto:Petr.Novak_at_trivadis.com>> Sent: Monday, March 01, 2021 8:22 AM To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>; Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org<mailto: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<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> im Auftrag von Beckstrom, Jeffrey <jbeckstrom_at_gcrta.org<mailto:jbeckstrom_at_gcrta.org>> Gesendet: Montag, 1. März 2021 14:01 An: oracle-l_at_freelists.org<mailto: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%7C44a709705b3b418bb47108d8dda27dbe%7Cebe8e20736ec47f48cb8f5f757605f5d%7C1%7C0%7C637503037264823999%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=R2KsDjRTIBHWxW%2B4WqAstWCTtQWMbucAUrRx51pDguY%3D&reserved=0> -- http://www.freelists.org/webpage/oracle-l