Oracle PGA参数

Oracle PGA参数
PGA: 程序全局区，服务器进程使用的内存区域，包括特定服务器进程的数据和控制信息，例如使用的操作系统资源等。
UGA: 用户全局区，特定Session使用的内存区域，例如Session的SQL工作区、登陆认证信息等。Session的SQL工作区大小对查询性能的影响比较关键，shared server模式时UGA从SGA的large pool（如果有设置）或者shared pool（没有设置large pool）中分配，dedicated server模式时UGA从PGA中分配。
CGA: 调用全局区，存放调用过程中需要的数据，例如parse调用、executive调用、fetch调用等。调用过程中实际需要的数据，例如SQL Area、Sort Area等位于UGA中，CGA存放的只是调用过程中的临时处理数据，例如I/O缓存、临时堆栈空间等。CGA位于PGA中（PGA是集中管理这些资源的地方），在调用开始时创建，调用执行过程中动态分配，调用结束后释放。

WORKAREA_SIZE_POLICY = { AUTO | MANUAL }
{ ALTER SYSTEM | ALTER SESSION } SET WORKAREA_SIZE_POLICY = { AUTO | MANUAL }
设为AUTO时，内存使用比较多的SQL操作，例如sort, group-by, hash-join, bitmap merge和bitmap create等，由Oracle自动分配；设为MANUAL时，这些操作将使用相应的*_AREA_SIZE参数设置的内存值。
*_AREA_SIZE作用于每个Session，分配的过小，很多Session可能没有足够的内存而效率低下，分配过多，大量Session可能浪费掉很多内存空间，9i开始的PGA自动管理功能改善了这个状况。使用PGA_AGGREGATE_TARGET设置整个PGA大小，Oracle将为每个Session按照实际需要为其分配PGA，并尽量维持PGA总量不超过PGA_AGGREGATE_TARGET值。

PGA_AGGREGATE_TARGET = integer [K | M | G]
ALTER SYSTEM
设定总的PGA内存大小。Oracle将尽量保证PGA内存总合不超过这个值，但不是绝对的，为了避免磁盘操作，有时可能会超过这个值。可以设置为总的内存数减去SGA的剩余值。
将该初始化参数设置为大于0的值，Oracle自动将WORKAREA_SIZE_POLICY设为AUTO；将该初始化参数设置为0，Oracle自动将WORKAREA_SIZE_POLICY设为MANUAL。设置了PGA_AGGREGATE_TARGET之后，会忽略所有*_AREA_SIZE设置。

HASH_AREA_SIZE = Integer
ALTER SESSION
单位为字节数。

SORT_AREA_SIZE = Integer
ALTER SESSION, ALTER SYSTEM ... DEFERRED
单位为字节数。如果没有设置SORT_AREA_RETAINED_SIZE，分配的内存在排序结束，开始返回记录之前释放，如果设置了SORT_AREA_RETAINED_SIZE，分配的内存在最后一条记录返回后释放。位图索引上的插入、更新也会用到SORT_AREA_SIZE（因为扫描完索引之后必须对各个位图进行排序，然后再合并成一个位图）。

SORT_AREA_RETAINED_SIZE = Integer
ALTER SESSION, ALTER SYSTEM ... DEFERRED
单位为字节数。指在UGA中保留的排序内存大小，排序操作结束，返回最后一条记录之后释放排序内存，只是释放给UGA，而不是操作系统。

BITMAP_MERGE_AREA_SIZE，CREATE_BITMAP_AREA_SIZE，位图索引创建、更新时使用的内存配置。

_pga_max_size: Maximum size of the PGA memory for one process。ALTER SYSTEM。默认值200M。
查看_pga_max_size值的方法:
select ksppinm "Name", ksppstvl/1024/1024 ||'M' "Value", ksppdesc "Desc"
from x$ksppi x, x$ksppcv y
where x.indx = y.indx and ksppinm ='_pga_max_size';

_smm_max_size: Maximum work area size in auto mode (serial)。{ALTER SESSION | ALTER SYSTEM}。
网上有很多描述_smm_max_size默认值的说法，其实都不对，追究其算法也没多少意义，反正如果你没有手动设置过_smm_max_size的值，一旦设置_pga_max_size或者PGA_AGGREGATE_TARGET，Oracle就会自动计算出_smm_max_size的值，如果手动设置过_smm_max_size则不会变化，Oracle自动计算出的_smm_max_size值，跟PGA_AGGREGATE_TARGET和_pga_max_size都有关系。查看_smm_max_size值的方法如下:
select ksppinm "Name", ksppstvl/1024 ||'M' "Value", ksppdesc "Desc"
from x$ksppi x, x$ksppcv y
where x.indx = y.indx and ksppinm ='_smm_max_size';

使用PGA_AGGREGATE_TARGET让Oracle自动分配PGA时，为了防止单个Session占用过多内存而导致整个服务器性能下降，这两个隐藏参数控制了单个进程的最大内存使用量。
这两个参数为隐藏参数，Oracle官方不进行技术支持，如果系统中存在大量的HASH JOIN、SORT等耗费内存的操作，可以考虑、测试这些参数对系统性能的影响，但注意其副作用。
没有使用并行查询时，单个Session SQL工作区内存使用最大值为min( 5%*PGA_AGGREGATE_TARGET , 50%*_pga_max_size , _smm_max_size)。

设置_pga_max_size: Alter System Set "_pga_max_size"=1024M。
设置_smm_max_size: Alter System Set "_smm_max_size"=307200，单位是K，只能以这种单位进行设置，数字后面不能使用K、M、G这些单位字符了。

监控、改善PGA设置
监控PGA的视图：v$sql_workarea_active、v$sql_workarea、v$sesstat、v$process、v$sysstat、v$sql_workarea_histogram等。
使用下面的视图查看Oracle建议的评估设置:
Select pga_target_for_estimate/1024/1024 ||'M' "Estimate PGA Target"
       ,estd_pga_cache_hit_percentage "Cache Hit(%)"
       ,estd_extra_bytes_rw/1024/1024 ||'M' "Extra Read/Write"
       ,estd_overalloc_count "Over alloc count"
From v$pga_target_advice
选出的4个列中，Over alloc count指示Oracle SQL工作区内存分配的三种情况：optimal完全可以在内存中完成操作；onepass需要进行一次磁盘交换；multipass需要进行多次磁盘交换。第四列的值就是需要进行磁盘交换的数量。
PGA_AGGREGATE_TARGET的值最好选择: Over alloc count为0、Cache Hit(%)尽可能高、Extra Read/Write尽可能低的Estimate PGA Target值。