上圖中，當開始處理一條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。 下面，我們舉例（如下圖所示）來說明全局內存治理器是如何計算并應用“內存限度”的。比如，
點擊查看大圖
當前系統中有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的大小的工作區。
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 = &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 = &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= &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 = &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
可以看到，為了執行測試語句，為該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= &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 = &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 = &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 = &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 我們看到，“期望尺寸”仍然是大約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 = &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 = &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文件，按回車鍵后，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 cost/cost@ora92 @d:/test/pga_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 = &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是肯定不能滿足需要的。
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。 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，命中率也不會有所提高。
