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&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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 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@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
--
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>
