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

這篇同樣是在it168上發表過的，現在把它們集中在blog裡。

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區域。這部分割槽域又可分成兩部分：

v      永久記憶體區域：這裡存放了相同SQL語句多次執行時都需要的一些遊標資訊，比如繫結變數資訊、資料型別轉換資訊等。這部分記憶體只有在遊標被關閉時才會被釋放。

v      執行時區域：這裡存放了當SQL語句執行時所使用的一些資訊。這部分割槽域的大小尺寸依賴於所要執行的SQL語句的型別（sort或hash-join等）和複雜度以及所要處理的資料行的行數以及行的大小。在處理SQL語句時的第一步就是要建立執行時區域，對於DML（INSERT、UPDATE、DELETE）語句來說，SQL語句執行完畢就釋放該區域；而對於查詢語句（SELECT）來說，則是在所有資料行都被獲取並傳遞給使用者以後被釋放，或者該查詢被取消以後也會被釋放。

·         Session相關的資訊。這部分資訊包括：

v      正在使用的包（package）的狀態資訊。

v      使用alter session這樣的命令所啟用的跟蹤資訊、或者所修改的session級別的優化器引數（optimizer_mode）、排序引數（sort_area_size等）、修改的NLS引數等。

v      所開啟的dblinks。

v      可使用的角色（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 pc=2

 parent=00000000 wner=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';

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