最近在網上看到一遍有關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）”的算法來實作的。如下圖所示。
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';}" οnclick="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" οnlοad="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';}" />
上圖中，當開始處理一條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。
下面，我們舉例（如下圖所示）來說明全局記憶體管理器是如何計算并應用“記憶體限度”的。比如，
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';}" οnclick="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" οnlοad="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';}" />
[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/[email protected]]cost/[email protected][/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
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 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@]
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/38542/viewspace-902835/，如需轉載，請注明出處，否則将追究法律責任。
轉載于:http://blog.itpub.net/38542/viewspace-902835/