In this case , memory_target is set as 0 and sga_max_size set as 22GB.
which means it's not AMM. And we got the error while we still had ~50%+
memory free on the host. So it mostly failed because it reached max
pga_aggregate_limit. So in this case should we increase the
pga_aggregate_limit or we should just keep it null so there would be no
hard limit defined?
On Wed, May 19, 2021 at 11:04 PM Powell, Mark <mark.powell2@xxxxxxx> wrote:
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Pap <oracle.developer35@xxxxxxxxx>
*Sent:* Wednesday, May 19, 2021 7:24 AM
*To:* Lok P <loknath.73@xxxxxxxxx>
*Cc:* Oracle L <oracle-l@xxxxxxxxxxxxx>
*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@xxxxxxxxx> 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?
Limiting Process Size with Database Parameter PGA_AGGREGATE_LIMIT (Doc ID
1520324.1)
Regards
Lok
On Wed, May 19, 2021 at 2:30 AM Pap <oracle.developer35@xxxxxxxxx> wrote:
We are seeing higher pga_allocated post 19C migration. Experienced
Ora-04030 already post migration in many cases.So trying to understand if
there is any change in behaviour as compared to 11.2.0.4? And how should we
debug and estimate the correct value here?
ORA-04030: out of process memory when trying to allocate 248 bytes (KSIPC
Top Loca,ksipc pga chnk)
Regards
Pap
