当前位置:首页 > 服务端 > Know more about PGA_AGGREGATE_LIMIT 12c 19c

Know more about PGA_AGGREGATE_LIMIT 12c 19c

Prior to 12c,PGA_AGGREGATE_TARGET was the Parameter used to control amount of memory allocated to User Processes(mainly work areas). However PGA_AGGREGATE_TARGET is a soft target and not a hard limit. The actual PGA usage can be as high as three times of the value of PGA_AGGREGATE_TARGET.This can lead to memory swapping & performance degradation.

In automatic PGA memory management mode, Oracle Database attempts to adhere to the PGA_AGGREGATE_TARGET value by dynamically controlling the amount of PGA memory allotted to work areas. However, PGA memory usage may exceed the PGA_AGGREGATE_TARGET setting at times due to the following reasons:

1.  The PGA_AGGREGATE_TARGET setting acts as a target, and not a limit. 
2. PGA_AGGREGATE_TARGET only controls allocations of tunable memory.

12c introduced new parameter PGA_AGGREGATE_LIMIT to allow database administrators to limit the aggregate pga memory usage.

The PGA_AGGREGATE_LIMIT initialization parameter enables you to specify a hard limit on PGA memory usage.  The background process CKPT checks every three seconds to see whether the amount of memory exceeds the value of the PGA_AGGREGATE_LIMIT initialization parameter.  If the PGA_AGGREGATE_LIMIT value is exceeded, Oracle Database aborts or terminates the sessions or processes that are consuming the most untunable PGA memory in the following order:

1.  Calls for sessions that are consuming the most untunable PGA memory are aborted. 
2.  If PGA memory usage is still over the PGA_AGGREGATE_LIMIT, then the sessions and processes that are consuming the most untunable PGA memory are terminated.

The sessions using the most untunable memory will have their calls aborted. Parallel queries will be treated as a unit. If the total PGA memory usage is still over the limit, then sessions using the most untunable memory will be terminated.

SYS processes and background processes other than job queue processes will not be subjected to any of the actions described in this section. Instead, if they are using the most untunable memory, they will periodically write a brief summary of their PGA usage to a trace file.

Untunable PGA memory is like context information for each session, for each open/active cursor, PL/SQL or Java memory.
Tunable PGA memory is memory allocated for intensive memory SQL operators like sorts, hash-join, group-by, bitmap merge and bitmap index create.

The PGA_AGGREGATE_LIMIT initialization parameter dynamically sets an instance-wide hard limit for PGA memory. a database restart is not necessary. Because the parameter responds to changing memory conditions, you do not need to set the parameter value explicitly. By default, PGA_AGGREGATE_LIMIT is set to:

In Oracle release 12.1:  the greater of the following:

1. 2 GB 
2. 200% of PGA_AGGREGATE_TARGET 
3. (Value of PROCESSES initialization parameter setting) * 3 MB

It will not exceed 120% of the physical memory size minus the total SGA size.

In Oracle release 12.2:

* If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.
* If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.
* If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGATE_LIMIT is set to 90% of the physical memory size minus the total SGA size.

In all cases, the default PGA_AGGREGATE_LIMIT is at least 2GB and at least 3MB times the PROCESSES parameter.

In Oracle release 18c and above, it should be at least 5MB times the PROCESSES parameter for an Oracle RAC instance.

The MGA (Managed Global Area) is accounted for out of the PGA.
What this means in practical terms is that the PGA size must be increased in 18c and above versions to accommodate the MGA.

A rule of thumb is:

PGA_AGGREGATE_LIMIT = (original PGA_AGGREGATE_LIMIT value) + ((maximum number of connected processes) * 4M)

Another effect of this issue is that customers can report increased PGA usage after upgrading to 18c and above.
This is expected as the MGA is now accounted for out of the PGA.

NOTE: If a value of 0 is specified for PGA_AGGREGATE_LIMIT, it means there is no limit to the aggregate PGA memory consumed by the instance.

 

On 19c(19.7+), The value of PGA_AGGREGATE_LIMIT has some internal restrictions. For example, its upper limit is calculated from physical memory and the SGA usage. from db alert log will show that.

Also, its lower limit requires to be at least 3MB * processes parameter (or 5MB * processes if RAC).

If error during  upgrade using DBUA cause by PGA_AGGREGATE_LIMIT,like this:

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

The solution is to look at increasing the PGA_AGGREGATE_TARGET to a higher value & retry the Upgrade.

the wait event “Acknowledge Over PGA Limit” is a new wait event that was introduced with PGA_AGGREGATE_LIMIT in 12.1,  and it will force a process that wants more PGA to wait a bit if the instance is getting close to hitting the limit.

The solution you can set PGA_AGGREGATE_LIMIT = 0 for unlimited.

References Doc ID 1520324.1

作者:雪竹子
来源链接:https://www.cnblogs.com/lkj371/p/15073666.html

版权声明:
1、Java侠(https://www.javaxia.com)以学习交流为目的,由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容,包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网,本站不享有版权,版权归原作者所有。

2、本站提供的内容仅用于个人学习、研究或欣赏,以及其他非商业性或非盈利性用途,但同时应遵守著作权法及其他相关法律的规定,不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容,请及时通知本站(javaclubcn@163.com),我们将第一时间核实后及时予以删除。





本文链接:https://www.javaxia.com/server/125135.html

标签: out of memory
分享给朋友:

“Know more about PGA_AGGREGATE_LIMIT 12c 19c” 的相关文章