Oracle Memory Management and HugePage （連載一）

作者：沃趣科技高階資料庫工程師  魏興華 

概述

大家好，我是魏興華，你們可以叫我肉絲，我的英文名是Rose魏。在這篇文章中，我給大家介紹一些Oracle記憶體管理和大頁的知識。Oracle發展這麼多年，提供了多種的記憶體管理方式，從最早SGA、PGA手工管理，到9I版本出現的PGA的自動管理，到10G版本出現的SGA自動管理（ASMM)，再到11G版本出現的memory自動管理（AMM)，Oracle基本是在朝著智慧化、傻瓜化、自動化的方向穩步前進著，對於初學Oracle的DBA來說，看到這些不同的記憶體管理方式一定心裡有著不同程度的疑惑，例如：

·          Oracle有這麼多記憶體分配的管理方式，我該使用哪一種？是使用11G版本推出的AMM管理方式，還是使用10G版本出現的ASMM管理方式？或者乾脆使用最舊的手工方式管理記憶體？

·         我該為我的例項SGA，PGA分別設定多大呢?

·         我該為buffer cache，shared pool分配多大的記憶體空間？

·         什麼情況下我該使用大頁？為什麼大頁這個詞最近幾年這麼容易聽到？
·         有沒有一些簡單粗暴的演算法來搞定這一切？

寫這篇文章的初衷也來源於上面這些非常正常的疑問，如果這篇文章能給有這些疑惑的DBA朋友一些指導和幫助，那肉絲我也就真的功莫大焉了。

Memory構成

在一個執行著Oracle資料庫的專用伺服器上，記憶體基本上被以下內容所佔用：
·               Kernel
·               OS Page Table
·               檔案系統Cache
·               SGA
·               PGA
·               Oracle程式
·              其他程式（RMAN，非Oracle的程式等等）

我們首先了解一下Oracle記憶體發展的歷程，基本上Oracle的記憶體管理在版本的演進過程中沿著越來越智慧化、自動化、傻瓜化的方向前進。接觸過MYSQL等資料庫的DBA朋友應該比較清楚，這些資料庫基本上都還需要DBA去決定每個記憶體元件的大小，而Oracle已經從9I版本開始就邁向智慧化、自動化的過程了。下圖是Oracle記憶體管理的一個演進圖：

PGA 自動管理

Oracle是多程式的架構，這點區別於MYSQL，MYSQL是單程式多執行緒的架構，Oracle會為每一個使用者連線建立一個獨立的作業系統程式來為使用者提供服務，這個程式叫做伺服器程式或者影子程式，它像是使用者的一個代理，來運算元據檔案或者SGA記憶體，由於伺服器程式的程式碼是Oracle公司開發的，所以Oracle公司完全相信這些程式或程式碼是安全的，因此這些程式可以直接運算元據檔案和SGA記憶體，這些程式接受使用者程式傳送的指令，並完成相關的操作，並根據需要來給使用者程式返回結果。由於伺服器程式是作業系統上的一個程式，因此它本身需要佔用一些作業系統記憶體，除此之外，程式在對資料進行讀取、排序、hash過程中，也會佔用一定量的記憶體，在Oracle 9I版本之前，對於伺服器程式的記憶體管理是由一些引數去控制的，以下引數代表每一個伺服器程式可以使用的不同區域的記憶體大小（都是程式的私有記憶體區域）：
·         SORT_AREA_SIZE
·         HASH_AREA_SIZE
·         BITMAP_MERGE_AREA_SIZE
·         CREATE_BITMAP_AREA_SIZE
例如，SORT_AREA_SIZE控制了每一個程式可用的排序區大小，HASH_AREA_SIZE引數控制了每一個程式可用的hash區大小，這些引數都有預設值，但是預設值是否合適，需要打上一個大大的問號，因為不同的任務對於PGA記憶體的不同區域有不同的要求，例如，如果是做排序操作，就對排序區記憶體要求較大，而對hash區沒任何的要求。當然如果預設值不合適，DBA可以手工調整這些區域的大小。

Oracle 9I版本出現了PGA的自動管理，不再需要像9I之前版本需要設定一系列引數來控制PGA的使用，只需要設定PGA_AGGREGATE_TARGET為一個值，就可以控制所有的伺服器程式的PGA使用量，至於每個伺服器程式使用了多少排序區，hash區，都交給Oracle去控制。

一般情況下對於PGA的大量使用有如下幾種操作：
·         hash 對於hash join操作，hash桶所佔用的記憶體就在程式的私有PGA記憶體中，而不是在共享記憶體SGA中，如果使用PGA手工管理的話，可以透過HASH_AREA_SIZE引數來動態調整會話進行hash操作能夠使用的記憶體量。
·          sort 對於排序操作，例如查詢語句裡的order by、建立索引的排序操作等佔用的記憶體也在PGA中，如果使用PGA手工管理，可以透過SORT_AREA_SIZE引數動態調整會話排序操作可以使用的記憶體量。
·         parallel 並行操作簡直可以說是PGA記憶體的殺手，每一個並行程式都能使用到最多2G的PGA記憶體，當然Oracle會確保所有的並行slave使用的PGA記憶體不能超過PGA_AGGREGATE_TARGET的一半。

現在Oracle的版本已經出到了12C，PGA的自動管理已經發展了很多個年頭，如果是個人，也應該是一個非常成熟的小夥子了，甚至是位大叔了????，絕大部分資料庫操作完全沒必要再去手工調整PGA的一些引數。不過，我們依然能從網際網路上、論壇上看到有很多DBA對這種手工調整PGA的技術崇拜有加（我以前也是），確實在一些情況下，透過手工調整PGA的相關記憶體區，可以達到加速排序等一些操作的目的，但是如果需要操作的資料量非常的大，那這種調整往往是費時費力，甚至是徒勞的，因為對於一個程式的私有PGA記憶體來說，像sort,hash等P區域的記憶體分配是有限制的，現在11GR2的版本對於每個程式的PGA記憶體最大限制預設是2個G，且排序區可以使用的只有1個G，如果你的排序等操作需要的記憶體遠遠不止1，2個G，那麼這種最佳化就非常的徒勞，甚至還可能變慢（肉絲親身遭遇過變慢的案例????）。是否使用PGA自動管理由引數WORKAREA_SIZE_POLICY控制，它的值可以為auto和manual，顧名思義，auto為PGA自動管理，manual為PGA手工管理，回到9I之前的使用方式。

這裡肉絲提供幾個大家可能會感興趣的隱含引數，比如我上面提到了每個程式最大能使用的PGA不能超過2個G，透過修改隱含引數可以突破這個限制。 >肉絲在這裡警戒各位，這些引數如果要在生產環境使用，請在你的資料庫版本下做好測試。
以下為11GR2版本的情況，其他版本並未做測試：
·         _PGA_MAX_SIZE 每個程式的PGA的最大記憶體大小。預設值為：2147483648，2個G，單位為B。
·         _SMM_MAX_SIZE 每個程式的工作區的大小，預設值為1/2 _PGA_MAX_SIZE，1048576 ，單位KB，1GB，排序區、hash區都屬於工作區的範圍。64位系統下真實使用的排序區記憶體不能超過4GB。
·         _SMM_PX_MAX_SIZE 所有並行查詢的SLAVE程式能夠用到的PGA總量。預設值為 1/2 pga_aggregate_target，單位為KB，RAC環境下，每個節點都可以用到這麼多記憶體。

以上全部為動態引數，可以在session/system級別來線上修改。
alter system set"_SMM_PX_MAX_SIZE"=10485760;
alter system set"_SMM_MAX_SIZE"=1048576;
alter system set"_PGA_MAX_SIZE"=2147483648;

上面的引數調整後，一定要設定對應的pga_aggregate_target，否則以上調整可能會不起作用，建議設定為修改後的_SMM_PX_MAX_SIZE的值的兩倍。

預設情況下，每個程式使用的排序區不能超過1G。由引數_SMM_MAX_SIZE（單位KB）控制，預設為_PGA_MAX_SIZE（單位B）的一半。 例如，並行度20建立索引，總共可以使用的排序區大小為20*1G=20G，但是同時還受引數_SMM_PX_MAX_SIZE的控制，所有的slave佔用的記憶體不能超過_SMM_PX_MAX_SIZE的值（單位為KB），預設為pga_aggregate_target的一半。同時64位系統下，每個程式可以使用的排序空間不能超過4個G。所以即使把_SMM_MAX_SIZE調整大於4個G也沒有用。_SMM_PX_MAX_SIZE，所有並行查詢的SLAVE程式能夠用到的PGA總量。每個RAC 節點都可以用到這麼多，限制的是本節點所有並行slave能夠消耗的PGA。

如何為PGA_AGGREGATE_TARGET設定一個合理的值？

PGA_AGGREGATE_TARGET的設定經常是一個摸索的過程，這裡給出官方的一個分配指導原則

PGA_AGGREGATE_TARGET =(TOTAL_MEM * 80%) * 20% for an OLTP system

PGA_AGGREGATE_TARGET = (TOTAL_MEM * 80%) * 50%for a DSS system

上面公式中的TOTAL_MEM * 80%代表著Oracle可以使用的所有記憶體為作業系統的80%，再根據不同型別業務的特點，OLTP系統，可以在此基礎上分配20%的記憶體給PGA，DSS分析型系統，可以給出剩餘記憶體的50%。這個只是一個指導的意見，具體情況要具體分析。例如，你的OLTP系統上有成千上萬個連線，那麼你可以粗略的按照每個程式佔用10M的記憶體來大體的計算一下PGA需要佔用的記憶體空間，再者，假如你的系統不但連線數非常多，而且活躍的連線數也非常的多，那麼你可以按照每個程式至少12M的記憶體來進行估算，更為重要的，系統中假如存在非常多的臨時性的計算任務，那麼要為PGA預留的記憶體就更多了。 例如，並行度設定為5建立索引，每個並行程式佔用的PGA記憶體接近1個G：

select *  from
(select PGA_USED_MEM/1024/1024,PGA_ALLOC_MEM/1024/1024,PGA_MAX_MEM/1024/1024

from v$process order by 1 desc) whererownum<14;

PGA_USED_MEM/1024/1024  PGA_ALLOC_MEM/1024/1024  PGA_MAX_MEM/1024/1024

---------------------- --------------------------------------------
    
1043.70779          1044.39673        1044.39673
904.724821          905.334227        905.334227
851.350813          851.959227        851.959227
804.526175          805.146727        805.146727
589.681547          590.209227        637.584227
27.0686626          27.5379925        27.5379925
27.0686626          27.5379925        27.5379925

所以你在為系統規劃PGA記憶體時不要忘了這些臨時性任務需要佔用的記憶體。他們可能是很大的一塊哦。

該為資料庫分配多少的PGA記憶體，除了把連線數的多少這個指標作為一個考量因素外，還需要關注活躍連線數的多少，這是因為很多系統連線數雖然非常的多，但是去資料庫裡一統計發現，絕大部分的連線已經幾個小時甚至幾天都沒活躍過了，這往往是應用程式的連線池不加以管理的結果（也可能是其他原因）。對於不活躍的連線，Oracle每個程式的PGA佔用不會太大，按照10M計算是個合理安全的值。AIX下可以大一些，按照每個15M-20M計算。

這裡再提供一種估算每個空閒的伺服器程式佔用OS記憶體的方法。 首先透過作業系統命令free -m檢視一下當前OS剩餘的記憶體，69590M

echo 3 >/proc/sys/vm/drop_caches

#free -m
               total       used      free     shared    buffers    cach
ed
Mem:       128923      69590      59332          0          4        2
27
-/+ buffers/cache:      69358     59564
Swap:       15999       1276      14723

然後建立2000個連線
test.sql
declare
L_N number;
begin
dbms_lock.sleep(600);
end;
/

#! /bin/sh
. /home/Oracle/.bash_profile
step=1
while [ $step -lt 2000 ]
do
nohup sqlplus test/test @test.sql &
let "step+=1"
echo $step
done

再次檢視剩餘的作業系統記憶體，透過沒建立連線之前的剩餘記憶體減去建立完2000個連線之後的剩餘記憶體可以估算出每個程式佔用的記憶體大約為（59332-44548）/ 2000=7M
free -m
                        total       used       free    shared    buffers     cach
ed
Mem:            128923      84374      44548          0         11        2
63
-/+ buffers/cache:      84099     44823
Swap:        15999       1276      14723

讀者需要牢記，減少的7M記憶體中，絕大部分都是程式本身佔用的，只有1-2M的記憶體是PGA佔用的。因此上面給大家推薦的值是10-12M，也就是給程式預留出一些PGA的記憶體來。

確認一個空閒程式佔用的真正PGA記憶體有多大，可以透過v$process檢視中PGA_ALLOC_MEM欄位來獲得，如下：只有1.49M。但是就像上面已經提到的，這個程式從作業系統層面看卻佔用了7M左右的記憶體。

select round(PGA_ALLOC_MEM/1024/1024,2) from v$process where spid=5553;

ROUND(PGA_ALLOC_MEM/1024/1024,2)
--------------------------------
                1.49

pga_aggregate_target引數指定的值並不是一個硬限制，直到Oracle 12C才提供了一個引數來強制限制PGA的使用量。如果讀者不知道該為自己系統的PGA設定一個什麼樣的值，可以透過檢視v
 sysmetric_history
selectbegin_time,end_time,value 
from v$sysmetric_history
where metric_name ='Total PGA Allocated';

BEGIN_TIME          END_TIME                 VALUE
-------------------   -------------------             ----------
2016-04-08 11:31:21 2016-04-08 11:32:21  323746816
2016-04-08 11:30:20 2016-04-08 11:31:21  323746816
2016-04-08 11:29:21 2016-04-08 11:30:20  328404992
2016-04-08 11:28:21 2016-04-08 11:29:21  323746816
2016-04-08 11:27:21 2016-04-08 11:28:21  323746816
2016-04-08 11:26:20 2016-04-08 11:27:21  323746816
2016-04-08 11:25:21 2016-04-08 11:26:20  323746816
2016-04-08 11:24:21 2016-04-08 11:25:21  326611968
2016-04-08 11:23:21 2016-04-08 11:24:21  323746816

上面查詢的輸出代表了各個時間段的，PGA記憶體的使用量，結果集並沒有完全列出來，讀者可以根據PGA各個時間段的使用量來更加精準的去為自己系統的PGA如何做設定做決策。設定PGA的過程是一個循序漸進的過程。再次強調，一個程式佔用的記憶體除了PGA之外，程式本身也會佔用記憶體，這點上面我們已經討論過。

按照Oracle 2015年OOW上的一份PPT提到，12C之前版本，PGA最多可用的記憶體可達到PGA_AGGREGATE_TARGET設定值的三倍，這裡你聽一下就行了，不必當真。

如果生產環境真的遭遇了PGA嚴重使用過量的情況，可以透過Event 10261 來限制某個/所有程式PGA的使用，level後面值的單位為KB。
alter session set events'10261 trace name context forever, level 100000';

一旦程式超出PGA的設定配額，會被後臺程式殺掉並報錯，不同的版本可能報錯的資訊不一樣：
·          For 11.2.0.4 and 12.1.0.1+, ORA-10260
·          For 11.1 - 11.2.0.3, ORA-600[723]

12C PGA_AGGREGATE_LIMIT

12C之前的版本對於PGA的使用限制並沒有一個硬限制，這個可能會導致一些問題，比如不加以限制後可能會導致OS SWAP的問題，一旦出現SWAP會導致Oracle效能的急劇下降，甚至導致DOWN機，我曾經遭遇過的一個案例是出現SWAP後，LGWR程式本身的記憶體出現了SWAP，資料庫系統的表現是幾乎完全HANG死，最後沒辦法只能重啟解決問題。

對於這個新特性的使用是透過引數PGA_AGGREGATE_LIMIT來限制PGA的使用上線，它是一個推導引數，在使用ASMM情況下，取以下的最大值
·           2GB
·         200% PGA_AGGREGATE_TARGET
·          3MB* PROCESSES

在使用AMM情況下，肉絲還未找到PGA_AGGREGATE_LIMIT取值的規律，如果你知道，請告訴我哦。