(轉)PGA自動管理原理深入分析及效能調整
最近在網上看到一遍有關pga的文章,覺得不錯,特放在自己的部落格中供大家學習!
1. PGA的概念以及所包含的記憶體結構
作為一個複雜的oracle資料庫系統來說，每時每刻都要處理不同的使用者所提交的SQL語句，獲取數
據並返回資料給使用者。眾所周知，解析SQL語句的工作是在oracle例項中的shared pool所完成的。那麼對於每個session來說，其執行SQL語句時所傳入的繫結變數放在哪裡？而且，對於那些需要執行比較複雜SQL的session來說，比如需要進行排序（sort）或hash連線（hash-join）時，這時這些session所需要的記憶體空間又從哪裡來？另外，還有與每個session相關的一些管理控制資訊又放在哪裡？對於諸如此類與每個session相關的一些記憶體的分配問題，oracle通過引入PGA這個記憶體元件來進行解決。
1.1 PGA的相關概念
PGA按照oracle官方文件解釋，叫做程式全域性區（Program Global Area），但也有些資料上說還可以理解為程式全域性區（Process Global Area）。這兩者沒有本質的區別，它首先是一個記憶體區域，其次，該區域中包含了與某個特定伺服器程式相關的資料和控制資訊。每個程式都具有自己私有的PGA區，這也就意味著，這塊區域只能被其所屬的程式進入，而不能被其他程式訪問，所以在PGA中不需要latch這樣的記憶體結構來保護其中的資訊。籠統的來說，PGA裡包含了當前程式所使用的有關作業系統資源的資訊（比如開啟的檔案控制程式碼等）以及一些與當前程式相關的一些私有的狀態資訊。
每個PGA區都包含兩部分：
固定PGA部分（Fixed PGA）：這部分包含一些小的固定尺寸的變數，以及指向變化PGA部分的指標。
變化PGA部分（Variable PGA）：這部分是按照堆（Heap）來進行組織的，所以這部分也叫做PGA堆。可以從X$KSMPP檢視中看到有關PGA堆的分佈資訊。PGA堆中所包含的記憶體結構包括：
有關一些固定表的永久性記憶體。
如果session使用的是專用連線方式（dedicated server），則還含有使用者全域性區（UGA－User Global Area）子堆。如果session使用的是共享連線方式（shared server），則UGA位於SGA中。
呼叫全域性區（CGA－Call Global Area）子堆。
1.2 UGA（使用者全域性區）的相關概念
UGA是包含與某個特定session相關資訊的記憶體區域，比如session的登入資訊以及session私有的SQL區域等。每個UGA也包含兩個部分：
固定UGA部分（Fixed UGA）：這部分包含一些小的固定尺寸的變數，以及指向變化UGA部分的指標。
變化UGA部分（Variable UGA）：這部分也是按照堆來進行組織的，可以從X$KSMUP檢視中看到有關UGA堆的分佈情況。UGA堆的分佈與OPEN_CURSORS、OPEN_LINKS等引數有關係。所謂的遊標（cursor）就是放在這裡的。UGA堆中所包含的記憶體結構包括：
私有SQL區域（Private SQL Area）：這部分割槽域包含繫結變數資訊以及執行時的記憶體結構等資料。每一個發出SQL語句的session都有自己的私有SQL區域。這部分割槽域又可分成兩部分：
永久記憶體區域：這裡存放了相同SQL語句多次執行時都需要的一些遊標資訊，比如繫結變數資訊、資料型別轉換資訊等。這部分記憶體只有在遊標被關閉時才會被釋放。
執行時區域：這裡存放了當SQL語句執行時所使用的一些資訊。這部分割槽域的大小尺寸依賴於所要執行的SQL語句的型別（sort或hash-join等）和複雜度以及所要處理的資料行的行數以及行的大小。在處理SQL語句時的第一步就是要建立執行時區域，對於DML（INSERT、UPDATE、DELETE）語句來說，SQL語句執行完畢就釋放該區域；而對於查詢語句（SELECT）來說，則是在所有資料行都被獲取並傳遞給使用者以後被釋放，或者該查詢被取消以後也會被釋放。
Session相關的資訊。這部分資訊包括：
正在使用的包（package）的狀態資訊。
使用alter session這樣的命令所啟用的跟蹤資訊、或者所修改的session級別的優化器引數（optimizer_mode）、排序引數（sort_area_size等）、修改的NLS引數等。
所開啟的dblinks。
可使用的角色（roles）等。
從上面可以很明顯的看出，我們最需要關注的就是私有SQL區域中的執行時區域了。實際上，從9i
以後，對這部分割槽域有了一個新的名稱：SQL工作區域（SQL Work Area）。SQL工作區域的大小依賴於所要處理的SQL語句的複雜程度而定。如果SQL語句包含諸如group by、Hash-join等這樣的操作，則會需要很大的SQL工作區域。實際上，我們調整PGA也就是調整這塊區域。後面還會說到這部分內容。
而UGA所處的位置完全由session連線的方式決定：
如果session是通過共享伺服器（shared server）方式連到資料庫的，則毫無疑問，UGA必須能夠被所有程式訪問，所以這個時候UGA是從SGA中進行分配的。進一步說，如果SGA中設定了large pool，則UGA從large pool裡進行分配；否則，如果沒有設定large pool，則UGA只能從shared pool裡進行分配了。
如果session是通過專用伺服器（dedicated server）方式連到資料庫的，則UGA是從程式的PGA中進行分配的。
1.3 CGA（呼叫全域性區）的相關概念
CGA也是一塊記憶體區域，但它是動態的，隨著呼叫（call）的開始而建立，在呼叫過程中一直存在，直到呼叫結束時被釋放。它存放的是在呼叫過程中所需要的資料。
我們知道，呼叫主要包括解析（parse）呼叫、執行（executive）呼叫、獲取（fetch）呼叫以及遞迴SQL呼叫和PL/SQL呼叫。從呼叫的種類可以看出，實際上在呼叫過程中所需要的資料，比如SQL AREA，PL/SQL AREA和SORT AREA基本都是放在UGA中的，因為這些資料在各個呼叫之間必須一直存在並可用。而在CGA中只存放了在呼叫過程中臨時需要的資料，比如直接I/O快取（Direct I/O Buffer）以及堆疊空間等資料結構。因此，沒有CGA中的資料結構，呼叫是無法完成的。
注意，CGA不象UGA可以位於SGA中（以共享伺服器模式連線），CGA一定是位於PGA中的。如果當前程式正在執行，則每個PGA中只有一個CGA。如果當前程式沒有執行，則該程式的PGA中就沒有CGA。
1.4 轉儲PGA
就象例項中的其他記憶體結構一樣，oracle同樣提供了可以將PGA轉儲到跟蹤檔案的方法。方法如下：
SQL> alter session set events 'immediate trace name heapdump level n';
其中的level n決定了將哪些記憶體堆轉儲到跟蹤檔案：
Level 1: PGA彙總資訊
Level 2: SGA彙總資訊
Level 4: UGA彙總資訊
Level 8: 當前呼叫的彙總資訊（CGA）
Level 16: 使用者呼叫的彙總資訊（CGA）
Level 32: Large pool的彙總資訊（LGA）
Level 1025: PGA詳細資訊
Level 2050: SGA詳細資訊
Level 5000: UGA 詳細資訊
Level 8200: 當前呼叫的詳細資訊
Level 16400: 使用者呼叫的詳細資訊
Level 32800: Large pool的詳細資訊
舉例來說，我們轉儲PGA的彙總資訊：
SQL> alter session set events 'immediate trace name heapdump level 1';
到user_dump_dest所定義的目錄下，找到跟蹤檔案並開啟，可以看到類似下面的資訊：
******************************************************
HEAP DUMP heap name="pga heap" desc=001DB880
extent sz=0x213c alt=84 het=32767 rec=0 flg=2 opc=2
parent=00000000 owner=00000000 nex=00000000 xsz=0x213c
EXTENT 0 addr=03700034
Chunk 370003c sz= 8500 perm "perm " alo=7524
EXTENT 1 addr=0351BC8C
Chunk 351bc94 sz= 9156 freeable "Fixed Uga "
EXTENT 2 addr=03519B3C
Chunk 3519b44 sz= 3764 perm "perm " alo=3764
Chunk 351a9f8 sz= 4196 free " "
Chunk 351ba5c sz= 540 freeable "kopolal dvoid "
……………
Chunk 45e988c sz= 4144 recreate "Alloc environm " latch=00000000
ds 45eade0 sz= 4144 ct= 1
Chunk 45ea8bc sz= 1484 freeable "kpuinit env han"
我們可以看到，其中的紅色部分就是在PGA中所包含的固定UGA部分。同時，我們可以使用如下的命令將PGA的子堆也給轉儲出來，其中9.2以前使用：
SQL> alter session set events 'immediate trace name heapdump_addr level n';
9.2以後使用：
SQL> alter session set events 'immediate trace name heapdump_addr level 1, addr n';
其中的n表示子堆的地址。而子堆的地址可以在PGA的轉儲檔案中找到。比如上面的例子中，我們可以看到這樣的一行：
ds 45eade0 sz= 4144 ct= 1
這裡的ds 45eade0就是某個子堆的地址，這是個十六進位制的數值，於是我們先將其轉換為十進位制數值：
SQL> select to_number('45eade0','xxxxxxxx') from dual;
TO_NUMBER('45EADE0','XXXXXXXX')
-------------------------------
73313760
這裡的73313760就是轉儲PGA子堆的命令中的n，所以我們可以執行（我的測試庫為9.2.0.5）：
SQL> ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level 1, addr 73313760';
2. PGA自動管理及深入研究
2.1 PGA自動管理概述
在9i之前，我們主要是通過設定sort_area_size、hash_area_size等引數值（通常都叫做*_area_size）來管理PGA的使用，不過嚴格說來，是對PGA中的UGA中的私有SQL區域進行管理，這塊記憶體區域又有個名稱叫做SQL工作區域。但是，這裡有個問題，就是這些引數都是針對某個session而言的，也就是說設定的引數值對所有連進來的session都生效。在資料庫實際執行過程中，總有些session需要的PGA多，而有些session需要的PGA少。如果都設定一個很小的*_area_size，則會使得某些SQL語句執行時由於需要將臨時資料交換到磁碟而導致效率低下。而如果都設定一個很大的值，又有可能一方面浪費空間，另一方面，消耗過多記憶體可能導致作業系統其他元件所需要的記憶體短缺，而引起資料庫整體效能下降。所以如何設定*_area_size的值一直都是DBA很頭疼的一個問題。
而從9i起所引入的一個新的特性可以有效的解決這個問題，這個特性就是自動PGA管理。DBA可以根據資料庫的負載情況估計所有session大概需要消耗的PGA的記憶體總和，然後把該值設定為初始化引數pga_aggregate_target的值即可。Oracle會按照每個session的需要為其分配PGA，同時會盡量維持整個PGA的記憶體總和不超過該引數所定義的值。這樣的話，oracle就能儘量避免整個PGA的記憶體容量異常增長而影響整個資料庫的效能。從而，就有效的解決了設定*_area_size所帶來的問題。
不過遺憾的是，9i下的PGA自動管理只對專用連線方式有效，對共享連線方式無效。10g以後對兩種連線方式都有效。
啟用PGA自動管理是很容易的，只要設定兩個初始化引數即可。首先，設定workarea_size_policy引數。該引數為auto（也是預設值）時，表示啟用PGA自動管理；而設定該引數為manual時，則表示禁用PGA自動管理，仍然沿用9i之前的方式，即使用*_area_size對PGA進行管理。其次，就是設定pga_aggregate_target了，該引數可以動態進行調整，範圍是從10MB到4096GB – 1個位元組。
2.2 PGA自動管理深入
PGA中對效能影響最大的就是SQL工作區了。通常說來，SQL工作區越大則對於SQL語句的執行的效率就高，從而對於使用者的響應時間就越少。理想情況下，SQL工作區應該可以容納SQL執行過程中所涉及到的所有輸入資料和控制資訊。當然，這只是理想情況，現實往往總是不能盡如人意，很多情況下SQL工作區是不能容納執行SQL所需要的記憶體空間的，從而不得不交換到臨時表空間裡。為了衡量執行SQL所需要的記憶體與實際分配給該SQL的SQL工作區之間的契合程度，oracle將所分配的SQL工作區大小分成三種型別：
optimal尺寸：SQL語句能夠完全在所分配的SQL工作區內完成所有的操作。這時的效能最佳。
onepass尺寸：SQL語句需要與磁碟上的臨時表空間互動一次才能夠在所分配的SQL工作區中完成所有的操作。 multipass尺寸：由於SQL工作區過小，從而導致SQL語句需要與磁碟上的臨時表空間互動多次才能完成所有的操作。這個時候的效能將急劇下降。
當系統整體負載不大時，oracle傾向於為每個session的PGA分配optimal尺寸大小的SQL工作區。
而隨著負載上升，比如連線的session逐漸增多導致同時執行的SQL語句越來越多時，oracle就會傾向於為每個session的PGA分配onepass尺寸大小的SQL工作區，甚至是multipass尺寸的SQL工作區了。
那麼，PGA自動管理機制在內部到底是如何實現的呢？很遺憾，oracle官方並沒有給出說明文件。其實這本身也說明了，PGA自動管理的內部演算法會隨著版本升級而發生變化。不過，知其然而不知其所以然，總是會讓諸如我等之類的技術人員感覺如梗在喉。還好，曾經就有一些專門做oracle優化的公司釋出的文件中介紹了PGA內部的實現原理，我想這可能是oracle公司透露給這些公司的。這裡就做些簡單的介紹，不過記住，這裡所描述的PGA自動管理的原理並不一定就是將來版本的原理，只能說是截至到9.2的PGA自動管理的原理。
PGA自動管理是採用名為“迴圈反饋（feedback loop）”的演算法來實現的。如下圖所示。
![(轉)PGA自動管理原理深入分析及效能調整]()
screen.width*0.7) {this.resized=true; this.width=screen.width*0.7; this.style.cursor='hand'; this.alt='Click here to open new windownCTRL+Mouse wheel to zoom in/out';}" onclick="if(!this.resized) {return true;} else {window.open('http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061120110245478.JPG');}" src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061120110245478.JPG" onload="if(this.width>screen.width*0.7) {this.resized=true; this.width=screen.width*0.7; this.alt='Click here to open new windownCTRL+Mouse wheel to zoom in/out';}" border="0" />
上圖中，當開始處理一條SQL時，oracle會使用本地記憶體管理器（local memory manager）對該SQL語句相關的work area profile進行註冊。work area profile是一組後設資料，描述了該SQL語句所需要的工作區的所有特徵，包括該SQL的型別（sort還是hash-join等）、該SQL語句的並行度、所需要的記憶體等資訊。它是SQL語句操作與記憶體管理器之間唯一的介面。當SQL語句執行完畢時，其對應的work area profile就會被刪除。而在SQL語句執行期間，為了反映SQL語句當前已經消耗的記憶體以及是否被交換到臨時表空間了等狀態資訊，oracle會不斷更新其對應的work area profile。所以說，SQL語句的work area profile是有生命週期的，始終能夠體現其對應SQL語句的工作區狀態。因此，我們可以說，在任何時間點，所有當前活動的work area profile就能夠基本體現當前所有session對PGA記憶體的需要以及當前正在使用的PGA記憶體。通過查詢檢視v$sql_workarea_active，可以顯示所有當前活動的work area profile的相關資訊。
現在，我們需要引入另外一個後臺守護程式（background daemon），叫做全域性記憶體管理器（global memory manager）。這個程式每隔3秒會啟動一次。每次啟動時，都會根據當前所有活動的work area profile的數量以及其他相關資訊計算出這個時候的SQL工作區的“記憶體限度（memory bound）”，也就是每個工作區最大盡量不能超過多大（不過，注意，嚴格說來應該是儘量不超過。實際上這個最大值是可以被超過的，後面會用個例項來說明）。然後立即釋出這個“記憶體限度”。
最後，本地記憶體管理器關閉“反饋迴圈”，並根據當前的“記憶體限度”以及當前work area profile，從而計算出當前SQL工作區應該具有的記憶體大小，併為程式分配該大小的記憶體以執行SQL語句，這個記憶體的大小尺寸就叫做“期望尺寸（expect size）”，可以從v$sql_workarea_active的expected_size列看到“期望尺寸”的大小。同時，這個“期望尺寸”會定時更新，並據此對SQL工作區進行調整。
Oracle內部對這個“期望尺寸”的大小有如下規則的限制：
“期望尺寸”不能小於最低的記憶體需求。
“期望尺寸”不能大於optimal尺寸。
如果“記憶體限度”介於最低的記憶體需求和optimal尺寸之間，則使用“記憶體限度”作為“期望尺寸”的大小，但是排序操作除外。因為排序操作演算法的限制，對於分配的記憶體在optimal尺寸和onepass尺寸之間時，排序操作不會隨著記憶體的增加而更快完成，除非能夠為排序操作分配optimal尺寸。所以，如果排序操作的“記憶體限度”介於onepass尺寸和optimal尺寸之間的話，“期望尺寸”取onepass尺寸。
如果SQL以並行方式執行，則“期望尺寸”為上面三個規則算出的值乘以並行度。
非並行模式下，按照通常的說法是“期望尺寸”不能超過min(5%*pga_aggregate_target,100MB)。但實際上，這是在不修改_pga_max_size和_smm_max_size這兩個隱藏引數的前提下，可以簡單的這麼認為。嚴格說來，應該是不能超過min(5%*pga_aggregate_target,50%*_pga_max_size,_smm_max_size)。對於並行的情況，就更加複雜，可以簡單認為不超過30%*pga_aggregate_target。
下面，我們舉例（如下圖所示）來說明全域性記憶體管理器是如何計算並應用“記憶體限度”的。比如，
![(轉)PGA自動管理原理深入分析及效能調整]()
screen.width*0.7) {this.resized=true; this.width=screen.width*0.7; this.style.cursor='hand'; this.alt='Click here to open new windownCTRL+Mouse wheel to zoom in/out';}" onclick="if(!this.resized) {return true;} else {window.open('http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061120110245772.JPG');}" src="http://oracle.chinaitlab.com/UploadFiles_7269/200611/20061120110245772.JPG" onload="if(this.width>screen.width*0.7) {this.resized=true; this.width=screen.width*0.7; this.alt='Click here to open new windownCTRL+Mouse wheel to zoom in/out';}" border="0" />
[2]
當前系統中有6個活動的work area profile。WP1所需要的onepass記憶體為7MB，而optimal記憶體為27MB。WP3是一個並行度為2的hash-join，它需要11MB的onepass記憶體，以及67MB的optimal的記憶體。假設pga_aggregate_target設定為133MB，則可以簡單的認為全域性記憶體管理器直接將133除以6，也就是大約20MB作為“記憶體限度”的值。於是該“記憶體限度”限制了分配給WP1的工作區只能為7MB，也就是onepass的大小，因為WP1是一個排序操作，如果給它分配20MB也不能使它在以optimal的方式完成。而對於20MB的“記憶體限度”，WP3可以分到40MB的工作區，因為WP3的並行度為2，所以可以分配20MB×2的大小的工作區。
2.3如何設定新資料庫的PGA值
我們一旦設定了pga_aggregate_target以後，所有的*_area_size就將被忽略。那麼，我們該如何來設定該引數的值呢？這依賴於資料庫的用途，如果資料庫為OLTP（聯機事務處理）應用的，則其應用一般都是小的短的程式，所需要的PGA也相應較少，所以該值該值通常為總共分配給oracle例項的20%，另外的80%則給了SGA；如果資料庫為OLAP（DSS）（資料倉儲或決策分析）應用的，則其應用一般都是很大的，執行時間很長的程式，因此需要的PGA就多。所以通常為PGA分配50%的記憶體。而如果資料庫為混合型別的，則情況比較複雜，一般會先分配40%的初始值，而後隨著資料庫的應用，而不斷對PGA進行監控，並進行相應的調整。
比如，對於8GB實體記憶體的資料庫伺服器來說，按照oracle推薦的，分配給oracle例項的記憶體為實體記憶體的80%。那麼對於OLTP應用來說，pga_aggregate_target的值大約就是1310MB ((8192 MB× 80%)×20%)。而對於OLAP來說，則該值大約就是3276MB (8192MB×80%)×50%)。
當然，這裡所說的都是對於一個新的資料庫來說，初始設定的值。這些值並不一定正確，可能設定過大，也可能設定過小。必須隨著系統的不斷執行，DBA需要不斷監控，從而對其進行調整。
3. PGA監控及調優
我們已經大致瞭解了有關PGA的相關理論知識，現在我們可以開始動手實踐來驗證上面的理論，並
可以開始對PGA的使用進行監控以及調優了。以下測試都是在windows XP、oracle 9.2.0.5，以及專用連
接模式下進行的。
3.1準備測試用例
首先，我們先建立一個測試用例。
SQL> create table pga_test as select * from dba_objects;
SQL> select count(*) from pga_test;
COUNT(*)
----------
6243
然後，引入幾個監控PGA的指令碼。
pga_by_hashvalue.sql，這是一個監控SQL語句所使用的SQL工作區的指令碼：
SELECT
b.sql_text,
a.operation_type,
a.policy,
a.last_memory_used/(1024*1024) as "Used MB" ,
a.estimated_optimal_size/(1024*1024) as "Est Opt MB",
a.estimated_onepass_size/(1024*1024) as "Est OnePass MB",
a.last_execution,
a.last_tempseg_size
FROM v$sql_workarea a,v$sql b
WHERE a.hash_value = b.hash_value
and a.hash_value = %26;amp;hashvalue
/
pga_by_session.sql，第二個指令碼是pga_by_session.sql，用來監控session所使用的PGA和UGA的大小：
select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = %26;amp;sid
and a.name like '%ga %'
order by a.name
/
第三個指令碼監控程式所使用的PGA的大小，pga_by_process.sql ：
SELECT
a.pga_used_mem "PGA Used",
a.pga_alloc_mem "PGA Alloc",
a.pga_max_mem "PGA Max"
FROM v$process a,v$session b
where a.addr = b.paddr
and b.sid= %26;amp;sid
/
3.2單個session對PGA使用情況的監控
我們分別建立5個session，第一個session（sess#1）執行測試語句；第二個session（sess#2）執行pga_by_hashvalue.sql指令碼；第三個session（sess#3）執行pga_by_session.sql指令碼；第四個session（sess#4）執行pga_by_process.sql指令碼；第五個session（sess#5）設定相關引數。以下按照順序描述整個測試的過程。
Sess#1：
SQL> select sid from v$mystat where rownum=1;
SID
----------
7
Sess#3查詢當前sid為7的session的PGA和UGA各為多少，可以看到，即使不執行任何的SQL，只要session連線了，就會消耗大約0.23MB的PGA記憶體：
SQL> @pga_by_session.sql;
NAME VALUE
------------------------------ ----------
session pga memory 238188
session pga memory max 238188
session uga memory 77008
session uga memory max 77008
Sess#5，我們將pga_aggregate_target設定為60MB：
SQL> alter system set pga_aggregate_target=60M;
Sess#1，執行測試語句：
SQL> set autotrace traceonly stat;
SQL> select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8;
Sess#5，找到sess#1中所執行的SQL語句的hash值：
SQL> select hash_value from v$sql where sql_text='select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8';
HASH_VALUE
----------
2656983355
Sess#2：
SQL> @d:pga_by_hashvalue.sql
輸入 hashvalue 的值: 2656983355
原值 12: and a.hash_value = %26;amp;hashvalue
新值 12: and a.hash_value = 2656983355
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8
SORT AUTO 3
66.1376953 2.75390625 2 PASSES 65011712
我們可以看到，該SQL語句所分配的工作區為3MB，這個值就是5%*pga_aggregate_target（60M*0.05）。符合前面說到的“期望尺寸”為min(5%*pga_aggregate_target,100MB)。
Sess#3：
SQL> @ pga_by_session.sql;
NAME VALUE
------------------------------ ----------
session pga memory 369796
session pga memory max 4956780
session uga memory 77008
session uga memory max 3677528
[3]
可以看到，為了執行測試語句，為該session分配的PGA為4956780個位元組，其中UGA為3677528個位元組，大約3.5M。同時可以看出，執行完測試語句以後，oracle就把該session的PGA空間回收了（PGA從4956780下降到369796，而UGA從3677528下降到77008），順帶提一下，在8i中分配了PGA以後是不會回收的，也就是說session pga memory始終等於session pga memory max，而9i以後的PGA的分配方式發生了改變，從而能夠在分配PGA以後還可以再回收一部分記憶體。結合上面為SQL語句所分配的3M的工作區，可以知道，UGA中的其他空間佔用大約0.5M。而SQL工作區佔整個PGA大小大約為64%，從這個方面也可以看出，SQL工作區是PGA中最佔空間、也是最重要的部分。
Sess#4：
SQL> @d:pga_by_process.sql
輸入 sid 的值: 7
原值 7: and b.sid= %26;amp;sid
新值 7: and b.sid= 7
PGA Used PGA Alloc PGA Max
---------- ---------- ----------
253932 382664 4969648
可以看到，這幾個檢視查出來的PGA的大小基本都是一致的。
我們繼續測試，從sess#2可以看出，如果要讓該SQL語句完全在記憶體中完成，需要大約67MB的PGA空間。根據5%的原理倒算，可以知道這個時候的pga_aggregate_target應該大於1340MB（67/0.05）。於是，我們設定1500MB，來看看是不是確實進行optimal了。順便提醒一下，並不是說你的電腦得有超過1500MB的實體記憶體你才可以設定1500M的pga_aggregate_target，事實上pga_aggregate_target是按需分配的，不象SGA，一旦設定就佔著記憶體，不用也得佔著。也就是說是PGA是隨著對記憶體需求的增長而不斷增長的。我測試的機器上只有1GB的實體記憶體，但做測試時完全可以將pga_aggregate_target設定5GB，甚至更高的10GB。
Sess#5，我們將pga_aggregate_target設定為1500MB：
SQL> alter system set pga_aggregate_target=1500M;
Sess#1：
SQL> select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8;
Sess#2：
SQL> @d:pga_by_hashvalue.sql
輸入 hashvalue 的值: 2656983355
原值 12: and a.hash_value = %26;amp;hashvalue
新值 12: and a.hash_value = 2656983355
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8
SORT AUTO 65.765625
73.9873047 2.90039063 OPTIMAL
我們可以看到，該SQL語句確實完全在記憶體裡完成了（LAST_EXECUTION為“OPTIMAL”）。同時，實際的“期望尺寸”始終會小於optimal（65.765625<73.9873047），也符合前面說的第二條規則。
我們繼續測試，看看SQL工作區的“期望尺寸”是否真的不能超過100MB。為此，需要設定5%*
pga_aggregate_target>100MB，因此pga_aggregate_target最少要大於2G，我們設定5GB。
Sess#5，我們將pga_aggregate_target設定為5GB：
SQL> alter system set pga_aggregate_target=5G;
Sess#1，注意，為了能夠佔用更多的PGA，這時的SQL語句已經把where條件修改了：
SQL> select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8;
Sess#5，找到該語句的hash值：
SQL> select hash_value from v$sql where sql_text='select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8';
HASH_VALUE
----------
3008669403
Sess#2：
SQL> /
輸入 hashvalue 的值: 3008669403
原值 12: and a.hash_value = %26;amp;hashvalue
新值 12: and a.hash_value = 3008669403
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8
SORT AUTO 87.265625
137.232422 3.87109375 1 PASS 127926272
可以看到，optimal尺寸已經超過100MB很多了，但是實際分配的“期望尺寸”卻只有88MB左右。而5G*0.05為250MB，為何該SQL用不了呢？這其實是由兩個隱藏引數決定的，分別是_pga_max_size和_smm_max_size。我們來看一下這兩個引數的含義和預設值：
Sess#5：
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');
KSPPINM KSPPSTVL KSPPDESC
-------------- ---------- -----------------------------------------------
_pga_max_size 209715200 Maximum size of the PGA memory for one process
_smm_max_size 102400 maximum work area size in auto mode (serial)
我們可以看到_pga_max_size預設值為200M（209715200/1024/1024），而_smm_max_size預設值為100MB（上面的查詢結果中顯示的單位是KB）。而每個session的PGA最多隻能使用_pga_max_size的一半，也就是100MB。
當你修改引數pga_aggregate_target的值時，Oracle系統會根據pga_aggregate_target和_pga_max_size這兩個值來自動修改引數_smm_max_size。具體修改的規則是：
如果_pga_max_size大於5%*pga_aggregate_target，則_smm_max_size為5%*pga_aggregate_target。
如果_pga_max_size小於等於5%*pga_aggregate_target，則_smm_max_size為50%*_pga_max_size。
有些資料上說，可以通過修改_pga_max_size來突破這個100MB的限制。真的是這樣嗎？我們來測試。Sess#5，修改引數_pga_max_size為600MB：
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 5368709120
SQL> alter system set "_pga_max_size"=600M;
我們將_pga_max_size的值設定為600M，其一半就是300MB，已經超過5%*pga_aggregate_target（即250MB）了。所以這兩者的較小值為250M，如果這時我們在sess#1中再次執行測試語句，應該可以使用超過100MB的SQL工作區了。我們來看測試結果。
Sess#1：
SQL> select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8;
Sess#2：
SQL> /
輸入 hashvalue 的值: 3008669403
原值 12: and a.hash_value = %26;amp;hashvalue
新值 12: and a.hash_value = 3008669403
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8
SORT AUTO 87.265625
137.232422 3.87109375 1 PASS 127926272
[4]
我們看到，“期望尺寸”仍然是大約88MB，並沒有突破100MB的限制。其中的問題就在於引數
_smm_max_size 上。我們來看這個時候該引數值是多少：
Sess#5：
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');
KSPPINM KSPPSTVL KSPPDESC
-------------- ---------- -----------------------------------------------
_pga_max_size 629145600 Maximum size of the PGA memory for one process
_smm_max_size 102400 maximum work area size in auto mode (serial)
可以看到引數_smm_max_size的值仍然是100MB。實際上，這也是一個對 “期望尺寸”的限制引數。這裡可以看到“期望尺寸”不能超過100MB。這時，我們只要簡單的執行：
Sess#5：
SQL> alter system set pga_aggregate_target=5G;
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');
KSPPINM KSPPSTVL KSPPDESC
-------------- ---------- -----------------------------------------------
_pga_max_size 629145600 Maximum size of the PGA memory for one process
_smm_max_size 262144 maximum work area size in auto mode (serial)
我們可以看到，只要設定一下pga_aggregate_target，就會按照前面所說的規則重新計算並設定_smm_max_size的值，該引數修改後的值為250MB。這個時候我們重複上面的測試：
Sess#1：
SQL> select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8;
Sess#2：
SQL> /
輸入 hashvalue 的值: 3008669403
原值 12: and a.hash_value = %26;amp;hashvalue
新值 12: and a.hash_value = 3008669403
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8
SORT AUTO 137.195313
154.345703 4.09179688 OPTIMAL
這時，我們看到，“期望尺寸”為138MB左右，終於超過了100MB。如果我們再次將引數_smm_max_size人為的降低到100MB，則“期望尺寸”又將不能突破100MB了。我們來看試驗。
Sess#5：
SQL> alter system set "_smm_max_size"=102400;
Sess#1：
SQL> select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8;
Sess#2：
SQL> /
輸入 hashvalue 的值: 3008669403
原值 12: and a.hash_value = %26;amp;hashvalue
新值 12: and a.hash_value = 3008669403
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8
SORT AUTO 87.265625
137.232422 3.87109375 1 PASS 127926272
可以看到，結果正如我們所預料的。由此，得出我們重要的結論，就是在非並行方式下，“期望尺寸”為min(5%*pga_aggregate_target,50%*_pga_max_size,_smm_max_size)，而不是很多資料上所說的不是很嚴密的min(5%*pga_aggregate_target,50%*_pga_max_size)。oracle當然是不推薦我們修改這兩個隱藏引數的。
3.3多個併發session對PGA使用情況的監控
現在我們可以來測試多個session併發時PGA的分配情況。測試併發的方式有很多，可以寫一個小程式迴圈建立多個連線，然後執行上面的測試語句，也可以藉助一些工具來完成。為了方便起見，我用了一個最簡單的方式。就是寫一個SQL文字，再寫一個bat檔案，該bat檔案中執行SQL文字。兩個檔案準備好以後，將bat檔案拷貝30份，然後選中這30份一摸一樣的bat檔案，按Enter鍵後，windows XP將同時執行這30個bat檔案，這樣就可以模擬出30個session同時連線並同時執行測試語句的環境了。具體這兩個檔案的具體內容如下：
pga_test.sql：
set autotrace traceonly stat;
select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8;
run.bat：
@sqlplus -s
[url=mailto:cost/cost@ora92]cost/cost@ora92[/url]
@d:testpga_test.sql
我們先將pga_aggregate_target設定為60MB。
Sess#5：
SQL> alter system set pga_aggregate_target=60M;
然後同時執行30個bat檔案從而啟動30個執行相同SQL測試語句的併發session，我執行下面的語句以顯示這時正在執行的30個session所消耗的PGA的總記憶體：
Sess#5：
SQL> select a.name, sum(b.value)/1024/1024 as "MB"
2 from v$statname a, v$sesstat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %'
5 and sid in(select sid from v$sql_workarea_active)
6 group by a.name;
NAME MB
---------------------------------------------------------------- ----------
session pga memory 45.9951134
session pga memory max 95.6863365
session uga memory 19.757431
session uga memory max 72.6992035
我們可以看到，session pga memory max顯示出大約96MB的PGA記憶體，很明顯，PGA的總容量已經超出了pga_aggregate_target（60M）的限制的容量。實際上這也就說明，該引數只是說明，oracle會盡量維護整個PGA記憶體不超過這個值，如果實在沒有辦法，也還是會突破該引數限制的。
同時，我們可以去檢視這個時候該測試SQL語句所分配的工作區變成了多少，同樣在Sess#2中：
SQL> @d:pga_by_hashvalue.sql
輸入 hashvalue 的值: 2656983355
原值 12: and a.hash_value = %26;amp;hashvalue
新值 12: and a.hash_value = 2656983355
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8
SORT AUTO 1.8984375
66.1376953 2.75390625 2 PASSES 65011712
從結果中我們可以看到，該SQL的工作區已經從單個session時的3MB下降到了大約1.9M，我們可以看到，30個session總共至少需要57MB（1.9M*30）的SQL工作區。明顯的，60MB的pga_aggregate_target是肯定不能滿足需要的。
[5]
3.4其他監控並調整PGA的方法
我們監控PGA的檢視除了上面介紹到的v$sql_workarea_active、v$sesstat、v$sql_workarea以及v$process以外，還有v$sql_workarea_histogram、v$pgastat以及v$sysstat。
v$sql_workarea_histogram記錄了每個範圍的SQL工作區內所執行的optimal、onepass、multipass的次數。如下所示：
SQL> select
2 low_optimal_size/1024 "Low (K)",
3 (high_optimal_size + 1)/1024 "High (K)",
4 optimal_executions "Optimal",
5 onepass_executions "1-Pass",
6 multipasses_executions ">1 Pass"
7 from v$sql_workarea_histogram
8 where total_executions <> 0;
結果類似如下所示，我們可以看到整個系統所需要的PGA的記憶體大小主要集中在什麼範圍裡面。
Low (K) High (K) Optimal 1-Pass >1 Pass
---------- ---------- ---------- ---------- ----------
8 16 360 0 0
。。。。。。。。。
65536 131072 0 2 0
另外，我們可以將上面的查詢語句改寫一下，以獲得optimal、onepass、multipass執行次數的百分比，很明顯，optimal所佔的百分比越高越好，如果onepass和multipass佔的百分比很高，就不需要增加pga_aggregate_target的值了，或者調整SQL語句以使用更少的PGA區。
SQL> select
2 optimal_count "Optimal",
3 round(optimal_count * 100 / total,2) "Optimal %",
4 onepass_count "OnePass",
5 round(onepass_count * 100 / total,2) "Onepass %",
6 multipass_count "MultiPass",
7 round(multipass_count * 100 / total,2) "Multipass %"
8 from (
9 select
10 sum(total_executions) total,
11 sum(optimal_executions) optimal_count,
12 sum (onepass_executions) onepass_count,
13 sum (multipasses_executions) multipass_count
14 from v$sql_workarea_histogram
15 where total_executions <> 0)
16 /
Optimal Optimal % OnePass Onepass % MultiPass Multipass %
---------- ---------- ---------- ---------- ---------- -----------
402 99.01 4 0.99 0 0
而v$pgastat則提供了有關PGA使用的整體的概括性的資訊。
SQL> select * from v$pgastat;
NAME VALUE UNIT
---------------------------------------- ---------- ------------
aggregate PGA target parameter 62914560 bytes
aggregate PGA auto target 51360768 bytes
global memory bound 104857600 bytes
total PGA inuse 5846016 bytes
total PGA allocated 8386560 bytes
maximum PGA allocated 66910208 bytes
total freeable PGA memory 0 bytes
PGA memory freed back to OS 0 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 51167232 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
over allocation count 0
bytes processed 142055424 bytes
extra bytes read/written 138369024 bytes
cache hit percentage 50.65 percent
從結果可以看出，第一行表示pga_aggregate_target設定為60M。PGA的一部分被用於無法動態調整的部分，比如UGA中的“session相關的資訊”等。而PGA記憶體的剩下部分則是可以動態調整的，由“aggregate PGA auto target”說明。我們來看第二行的值，就表示可以動態調整的記憶體數量，該值不能與pga_aggregate_target設定的值差太多。如果該值太小，則oracle沒有足夠的記憶體空間來動態調整session的記憶體工作區。其中的global memory bound表示一個工作區的最大尺寸，並且oracle推薦只要該統計值低於1M時，就應該增加pga_aggregate_target的值。另外，9i還提供了兩個有用的指標：over allocation count和cache hit percentage。如果在使用SQL工作區過程中，oracle認為pga_aggregate_target過小，則它自己會去多分配需要的記憶體。則多分配的次數就累加在over allocation count指標裡。該值越小越好，最好為0。cache hit percentage則表示完全在記憶體裡完成的操作的位元組數與所有完成的操作(包括optimal、onepass、multipass)的位元組數的比率。如果所有的操作都是optimal類，則該值為100%。
最後，我們可以查詢v$sysstat檢視，獲得optimal、onepass、multipass執行的總次數：
SQL> select * from v$sysstat where name like 'workarea executions%';
STATISTIC# NAME CLASS VALUE
---------- ---------------------------------------- ---------- ----------
230 workarea executions - optimal 64 360
231 workarea executions - onepass 64 2
232 workarea executions - multipass 64 0
我們可以計算optimal次數佔總次數的比率，比如上例中，360/(360+2+0)=99.45%，該比率越大越好，如果發現onepass和multipass較多，則需要增加pga_aggregate_target，或者調整SQL語句以使用更少的PGA區。
那麼我們如何找到需要調整以使用更少的PGA的SQL語句呢？我們可以將v$sql_workarea中的記錄按照estimated_optimal_size欄位由大到小的排序，選出排在前幾位的hash值，同時還可以選出last_execution值為“n PASSES”（這裡的n大於或等於2）的hash值，將這些hash值與v$sql關聯後找出相應的SQL語句，進行調整，以便使其使用更少的PGA。
[6]
3.5 PGA的自動建議特性
那麼，如果我們需要調整pga_aggregate_target時，到底我們應該設定多大呢？oracle為了幫助我們確定這個引數的值，引入了一個新的檢視：v$pga_target_advice。為了使用該檢視，需要將初始化引數statistics_level設定為typical（預設值）或all。
SQL> select
2 round(pga_target_for_estimate /(1024*1024)) "Target (M)",
3 estd_pga_cache_hit_percentage "Est. Cache Hit %",
4 round(estd_extra_bytes_rw/(1024*1024)) "Est. ReadWrite (M)",
5 estd_overalloc_count "Est. Over-Alloc"
6 from v$pga_target_advice
7 /
Target (M) Est. Cache Hit % Est. ReadWrite (M) Est. Over-Alloc
---------- ---------------- ------------------ ---------------
15 34 264 1
30 34 264 0
45 34 264 0
60 67 66 0
72 67 66 0
84 67 66 0
96 67 66 0
108 67 66 0
120 67 66 0
180 67 66 0
240 67 66 0
360 67 66 0
480 67 66 0
該輸出告訴我們，按照系統目前的運轉情況，我們pga設定的不同值所帶來的不同效果。根據該輸出，我們找到能使estd_overalloc_count為0的最小pga_aggregate_target的值。從這裡可以看出，是30M。注意，隨著我們增加pga的尺寸，estd_pga_cache_hit_percentage不斷增加，同時estd_extra_bytes_rw(表示onepass、multipass讀寫的位元組數)不斷減小。從上面的結果，我們可以知道，將pga_aggregate_target設定為60MB是最合理的，因為即便將其設定為480MB，命中率也不會有所提高。
同時，我們知道v$tempstat裡記錄了讀寫臨時表空間的資料塊數量以及所花費的時間。這樣，我們就可以結合v$pga_target_advice和v$tempstat這兩個檢視。可以得到每一種估計PGA值下的響應時間大致是多少，從而可以換一個角度來顯示PGA的建議值：
SQL> SELECT 'PGA Aggregate Target' component,
2 ROUND (pga_target_for_estimate / 1048576) target_size,
3 estd_pga_cache_hit_percentage cache_hit_ratio,
4 ROUND ( ( ( estd_extra_bytes_rw / DECODE ((b.BLOCKSIZE * i.avg_blocks_per_io),0, 1,
5 (b.BLOCKSIZE * i.avg_blocks_per_io)))* i.iotime)/100 ) "response_time(sec)"
6 FROM v$pga_target_advice,
7 (SELECT /*+AVG TIME TO DO AN IO TO TEMP TABLESPACE*/
8 AVG ( (readtim + writetim) /
9 DECODE ((phyrds + phywrts), 0, 1, (phyrds + phywrts)) ) iotime,
10 AVG ( (phyblkrd + phyblkwrt)/
11 DECODE ((phyrds + phywrts), 0, 1, (phyrds + phywrts))) avg_blocks_per_io
12 FROM v$tempstat) i,
13 (SELECT /* temp ts block size */ VALUE BLOCKSIZE
14 FROM v$parameter WHERE NAME = 'db_block_size') b;
COMPONENT TARGET_SIZE CACHE_HIT_RATIO response_time(sec)
-------------------- ----------- --------------- ------------------
PGA Aggregate Target 15 34 85
PGA Aggregate Target 30 34 85
PGA Aggregate Target 45 34 85
PGA Aggregate Target 60 68 21
PGA Aggregate Target 72 68 21
PGA Aggregate Target 84 68 21
PGA Aggregate Target 96 68 21
PGA Aggregate Target 108 68 21
PGA Aggregate Target 120 68 21
PGA Aggregate Target 180 68 21
PGA Aggregate Target 240 68 21
PGA Aggregate Target 360 68 21
PGA Aggregate Target 480 68 21
注意，每次我們調整了pga_aggregate_target引數以後，都應該在系統執行一、兩天以後檢查檢視：v$sysstat、v$pgastat、v$pga_target_advice，以確定修改的值是否滿足系統的需要。
[@more@]
作為一個複雜的oracle資料庫系統來說，每時每刻都要處理不同的使用者所提交的SQL語句，獲取數
據並返回資料給使用者。眾所周知，解析SQL語句的工作是在oracle例項中的shared pool所完成的。那麼對於每個session來說，其執行SQL語句時所傳入的繫結變數放在哪裡？而且，對於那些需要執行比較複雜SQL的session來說，比如需要進行排序（sort）或hash連線（hash-join）時，這時這些session所需要的記憶體空間又從哪裡來？另外，還有與每個session相關的一些管理控制資訊又放在哪裡？對於諸如此類與每個session相關的一些記憶體的分配問題，oracle通過引入PGA這個記憶體元件來進行解決。
1.1 PGA的相關概念
PGA按照oracle官方文件解釋，叫做程式全域性區（Program Global Area），但也有些資料上說還可以理解為程式全域性區（Process Global Area）。這兩者沒有本質的區別，它首先是一個記憶體區域，其次，該區域中包含了與某個特定伺服器程式相關的資料和控制資訊。每個程式都具有自己私有的PGA區，這也就意味著，這塊區域只能被其所屬的程式進入，而不能被其他程式訪問，所以在PGA中不需要latch這樣的記憶體結構來保護其中的資訊。籠統的來說，PGA裡包含了當前程式所使用的有關作業系統資源的資訊（比如開啟的檔案控制程式碼等）以及一些與當前程式相關的一些私有的狀態資訊。
每個PGA區都包含兩部分：
固定PGA部分（Fixed PGA）：這部分包含一些小的固定尺寸的變數，以及指向變化PGA部分的指標。
變化PGA部分（Variable PGA）：這部分是按照堆（Heap）來進行組織的，所以這部分也叫做PGA堆。可以從X$KSMPP檢視中看到有關PGA堆的分佈資訊。PGA堆中所包含的記憶體結構包括：
有關一些固定表的永久性記憶體。
如果session使用的是專用連線方式（dedicated server），則還含有使用者全域性區（UGA－User Global Area）子堆。如果session使用的是共享連線方式（shared server），則UGA位於SGA中。
呼叫全域性區（CGA－Call Global Area）子堆。
1.2 UGA（使用者全域性區）的相關概念
UGA是包含與某個特定session相關資訊的記憶體區域，比如session的登入資訊以及session私有的SQL區域等。每個UGA也包含兩個部分：
固定UGA部分（Fixed UGA）：這部分包含一些小的固定尺寸的變數，以及指向變化UGA部分的指標。
變化UGA部分（Variable UGA）：這部分也是按照堆來進行組織的，可以從X$KSMUP檢視中看到有關UGA堆的分佈情況。UGA堆的分佈與OPEN_CURSORS、OPEN_LINKS等引數有關係。所謂的遊標（cursor）就是放在這裡的。UGA堆中所包含的記憶體結構包括：
私有SQL區域（Private SQL Area）：這部分割槽域包含繫結變數資訊以及執行時的記憶體結構等資料。每一個發出SQL語句的session都有自己的私有SQL區域。這部分割槽域又可分成兩部分：
永久記憶體區域：這裡存放了相同SQL語句多次執行時都需要的一些遊標資訊，比如繫結變數資訊、資料型別轉換資訊等。這部分記憶體只有在遊標被關閉時才會被釋放。
執行時區域：這裡存放了當SQL語句執行時所使用的一些資訊。這部分割槽域的大小尺寸依賴於所要執行的SQL語句的型別（sort或hash-join等）和複雜度以及所要處理的資料行的行數以及行的大小。在處理SQL語句時的第一步就是要建立執行時區域，對於DML（INSERT、UPDATE、DELETE）語句來說，SQL語句執行完畢就釋放該區域；而對於查詢語句（SELECT）來說，則是在所有資料行都被獲取並傳遞給使用者以後被釋放，或者該查詢被取消以後也會被釋放。
Session相關的資訊。這部分資訊包括：
正在使用的包（package）的狀態資訊。
使用alter session這樣的命令所啟用的跟蹤資訊、或者所修改的session級別的優化器引數（optimizer_mode）、排序引數（sort_area_size等）、修改的NLS引數等。
所開啟的dblinks。
可使用的角色（roles）等。
從上面可以很明顯的看出，我們最需要關注的就是私有SQL區域中的執行時區域了。實際上，從9i
以後，對這部分割槽域有了一個新的名稱：SQL工作區域（SQL Work Area）。SQL工作區域的大小依賴於所要處理的SQL語句的複雜程度而定。如果SQL語句包含諸如group by、Hash-join等這樣的操作，則會需要很大的SQL工作區域。實際上，我們調整PGA也就是調整這塊區域。後面還會說到這部分內容。
而UGA所處的位置完全由session連線的方式決定：
如果session是通過共享伺服器（shared server）方式連到資料庫的，則毫無疑問，UGA必須能夠被所有程式訪問，所以這個時候UGA是從SGA中進行分配的。進一步說，如果SGA中設定了large pool，則UGA從large pool裡進行分配；否則，如果沒有設定large pool，則UGA只能從shared pool裡進行分配了。
如果session是通過專用伺服器（dedicated server）方式連到資料庫的，則UGA是從程式的PGA中進行分配的。
1.3 CGA（呼叫全域性區）的相關概念
CGA也是一塊記憶體區域，但它是動態的，隨著呼叫（call）的開始而建立，在呼叫過程中一直存在，直到呼叫結束時被釋放。它存放的是在呼叫過程中所需要的資料。
我們知道，呼叫主要包括解析（parse）呼叫、執行（executive）呼叫、獲取（fetch）呼叫以及遞迴SQL呼叫和PL/SQL呼叫。從呼叫的種類可以看出，實際上在呼叫過程中所需要的資料，比如SQL AREA，PL/SQL AREA和SORT AREA基本都是放在UGA中的，因為這些資料在各個呼叫之間必須一直存在並可用。而在CGA中只存放了在呼叫過程中臨時需要的資料，比如直接I/O快取（Direct I/O Buffer）以及堆疊空間等資料結構。因此，沒有CGA中的資料結構，呼叫是無法完成的。
注意，CGA不象UGA可以位於SGA中（以共享伺服器模式連線），CGA一定是位於PGA中的。如果當前程式正在執行，則每個PGA中只有一個CGA。如果當前程式沒有執行，則該程式的PGA中就沒有CGA。
1.4 轉儲PGA
就象例項中的其他記憶體結構一樣，oracle同樣提供了可以將PGA轉儲到跟蹤檔案的方法。方法如下：
SQL> alter session set events 'immediate trace name heapdump level n';
其中的level n決定了將哪些記憶體堆轉儲到跟蹤檔案：
Level 1: PGA彙總資訊
Level 2: SGA彙總資訊
Level 4: UGA彙總資訊
Level 8: 當前呼叫的彙總資訊（CGA）
Level 16: 使用者呼叫的彙總資訊（CGA）
Level 32: Large pool的彙總資訊（LGA）
Level 1025: PGA詳細資訊
Level 2050: SGA詳細資訊
Level 5000: UGA 詳細資訊
Level 8200: 當前呼叫的詳細資訊
Level 16400: 使用者呼叫的詳細資訊
Level 32800: Large pool的詳細資訊
舉例來說，我們轉儲PGA的彙總資訊：
SQL> alter session set events 'immediate trace name heapdump level 1';
到user_dump_dest所定義的目錄下，找到跟蹤檔案並開啟，可以看到類似下面的資訊：
******************************************************
HEAP DUMP heap name="pga heap" desc=001DB880
extent sz=0x213c alt=84 het=32767 rec=0 flg=2 opc=2
parent=00000000 owner=00000000 nex=00000000 xsz=0x213c
EXTENT 0 addr=03700034
Chunk 370003c sz= 8500 perm "perm " alo=7524
EXTENT 1 addr=0351BC8C
Chunk 351bc94 sz= 9156 freeable "Fixed Uga "
EXTENT 2 addr=03519B3C
Chunk 3519b44 sz= 3764 perm "perm " alo=3764
Chunk 351a9f8 sz= 4196 free " "
Chunk 351ba5c sz= 540 freeable "kopolal dvoid "
……………
Chunk 45e988c sz= 4144 recreate "Alloc environm " latch=00000000
ds 45eade0 sz= 4144 ct= 1
Chunk 45ea8bc sz= 1484 freeable "kpuinit env han"
我們可以看到，其中的紅色部分就是在PGA中所包含的固定UGA部分。同時，我們可以使用如下的命令將PGA的子堆也給轉儲出來，其中9.2以前使用：
SQL> alter session set events 'immediate trace name heapdump_addr level n';
9.2以後使用：
SQL> alter session set events 'immediate trace name heapdump_addr level 1, addr n';
其中的n表示子堆的地址。而子堆的地址可以在PGA的轉儲檔案中找到。比如上面的例子中，我們可以看到這樣的一行：
ds 45eade0 sz= 4144 ct= 1
這裡的ds 45eade0就是某個子堆的地址，這是個十六進位制的數值，於是我們先將其轉換為十進位制數值：
SQL> select to_number('45eade0','xxxxxxxx') from dual;
TO_NUMBER('45EADE0','XXXXXXXX')
-------------------------------
73313760
這裡的73313760就是轉儲PGA子堆的命令中的n，所以我們可以執行（我的測試庫為9.2.0.5）：
SQL> ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level 1, addr 73313760';
2. PGA自動管理及深入研究
2.1 PGA自動管理概述
在9i之前，我們主要是通過設定sort_area_size、hash_area_size等引數值（通常都叫做*_area_size）來管理PGA的使用，不過嚴格說來，是對PGA中的UGA中的私有SQL區域進行管理，這塊記憶體區域又有個名稱叫做SQL工作區域。但是，這裡有個問題，就是這些引數都是針對某個session而言的，也就是說設定的引數值對所有連進來的session都生效。在資料庫實際執行過程中，總有些session需要的PGA多，而有些session需要的PGA少。如果都設定一個很小的*_area_size，則會使得某些SQL語句執行時由於需要將臨時資料交換到磁碟而導致效率低下。而如果都設定一個很大的值，又有可能一方面浪費空間，另一方面，消耗過多記憶體可能導致作業系統其他元件所需要的記憶體短缺，而引起資料庫整體效能下降。所以如何設定*_area_size的值一直都是DBA很頭疼的一個問題。
而從9i起所引入的一個新的特性可以有效的解決這個問題，這個特性就是自動PGA管理。DBA可以根據資料庫的負載情況估計所有session大概需要消耗的PGA的記憶體總和，然後把該值設定為初始化引數pga_aggregate_target的值即可。Oracle會按照每個session的需要為其分配PGA，同時會盡量維持整個PGA的記憶體總和不超過該引數所定義的值。這樣的話，oracle就能儘量避免整個PGA的記憶體容量異常增長而影響整個資料庫的效能。從而，就有效的解決了設定*_area_size所帶來的問題。
不過遺憾的是，9i下的PGA自動管理只對專用連線方式有效，對共享連線方式無效。10g以後對兩種連線方式都有效。
啟用PGA自動管理是很容易的，只要設定兩個初始化引數即可。首先，設定workarea_size_policy引數。該引數為auto（也是預設值）時，表示啟用PGA自動管理；而設定該引數為manual時，則表示禁用PGA自動管理，仍然沿用9i之前的方式，即使用*_area_size對PGA進行管理。其次，就是設定pga_aggregate_target了，該引數可以動態進行調整，範圍是從10MB到4096GB – 1個位元組。
2.2 PGA自動管理深入
PGA中對效能影響最大的就是SQL工作區了。通常說來，SQL工作區越大則對於SQL語句的執行的效率就高，從而對於使用者的響應時間就越少。理想情況下，SQL工作區應該可以容納SQL執行過程中所涉及到的所有輸入資料和控制資訊。當然，這只是理想情況，現實往往總是不能盡如人意，很多情況下SQL工作區是不能容納執行SQL所需要的記憶體空間的，從而不得不交換到臨時表空間裡。為了衡量執行SQL所需要的記憶體與實際分配給該SQL的SQL工作區之間的契合程度，oracle將所分配的SQL工作區大小分成三種型別：
optimal尺寸：SQL語句能夠完全在所分配的SQL工作區內完成所有的操作。這時的效能最佳。
onepass尺寸：SQL語句需要與磁碟上的臨時表空間互動一次才能夠在所分配的SQL工作區中完成所有的操作。 multipass尺寸：由於SQL工作區過小，從而導致SQL語句需要與磁碟上的臨時表空間互動多次才能完成所有的操作。這個時候的效能將急劇下降。
當系統整體負載不大時，oracle傾向於為每個session的PGA分配optimal尺寸大小的SQL工作區。
而隨著負載上升，比如連線的session逐漸增多導致同時執行的SQL語句越來越多時，oracle就會傾向於為每個session的PGA分配onepass尺寸大小的SQL工作區，甚至是multipass尺寸的SQL工作區了。
那麼，PGA自動管理機制在內部到底是如何實現的呢？很遺憾，oracle官方並沒有給出說明文件。其實這本身也說明了，PGA自動管理的內部演算法會隨著版本升級而發生變化。不過，知其然而不知其所以然，總是會讓諸如我等之類的技術人員感覺如梗在喉。還好，曾經就有一些專門做oracle優化的公司釋出的文件中介紹了PGA內部的實現原理，我想這可能是oracle公司透露給這些公司的。這裡就做些簡單的介紹，不過記住，這裡所描述的PGA自動管理的原理並不一定就是將來版本的原理，只能說是截至到9.2的PGA自動管理的原理。
PGA自動管理是採用名為“迴圈反饋（feedback loop）”的演算法來實現的。如下圖所示。
上圖中，當開始處理一條SQL時，oracle會使用本地記憶體管理器（local memory manager）對該SQL語句相關的work area profile進行註冊。work area profile是一組後設資料，描述了該SQL語句所需要的工作區的所有特徵，包括該SQL的型別（sort還是hash-join等）、該SQL語句的並行度、所需要的記憶體等資訊。它是SQL語句操作與記憶體管理器之間唯一的介面。當SQL語句執行完畢時，其對應的work area profile就會被刪除。而在SQL語句執行期間，為了反映SQL語句當前已經消耗的記憶體以及是否被交換到臨時表空間了等狀態資訊，oracle會不斷更新其對應的work area profile。所以說，SQL語句的work area profile是有生命週期的，始終能夠體現其對應SQL語句的工作區狀態。因此，我們可以說，在任何時間點，所有當前活動的work area profile就能夠基本體現當前所有session對PGA記憶體的需要以及當前正在使用的PGA記憶體。通過查詢檢視v$sql_workarea_active，可以顯示所有當前活動的work area profile的相關資訊。
現在，我們需要引入另外一個後臺守護程式（background daemon），叫做全域性記憶體管理器（global memory manager）。這個程式每隔3秒會啟動一次。每次啟動時，都會根據當前所有活動的work area profile的數量以及其他相關資訊計算出這個時候的SQL工作區的“記憶體限度（memory bound）”，也就是每個工作區最大盡量不能超過多大（不過，注意，嚴格說來應該是儘量不超過。實際上這個最大值是可以被超過的，後面會用個例項來說明）。然後立即釋出這個“記憶體限度”。
最後，本地記憶體管理器關閉“反饋迴圈”，並根據當前的“記憶體限度”以及當前work area profile，從而計算出當前SQL工作區應該具有的記憶體大小，併為程式分配該大小的記憶體以執行SQL語句，這個記憶體的大小尺寸就叫做“期望尺寸（expect size）”，可以從v$sql_workarea_active的expected_size列看到“期望尺寸”的大小。同時，這個“期望尺寸”會定時更新，並據此對SQL工作區進行調整。
Oracle內部對這個“期望尺寸”的大小有如下規則的限制：
“期望尺寸”不能小於最低的記憶體需求。
“期望尺寸”不能大於optimal尺寸。
如果“記憶體限度”介於最低的記憶體需求和optimal尺寸之間，則使用“記憶體限度”作為“期望尺寸”的大小，但是排序操作除外。因為排序操作演算法的限制，對於分配的記憶體在optimal尺寸和onepass尺寸之間時，排序操作不會隨著記憶體的增加而更快完成，除非能夠為排序操作分配optimal尺寸。所以，如果排序操作的“記憶體限度”介於onepass尺寸和optimal尺寸之間的話，“期望尺寸”取onepass尺寸。
如果SQL以並行方式執行，則“期望尺寸”為上面三個規則算出的值乘以並行度。
非並行模式下，按照通常的說法是“期望尺寸”不能超過min(5%*pga_aggregate_target,100MB)。但實際上，這是在不修改_pga_max_size和_smm_max_size這兩個隱藏引數的前提下，可以簡單的這麼認為。嚴格說來，應該是不能超過min(5%*pga_aggregate_target,50%*_pga_max_size,_smm_max_size)。對於並行的情況，就更加複雜，可以簡單認為不超過30%*pga_aggregate_target。
下面，我們舉例（如下圖所示）來說明全域性記憶體管理器是如何計算並應用“記憶體限度”的。比如，
[2]
當前系統中有6個活動的work area profile。WP1所需要的onepass記憶體為7MB，而optimal記憶體為27MB。WP3是一個並行度為2的hash-join，它需要11MB的onepass記憶體，以及67MB的optimal的記憶體。假設pga_aggregate_target設定為133MB，則可以簡單的認為全域性記憶體管理器直接將133除以6，也就是大約20MB作為“記憶體限度”的值。於是該“記憶體限度”限制了分配給WP1的工作區只能為7MB，也就是onepass的大小，因為WP1是一個排序操作，如果給它分配20MB也不能使它在以optimal的方式完成。而對於20MB的“記憶體限度”，WP3可以分到40MB的工作區，因為WP3的並行度為2，所以可以分配20MB×2的大小的工作區。
2.3如何設定新資料庫的PGA值
我們一旦設定了pga_aggregate_target以後，所有的*_area_size就將被忽略。那麼，我們該如何來設定該引數的值呢？這依賴於資料庫的用途，如果資料庫為OLTP（聯機事務處理）應用的，則其應用一般都是小的短的程式，所需要的PGA也相應較少，所以該值該值通常為總共分配給oracle例項的20%，另外的80%則給了SGA；如果資料庫為OLAP（DSS）（資料倉儲或決策分析）應用的，則其應用一般都是很大的，執行時間很長的程式，因此需要的PGA就多。所以通常為PGA分配50%的記憶體。而如果資料庫為混合型別的，則情況比較複雜，一般會先分配40%的初始值，而後隨著資料庫的應用，而不斷對PGA進行監控，並進行相應的調整。
比如，對於8GB實體記憶體的資料庫伺服器來說，按照oracle推薦的，分配給oracle例項的記憶體為實體記憶體的80%。那麼對於OLTP應用來說，pga_aggregate_target的值大約就是1310MB ((8192 MB× 80%)×20%)。而對於OLAP來說，則該值大約就是3276MB (8192MB×80%)×50%)。
當然，這裡所說的都是對於一個新的資料庫來說，初始設定的值。這些值並不一定正確，可能設定過大，也可能設定過小。必須隨著系統的不斷執行，DBA需要不斷監控，從而對其進行調整。
3. PGA監控及調優
我們已經大致瞭解了有關PGA的相關理論知識，現在我們可以開始動手實踐來驗證上面的理論，並
可以開始對PGA的使用進行監控以及調優了。以下測試都是在windows XP、oracle 9.2.0.5，以及專用連
接模式下進行的。
3.1準備測試用例
首先，我們先建立一個測試用例。
SQL> create table pga_test as select * from dba_objects;
SQL> select count(*) from pga_test;
COUNT(*)
----------
6243
然後，引入幾個監控PGA的指令碼。
pga_by_hashvalue.sql，這是一個監控SQL語句所使用的SQL工作區的指令碼：
SELECT
b.sql_text,
a.operation_type,
a.policy,
a.last_memory_used/(1024*1024) as "Used MB" ,
a.estimated_optimal_size/(1024*1024) as "Est Opt MB",
a.estimated_onepass_size/(1024*1024) as "Est OnePass MB",
a.last_execution,
a.last_tempseg_size
FROM v$sql_workarea a,v$sql b
WHERE a.hash_value = b.hash_value
and a.hash_value = %26;amp;hashvalue
/
pga_by_session.sql，第二個指令碼是pga_by_session.sql，用來監控session所使用的PGA和UGA的大小：
select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = %26;amp;sid
and a.name like '%ga %'
order by a.name
/
第三個指令碼監控程式所使用的PGA的大小，pga_by_process.sql ：
SELECT
a.pga_used_mem "PGA Used",
a.pga_alloc_mem "PGA Alloc",
a.pga_max_mem "PGA Max"
FROM v$process a,v$session b
where a.addr = b.paddr
and b.sid= %26;amp;sid
/
3.2單個session對PGA使用情況的監控
我們分別建立5個session，第一個session（sess#1）執行測試語句；第二個session（sess#2）執行pga_by_hashvalue.sql指令碼；第三個session（sess#3）執行pga_by_session.sql指令碼；第四個session（sess#4）執行pga_by_process.sql指令碼；第五個session（sess#5）設定相關引數。以下按照順序描述整個測試的過程。
Sess#1：
SQL> select sid from v$mystat where rownum=1;
SID
----------
7
Sess#3查詢當前sid為7的session的PGA和UGA各為多少，可以看到，即使不執行任何的SQL，只要session連線了，就會消耗大約0.23MB的PGA記憶體：
SQL> @pga_by_session.sql;
NAME VALUE
------------------------------ ----------
session pga memory 238188
session pga memory max 238188
session uga memory 77008
session uga memory max 77008
Sess#5，我們將pga_aggregate_target設定為60MB：
SQL> alter system set pga_aggregate_target=60M;
Sess#1，執行測試語句：
SQL> set autotrace traceonly stat;
SQL> select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8;
Sess#5，找到sess#1中所執行的SQL語句的hash值：
SQL> select hash_value from v$sql where sql_text='select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8';
HASH_VALUE
----------
2656983355
Sess#2：
SQL> @d:pga_by_hashvalue.sql
輸入 hashvalue 的值: 2656983355
原值 12: and a.hash_value = %26;amp;hashvalue
新值 12: and a.hash_value = 2656983355
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8
SORT AUTO 3
66.1376953 2.75390625 2 PASSES 65011712
我們可以看到，該SQL語句所分配的工作區為3MB，這個值就是5%*pga_aggregate_target（60M*0.05）。符合前面說到的“期望尺寸”為min(5%*pga_aggregate_target,100MB)。
Sess#3：
SQL> @ pga_by_session.sql;
NAME VALUE
------------------------------ ----------
session pga memory 369796
session pga memory max 4956780
session uga memory 77008
session uga memory max 3677528
[3]
可以看到，為了執行測試語句，為該session分配的PGA為4956780個位元組，其中UGA為3677528個位元組，大約3.5M。同時可以看出，執行完測試語句以後，oracle就把該session的PGA空間回收了（PGA從4956780下降到369796，而UGA從3677528下降到77008），順帶提一下，在8i中分配了PGA以後是不會回收的，也就是說session pga memory始終等於session pga memory max，而9i以後的PGA的分配方式發生了改變，從而能夠在分配PGA以後還可以再回收一部分記憶體。結合上面為SQL語句所分配的3M的工作區，可以知道，UGA中的其他空間佔用大約0.5M。而SQL工作區佔整個PGA大小大約為64%，從這個方面也可以看出，SQL工作區是PGA中最佔空間、也是最重要的部分。
Sess#4：
SQL> @d:pga_by_process.sql
輸入 sid 的值: 7
原值 7: and b.sid= %26;amp;sid
新值 7: and b.sid= 7
PGA Used PGA Alloc PGA Max
---------- ---------- ----------
253932 382664 4969648
可以看到，這幾個檢視查出來的PGA的大小基本都是一致的。
我們繼續測試，從sess#2可以看出，如果要讓該SQL語句完全在記憶體中完成，需要大約67MB的PGA空間。根據5%的原理倒算，可以知道這個時候的pga_aggregate_target應該大於1340MB（67/0.05）。於是，我們設定1500MB，來看看是不是確實進行optimal了。順便提醒一下，並不是說你的電腦得有超過1500MB的實體記憶體你才可以設定1500M的pga_aggregate_target，事實上pga_aggregate_target是按需分配的，不象SGA，一旦設定就佔著記憶體，不用也得佔著。也就是說是PGA是隨著對記憶體需求的增長而不斷增長的。我測試的機器上只有1GB的實體記憶體，但做測試時完全可以將pga_aggregate_target設定5GB，甚至更高的10GB。
Sess#5，我們將pga_aggregate_target設定為1500MB：
SQL> alter system set pga_aggregate_target=1500M;
Sess#1：
SQL> select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8;
Sess#2：
SQL> @d:pga_by_hashvalue.sql
輸入 hashvalue 的值: 2656983355
原值 12: and a.hash_value = %26;amp;hashvalue
新值 12: and a.hash_value = 2656983355
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8
SORT AUTO 65.765625
73.9873047 2.90039063 OPTIMAL
我們可以看到，該SQL語句確實完全在記憶體裡完成了（LAST_EXECUTION為“OPTIMAL”）。同時，實際的“期望尺寸”始終會小於optimal（65.765625<73.9873047），也符合前面說的第二條規則。
我們繼續測試，看看SQL工作區的“期望尺寸”是否真的不能超過100MB。為此，需要設定5%*
pga_aggregate_target>100MB，因此pga_aggregate_target最少要大於2G，我們設定5GB。
Sess#5，我們將pga_aggregate_target設定為5GB：
SQL> alter system set pga_aggregate_target=5G;
Sess#1，注意，為了能夠佔用更多的PGA，這時的SQL語句已經把where條件修改了：
SQL> select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8;
Sess#5，找到該語句的hash值：
SQL> select hash_value from v$sql where sql_text='select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8';
HASH_VALUE
----------
3008669403
Sess#2：
SQL> /
輸入 hashvalue 的值: 3008669403
原值 12: and a.hash_value = %26;amp;hashvalue
新值 12: and a.hash_value = 3008669403
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8
SORT AUTO 87.265625
137.232422 3.87109375 1 PASS 127926272
可以看到，optimal尺寸已經超過100MB很多了，但是實際分配的“期望尺寸”卻只有88MB左右。而5G*0.05為250MB，為何該SQL用不了呢？這其實是由兩個隱藏引數決定的，分別是_pga_max_size和_smm_max_size。我們來看一下這兩個引數的含義和預設值：
Sess#5：
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');
KSPPINM KSPPSTVL KSPPDESC
-------------- ---------- -----------------------------------------------
_pga_max_size 209715200 Maximum size of the PGA memory for one process
_smm_max_size 102400 maximum work area size in auto mode (serial)
我們可以看到_pga_max_size預設值為200M（209715200/1024/1024），而_smm_max_size預設值為100MB（上面的查詢結果中顯示的單位是KB）。而每個session的PGA最多隻能使用_pga_max_size的一半，也就是100MB。
當你修改引數pga_aggregate_target的值時，Oracle系統會根據pga_aggregate_target和_pga_max_size這兩個值來自動修改引數_smm_max_size。具體修改的規則是：
如果_pga_max_size大於5%*pga_aggregate_target，則_smm_max_size為5%*pga_aggregate_target。
如果_pga_max_size小於等於5%*pga_aggregate_target，則_smm_max_size為50%*_pga_max_size。
有些資料上說，可以通過修改_pga_max_size來突破這個100MB的限制。真的是這樣嗎？我們來測試。Sess#5，修改引數_pga_max_size為600MB：
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 5368709120
SQL> alter system set "_pga_max_size"=600M;
我們將_pga_max_size的值設定為600M，其一半就是300MB，已經超過5%*pga_aggregate_target（即250MB）了。所以這兩者的較小值為250M，如果這時我們在sess#1中再次執行測試語句，應該可以使用超過100MB的SQL工作區了。我們來看測試結果。
Sess#1：
SQL> select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8;
Sess#2：
SQL> /
輸入 hashvalue 的值: 3008669403
原值 12: and a.hash_value = %26;amp;hashvalue
新值 12: and a.hash_value = 3008669403
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8
SORT AUTO 87.265625
137.232422 3.87109375 1 PASS 127926272
[4]
我們看到，“期望尺寸”仍然是大約88MB，並沒有突破100MB的限制。其中的問題就在於引數
_smm_max_size 上。我們來看這個時候該引數值是多少：
Sess#5：
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');
KSPPINM KSPPSTVL KSPPDESC
-------------- ---------- -----------------------------------------------
_pga_max_size 629145600 Maximum size of the PGA memory for one process
_smm_max_size 102400 maximum work area size in auto mode (serial)
可以看到引數_smm_max_size的值仍然是100MB。實際上，這也是一個對 “期望尺寸”的限制引數。這裡可以看到“期望尺寸”不能超過100MB。這時，我們只要簡單的執行：
Sess#5：
SQL> alter system set pga_aggregate_target=5G;
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');
KSPPINM KSPPSTVL KSPPDESC
-------------- ---------- -----------------------------------------------
_pga_max_size 629145600 Maximum size of the PGA memory for one process
_smm_max_size 262144 maximum work area size in auto mode (serial)
我們可以看到，只要設定一下pga_aggregate_target，就會按照前面所說的規則重新計算並設定_smm_max_size的值，該引數修改後的值為250MB。這個時候我們重複上面的測試：
Sess#1：
SQL> select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8;
Sess#2：
SQL> /
輸入 hashvalue 的值: 3008669403
原值 12: and a.hash_value = %26;amp;hashvalue
新值 12: and a.hash_value = 3008669403
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8
SORT AUTO 137.195313
154.345703 4.09179688 OPTIMAL
這時，我們看到，“期望尺寸”為138MB左右，終於超過了100MB。如果我們再次將引數_smm_max_size人為的降低到100MB，則“期望尺寸”又將不能突破100MB了。我們來看試驗。
Sess#5：
SQL> alter system set "_smm_max_size"=102400;
Sess#1：
SQL> select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8;
Sess#2：
SQL> /
輸入 hashvalue 的值: 3008669403
原值 12: and a.hash_value = %26;amp;hashvalue
新值 12: and a.hash_value = 3008669403
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8
SORT AUTO 87.265625
137.232422 3.87109375 1 PASS 127926272
可以看到，結果正如我們所預料的。由此，得出我們重要的結論，就是在非並行方式下，“期望尺寸”為min(5%*pga_aggregate_target,50%*_pga_max_size,_smm_max_size)，而不是很多資料上所說的不是很嚴密的min(5%*pga_aggregate_target,50%*_pga_max_size)。oracle當然是不推薦我們修改這兩個隱藏引數的。
3.3多個併發session對PGA使用情況的監控
現在我們可以來測試多個session併發時PGA的分配情況。測試併發的方式有很多，可以寫一個小程式迴圈建立多個連線，然後執行上面的測試語句，也可以藉助一些工具來完成。為了方便起見，我用了一個最簡單的方式。就是寫一個SQL文字，再寫一個bat檔案，該bat檔案中執行SQL文字。兩個檔案準備好以後，將bat檔案拷貝30份，然後選中這30份一摸一樣的bat檔案，按Enter鍵後，windows XP將同時執行這30個bat檔案，這樣就可以模擬出30個session同時連線並同時執行測試語句的環境了。具體這兩個檔案的具體內容如下：
pga_test.sql：
set autotrace traceonly stat;
select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8;
run.bat：
@sqlplus -s
[url=mailto:cost/cost@ora92]cost/cost@ora92[/url]
@d:testpga_test.sql
我們先將pga_aggregate_target設定為60MB。
Sess#5：
SQL> alter system set pga_aggregate_target=60M;
然後同時執行30個bat檔案從而啟動30個執行相同SQL測試語句的併發session，我執行下面的語句以顯示這時正在執行的30個session所消耗的PGA的總記憶體：
Sess#5：
SQL> select a.name, sum(b.value)/1024/1024 as "MB"
2 from v$statname a, v$sesstat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %'
5 and sid in(select sid from v$sql_workarea_active)
6 group by a.name;
NAME MB
---------------------------------------------------------------- ----------
session pga memory 45.9951134
session pga memory max 95.6863365
session uga memory 19.757431
session uga memory max 72.6992035
我們可以看到，session pga memory max顯示出大約96MB的PGA記憶體，很明顯，PGA的總容量已經超出了pga_aggregate_target（60M）的限制的容量。實際上這也就說明，該引數只是說明，oracle會盡量維護整個PGA記憶體不超過這個值，如果實在沒有辦法，也還是會突破該引數限制的。
同時，我們可以去檢視這個時候該測試SQL語句所分配的工作區變成了多少，同樣在Sess#2中：
SQL> @d:pga_by_hashvalue.sql
輸入 hashvalue 的值: 2656983355
原值 12: and a.hash_value = %26;amp;hashvalue
新值 12: and a.hash_value = 2656983355
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8
SORT AUTO 1.8984375
66.1376953 2.75390625 2 PASSES 65011712
從結果中我們可以看到，該SQL的工作區已經從單個session時的3MB下降到了大約1.9M，我們可以看到，30個session總共至少需要57MB（1.9M*30）的SQL工作區。明顯的，60MB的pga_aggregate_target是肯定不能滿足需要的。
[5]
3.4其他監控並調整PGA的方法
我們監控PGA的檢視除了上面介紹到的v$sql_workarea_active、v$sesstat、v$sql_workarea以及v$process以外，還有v$sql_workarea_histogram、v$pgastat以及v$sysstat。
v$sql_workarea_histogram記錄了每個範圍的SQL工作區內所執行的optimal、onepass、multipass的次數。如下所示：
SQL> select
2 low_optimal_size/1024 "Low (K)",
3 (high_optimal_size + 1)/1024 "High (K)",
4 optimal_executions "Optimal",
5 onepass_executions "1-Pass",
6 multipasses_executions ">1 Pass"
7 from v$sql_workarea_histogram
8 where total_executions <> 0;
結果類似如下所示，我們可以看到整個系統所需要的PGA的記憶體大小主要集中在什麼範圍裡面。
Low (K) High (K) Optimal 1-Pass >1 Pass
---------- ---------- ---------- ---------- ----------
8 16 360 0 0
。。。。。。。。。
65536 131072 0 2 0
另外，我們可以將上面的查詢語句改寫一下，以獲得optimal、onepass、multipass執行次數的百分比，很明顯，optimal所佔的百分比越高越好，如果onepass和multipass佔的百分比很高，就不需要增加pga_aggregate_target的值了，或者調整SQL語句以使用更少的PGA區。
SQL> select
2 optimal_count "Optimal",
3 round(optimal_count * 100 / total,2) "Optimal %",
4 onepass_count "OnePass",
5 round(onepass_count * 100 / total,2) "Onepass %",
6 multipass_count "MultiPass",
7 round(multipass_count * 100 / total,2) "Multipass %"
8 from (
9 select
10 sum(total_executions) total,
11 sum(optimal_executions) optimal_count,
12 sum (onepass_executions) onepass_count,
13 sum (multipasses_executions) multipass_count
14 from v$sql_workarea_histogram
15 where total_executions <> 0)
16 /
Optimal Optimal % OnePass Onepass % MultiPass Multipass %
---------- ---------- ---------- ---------- ---------- -----------
402 99.01 4 0.99 0 0
而v$pgastat則提供了有關PGA使用的整體的概括性的資訊。
SQL> select * from v$pgastat;
NAME VALUE UNIT
---------------------------------------- ---------- ------------
aggregate PGA target parameter 62914560 bytes
aggregate PGA auto target 51360768 bytes
global memory bound 104857600 bytes
total PGA inuse 5846016 bytes
total PGA allocated 8386560 bytes
maximum PGA allocated 66910208 bytes
total freeable PGA memory 0 bytes
PGA memory freed back to OS 0 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 51167232 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
over allocation count 0
bytes processed 142055424 bytes
extra bytes read/written 138369024 bytes
cache hit percentage 50.65 percent
從結果可以看出，第一行表示pga_aggregate_target設定為60M。PGA的一部分被用於無法動態調整的部分，比如UGA中的“session相關的資訊”等。而PGA記憶體的剩下部分則是可以動態調整的，由“aggregate PGA auto target”說明。我們來看第二行的值，就表示可以動態調整的記憶體數量，該值不能與pga_aggregate_target設定的值差太多。如果該值太小，則oracle沒有足夠的記憶體空間來動態調整session的記憶體工作區。其中的global memory bound表示一個工作區的最大尺寸，並且oracle推薦只要該統計值低於1M時，就應該增加pga_aggregate_target的值。另外，9i還提供了兩個有用的指標：over allocation count和cache hit percentage。如果在使用SQL工作區過程中，oracle認為pga_aggregate_target過小，則它自己會去多分配需要的記憶體。則多分配的次數就累加在over allocation count指標裡。該值越小越好，最好為0。cache hit percentage則表示完全在記憶體裡完成的操作的位元組數與所有完成的操作(包括optimal、onepass、multipass)的位元組數的比率。如果所有的操作都是optimal類，則該值為100%。
最後，我們可以查詢v$sysstat檢視，獲得optimal、onepass、multipass執行的總次數：
SQL> select * from v$sysstat where name like 'workarea executions%';
STATISTIC# NAME CLASS VALUE
---------- ---------------------------------------- ---------- ----------
230 workarea executions - optimal 64 360
231 workarea executions - onepass 64 2
232 workarea executions - multipass 64 0
我們可以計算optimal次數佔總次數的比率，比如上例中，360/(360+2+0)=99.45%，該比率越大越好，如果發現onepass和multipass較多，則需要增加pga_aggregate_target，或者調整SQL語句以使用更少的PGA區。
那麼我們如何找到需要調整以使用更少的PGA的SQL語句呢？我們可以將v$sql_workarea中的記錄按照estimated_optimal_size欄位由大到小的排序，選出排在前幾位的hash值，同時還可以選出last_execution值為“n PASSES”（這裡的n大於或等於2）的hash值，將這些hash值與v$sql關聯後找出相應的SQL語句，進行調整，以便使其使用更少的PGA。
[6]
3.5 PGA的自動建議特性
那麼，如果我們需要調整pga_aggregate_target時，到底我們應該設定多大呢？oracle為了幫助我們確定這個引數的值，引入了一個新的檢視：v$pga_target_advice。為了使用該檢視，需要將初始化引數statistics_level設定為typical（預設值）或all。
SQL> select
2 round(pga_target_for_estimate /(1024*1024)) "Target (M)",
3 estd_pga_cache_hit_percentage "Est. Cache Hit %",
4 round(estd_extra_bytes_rw/(1024*1024)) "Est. ReadWrite (M)",
5 estd_overalloc_count "Est. Over-Alloc"
6 from v$pga_target_advice
7 /
Target (M) Est. Cache Hit % Est. ReadWrite (M) Est. Over-Alloc
---------- ---------------- ------------------ ---------------
15 34 264 1
30 34 264 0
45 34 264 0
60 67 66 0
72 67 66 0
84 67 66 0
96 67 66 0
108 67 66 0
120 67 66 0
180 67 66 0
240 67 66 0
360 67 66 0
480 67 66 0
該輸出告訴我們，按照系統目前的運轉情況，我們pga設定的不同值所帶來的不同效果。根據該輸出，我們找到能使estd_overalloc_count為0的最小pga_aggregate_target的值。從這裡可以看出，是30M。注意，隨著我們增加pga的尺寸，estd_pga_cache_hit_percentage不斷增加，同時estd_extra_bytes_rw(表示onepass、multipass讀寫的位元組數)不斷減小。從上面的結果，我們可以知道，將pga_aggregate_target設定為60MB是最合理的，因為即便將其設定為480MB，命中率也不會有所提高。
同時，我們知道v$tempstat裡記錄了讀寫臨時表空間的資料塊數量以及所花費的時間。這樣，我們就可以結合v$pga_target_advice和v$tempstat這兩個檢視。可以得到每一種估計PGA值下的響應時間大致是多少，從而可以換一個角度來顯示PGA的建議值：
SQL> SELECT 'PGA Aggregate Target' component,
2 ROUND (pga_target_for_estimate / 1048576) target_size,
3 estd_pga_cache_hit_percentage cache_hit_ratio,
4 ROUND ( ( ( estd_extra_bytes_rw / DECODE ((b.BLOCKSIZE * i.avg_blocks_per_io),0, 1,
5 (b.BLOCKSIZE * i.avg_blocks_per_io)))* i.iotime)/100 ) "response_time(sec)"
6 FROM v$pga_target_advice,
7 (SELECT /*+AVG TIME TO DO AN IO TO TEMP TABLESPACE*/
8 AVG ( (readtim + writetim) /
9 DECODE ((phyrds + phywrts), 0, 1, (phyrds + phywrts)) ) iotime,
10 AVG ( (phyblkrd + phyblkwrt)/
11 DECODE ((phyrds + phywrts), 0, 1, (phyrds + phywrts))) avg_blocks_per_io
12 FROM v$tempstat) i,
13 (SELECT /* temp ts block size */ VALUE BLOCKSIZE
14 FROM v$parameter WHERE NAME = 'db_block_size') b;
COMPONENT TARGET_SIZE CACHE_HIT_RATIO response_time(sec)
-------------------- ----------- --------------- ------------------
PGA Aggregate Target 15 34 85
PGA Aggregate Target 30 34 85
PGA Aggregate Target 45 34 85
PGA Aggregate Target 60 68 21
PGA Aggregate Target 72 68 21
PGA Aggregate Target 84 68 21
PGA Aggregate Target 96 68 21
PGA Aggregate Target 108 68 21
PGA Aggregate Target 120 68 21
PGA Aggregate Target 180 68 21
PGA Aggregate Target 240 68 21
PGA Aggregate Target 360 68 21
PGA Aggregate Target 480 68 21
注意，每次我們調整了pga_aggregate_target引數以後，都應該在系統執行一、兩天以後檢查檢視：v$sysstat、v$pgastat、v$pga_target_advice，以確定修改的值是否滿足系統的需要。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/38542/viewspace-902835/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PGA自動管理原理深入分析及效能調整（六）
- PGA自動管理原理深入分析及效能調整（一）
- PGA自動管理原理深入分析及效能調整（五）
- log buffer及日誌管理深入分析及效能調整（一）
- log buffer及日誌管理深入分析及效能調整（七）
- log buffer及日誌管理深入分析及效能調整（六）
- log buffer及日誌管理深入分析及效能調整（五）
- log buffer及日誌管理深入分析及效能調整（三）
- Shared pool深入分析及效能調整
- Shared pool深入分析及效能調整(一)
- Shared pool深入分析及效能調整(二)
- Oracle效能調整－1（轉）Oracle
- Oracle效能調整－2（轉）Oracle
- Oracle效能調整－3（轉）Oracle
- 【效能調整】等待事件(九) latch原理事件
- Oracle9i自動PGA管理(zt)Oracle
- 變更oracle 11.2.0.3 rac sga手工管理為sga及pga全自動管理Oracle
- 自動記憶體調整中真正決定自動調整的引數記憶體
- 用於效能調整的動態效能檢視——效能調整手冊和參考
- buffer cache深度分析及效能調整（五）
- buffer cache深度分析及效能調整（六）
- buffer cache深度分析及效能調整（四）
- Oracle效能調整之--DML語句效能調整Oracle
- Oracle9i中的PGA自動管理Oracle
- oracle 效能調整Oracle
- oracle資料庫的效能調整(轉)Oracle資料庫
- oracle例項記憶體（SGA和PGA）調整Oracle記憶體
- autorandr：自動調整螢幕佈局
- <轉>oracle效能調整讀書筆記(1)Oracle筆記
- <轉>oracle效能調整讀書筆記(2)Oracle筆記
- <轉>oracle效能調整讀書筆記(3)Oracle筆記
- <轉>oracle效能調整讀書筆記(4)Oracle筆記
- <轉>oracle效能調整讀書筆記(5)Oracle筆記
- Oracle9i 自動管理PGA記憶體(zt)Oracle記憶體
- solaris記憶體引數調整及管理記憶體
- oracle效能調整(1)Oracle
- oracle效能調整(2)Oracle
- ORACLE效能調整--1Oracle