Re: PGA on higher version

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_at_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_at_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_at_gmail.com> 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_at_gmail.com> 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_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
>>>>
>>>

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