Re: PGA on higher version

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_at_dxc.com> 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_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> 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_at_gmail.com> 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
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l