Re: PGA_AGGREGATE_TARGET, AUTO PGA TARGET and WORKAREA_SIZE_POLICY

/>> As for the untunable memory, the expression refers to the segments over which the administrator has no control, like code, stack and bss./

"Untunable" does not refer to different types of memory image segment such as code, stack, and "bss", because canceling calls or killing processes will not reduce the usage of code/text and shared memory on the system.  Those types of memory are shared amongst processes, so it would be necessary to kill *all* processes using that code/text segment or shared memory segment in order to see memory utilization begin to decrease.

What the PGA workarea policies with the Oracle RDBMS are concerned about is what they are charged with managing:  the amount of heap memory consumed collectively by Oracle processes.

What is "tunable" by the PGA workarea management logic are things like sort area, hash area, etc.

What is "untunable" are data structures in the PGA that cannot be controlled by the RDBMS instance, because (for example) they are controlled by application code, such as PL/SQL collections, SQL statement cursor areas opened and held open by application logic, etc.

What the documentation is saying is that, when PGA_AGGREGATE_LIMIT is exceeded, the RDBMS will first "attack" sessions using lots of "untunable" PGA, because there is nothing else that the Oracle kernel can do to control that.  Tunable memory can be adjusted, but untunable memory can only be killed, and heap memory is always released with the calling operation or process is killed.

Hope this helps...

On 10/9/17 13:06, Mladen Gogala wrote:
>
> Hi Ram,
>
> SYS processes are exempt from almost any limitations. You should
> never, ever log into database as SYS, especially if the database is
> 12c. Previous releases could have not been backed up without logging
> in as SYS, but 12c has a special role called "SYSBACKUP" which allows
> the database to backed up without logging in as SYS. Logging in as SYS
> means that you are doing database maintenance. Period. In such
> context, it doesn't matter how much PGA is burned by the user SYS.
>
> As for the untunable memory, the expression refers to the segments
> over which the administrator has no control, like code, stack and bss.
> Overall memory parameters are controlled by the OS. For Linux, the
> page I usually recommend is the following:
>
> https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Performance_Tuning_Guide/s-memory-tunables.html
>
> PS:
> ----
> Untunable memory is also defined by an undocumented parameter
> _ora_alzheimer_max_size.
>
>
> On 10/09/2017 02:30 PM, Ram Raman wrote:
>> Thanks, I did not realize that SYS' processes are exempted from that
>> limit. So, it is still possible for the usage to exceed PAL, even in
>> 12c.
>>
>> Can someone explain what is "most untunable memory" ?
>>
>>
>> On Fri, Oct 6, 2017 at 5:57 PM, Mladen Gogala
>> <gogala.mladen_at_gmail.com <mailto:gogala.mladen_at_gmail.com>> wrote:
>>
>> Hi Ravi,
>>
>> I know that this is unexpected, but Oracle actually puts the
>> documentation on the Internet for free. The first thing I would
>> try is this:
>>
>> https://docs.oracle.com/database/121/REFRN/GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3.htm#REFRN10328
>> <https://docs.oracle.com/database/121/REFRN/GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3.htm#REFRN10328>
>>
>> This document has a section with the title "What happens when
>> PGA_AGGREGATE_LIMIT is exceeded". I will not ruin you the
>> pleasure of reading the documentation by telling you the ending.
>>
>> Regards
>>
>>
>> On 10/06/2017 06:05 PM, Ravi Teja Bellamkonda wrote:
>>> It would be really helpful if someone can throw some light on
>>> this. I understand that workarea_size_policy parameter set to
>>> auto is related to this. But wanted to confirm that PGA used
>>> actually crosses the PGA_AGGREGATE_LIMIT. Is there any
>>> documentation which states that PGA_AGGREGATE_LIMIT is a soft limit.
>>>
>>> It would be very helpful, if someone can clarify.
>>>
>>> --
>>> Thanks & Regards,
>>> Ravi Teja
>>
>> --
>> Mladen Gogala
>> Oracle DBA
>> Tel:(347) 321-1217 <tel:%28347%29%20321-1217>
>>
>>
>>
>>
>> --
>>
>
> --
> Mladen Gogala
> Oracle DBA
> Tel: (347) 321-1217

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