Pap, there are a fair number of but reports associated with using pga_aggregate_limit.
I am listing just a few, the 19.7 one may be of interest to you
-- 12.1 and 12.2
Bug 24416451 - PGA Capping Way Under PGA_AGGREGATE_LIMIT (Doc ID 24416451.8)
-- 19.7+
Cannot Increase or Decrease The Value of PGA_AGGREGATE_LIMIT on 19c (Doc ID 2685564.1)
-- below 20
Bug 30028599 - ORA-4036: PGA memory used by the instance exceeds pga_aggregate_limit (Doc ID 30028599.8)
--
ORA-27090 Is Reported When PGA_AGGREGATE_LIMIT Is Set To 0 On HP-UX (Doc ID 2706572.1)
The normal way we handle reoccurring ORA-04030 errors is to reduce the size of our SGA to make more memory available to the OS to be used for PGA on systems that do not use AMM. With AMM we would reset oga_aggregae_limit and try to pga_aggregate_target to see if the system will accept that. If not, we would probably switch to ASMM for memory managment.
Mark Powell
Database Administration
(313) 592-5148
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Pap <oracle.developer35_at_gmail.com>
Sent: Wednesday, May 19, 2021 7:24 AM
To: Lok P <loknath.73_at_gmail.com>
Cc: Oracle L <oracle-l_at_freelists.org>
Subject: Re: PGA on higher version
Thank you.
v$process_memory shows the few top sessions occupying ~80-90MBs of memory each but all of them belong to the category 'Other'. Not seeing any such sql which can be suspected.
So how can we be sure it's just the default organic demand because of the way 19C processes works or we really have some odd sql causing this issue?
We have memory_target and memory_max_target set as '0'. Pga_aggregate_target set as 9GB and pga_aggregate_limit set as 18GB. Number of processes is staying around ~1000. But from DBA_HIST_PGA_STAT it seems we have "total pga allocated" staying almost always around ~15GB, so does it mean that we really need to increase PGA_AGGREgATE_LIMIT here?
On Wed, May 19, 2021 at 4:46 PM Lok P <loknath.73_at_gmail.com<mailto:loknath.73_at_gmail.com>> wrote:
Below does point to the fact that recent Oracle versions need higher PGA. But i would say , do check v$process_memory to see if any one odd sql is causing this. What are the pga parameters set?
