PGA使用大小与pga_aggregate_target参数的无关性测试

PGA的使用到底受什么限制，是pga_aggregate_target吗？答案是否定的，根据TOM的说法，做了如下测试：

SQL> create or replace package demo_pkg
as
        type array is table of char(2000) index by binary_integer;
        g_data array;
end;
/  2    3    4    5    6 
 
Package created.
 
SQL> 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%';
  2    3    4 
NAME                                                             VALUE
---------------------------------------------------------------- ------------
session uga memory                                                  1,609,968
session uga memory max                                              1,609,968
session pga memory                                                  2,485,844
session pga memory max                                             20,246,100
  
SQL> begin
        for i in 1 .. 100000
        loop
                demo_pkg.g_data(i) := 'x';
        end loop;
end;
/  2    3    4    5    6    7 
 
PL/SQL procedure successfully completed.
 
SQL> 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%';  2    3    4 
 
NAME                                                             VALUE
---------------------------------------------------------------- ------------
session uga memory                                                313,349,536
session uga memory max                                            313,349,536
session pga memory                                                314,437,204
session pga memory max                                            314,437,204
 
SQL> show parameter pga_agg
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 100M

显然，PGA的使用值已经远远超过了pga_aggregate_target参数设定的值，pga_aggregate_target所设置的大小，只是一个意向值而已。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-524636/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-524636/