程序全局区PGA(Program Global Area),是服务器进程(Server Process)使用的一块包含数据和控制信息的内存区域,在服务器进程启动或者创建时分配(系统运行时,排序,连接等操作可根据需要进一步分配)。并为Server Process排他访问。共享服务器和专用服务器模式的PGA内容不同。但都包含似有SQL区(存放绑定信息,运行时内存结构等)和session信息等内容。
PGA合计(Aggregated PGA)为所有服务器进程分配的PGA总和。在Oracle 9i以前的版本中PGA由一系列内存区域组成,由参数*_area_size参数控制。
在Oracle 8i中,这些参数主要有:
SQL> show parameter _area_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_size integer 65536
通过手工修改sort_area_size hash_area_size等参数来控制PGA的使用。
在Oracle 9i之前,PGA的计算和控制都是比较复杂的。从Oracle 9i开始,Oracle提供了自动SQL执行内存管理(Automated SQL Execution Memory Management)新特性使得Oracle可以自动调整SQL内存区,而不用关闭数据。这个功能不仅简化了DBA的工作还提高了数据库的性能。
使用自动的PGA管理需要以下几个初始化参数
1. PGA_AGGREGATE_TARGET:此参数用来指定所有session总计可以使用最大PGA大小,该参数可动态修改,取值范围10M-4096G-1
2. WORKAREA_SIZE_POLICY:此参数控制PGA自动管理功能是否启用,有两个取值AUTO,MANUAL。顾名思义AUTO为自动管理,MANUAL为延续以前的手工管理,默认为AUTO。
需要注意的是PGA_AGGREGATE_TARGET在9I中只对专用服务器模式(Dedicated Server)有效;从10G开始对共享服务器模式(Shared Server)同样有效。PGA_AGGREGATE_TARGET参数同事限制全局PGA和私有工作区内存分配:
1. 对于串行操作,单个SQL操作能使用的PGA按照以下原则分配:MIN(5%*PGA_AGGREGATE_TARGET,100MB)
2. 对于并行操作:30%*PGA_AGGREGATE_TARGET/DOP (DOP=Degree Of Parallelism 并行度)
无论是否启用自动PGA调整,Oracle都需要遵循以下原则:UNTUNABLE MEMORY SIZE + TUNABLE MEMORY SIZE <= PGA_AGGREGATE_TARGET 数据库只能控制可调整部分的内存分配,如果可调整的部分过小则Oracle不会强制启用这个等式。另外PGA_AGGREGATE_TARGET参数在CBO优化器模式下,对于SQL的执行计划会产生影响。Oracle在评估计划时会根据PGA_AGGREGATE_TARGET参数评估sort,hash-join,bitmap操作时能够使用的最大或者最小内存,从而选择最优的执行计划。
对于PGA_AGGREGATE_TARGET的设置,Oracle提供以下建议方案:
1. OLTP系统:PGA_AGGREGATE_TARGET < (<Total Physical Memory> * 80%) * 20%
2. DDS系统:PGA_AGGREGATE_TARGET < (<Total Physical Memory> * 80%) * 50%
这只是一个建议设置,具体的设置还是需要根据具体性能指标来调整和优化PGA的使用。伴随着此特性的引入,V$PROCESS视图增加了相应的字段来记录进程的PGA耗用。
SQL在工作区中以3中方式执行:
1. Optimal(优化方式):指所有处理可以在内存中完成。
2. Onepass:大部分操作可以在内存中完成,但是需要使用到磁盘操作。
3. Multipass:大量操作需要产生磁盘交互,性能极差。
通常对于PGA的优化目标,就是使得Optimal的执行尽量高,同时使Multipass操作尽量低。期望目标即为:workarea execution –optimal >= 90%; workarea execution –multipass = 0%。以下为一个生产库中的查询结果
SQL> select name,value,100*(value/decode ((select sum(value) from v$sysstat where name like 'workarea executions%'),0,null,(select sum(value) from v$sysstat where name like 'workarea executions%'))) pct from v$sysstat where name like 'workarea executions%';
NAME VALUE PCT
----------------------------------- ---------- ----------
workarea executions - optimal 464784 99.9851566
workarea executions - onepass 67 .014413159
workarea executions - multipass 2 .000430244
伴随自动PGA调整的引入,Oracle随之引入了一系列视图,V$PGASTAT就是其中之一。可以通过查看global memory bound来查看数据库允许的最高PGA内存使用量,一般来说PGA的global memory bound会一直处于5%* PGA_AGGREGATE_TARGET,直到5%* PGA_AGGREGATE_TARGET超过了50%*_pga_max_size(_pga_max_size为隐含参数),那么PGA的global memory bound将为50%*_pga_max_size。对于PGA的控制还有一系列的隐含参数,可以通过命令查询(输入值为:smm):SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describe FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.inst_id = USERENV ('Instance') AND y.inst_id = USERENV ('Instance') AND x.indx = y.indx AND x.ksppinm LIKE '%&par%';