Re: PGA on higher version

Pap,

I have also been confronted with an increase in PGA consumption recently because of MMON_SLAVE not freeing up its used PGA memory. There is a MOS note which explains this issue

High PGA Consumption By Mmon Slaves In The Server (Doc ID 2700130.1)

I have also disabled the tunning advisor and the ADDM generation because I have observed a PGA leak when the ADDM advisor is launched as well.

Bug 31695062 - PGA Leak in ADDM Advisor Framework (Doc ID 31695062.8)

 This is why for almost all 19c applications I am working on, I tend to disable both the tuning advisor and the space advisor

Best regards
Mohamed

Le jeu. 20 mai 2021 à 20:08, Pap <oracle.developer35_at_gmail.com> a écrit :

> 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
>>
>>
>>
>>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

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