Oracle資料庫work area size & pga_aggregate_target引數

單個session的PGA記憶體使用量限制有比較多的概念，現在統一梳理一下：

 
1、關於PGA記憶體是自動管理還是手工管理，以及*_area_size（例如 sort_area_size）引數是否生效
workarea_size_policy:該引數定義資料庫PGA記憶體是否自動管理，同時涉及到SQL執行中各種記憶體引數設定是否生效；當PGA記憶體為自動記憶體管理（workarea_size_policy =auto），有關SQL執行的各種記憶體引數設定（例如sort_area_size 排序記憶體大小）均不生效，但至於SQL執行的各種記憶體引數設定生否真的生效，還與pga_aggregate_target值有關，具體如下：
（1）workarea _size_policy設定為auto，pga_aggregate_target設定為0 ，不會使用自動記憶體管理，因為當pga_aggregate_target設定為0時，workarea _size_policy自動設定為manual。
（2）workarea _size_policy設定為manual，pga_aggregate_target設定為非0，會使用自動記憶體管理。因為，如果設定pga_aggregate_targe為非0，workarea _size_policy自動設定為auto，因此，使用自動管理。
（3）如果不設定pga_aggregate_targe，那麼系統預設也是使用自動管理，並且設定該引數的值為SGA的20%和10M中較大的值。
 
總之：當記憶體為自動管理時，sort_area_size不生效；當記憶體為手動管理時，sort_area_size生效。
 
select name, value, isdefault, description
  from v$parameter
 where name in ('workarea_size_policy', 'pga_aggregate_target')
 or  name like 'sort%';
 
2、當PGA記憶體為手動管理時，*_area_size引數設定是有效的，sort_area_size引數詳解：
 
sort_area_size：該引數的配置值為每個session的最大排序空間(單位：byte）。同時與該引數有關的另外一個引數是sort_area_retained_size。
 
select * from v$parameter where name like '%area_size%';
 
sort_area_retained_size：該引數是控制一個session在排序結束後，該session用的記憶體排序區（最大為sort_area_size）降低到的空間值。例如：sort_area_size=100k, sort_area_retained_size=20k,當排序已經完成後，在記錄被取出返回的過程中，oracle將逐步釋放排序中使用的記憶體空間（例如使用了最大100k），直至釋放到剩餘20k即sort_area_retained_size空間大小時，該session的排序記憶體空間將不再繼續釋放，sort_area_retained_size的記憶體空間將在排序記錄的最後一條記錄取走返回後再全部釋放。
 
sort_area_retained_size通常設定的值與sort_area_size同樣大，預設設定sort_area_retained_size=0，即與sort_area_size同樣大。
10G中文件解釋:SORT_AREA_SIZE specifies (in bytes) the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory.
Each sort in a query can consume memory up to the amount specified by SORT_AREA_SIZE, and there can be multiple sorts in a query. Also, if a query is executed in parallel, each PQ slave can consume memory up to the amount specified by SORT_AREA_SIZE for each sort it does.
 
3、當PGA記憶體為自動管理時，允許的最大排序記憶體空間是多少？
 
1）PGA_AGGREGATE_TARGET：該引數引數同時限制全域性PGA分配和私有工作區記憶體分配，對於單個session可調整的記憶體部分（即work areas）的限制，具體如下：
a)對於序列操作，單個SQL操作能夠使用的PGA記憶體work areas按照以下原則分配：
MIN（5% * PGA_AGGREGATE_TARGET，100MB）
b)對於並行操作，單個SQL操作能夠使用的PGA記憶體work areas按照以下原則分配：
30%* PGA_AGGREGATE_TARGET /DOP （DOP=Degree Of Parallelism 並行度）
 
select * from v$parameter where name like '%pga_aggregate_target%';
 
但我們透過v$sesstat或者v$process，可以看到有的session或者程式使用的PGA記憶體量大於100M，這是為什麼呢？是否是上面的說明準確呢。
 
select a.sid, round(a.value/1024/1024,2)||'M' pga_area
  from v$sesstat a, v$statname b
 where a.statistic# = b.statistic#
   and b.name = 'session pga memory';
 
select pid,
       pga_used_mem / 1024 / 1024,
       pga_alloc_mem / 1024 / 1024,
       pga_freeable_mem / 1024 / 1024,
       pga_max_mem / 1024 / 1024
  from v$process;
 
2）檢視v$pgastat展示了資料庫當前PGA總體的使用狀況
 
select * from v$pgastat；
total PGA allocated：該引數顯示的值是目前資料庫正在使用中的PGA記憶體量，這個值是有可能大於PGA_AGGREGATE_TARGET配置值的。
over allocation count：該引數記錄的是從資料庫啟動以來，PGA使用總量大於PGA_AGGREGATE_TARGET配置值的累計次數。
 
從上面的兩個引數來看，在全域性的範圍內，PGA分配給各個session的總PGA使用量的彙總在某些情況下，是允許超過PGA_AGGREGATE_TARGET配置值的。
 
total PGA allocated - Current amount of PGA memory allocated by the instance. Oracle attempts to keep this number below the value of the PGA_AGGREGATE_TARGET initialization parameter. However, it is possible for the PGA allocated to exceed that value by a small percentage and for a short period of time when the work area workload is increasing very rapidly or when PGA_AGGREGATE_TARGET is set to a small value. 


over allocation count - This statistic is cumulative since instance startup. Over allocating PGA memory can happen if the value of PGA_AGGREGATE_TARGET is too small. When this happens, Oracle cannot honor the value of PGA_AGGREGATE_TARGET and extra PGA memory needs to be allocated.
 
3）詳細分析PGA的組成
首先需要說明，PGA分成兩部分：
  Untunable Memory Size + Tunable Memory Size <= PGA_AGGREGATE_TARGET


這兩部分各自是些什麼成份﹕
a)不可調整部分(Untunable Memory Size)﹕PL/SQL arrays, stack space, session variables等等。如果設定的PGA（也就是PGA_AGGREGATE_TARGET的設定)小於該部分﹐那一定會超過PGA_AGGREGATE_TARGET的值﹐並且該部分是不可以shrink的，即data areas。
 
b)可調整部分(Tunable Memory Size)﹕sort area,hash area,bitmap create area, bitmap merge area 這部分也就oracle concept內所說的work areas。
 
單個session在排序或者hash 時，最大允許的work areaa 是隱含引數_pga_max_size配置值的一半（隱含引數_pga_max_size的預設值為200M） 和pga_aggregate_target引數配置值的5% 中的最小值。
 
對於並行操作，單個session操作能夠使用的PGA記憶體work areas是pga_aggregate_target引數配置值的30%除以併發度。例如：如果SQL的hints為 PARALLEL 4，pga_aggregate_target為200M，那麼單個session的最大PGA分配空間為15M （200M*30% / 4 = 15M ）。
 
The limits of sorting and hashing：
There are important limitations of pga_aggregate_target:   The total work area cannot exceed 200 megabytes of RAM because of the default setting for _pga_max_size.  No RAM sort may use more than 5% of pga_aggegate_target or _pga_max_size, whichever is smaller. This means that no task may use more than 200 megabytes for sorting or hash joins. The algorithm further reduces this to (200/2) for sorts so the actual limit for pure sorts will be 100 megabytes.
 
pga_aggegate_target *5%=_smm_max_size
pga_aggegate_target *30%=_smm_px_max_size


_smm_px_max_size – This parameter is used for Oracle parallel query, and defaults to 30% of the pga_aggregate_target setting, divided by degree of parallelism (as set by a PARALLEL hint, "alter table xxx parallel" command, or the parallel_automatic_tuning initialization parameter). For example, by default a DEGREE=4 parallel query would have a maximum sort area value of 15 megabytes per session with a 200 megabyte pga_aggregate_target setting. Remember, parallel full-table scans bypass the data buffers and store the incoming data rows in the PGA region and not inside the data buffers (as defined by the db_cache_size parameter).
 
綜上所述：PGA記憶體為自動管理時，pga_aggregate_target引數配置值為整個資料庫的PGA記憶體量，單個session分配的PGA容量是有限制的，包含兩部分data areas和work areas，具體為：序列操作，單個session操作能夠使用的PGA記憶體work areas為 MIN（5% * PGA_AGGREGATE_TARGET，100MB）；並行操作為30%*PGA_AGGREGATE_TARGET /DOP （DOP=Degree Of Parallelism 並行度）；如果單個session在PGA中data areas載入了大量的業務資料（全表掃描，併發查詢），即data areas可以超過PGA容量限制。