PGA自動管理原理深入分析及效能調整（五）

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 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。

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

PGA自動管理原理深入分析及效能調整（五）

3.3多個併發session對PGA使用情況的監控

3.4其他監控並調整PGA的方法

相關文章