Oracle9i中的PGA

1.1 PGA (Program Global Area)

程式全域性區(PGA)即可以理解為Process Global Area,也可以理解為Program Global Area.它的記憶體段是在程式私有區(Process Private Memory)而不是在共享區(Shared Memory).它是個全域性區意味著它包含了所有程式碼有可能進入的全域性變數和資料結構,但是它是不被所有程式共享的.每個Oracle的伺服器程式都包含有屬於自己的PGA,它只包含了本程式的相關特定資訊.PGA中的結構不需要由latches來保護,因為其它的程式是不能進入到這裡面來訪問的.

PGA包含的是有關程式正在使用的作業系統資源資訊以及程式的狀態資訊,而其它的程式所使用的Oracle的共享資源是在SGA中.PGA是私有的而不是共享的,這個機制是有必要的,因為當程式死掉後可以把這些資源清除和釋放掉.

PGA包含兩個主要區域:Fixed PGA和Variable PGA或稱為PGA Heap. Fixed PGA的作用跟Fixed SGA是類似的,都包含原子變數(不可分的),小的資料結構和指向Variable PGA的指標.

Variable PGA是一個堆.它的Chunks可以從Fixed Table X$KSMPP檢視得到,這個表的結構跟前面有提到的X$KSMSP是相同的.PGA HEAP包含了一些有關Fixed Table的永久性記憶體,它跟某些引數的設定有依賴關係.這些引數包含DB_FILES,LOG_FILES,CONTROL_FILES.

1.2 UGA (User Global Area)

UGA(User Global Area)包含的是特定會話的資訊,有如下一些:

所開啟遊標的持續和執行時間內的區域

包的狀態資訊,特定的變數

Java會話狀態

可以用的ROLES

被ENABLE的跟蹤事件

起作用的NLS引數設定

開啟的DBLINK

會話的入口控制

跟PGA一樣,UGA也由兩區組成:Fixed UGA和Variable UGA,也稱為UGA HEAP. Fixed UGA包含了大約70個原子變數,小的資料結構和指向Variable UGA的指標.

UGA HEAP中的Chunks可以從它們自己的會話中透過檢視錶X$KSMUP獲得相關資訊,這個表的結構跟X$KSMSP是一樣的.UGA HEAP包含了一些有關fixed tables的永久性記憶體段,跟一些引數的設定有依賴關係.這些引數有OPEN_CURSORS,OPEN_LINKS,和MAX_ENABLE_ROLES.

UGA在記憶體中的位置依賴於會話的配置方式.如果會話連線的配置方式是專用伺服器模式(DDS)即是一個會話對應一個程式,則UGA是放在PGA中的.在PGA中,Fixed UGA是其中的一個Chunk,而UGA HEAP是PGA的一個子堆(Subheap).如果會話連線是配置為共享伺服器模式(MTS), Fixed UGA是SHARED POOL中的一個Chunk,而UGA HEAP則是SHARED POOL中的子堆(Subheap)

1.3 CGA (Call Global Area)

跟其它的全域性區不同,Call Global Area是短暫性存在的.它只有在呼叫資料期間存在,一般是在對例項的最低階別的呼叫時才需要CGA,如下:

分析一個SQL語句

執行一個SQL語句

取出一個SELECT語句的輸出

一個單獨的CGA在遞迴呼叫時是需要的.在SQL語句的分析過程中,對資料字典資訊的遞迴呼叫是需要的,因為要對SQL語句進行語法分析,還有在語句的最佳化期間要計算執行計劃.執行PL/SQL塊時在處理SQL語句的執行時也是需要遞迴呼叫的,在DML語句的執行時要處理觸發器執行也是需要遞迴呼叫的.

不管UGA是放在PGA中還是在SGA中,CGA都是PGA的一個子堆(Subheap).這個事實的一個重要推論是在一個呼叫的期間會話必須是一個程式.對於在一個MTS的Oracle資料庫程式應用開發時關於這一點的理解是很重要的.如果相應的呼叫較多,就得增加processes的數量以適應呼叫的增加.

沒有CGA中的資料結構,CALLS是沒法工作的.而實際上跟一次CALL相關的資料結構一般都是放在UGA中,如SQL AREA,PL/SQL AREA和SORT AREA它們都必須在UGA中,因為它們要在各CALLS之間要一直存在並且可用.而CGA中所包含的資料結構是要在一次CALL結束後能夠釋放的.例如CGA包含了關於遞迴呼叫的資訊,直接I/O BUFFER等還有其它的一些臨時性的資料結構.

Java Call Memory也是在CGA中.這一段記憶體比Oracle的其它記憶體段管理得更密集.它分成三個Space: Stack Space, New Space, Old Space.在New Space和Old Space中不再被參考使用的Chunks,根據它們在使用期間的長度及SIZE的不同,在呼叫的執行過程中將被當成不用的Chunks收集起來.New Space Chunks很多次的不用的Chunks的反覆收集過程中沒有被收集的Chunks將會被放到Old Space Chunks中.這是在Oracle記憶體管理中唯一的一個廢物收集(garbage collection),其它的Oracle記憶體段都是釋放Dead Chunks.

下面這幅圖表現了共享伺服器模式和專用伺服器模式下,PGA、UGA的不同情況：

2、PGA記憶體的分配與回收

2.1 Process Memory Allocation

跟SGA不一樣的是,SGA在例項啟動之後SIZE就已經是定下來的,而PGA的SIZE是會增長的.透過使用malloc()或者sbrk()系統呼叫來為程式增加堆資料段大小而使得PGA的SIZE的增長.OS的新虛擬記憶體會被做為PGA HEAP中的一個新的區被加到PGA中來.這些區一般只幾KB大,如果有需要,Oracle將會給分配上千個區.

作業系統對每個程式的堆資料段的增長是有限制的.大部分的情況是作業系統的記憶體引數進行限制(kernel parameter: MAXDSIZ),有一些情況它的預設值是可以以每個程式為基準進行修改的.對於所有的程式,作業系統對整個虛擬記憶體也有一個系統全域性性的限制,這個限制跟系統的SWAP SPACE相關.一旦超過了這兩個限制,Oracle的程式在執行中會遇到ORA-4030錯誤.

ORA-4030這個錯誤的產生一般不是因為每個程式的資源限制而是因為SWAP SPACE空間不足造成.為了診斷這個問題可以使用作業系統的一些選項來檢視SWAP SPACE的使用情況.另外,在一些作業系統中,Oracle包含了一個工具叫maxmem,它可以用來檢視每個程式可以被分配的堆資料段的最大SIZE以及哪一個限制是第一次超過的.

如果這個問題的出現是因為SWAP SPACE空間不足,而且換頁的動作非常頻繁而且較多,則需要減少系統一級的虛擬記憶體的使用,這個可以透過減少程式數也可以透過減少每個程式的記憶體限制.如果換頁動作不頻繁而且比較少,則需要調大SWAP SPACE SIZE.

2.2 Process Memory Deallocation

Oracle堆的增長比它們的收縮要來得容易,當然它們的SIZE也是可以收縮的.在V$MYSTAT和V$SESSTAT檢視中,session的統計資訊session uga memory和session pga memory分別顯示了當前session的UGA和PGA的記憶體大小,包含內部的空閒空間.相應的統計資訊session uga memory max 和 session pga memory max分別顯示了在session的生存期間所使用過得最大的UGA和最大的PGA.

UGA和PGA只有在特定的操作後才會收縮,這些操作如一次磁碟排序的合併操作,或者用程式DBMS_SESSION.FREE_UNUSED_USER_MEMORY顯示釋放記憶體.只有整個free heap extent會被釋放給父堆或者是程式堆資料段,所以有一部分的內部free space在記憶體釋放後仍然存在於subheap中.

在大多的作業系統環境下,Oracle是不會減少程式堆資料段也不會釋放虛擬記憶體並將其返還給作業系統的.所以從一個作業系統的檢視中,一個Oracle的程式將會把虛擬記憶體SIZE作為HWM而保留著.如果有必要時,Oracle是會將一些沒用的虛擬記憶體頁換頁出去的.因為這個原因,有關Oracle程式的虛擬記憶體頁的作業系統統計資訊都是很難理解的.所以一般用的是Oracle內部統計資訊來代替使用作業系統的統計資訊.

程式DBMS_SESSION.FREE_UNUSED_USER_MEMORY只能在連線是配置為MTS模式的應用才能使用.這個最好是少點使用,因為它只釋放大的包的array變數所佔用的記憶體返還給Large Pool或者是Shared Pool.一般地,UGA HEAP的記憶體應該首先被釋放,可以透過指派新的空array給array變數使用,也可以透過呼叫程式DBMS_SESSION.RESET_PACKAGE.

3、Oracle9i中的PGA自動管理

Oracle9i之前的PGA管理,需要手工設定HASH_AREA_SIZE/SORT_AREA_SIZE等引數，每個連線所佔用的PGA大概為：

UNIX: 1M+HASH_AREA_SIZE+SORT_AREA_SIZE

WINDOWS: 2M+HASH_AREA_SIZE+SORT_AREA_SIZE

　其中1M、2M分別為在unix和windows下的os所佔用的記憶體,用該值乘上估算的連線可以大致計算出所有連線PGA所佔用的記憶體空間。

在Oracle9i中提供了自動管理PGA的新特性，可以自動並動態的在各個Session之間調整記憶體分配。PGA可以分類分為可調整和非可調整，可調整記憶體主要用於DSS系統中。

3.1 跟PGA相關的初始化引數

A．WORKAREA_SIZE_POLICY 可選值為’AUTO’和’MANUAL’,預設值’AUTO’

設定為MANUAL，PGA的分配和管理與之前的資料庫版本一致。設定為AUTO則使用9i的新特性自動管理PGA。

B．PGA_AGGREGATE_TARGET

預設值為25M，如果WORKAREA_SIZE_POLICY設定為AUTO，該值定義最大可用的PGA記憶體。

3.2 確定PGA_AGGREGATE_TARGET的大小

A．估算：

對於OLTP系統 ： PGA_AGGREGATE_TARGET = ( * 80%) * 20%

對於DSS系統 ： PGA_AGGREGATE_TARGET = ( * 80%) * 50%

這種估算基於80％記憶體用於Oracle，其中對於OLTP系統有20％用於PGA，而對於DSS系統有50％用於PGA。

B．使用檢視v$pga_target_advice的建議值

3.3 透過動態效能檢視監控PGA的使用

1) V$PGASTAT：該檢視提供了例項級別上PGA記憶體使用的統計資訊。

下面是對其中一些統計值的解釋：

aggregate PGA auto target ：該列是Oracle9i連線的可用記憶體總數，來源於init.ora轉念館引數pga_aggregate_target

global memory bound ：改統計量測量了一個工作區的最大尺寸，並且Oracle推薦只要該統計量低於1M時,就應該增加pga_aggregate_target的值。

total PGA allocated ：該統計量顯示了資料庫上所有PGA記憶體使用的最高標準。當使用增加時，該值會接近pga_aggregate_target的值。

total PGA used for auto workarea ： 該統計量監控正在以自動記憶體模式執行的所有連線的RAM消耗。記住,Oracle並不是允許所有內部程式都使用自動記憶體功能。例如,Java和 PL/SQL將分配RAM記憶體,但並不計入整個PGA統計量中。因此，你可以減去已分配的PGA的值，以檢視連線佔用的記憶體數量以及Java和PL /SQL消耗的RAM記憶體。

estimated PGA memory for optimal/one-pass ：該統計量估計在最優模式下需要多少記憶體來執行所有任務連線的RAM需求。記住，當Oracle9i經歷記憶體短缺時，Oracle9i將呼叫多通道操作， 以努力查詢最近空閒的RAM記憶體的位置。該統計量對監控Oracle9i中的RAM消耗是重要的，並且大多數Oracle DBA將增加pga_aggregate_target達到該值。

2) 檢視v$sql_workare_active顯示例項中當前正在執行的所有工作區

以下查詢顯示例項中活動的工作區域的使用情況，但低於64K的排序操作在該檢視中不會顯示。

SELECT to_number(decode(SID, 65535, NULL, SID)) sid,operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2

3) 檢視v$sysstat中包含啟動資料庫例項後，工作區以三種模式執行的時間總數

SELECT name,value FROM v$sysstat WHERE name like 'workarea exec%';

optimal: workarea execution of which all processes such as sort are executed on memory
onepass: workarea execution that requires the minimum writes to a disk
multipass: workarea execution that requires heavy writes to a disk because SQL workarea is too low

4、可選的客戶端連線方式

如果伺服器是專用伺服器模式，那麼客戶端只能以DEDICATED方式連上資料庫；如果伺服器是共享伺服器模式，則客戶端可以透過修改tnsnames.ora檔案中的配置引數server來改變與資料庫的連線方式：DEDICATED或SHARED。例如：

-----------tnsnames.ora begins---------------
#MTS
ORADB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oradb)
(SERVER = SHARED)
)
)

#DEDICATED
ORADB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oradb)
(SERVER = DEDICATED)
)
)

--------------tnsnames.ora ends----------------

當客戶端使用ORADB1連線一個共享伺服器時，UGA在共享池中，排序發生在共享池中；當客戶端使用ORADB2連線一個共享伺服器時，UGA在PGA中，排序發生在PGA中。會話的間接模式可以從V$SESSION中的欄位server獲得：

SQL> CONNECT SCOTT/TIGER@ORADB1
Connected
SQL> SELECT SID,SERVER FROM V$SESSION WHERE AUDSID =(SELECT USERENV('SESSIONID') FROM DUAL);
      SID  SERVER
-----------------
        7  SHARED

SQL> CONNECT SCOTT/TIGER@ORADB2
Connected
SQL> SELECT SID,SERVER FROM V$SESSION WHERE AUDSID =(SELECT USERENV('SESSIONID') FROM DUAL);
      SID  SERVER
-----------------
        8  DEDICATED

5、Undocumented PGA

本節主要研究帶排序的SQL語句每次執行時所能使用的最大PGA記憶體是由哪些引數來控制的。以下測試都是基於專用伺服器模式的，共享伺服器模式的本節暫不考慮，Oracle9.2.0.6 for Linux ,RAM 4G。相關係統引數：

SQL>select ksppinm, ksppstvl, ksppdesc from x$ksppi x, x$ksppcv y
where x.indx = y.indx and ksppinm in
('_pga_max_size','_smm_max_size','_smm_px_max_size','pga_aggregate_target');

KSPPINM KSPPSTVL KSPPDESC

---------------- --------- ------------------------------------------------------------
pga_aggregate_target 41943040 Target size for the aggregate PGA memory consumed by the instanc
_pga_max_size 209715200 Maximum size of the PGA memory for one process
_smm_max_size 2048 maximum work area size in auto mode (serial)
_smm_px_max_size 12288 maximum work area size in auto mode (global)

其中,_smm_max_size和_smm_px_max_size的單位是KB，其它都是Byte,_pga_max_size預設值是200M。

當你設定引數pga_aggregate_target時，Oracle系統會根據pga_aggregate_target和 _pga_max_size這兩個值來自動修改引數_smm_max_size,根據 pga_aggregate_target修改_smm_px_max_size，具體規則如下：

<1> When _pga_max_size > 5% of pga_aggregate_target, then _smm_max_size = 5% of pga_aggregate_target

<2> When _pga_max_size <= 5% of pga_aggregate_target, then _smm_max_size = 50% of _pga_max_size

<3> _smm_px_max_size = 30% of pga_aggregate_target

注意：你可以手工修改_smm_max_size和_smm_px_max_size，但Oracle不支援使用者這樣做。

5.1 非並行查詢

對於非並行查詢來說，單條sql語句所能用到的PGA記憶體最大值由以下公式決定：

MIN(pga_aggregate_target,50%*_pga_max_size,_smm_max_size)

在網上經常可以看到有些資料說取MIN(pga_aggregate_target*5%,100M),這種情況沒有考慮直接修改隱藏引數_smm_max_size、 _pga_max_size（預設200M）的情況，在這兩個引數不進行人工修改的情況下，這個式子才成立。

下面給出一個測試的結果表，每次sql完畢後查詢其使用的最大記憶體所用sql如下（為了保證使用了最大記憶體，每次結果中NUMBER_PASSES>0,TEMPSEG_SIZE>0）：

SQL>select t.OPERATION_TYPE,t.SID,t.WORK_AREA_SIZE,t.ACTUAL_MEM_USED,
t.MAX_MEM_USED,t.NUMBER_PASSES,t.TEMPSEG_SIZE
from v$sql_workarea_active t;
其中MAX_MEM_USED就是該sql曾經使用到的最大記憶體

使用以下語句在SYS下修改引數，注意這些引數只能在system level修改，不能在session level進行修改：

SQL> alter system set "pga_aggregate_target"="256m";

SQL> alter system set "_pga_max_size"="30m";

SQL> alter system set "_smm_max_size"=10240;

生成測試資料：

SQL> cretae table test as select * from dba_objects where rownum <= 20000;

SQL> insert into test select * from test;

........ 反覆多次，直到資料量有512萬

SQL> select * from test order by 1,2,3,4,5,6,7,8,9,10;

5.2 並行查詢

對於並行查詢來說，情況要更加複雜一些，主要是對於所有的從屬程式（slave）來說，它們所使用的記憶體有個上限，就是隱藏引數 _smm_px_max_size值。預設情況下，當你只設定pga_aggregate_target值時，系統會同時配置 _smm_px_max_size,使其等於30%*pga_aggregate_target。

簡單來說，對於並行查詢中的每個slave process，其能使用的最大記憶體為：
MIN(pga_max_size/2,_smm_max_size，_smm_px_max_size/degree) degree是查詢時的並行度

Don Burleson有篇文章《Undocumented secrets for super-sizing your PGA》 講到了並行查詢下引數_smm_px_max_size的作用，但是他沒有考慮到引數_smm_max_size對每個程式記憶體的限制作用，因此他的結論 也是有問題的，對此Jonathan Lewis和Tom Kyte都指出了其錯誤。另外，在我的測試中，我還發現這個最大值跟pga_max_size*50%也是有關係的。

參考資料：

<1> http://www.pythian.com/documents/Working_with_Automatic_PGA.ppt

<2>

<3> Don Burleson:Undocumented secrets for super-sizing your PGA

<4> Jonathan Lewis:

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/82387/viewspace-1027512/，如需轉載，請註明出處，否則將追究法律責任。