MEMORY_MAX_TARGET不能完全限制oracle使用内存的比率

MEMORY_MAX_TARGET不能限制oracle使用内存的比率，因为pga的使用跟程序代码等有关。 如下摘自mos文章
In this Document




Symptoms


Cause


Solution


References
 
Applies to:


Oracle Database - Enterprise Edition - Version 11.1.0.7 to 11.2.0.4 [Release 11.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 24-Dec-2013***
 Symptoms


Parameter MEMORY_MAX_TARGET is the maximum limit for parameter MEMORY_TARGET. By setting MEMORY_MAX_TARGET to a value higher than MEMORY_TARGET, MEMORY_TARGET can be dynamically expanded while instance is up and running.


However, the MEMORY_TARGET/MEMORY_MAX_TARGET values can be exceeded in some cases. Total memory can grow beyond the MEMORY_TARGET/MEMORY_MAX_TARGET values. There are some operations that have to be given memory regardless of the memory limits set by MEMORY_TARGET/MEMORY_MAX_TARGET values (assuming that memory is available at the OS level). An example of such a case is when using PL/SQL memory collections such as PL/SQL tables and varrays. In this case PGA may grow more than calculated PGA_AGGREGATE_TARGET and this leads to an amount of memory allocated for the instance (SGA+PGA) more than values set for MEMORY_TARGET or MEMORY_MAX_TARGET.


 Cause


This is normal behavior. PGA_AGGREGATE_TARGET does not limit the amount of PGA memory usage. It is only a target and is used to dynamically size the process work areas. It does not affect other areas of the PGA that are allowed to grow beyond this limit. There are certain areas of PGA that cannot be controlled by initialization parameters. Such areas include PL/SQL memory collections such as PL/SQL tables and varrays. Depending on the programming code and amount of data being handled these areas can grow very large and can consume large amounts of memory.


This behavior was confirmed in:
Bug 10078425 - PGA OF A SESSION EXCEEDING MEMORY_TARGET/MEMORY_MAX_TARGET -- closed as "Not a bug".


Documentation Bug 10322943 - DOC: MEMORY_TARGET/MEMORY_MAX_TARGET CAN BE EXCEEDED
was filed to add update this is possible.


Total memory usage can grow beyond the value of MEMORY_TARGET. For example,  memory is allocated to PL/SQL tables and varrays regardless of the value of
MEMORY_TARGET, as long as memory is available at the operating system level.




 Solution


In cases where PGA is growing very large because PL/SQL tables or varrays are being used and large amount of records are being handled in memory, expect to use a large amount of process memory. Compensate for this by having enough free physical memory present on the server to handle such application code.


High memory usage can be avoided by limiting the amount of data being handled by PL/SQL


Or


by changing the PL/SQL code approach and using features such as using LIMIT clause with BULK COLLECT.