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

Thank you, but I don't trust DBAs. They spread lies :)

Best Regards,
Amit

On Wed, Mar 31, 2021 at 3:54 PM Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> 1 single process can take up all PGA,it depends what is doing, you might
> hit a bug or memory leak as well. So your dba can be correct
>
> On Wed, Mar 31, 2021, 22:50 Andrew Kerber <andrew.kerber_at_gmail.com> wrote:
>
>> 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