/>> 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@xxxxxxxxx <mailto:gogala.mladen@xxxxxxxxx>> 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
