Thanks. We tried mainly three queries in current database,
The first result below is from v$sesstat showing total grouping of PGA
memory and it says the top two contributors are 'session pga memory max'
and 'session uga memory max'.
The Second query is from the same v$sesstat but ranking the top -5 for each
category 'session pga memory' and 'session uga memory' and its pointing to
all from background session and module 'MMON_SLAVE'. And ACTION columns
were pointing to Automatic Report Flush,KDILM background EXEcution,Intensive
AutoTask ,dispatcher, KDILM background CLeaNup.
Third, we tried to fetch the breakup for one of the top 'MMON' sessions
and most of the memory is occupied by the 'Other' category.
So does this mean that we really need more memory here as we don't see any
application query on top but all background queries/processes only. Or as
because we see the total PGA allocated increasing day by day so it can be
memory leak.
NAME TOTAL
session pga memory 6.21
session pga memory max 12.08
session referenced a buffer on xmem 0
session uga memory 1.16
session uga memory max 5.64
NAME BYTES SIZE UNITS RNK SID SERIAL# WAIT_CLASS EVENT SQL_ID MODULE
session pga memory 576157968 549.5 MB 1 87 61389 Idle class slave wait
MMON_SLAVE
session pga memory 522287376 498.1 MB 2 3088 62371 Idle class slave wait
MMON_SLAVE
session pga memory 508786960 485.2 MB 3 3642 59840 Idle class slave wait
MMON_SLAVE
session pga memory 421641136 402.1 MB 4 581 21065 ON CPU ON CPU XXXXX
XXXX@YYY
session pga memory 414152976 395 MB 5 1048 58619 Idle class slave wait
MMON_SLAVE
session uga memory 407218048 388.4 MB 1 581 21065 ON CPU ON CPU XXXXX
XXXX@YYY
session uga memory 33540576 32 MB 2 87 61389 Idle class slave wait
MMON_SLAVE
session uga memory 33357960 31.8 MB 3 3642 59840 Idle class slave wait
MMON_SLAVE
session uga memory 30655168 29.2 MB 4 1048 58619 Idle class slave wait
MMON_SLAVE
session uga memory 30045808 28.7 MB 5 3088 62371 Idle class slave wait
MMON_SLAVE
SID SPID TOTAL_ALLOCATED INST_ID PID SERIAL# CATEGORY ALLOCATED USED
MAX_ALLOCATED CON_ID
87 26601 569.544 1 146 213 SQL 820048 224816 127728616 0
87 26601 569.544 1 146 213 Other 573559733 573559733 0
87 26601 569.544 1 146 213 Freeable 20774912 0 0
87 26601 569.544 1 146 213 PL/SQL 2055440 0 4742928 0
On Thu, May 20, 2021 at 9:51 PM Lok P <loknath.73@xxxxxxxxx> wrote:
Try these below ones and see where it's currently going?
https://github.com/xtender/xt_scripts/blob/master/pga_top.sql
https://github.com/xtender/xt_scripts/blob/master/pga/pga_usage_by_sid.sql
https://github.com/xtender/xt_scripts/blob/master/pga/details_simple.sql
On Wed, May 19, 2021 at 4:54 PM Pap <oracle.developer35@xxxxxxxxx> wrote:
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
