ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle PGA 的使用

Oracle PGA 的使用

原创 Linux操作系统 作者:xchui702 时间:2011-07-08 15:03:03 0 删除 编辑

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);

 ADDR  PID  spid  PGA_USED_MEM  PGA_ALLOC_MEM  PGA_FREEABLE_MEM PGA_MAX_MEM
 
 2ADB0C9C 32   2973  1223885  2724725  0  2724725

PGA_ALLOC_MEM=PGA_MAX_MEM > PGA_USED_MEM

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

 ADDR  PID  spid  PGA_USED_MEM  PGA_ALLOC_MEM  PGA_FREEABLE_MEM PGA_MAX_MEM
 
 2ADB0C9C 32   2973  7740285  9540469 131072  9540469

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

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

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

 ADDR  PID  spid  PGA_USED_MEM  PGA_ALLOC_MEM  PGA_FREEABLE_MEM PGA_MAX_MEM
 
 2ADB0C9C 32   2973  1454589  4166517 1048576  9540469

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

 ADDR  PID  spid  PGA_USED_MEM  PGA_ALLOC_MEM  PGA_FREEABLE_MEM PGA_MAX_MEM
 
 2ADB0C9C 32   2973  13962457  15618289 0  15618289

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

 ADDR  PID  spid  PGA_USED_MEM  PGA_ALLOC_MEM  PGA_FREEABLE_MEM PGA_MAX_MEM
 
 2ADB0C9C 32   2973  3071533  4072049 851968  15618289

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

insert into test select * from test;

 ADDR  PID  spid  PGA_USED_MEM  PGA_ALLOC_MEM  PGA_FREEABLE_MEM PGA_MAX_MEM
 
 2ADB0C9C 32   2973  5921269  11870833 917504  24322673

最大分配内存达到了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

SPID Category ALLOCATED Used MAX_ALLOCATED
2973 SQL 1591460 1554680 12337740
2973 PL/SQL 26292 21360 30444
2973 Freeable 1048576 0  
2973 Other 10842905   10905913

那么如何判断当前系统的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 ;

Name Value PCT
workarea executions - optimal 20831 99.85619098
workarea executions - onepass 28 0.134221754
workarea executions - multipass 2 0.009587268

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

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

select * from test order by 1, 2;

 ADDR  PID  spid  PGA_USED_MEM  PGA_ALLOC_MEM  PGA_FREEABLE_MEM PGA_MAX_MEM
 
 2ADB0C9C 32   2973  10147905  19800689 8847360  24322673

发现每个进程最多能分配的内存只有: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 ;

Name Value PCT
workarea executions - optimal 145947 99.9760244413695
workarea executions - onepass 32 0.0219205107479004
workarea executions - multipass 3 0.00205504788261566

此时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

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

下一篇: Oracle 恢复一例
请登录后发表评论 登录
全部评论

注册时间:2011-05-16

  • 博文量
    38
  • 访问量
    118323