Re: PGA on higher version

It seems at least the top 4, MMON actions which you mentioned will vanish if you set *"_report_capture_cycle_time"=0* as mentioned in doc -Doc ID 2700130.1. But then the downside of that is you may encounter the below bug which will make your sql_monitor history blank which you may not want.

Sql Monitor History Reports Empty due to Error 'No SQL executions were monitored' (Doc ID 2352222.1
<https://support.oracle.com/rs?type=doc&amp;id=2352222.1>)

Automatic Report Flush

KDILM background EXEcution

Intensive AutoTask Dispatcher

KDILM background CLeaNup

On Sat, May 22, 2021 at 4:59 PM Pap <oracle.developer35_at_gmail.com> wrote:

> Thank you Mohamed.
>
> You pointed to 'ADDM' advisor framework pga leak bug, and in doc it's
> saying no work around but patch. But as you mentioned, in your case you
> disabled "ADDM generation", so where can i see the status of that task, if
> that is disabled or not in our case and is there any downside of disabling
> that? Is it simply going to disable the ADDM generation capability?
>
> We see in dba_autotask_operation/client, we already have all the advisor
> autotasks disabled except 'auto optimizer stats collection'. Hope this is
> okay, because 'auto optimizer stats collection is kind of a 'catch all' Job
> which ensures stale stats collection apart from our own manual stats
> collection job.
>
> CLIENT_NAME STATUS
>
> auto optimizer stats collection ENABLED
>
> auto space advisor DISABLED
>
> sql tuning advisor DISABLED
>
> Another thing we were seeing is, the MMON_SLAVE process those coming on
> top are doing below actions. But do you think any of these below
> process/task should be turned off to alleviate high PGA related bugs.
>
> I think second last is about the automatic indexing feature.
>
> MMON Actions:-
>
> ***********
>
> Automatic Report Flush
>
> KDILM background EXEcution
>
> Intensive AutoTask Dispatcher
>
> KDILM background CLeaNup
>
> ADR Container Space Management Statistics Flush
>
> Index usage tracking statistics flush
>
> Monitor FRA Space
>
>
> Regards
>
> Pap
>
> On Fri, May 21, 2021 at 12:30 PM Mohamed Houri <mohamed.houri_at_gmail.com>
> wrote:
>
>> 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