oracle10g PGA

Oracle記憶體管理之PGA

    Oracle資料庫例項啟動時，就需要分配共享記憶體，啟動後臺程式，如何分配和設定共享記憶體引數，對於Oracle來說是非常重要的。不當的記憶體分配輕則影響效能，重則導致資料庫故障。

    Oracle資料庫所使用的記憶體主要涉及兩個方面：PGA和SGA。本章就Oracle的記憶體管理問題進行探討。

PGA管理

    PGA指的是程式全域性區（Program Global Area），是伺服器程式（Server Process）使用的一塊包含資料和控制資訊的記憶體區域，PGA是非共享的記憶體，在伺服器程式啟動或建立時分配（在系統執行時，排序、連線等操作也可能需要進一步的PGA分配），併為Server Process排他訪問，所以PGA中的資料結構並不需要通過Latch來保護。

什麼是PGA

    程式的建立通常有兩種方式：

（1）專用伺服器模式（Dedicated Server）

在專用伺服器模式下，Oracle會為每個會話啟動一個Oracle程式，

（2）共享伺服器模式（Share Server）

而在共享伺服器模式下，通常在伺服器端啟動一定數量的伺服器程式，然後由多個使用者請求共享同一個Oracle服務程式

    通常資料庫都應當執行在專用伺服器模式下。PGA的內容也依專用模式和共享模式而有所不同。

    從記憶體分配和使用上PGA可以被分為兩個區域：

（1）   固定PGA（Fixed PGA）

固定PGA和固定SGA類似，包含了大量原子變數、小的資料結構和指向可變PGA的指標，這些變數在原始碼中定義，在編譯時分配，可以被認為是PGA的保留記憶體

（2）   可變PGA（Variable PGA）

可變PGA通過具體的記憶體Heap分配來實現，其空間分配與使用時可以變化的，通過內部檢視X$KSMPP可以查詢可變PGA的分配和使用情況。

    可變PGA部分實際上是我們最為關注的PGA部分。雖然PGA的內容對於專用和共享模式會有所不同，但是通常來說，可變PGA由以下兩部分組成。

（1）   會話記憶體（Session Area）

用於儲存會話專有的資訊，如登入資訊、跟蹤資訊、Alter session命令修改的環境引數資訊、會話擁有的角色資訊等。

對於共享伺服器模式，這部分記憶體是共享而非私有的

（2）   私有SQL區（Private SQL Area）

包含繫結變數資訊、查詢執行狀態資訊等。每個發出的SQL查詢會話都擁有一塊私有SQL區，對於專用伺服器模式，這部分記憶體在PGA中分配，對於共享伺服器模式，這部分記憶體在SGA中分配

    私有SQL區以由以下兩部分組成：

（1）   永久區域（Persistent Area）

這個區域包含繫結變數、資料型別轉換等CURSOR的結構資訊，在CURSOR被關閉時釋放

（2）   執行時區域（Runtime Area）

Run-Time Area區域在SQL執行過程中使用，其大小依賴於SQL語句的操作方式，（Sort或者Hash-Join）、要處理的資料行數和每行記錄集的大小，也就是要處理的資料越多執行處理的步驟越多，這塊區域空間需求越大。如果是DML語句，SQL語句執行完畢這塊區域就會釋放；而如果是查詢語句，則只有在整個結果集都傳遞給使用者後，或者使用者取消查詢後這部分空間才會被釋放。

一旦SQL語句處理完畢後，Run-Time Area就被釋放，而Private SQL Area部分就可以被其他SQL語句重用，每次對Private SQL Area重用都必需重新初始化。

  UGA（User Global Area）使用者全域性區

在共享伺服器模式下，一個共享服務程式被多個使用者程式共享，此時UGA是Share Pool或Large Pool的一部分，而在專用伺服器模式下，UGA則是PGA的一部分。

不考慮Share Server模式，在Dedicated模式下，PGA與UGA關係，就如同Process和Session的關係，PGA是服務於程式的記憶體結構，包含程式資訊；而UGA是服務於會話的，它包含的是會話的資訊。

在UGA中包含如下資訊：

（1）   開啟遊標的永久區域和執行區域

（2）   包的狀態資訊以及變數資訊

（3）   Java會話的狀態資訊

（4）   啟用角色資訊、跟蹤事件

（5）   起作用的NLS引數

（6）   所有開啟的database links

（7）   會話訪問控制資訊等

和PGA一樣，UGA也由兩組區域組成：

（1）   固定UGA區域

固定UGA包含了大約70個原子變數，小的資料結構以及指向UGA堆的指標

（2）   可變的UGA區域

UGA中的記憶體分配可以通過內部表X$KSMUP查詢得到。UGA堆包含了一些固定表（X$表）的永久記憶體（依賴於特定引數的設定），如OPEN_CURSORS，OPEN_LINSK和MAX_ENABLED_ROLES。除此以外，大部分的UGA用於私有SQL區和PL/SQL區。

從Oracle9iR2開始，有一系列新的隱含引數被引入用於控制PGA的自動管理，這其中有一個關鍵的引數是_use_realfree_heap，當設定這個引數為true時，Oracle會為CGA、UGA單獨分配堆，而不從PGA中分配。這的預設值為false，而當設定了pga_aggregate_target後，這的值自動被改為true。

_use_realfree_heap是自動管理PGA技術的關鍵技術變化，realfree代表實時釋放。Oracle9i之前手工管理的PGA的主要問題在於，UGA預設在PGA中分配，當會話執行了諸如排序、HASH-JOIN等操作，耗用了大量的PGA記憶體，而當會話執行完畢後，記憶體會釋放給PGA而不是OS，在很多時候這會導致過度的PGA記憶體使用；從Oracle9iR2開始，自動的PGA記憶體管理當_use_realfree_heap為true時，當呼叫結束時將不必將記憶體返回給PGA而直接返回給OS，從而實現了更好的PGA記憶體分配與使用。

通過v$pgastat檢視，可以查詢PGA累計釋放回OS的記憶體空間：

SQL> select name,value from v$pgastat where name like '%OS%';

NAME                         VALUE

------------------------------ ----------

PGA memory freed back to OS     149880832

CGA（Call Globall Araa）呼叫全域性區

與其它的全域性區不同，CGA的存在是瞬間的，只存在於呼叫過程中，而且無論UGA存在於PGA還是SGA，CGA都是PGA的SubHeap。

對於例項的一些低層次的呼叫（Low-Level Call）需要CGA，包括分析SQL語句、執行SQL語句以及獲取查詢結果都需要使用CGA。

在SQL執行過程中的每個遞迴呼叫都需要一個獨立的CGA；在SQL語句的解析過程中，查詢資料字典資訊、對SQL進行語法及語義的解析、SQL的優化以及不同執行計劃的評估都需要使用CGA。

當然，呼叫並不是只通過CGA中的資料結構來工作，實際上呼叫所需要的大部分的重要資料結構都來自UGA（如SQL AREA、PL/SQL AREA、Sort Area都存放在UGA中，因為這些結構在呼叫期間需要一直使用），CGA中只包含了呼叫以後可以被釋放的資料。如CGA中包含了Direct I/O BUFFER、遞迴呼叫資訊、表示式評估的堆疊資訊等，此外，Java呼叫記憶體也在CGA中分配。

    PGA管理技術的變遷

在Oracle9i以前的版本中，PGA由一系列的記憶體區域組成，這些區域包括主要由*_area_size引數控制。對於PGA記憶體的使用也是通過控制這些引數來實現。

但是這種獨立管理的方式存在一個極大的弊端，以SORT操作為例，如果我們為了使特定的排序操作能夠在記憶體中完成，可能需要設定較大的sort_area_size，但是由於程式的獨立PGA記憶體難於回收和共享，這樣可能導致過度的PGA記憶體消耗，所以合理設定和調整PGA在Oracle9i之前是一件比較複雜的事情；從Oracle9i開始，Oracle提供了一種新的PGA記憶體管理方法:自動化SQL執行記憶體管理（Automated SQL Execution Memory Management），也稱為自動PGA管理，使用這個特性，Oracle可以在一個總體PGA使用限制下自動管理和調整SQL記憶體區，從而大大簡化了DBA的工作，同時也提高了資料庫的效能。

實現自動的PGA管理，Oracle引入了幾個新的初始化引數：

（1）   PGA_AGGREGATE_TARGET

這個引數用來指定所有session總計可以使用最大PGA記憶體，這個引數可以動態更改，取值範圍從10M-4096G

（2）   WORKAREA_SIZE_POLICY

這個引數用於開關PGA記憶體自動管理功能。該引數有AUTO和MANUAL兩個選項，當設定為AUTO時，資料庫使用自動記憶體管理功能，當設定為MANUAL時，則仍然使用之前的手工管理方式。預設這個引數的值為AUTO

        此外，需要注意的是，在不同的版本中，自動PGA管理的範疇不同：

（1）   在Oracle9i中，PGA_AGGREGATE_TARGET引數僅對專用伺服器模式下有效，對共享伺服器模式連線無效。

（2）   在Oracle10g中，在兩種連線模式中都有效

引數的設定與記憶體的分配

    PGA_AGGREGATE_TARGET引數同時限制全域性PGA分配和私有工作區分配

    在Oracle9i以及Oracle10gR1中，單個SQL操作記憶體使用存在如下限制：

（1）   對於序列操作，單個SQL操作能夠使用的PGA記憶體按照以下原則分配

MIN (5%*PGA_AGGREGATE_TARGET,100MB)

此處5%*P_A_T實際上是由一個內部引數_smm_max_size決定的，該引數限制自動工作區模式下最大的工作區使用（maximum work area size in auto mode-serial）

（2）   對於並行操作，能夠使用的PGA記憶體按照以下原則分配

30%*PGA_AGGREGATE_TARGET/DOP

        在Oracle10gR2以及Oracle11g中，記憶體使用存在如下限制：

（1）   對於序列操作，能夠使用的PGA記憶體按照以下原則分配

如果P_A_T<=500MB,則_smm_max_size = 20%*P_A_T

如果P_A_T在500MB和1000MB之間，_smm_max_size = 100MB

如果P_A_T在1001MB和2569MB之間，_smm_max_size = 10%*P_A_T

如果P_A_T>2560MB,則_smm_max_size = 262,060MB

（2）   對於並行操作，能夠使用的PGA記憶體按照以下原則分配

50%*P_A_T/DOP

但是注意，當DOP<=5時，_smm_max_size限制生效，並行度超過5時另外一個限制並行的引數_smm_px_max_size才會生效

從Oracle10g開始的新的PGA管理演算法受一個新增的隱含引數_newsort_enabled影響，如果該引數設定為False，則資料庫會使用之前Oracle9iR2中的演算法規則

1  select x.ksppinm NAME,y.ksppstvl VALUE,x.ksppdesc PDESC

2  from sys.x$ksppi x,sys.x$ksppcv y

3  where x.indx = y.indx

4* and x.ksppinm like '%&par%'

SQL> /

Enter value for par: newsort_enabled

old   4: and x.ksppinm like '%&par%'

new   4: and x.ksppinm like '%newsort_enabled%'

NAME                           VALUE                PDESC

------------------------------ -------------------- ------------------------------------------------------------

_newsort_enabled               TRUE                 controls whether new sorts can be used as system sort

要理解PGA的自動調整，還需要區分可調整記憶體（TUNABLE MEMORY SIZE）與不可調整記憶體（UNTUNABLE MEMORY SIZE）。可調整記憶體是由SQL工作區使用的，其餘部分是不可調整記憶體。

        啟用了自動PGA調整之後，Oracle仍然需要遵循以下原則：

            UNTUNABLE MEMORY SIZE + TUNABLE MEMORY SIZE <= PGA_AGGREGATE_TARGET

資料庫系統只能控制可調整部分的記憶體分配，如果可調整的部分過小，則Oracle永遠也不會強制啟用這個等式。

另外，PGA_AGGREGATE_TARGET引數在CBO優化器模式下，對於SQL的執行計劃會產生影響。Oracle在評估執行計劃時會根據PGA_AGGREGATE_TARGET引數評估在SORT、HAS-JOIN或BITMAP操作時能夠使用的最大記憶體或最小記憶體，從而選擇最優的的執行計劃。

對於PGA_AGGREGATE_TARGET引數的設定，Oracle提供這樣一個建議方案：

（1）   對於OLTP系統

PGA_AGGREGATE_TARGET=(TOTAL PHYSICAL MEMORY * 80%)*20%

（2）   對於DSS系統

PGA_AGGREGATE_TARGET=(TOTAL PHYSICAL MEMORY * 80%)*(20~50)%

注：在某些OS上單個程式使用的的真實記憶體可能遠大於在Oracle中看到的PGA大小，如AIX，在AIX上通常建議Oracle使用記憶體不超過實體記憶體的70%

        這只是一個建議設定，具體的更進一步的優化應該根據具體的效能指標來調整。

    伴隨著這個新的特性的加入，可以在v$process和v$process_memory檢視中獲取具體的PGA記憶體消耗。

    SQL在工作區中以3種方式執行：

（1）   Optimal

優化方式，指所有的處理可以在記憶體中完成

（2）   Onepass

大部分的操作可以在記憶體中完成，但是需要使用到磁碟操作

（3）   Multipass

大量的操作需要產生磁碟互動，效能極差

通常對於PGA的優化的目標就是使得optimal的執行儘量高，也就是儘量在記憶體中完成所有排序操作；同時使得multipass操作儘量低。

如下所示：

SQL> l

  1  select name,value,

  2  100*(value/decode((select sum(value) from v$sysstat

  3                            where name like '%workarea executions%'),0,null,

  4                    (select sum(value) from v$sysstat

  5                            where name like '%workarea executions%'))) pct

  6* from v$sysstat where name like '%workarea executions%'

SQL> /

NAME                                         VALUE        PCT

---------------------------------------- --------- ----------

workarea executions - optimal                17361        100

workarea executions - onepass                   0          0

workarea executions - multipass                  0          0

自動PGA管理實現原理

    自動PGA管理採用反饋環（Feedback Loop）演算法實現，其原理如下圖所示：

Set of Active Work Area Profiles

Register Workarea

Get Workarea Size

Active Statements

 

 

 

Local Memory Manager

WPn

Global Memory Manager

 

 

 

 

 

 

 

當活動程式開始執行SQL語句時，首先會通過Local Memory Manager註冊一個Active Workarea Profile。

Workarea Profile是程式與記憶體管理器之間通訊的唯一介面，Profile包含了這個Workarea的一系列屬性。

    Workarea Profile集通過Local Memory Manager維護，儲存在SGA之中，由於Profile經常被更新，所以所有Active Profile基本可以反應當前PGA記憶體需要和當前正在使用的記憶體。有了這些Profile資訊，後臺的Global Memory Manager就可以計算出一個既能提供較好效能的Global Memory Bound，這個值用於限制單個程式使用的PGA記憶體上限；Global Memory Manager每隔3秒更新一次Memory Bound，Local Memory Manager得到Memory Bound後會計算出每個Active Segment所需要分配的PGA記憶體大小，在這裡被稱為Expect Size，然後每個Active Segment將會在自己所分配到的Expect Size記憶體中進行運算。

    在以上流程中，Global Memory Manager並不直接參與PGA記憶體的分配，但是通過其計算得出的Global Memory Bound將影響所有程式的PGA分配。

    Global Memory Manage由CKPT後臺程式實現，如下所示。

SQL> select description,dest from x$messages where description like '%SQL%';

DESCRIPTION                                                      DEST

---------------------------------------------------------------- ----------------------------------------------------------------

SQL Memory Management Calculation                                CKPT

在Oracle10g以及11g中，可以通過v$sgastat檢視來查詢工作區的管理記憶體分配，如下：

SQL> select  * from v$sgastat where name like 'work area%';

POOL         NAME                            BYTES

------------ -------------------------- ----------

shared pool  work area tab                  276576

伴隨著新特性的引入，Oracle引入了一系列新的檢視，如v$pgastat，內容如下：

SQL> select * from v$pgastat;

NAME                                                                  VALUE UNIT

---------------------------------------------------------------- ---------- ------------

aggregate PGA target parameter                                     94371840 bytes

aggregate PGA auto target                                          68585472 bytes

global memory bound                                                18874368 bytes

total PGA inuse                                                    18197504 bytes

total PGA allocated                                                42578944 bytes

maximum PGA allocated                                              76462080 bytes

total freeable PGA memory                                           5701632 bytes

process count                                                            19

max processes count                                                      28

PGA memory freed back to OS                                       212729856 bytes

total PGA used for auto workareas                                         0 bytes

maximum PGA used for auto workareas                                 5085184 bytes

total PGA used for manual workareas                                       0 bytes

maximum PGA used for manual workareas                                     0 bytes

over allocation count                                                     0

bytes processed                                                   498573312 bytes

extra bytes read/written                                                  0 bytes

cache hit percentage                                                    100 percent

recompute count (total)                                               11387

隱含引數_pga_max_size控制單程式序列操作PGA的使用上限，不能超過該引數的1/2。從Oracle10g開始，該引數會隨著PGA_AGGREGATE_TARGET引數的調整而自動調整，從而提供更好的效能。

NAME                 VALUE           PDESC

-------------------- --------------- ------------------------------------------------------------

_pga_max_size        209715200       Maximum size of the PGA memory for one process

 

PGA的調整建議

    伴隨著自動PGA調整功能的引入，Oracle同時引入相應的動態效能檢視用於優化建議。PGA的優化建議通過v$pga_target_advice和v$pga_target__histogram提供。

    v$pga_target_advic檢視通過不同PGA設定進行評估，給出在不同設定下的PGA命中率和OverAlloc資訊。

    SQL> select PGA_TARGET_FOR_ESTIMATE/1024/1024 PGA_EST_MB,

  2         PGA_TARGET_FACTOR,ESTD_PGA_CACHE_HIT_PERCENTAGE,

  3         ESTD_OVERALLOC_COUNT

  4         from v$pga_target_advice;

PGA_EST_MB PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT

---------- ----------------- ----------------------------- --------------------

     11.25              .125                            96                    4

      22.5               .25                            96                    4

        45                .5                           100                    0

      67.5               .75                           100                    0

        90                 1                           100                    0

       108               1.2                           100                    0

v$PGA_TARGET_ADVICE_HISTOGRAM檢視可以通過對不同工作區大小的取樣評估提供統計資訊供分析使用。

PGA的轉儲

    對於PGA的進一步研究可以通過轉儲PGA記憶體結構來實現，Oracle提供如下命令用於將PGA轉儲到跟蹤檔案:

    Alter session set events ‘immediate trace name heapdump level n’;

    Level 1 PGA彙總資訊

    Level 2 SGA彙總資訊

    Level 4 UGA彙總資訊

    Level 8 當前呼叫的彙總資訊（CGA）

    Level 16    使用者呼叫的彙總資訊（CGA）

    Level 32    Large Pool的彙總資訊

    Level 1025  PGA詳細資訊

    Level 2050  SGA詳細資訊

    Level 5000  UGA詳細資訊

    Level 8200  當前呼叫的詳細資訊

    Level 16400 使用者呼叫的詳細資訊

    Level 32800 Large Pool的詳細資訊