Re: Does Oracle PGA take out memory out of SGA?

How many processes? how many sessions? Each user session will take up a small amount of system memory, so if you have a lot of sessions they might be eating up memory.

On Wed, Mar 31, 2021 at 2:47 PM Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> Hi All,
>
> Below are the settings in my database -
>
> NAME TYPE VALUE
> -------------------- ----------- -----
> pga_aggregate_target big integer 12G
> sga_max_size big integer 130G
> sga_target big integer 0
> memory_target big integer 0
> pga_aggregate_limit big integer 0
>
> Also, I don't know about MTS, if you can tell me how to check then I can
> check and share details here.
>
>
> Best Regards,
> Amit
>
>
> On Wed, Mar 31, 2021 at 3:32 PM Luis Claudio Dias dos Santos <
> lsantos_at_pobox.com> wrote:
>
>> In a nutshell: No.
>>
>> PGA is a different pool from SGA. And, more important, SGA has a static
>> preallocated size. While PGA "size" is always a limit, and is allocated on
>> demand.
>>
>> Parameter pga_aggregate_target defines a somewhat *soft* limit for PGA.
>> From 12c upwards the parameter pga_aggregate_limit (defaults
>> to pga_aggregate_target*2) defines the hard limit.
>>
>> In some setups using MTS some memory structures normally allocated from
>> PGA are allocated from SGA. This is comprehensible as, in MTS, there isn't
>> the 1:1 relation between session and process.
>>
>> *--*
>> *Att*
>>
>>
>> *Luis Santos*
>>
>>
>>
>> Em qua., 31 de mar. de 2021 às 16:04, Amit Saroha <eramitsaroha_at_gmail.com>
>> escreveu:
>>
>>> Dear All,
>>>
>>> Please help me understanding PGA and SGA.
>>>
>>> I was under the impression that PGA and Memory and SGA Memory are
>>> differently allocated. But, in a recent situation, we observed *ORA-7445
>>> [kghalf] *and my DBA is saying - out of a total of 256 GB system memory
>>> one session's PGA consumed everything and only 8 MB was left for other
>>> sessions. But, apart from one process, no other processes were impacted so
>>> I have doubt that - Oracle consumes SGA memory and Allocates it to PGA of a
>>> single process.
>>>
>>> Please help me understand.
>>>
>>>
>>> Best Regards,
>>> Amit
>>>
>>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

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