SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 200M
是一个上限目标,而不是启动数据库时预分配的内存大小。可以把
pga_aggregate_target 设置为一个超大的值(远远大于服务器上实际可用的物理内存量)
串行(非并行查询)会话会使用PGA_AGGREGATE_TARGET 中的很少一部分,大约5%或者更少。
并行查询最多可以使用PGA_AGGREGATE_TARGET 的30%
如果测量会话当前使用的PGA,可以看到下面的结果:
SQL> set linesize 200
SQL> select a.name,
to_char(b.value,'999,999,999') bytes,
to_char(round(b.value / 1024 / 1024, 1), '99,999.9') mbytes
from v$statname a, v$mystat b
where a.statistic# = b.STATISTIC#
and a.name like '%ga memory%'; 2 3 4 5 6
NAME BYTES MBYTES
---------------------------------------------------------------- ------------ ---------
session uga memory 1,302,484 1.2
session uga memory max 1,491,448 1.4
session pga memory 1,933,928 1.8
session pga memory max 2,130,536 2.0
创建package:
SQL> create or replace package demo_pkg
2 as
3 type array is table of char(2000) index by binary_integer;
4 g_data array;
5 end;
6 /
Package created.
SQL> begin
2 for i in 1 .. 200000
3 loop
4 demo_pkg.g_data(i) := 'x';
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select a.name,
to_char(b.value,'999,999,999') bytes,
to_char(round(b.value / 1024 / 1024, 1), '99,999.9') mbytes
from v$statname a, v$mystat b
where a.statistic# = b.STATISTIC#
and a.name like '%ga memory%'; 2 3 4 5 6
NAME BYTES MBYTES
---------------------------------------------------------------- ------------ ---------
session uga memory 469,516,748 447.8
session uga memory max 469,516,748 447.8
session pga memory 470,368,228 448.6
session pga memory max 470,368,228 448.6
现在,数据库本身无法控制PGA中分配的这些内存,已经超过了pga_aggregate_target,
但数据库对此无机可施。