Oracle  10G 中PGA 的使用有什么限制？ 最大可以使用多少PGA?  如果监控PGA的使用量了？

先建立会话，不执行任何语句：

SQL> select * from dual;

DUMMY

-----

X

此时PGA 的分配情况是：通过以下查询获得

select a.ADDR,a.PID,a.spid,a.PGA_USED_MEM,a.PGA_ALLOC_MEM,a.PGA_FREEABLE_MEM,a.PGA_MAX_MEM
  from v$process a where a.SPID in (2973);

PGA_ALLOC_MEM=PGA_MAX_MEM > PGA_USED_MEM

当执行 select * from test order by 1, 2; 后，执行以上查询返回结果如下：

此时结果还在输出，但是以上关于PGA分配的数据不再改变，也说明了SQL的执行过程，Fetch 之前所有结果都排序好了。所以当排序结果集很大，而PGA和temproary 空间

不够大时，会报空间不足的错误。

此语句结束后，PGA的分配情况如下：此时该进程使用的PGA已经减少到1454589, 当前分配的内存变为了4166517, 在所分配的内存中，可以释放1048576.历史最大分配还是9540469.

创建一个更大的表：insert into test select * from dba_tables; 重新执行排序操作：执行过程中PGA使用如下

试验另外一个查询：select * from test order by 1, 2,3,4; 排序的字段增加两个是否会增加PGA的使用了？ 多个字段的排序所使用的内存更小。

再次加大数据量，寻找每个进程最大能使用的PGA 量：select * from test order by 1, 2;

insert into test select * from test;

最大分配内存达到了24322673, 超过了 隐藏参数 _smm_max_size = 11878 K的限制。

具体的PGA的分配情况：

select b.PROGRAM,b.SPID,a.CATEGORY,a.ALLOCATED,a.USED,a.MAX_ALLOCATED
from v$process_memory a,v$process b
where a.PID=b.pid and b.SPID=2973

那么如何判断当前系统的PGA设置是否合理了？ 通常我们应该避免使用临时表空间来代替PGA做处理，比如排序。

select t.name,t.value,100*t.value/decode(q.sv,0,null,q.sv)
from
(select * from v$sysstat a where a.NAME like '%workarea execution%' ) t,
(select sum(value) sv from v$sysstat  where NAME like '%workarea execution%'  )q ;

调整的目标是：workarea executions - optimal >90%, 而 workarea executions - multipass =0%，如达不到，需要修改程序或提高PGA.

进一步加大数据量, 做测试：最后一次行数达到：1612928*2 行。

select * from test order by 1, 2;

发现每个进程最多能分配的内存只有：24322673.

select x.ksppinm name, y.ksppstvl value, x.ksppdesc pdesc
  from sys.x$ksppi x, sys.x$ksppcv y
 where x.indx=y.indx
   and x.ksppinm like '%&par%';  -- 获得 参数 _smm_max_size = 11878 K

24322673 约等于11878*1024*2 , 也就是最大可以分配的内存是_smm_max_size的两倍。

select t.name,t.value,100*t.value/decode(q.sv,0,null,q.sv)
from
(select * from v$sysstat a where a.NAME like '%workarea execution%' ) t,
(select sum(value) sv from v$sysstat  where NAME like '%workarea execution%'  )q ;

此时multipass 增加了，是因为内存不足用于排序。

从explanation plan 中查看 sort operation 的 bytes 为37577952 ,大于24322673, 必然会引起磁盘排序。

SELECT STATEMENT, GOAL = ALL_ROWS   Cost=11137 Cardinality=404064 Bytes=37577952 Operation=SELECT STATEMENT
 SORT ORDER BY   Cost=11137 Cardinality=404064 Bytes=37577952 Operation=SORT
  TABLE ACCESS FULL Object wner=SYS Object name=TEST Cost=2448 Cardinality=404064 Bytes=37577952 Operation=TABLE ACCESS