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值。