pga_aggregate_target和_pga_max_size都不能绝对限制实际PGA的使用

和sga_target不同，pga_aggregate_target并不能限制PGA的大小。另一个差别是，SGA是数据库启动时按照sga_max_size预先分配的，而PGA则是“按需分配”的。

以下详细解释下，为什么pga_aggregate_target并不能限制PGA的大小。一般的文档会把PGA分为stack space和UGA两个部分，但这种提法太笼统，不利于我们理解这个问题。我们把它分为work area和work area之外的部分。

所谓work area，就是session要执行SQL，需要在数据库内部分配的，为了存放中间结果的内存。比如sort area，为了排序用的内存，比如hash area，为了hash join用的内存，这一部分直接和SQL执行相关，影响SQL执行的效率，比如更大hash area会让hash join更快。pga_aggregate_target实际上只限制work area的大小。当work area达到pga_aggregate_target的限制，则会产生4030错误。隐含参数_pga_max_size只是限制单个进程使用的work area大小，也是在pga_aggregate_target的限制之内的更小限制。而work area之外的内存，不被pga_aggregate_target和_pga_max_size所限制。所以你经常会看到PGA的大小超过了pga_aggregate_target。这通常是因为PL/SQL中的变量和数组中装入了巨大的数据造成的。通常的例子是bulk collect，imp/exp，sql loader等工具也会产生类似的问题。

我下面给出一个具体的示例：
alter system set "_pga_max_size"=10m scope=both;
alter system set pga_aggregate_target=100m scope=both;
SQL> shutdown immediate
startup
SQL> select ksppinm as "hidden parameter", ksppstvl as "value" from x$ksppi join x$ksppcv using （indx） where ksppinm like "%_pga_max_size%" order by ksppinm;
hidden parameter
--------------------------------------------------------------------------------
value
--------------------------------------------------------------------------------
_pga_max_size
10485760
<=========限制单个进程使用PGA 100M（其实是work area）

执行一个PL/SQL，使用bulk collect使其内存使用大大超过pga_aggregate_target和_pga_max_size：
DECLARE
CURSOR c1 IS
SELECT *
FROM Oracle.employees;
TYPE emp_row IS TABLE OF c1%ROWTYPE;
table_set emp_row;
BEGIN
-- Assign values to nested table of records:
SELECT *
BULK COLLECT INTO table_set
FROM oracle.employees;
-- Print nested table of records:

FOR i IN table_set.FIRST .. table_set.LAST LOOP
DBMS_OUTPUT.PUT_LINE （
table_set（i）.email || " " ||
table_set（i）.last_name || ", " ||
table_set（i）.first_name
）;
END LOOP;END;
/

select pid,spid,program,pga_used_mem/1024/1024,pga_alloc_mem/1024/1024 from v$process
where spid=3735758

PID SPID PROGRAM PGA_USED_MEM/1024/1024 PGA_ALLOC_MEM/1024/1024
--- ------------ ------------------------------------------------ ---------------------- -----------------------
19 3735758 oracle@nascds5 （TNS V1-V3） 616.22734165191650390625 616.63962650299072265625
《=========PGA使用了600M

select a.name,to_char（b.value,"999,999,999"） value
from v$statname a,v$mystat b
where a.statistic# = b.statistic#
and a.name like "%ga memory%";

NAME VALUE
---------------------------------------------------------------- ------------
session uga memory 1,896,024
session uga memory max 1,896,024
session pga memory 2,330,120
session pga memory max 647,400,968 <==========Peak PGA size for the session. 647,400,968

12c新追加了一个参数PGA_AGGREGATE_LIMIT来限制PGA的大小：
Limiting process size with database parameter PGA_AGGREGATE_LIMIT （Doc ID 1520324.1）更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址