Re: PGA on higher version

Hello Pap,

One way to check that ADDM is disabled or not is to check its recommendations presence or absence in an AWR report respectively.

I see no risk to disable ADDM since I can manage easily to find all the tuning recommendations this advisor can point out

Yes, I have disabled the tuning and space advisor in almost all my 19c databases.

Yes, I keep the automatic stats collection job so that table/index/column with stale statistics are gathered during the windows maintenance.

You should only check the value of the preference AUTOSTATS_TARGET. Because you might see that the automatic stats collection job is, indeed, enabled, but it concerns only the Oracle dictionary tables if that preference is set to ORACLE instead of AUTO

SQL> _at_getPrefs
Enter value for preference: AUTOSTATS_TARGET Enter value for tablename:

PREFS

• Name : get_prefs
• Date : October 2018
• Author : Mohamed Houri
• Purpose: gets the value of dbms_stats preference at global or table level
  --
  --
  
• Input arguments:
• pname - preference name
• The default value for the following preferences can be retrieved.
• CASCADE
• DEGREE
• ESTIMATE_PERCENT
• METHOD_OPT
• NO_INVALIDATE
• GRANULARITY
• PUBLISH
• INCREMENTAL
• INCREMENTAL_LEVEL
• INCREMENTAL_STALENESS
• GLOBAL_TEMP_TABLE_STATS
• STALE_PERCENT
• AUTOSTATS_TARGET
• CONCURRENT
• TABLE_CACHED_BLOCKS
• OPTIONS
• STAT_CATEGORY
• PREFERENCE_OVERRIDES_PARAMETER
• APPROXIMATE_NDV_ALGORITHM
• AUTO_STAT_EXTENSIONS
• WAIT_TIME_TO_UPDATE_STATS
  --
  --
  
• ownname - owner name
• tabname - table name
  --
  --
  
• Exceptions:
• ORA-20001: Invalid input values
  --
  --**************************************************************************
  

select dbms_stats.get_prefs('&preference', tabname => '&tablename') prefs from dual;

For the other 19c job which you should check their status, I would highly recommend reading the following blog post by Mike Dietrich :

https://mikedietrichde.com/2020/05/28/do-you-love-unexpected-surprises-sys_auto_sts-in-oracle-19-7-0/

Best regards
Mohamed Houri

Le sam. 22 mai 2021 à 13:29, Pap <oracle.developer35_at_gmail.com> a écrit :

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

--

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 Received on Sat May 22 2021 - 17:17:55 CEST