Oracle記憶體分配與調整

 前言
對於 oracle 的記憶體的管理，截止到9iR2，都是相當重要的環節，管理不善，將可能給
資料庫帶來嚴重的效能問題。下面我們將一步一步就記憶體管理的各個方面進行探討。
l 概述
oracle 的記憶體可以按照共享和私有的角度分為系統全域性區和程式全域性區，也就是SGA
和PGA(process global area or private global area)。對於SGA區域內的記憶體來說，是共享的全
局的，在UNIX 上，必須為oracle 設定共享記憶體段（可以是一個或者多個），因為oracle 在
UNIX上是多程式；而在WINDOWS上oracle是單程式（多個執行緒），所以不用設定共享內
存段。PGA是屬於程式（執行緒）私有的區域。在oracle 使用共享伺服器模式下（MTS）,PGA
中的一部分，也就是UGA會被放入共享記憶體large_pool_size 中。
對於SGA部分，我們透過sqlplus 中查詢可以看到：
SQL> select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 454032
Variable Size 109051904
Database Buffers 385875968
Redo Buffers 667648
Fixed Size
oracle 的不同平臺和不同版本下可能不一樣，但對於確定環境是一個固定的值，裡面
儲存了SGA 各部分元件的資訊，可以看作引導建立SGA的區域。
Variable Size
包含了shared_pool_size、java_pool_size、large_pool_size 等記憶體設定
Database Buffers
指資料緩衝區，在8i 中包含db_block_buffer*db_block_size、buffer_pool_keep、
buffer_pool_recycle 三部分記憶體。在9i 中包含db_cache_size、db_keep_cache_size、
db_recycle_cache_size、db_nk_cache_size。
Redo Buffers
指日誌緩衝區，log_buffer。在這裡要額外說明一點的是，對於v$parameter、v$sgastat、
v$sga查詢值可能不一樣。v$parameter 裡面的值，是指使用者在初始化引數檔案裡面設定
的值，v$sgastat是oracle 實際分配的日誌緩衝區大小（因為緩衝區的分配值實際上是離
散的，也不是以block 為最小單位進行分配的），v$sga 裡面查詢的值，是在oracle 分配
了日誌緩衝區後，為了保護日誌緩衝區，設定了一些保護頁，通常我們會發現保護頁大小大
約是11k(不同環境可能不一樣)。參考如下內容
SQL> select substr(name,1,10) name,substr(value,1,10) value
2 from v$parameter where name = 'log_buffer';
NAME VALUE
-------------------- --------------------
log_buffer 524288
SQL> select * from v$sgastat ;
POOL NAME BYTES
----------- -------------------
fixed_sga 454032
buffer_cache 385875968
log_buffer 656384
SQL> select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 454032
Variable Size 109051904
Database Buffers 385875968
Redo Buffers 667648
關於各部分記憶體的作用，參考oracle體系結構，在此不再敘述。
l SGA的大小
那麼我們現在來考察記憶體引數的設定。實際上，對於特定的環境，總是存在著不同的最
優設定的，沒有任何一種普遍適用的最優方案。但為什麼在這裡我們還要來談設定這個話題
呢，那僅僅是出於一個目的，避免過度的犯錯誤。事實上，在任何一個生產系統正式投入使
用之前，我們不擁有任何系統執行資訊讓我們去調整，這樣就只有兩種可能，一是根據文件
推薦設定，另外一種就是根據經驗設定。相對來說，根據經驗的設定比根據文件的設定要可
靠一些。尤其是那些24*7 的系統，我們更要減少錯誤的發生。那麼我們嘗試去了解不同的
系統不同的應用的具體設定情況，從而提供一個參照資訊給大家。
為了得出一個參照設定，我們就必須假定一個參照環境。以下所有設定我們基於這樣一
個假定，那就是硬體伺服器上只考慮存在作業系統和資料庫，在這個單一的環境中，我們來
考慮記憶體的設定。
在設定引數之前呢，我們首先要問自己幾個問題
一：實體記憶體多大
二：作業系統估計需要使用多少記憶體
三：資料庫是使用檔案系統還是裸裝置
四：有多少併發連線
五：應用是OLTP 型別還是OLAP 型別
根據這幾個問題的答案，我們可以粗略地為系統估計一下記憶體設定。那我們現在來逐
個問題地討論，首先實體記憶體多大是最容易回答的一個問題，然後作業系統估計使用多少內
存呢？從經驗上看，不會太多，通常應該在200M 以內（不包含大量程式PCB）。
接下來我們要探討一個重要的問題，那就是關於檔案系統和裸裝置的問題，這往往容
易被我們所忽略。作業系統對於檔案系統，使用了大量的buffer 來快取作業系統塊。這樣當
資料庫獲取資料塊的時候，雖然SGA 中沒有命中，但卻實際上可能是從作業系統的檔案緩
存中獲取的。而假如資料庫和作業系統支援非同步IO，則實際上當資料庫寫程式DBWR寫磁
盤時，作業系統在檔案快取中標記該塊為延遲寫，等到真正地寫入磁碟之後，作業系統才通
知DBWR寫磁碟完成。對於這部分檔案快取，所需要的記憶體可能比較大，作為保守的估計，
我們應該考慮在0.2——0.3 倍記憶體大小。但是如果我們使用的是裸裝置，則不考慮這部分
快取的問題。這樣的情況下SGA就有調大的機會。
關於資料庫有多少併發連線，這實際上關係到PGA 的大小（MTS 下還有
large_pool_size）。事實上這個問題應該說還跟OLTP 型別或者OLAP 型別相關。對於OLTP
型別oracle 傾向於可使用MTS,對於OLAP 型別使用獨立模式，同時OLAP 還可能涉及到大
量的排序操作的查詢，這些都影響到我們記憶體的使用。那麼所有的問題綜合起來，實際上主
要反映在UGA的大小上。UGA主要包含以下部分記憶體設定
SQL> show parameters area_size
NAME TYPE VALUE
------------------------------------ ------- -------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_size integer 65536
SQL>
在這部分記憶體中我們最關注的通常是sort_area_size，這是當查詢需要排序的時候，資料
庫會話將使用這部分記憶體進行排序，當記憶體大小不足的時候，使用臨時表空間進行磁碟排序。
由於磁碟排序效率和記憶體排序效率相差好幾個數量級，所以這個引數的設定很重要。這四個
引數都是針對會話進行設定的，是單個會話使用的記憶體的大小，而不是整個資料庫使用的。
偶爾會看見有人誤解了這個引數以為是整個資料庫使用的大小，這是極其嚴重的錯誤。假如
設定了MTS，則UGA被分配在large_pool_size，也就是說放在了共享記憶體裡面，不同程式
（執行緒）之間可以共享這部分記憶體。在這個基礎上，我們假設資料庫存在併發執行server
process 為100 個，根據上面我們4 個引數在oracle8.1.7 下的預設值，我們來計算獨立模式
下PGA 的大致大小。由於會話並不會經常使用create_bitmap_area_size 、
bitmap_merge_area_size，所以我們通常不對四個引數求和。在考慮到除這四個引數外會話所
儲存的變數、堆疊等資訊，我們估計為2M，則200 個程式最大可能使用200M 的PGA。
現在，根據上面這些假定，我們來看SGA 實際能達到多少記憶體。在1G 的記憶體的服務
器上，我們能分配給SGA 的記憶體大約為400—500M。若是2G 的記憶體，大約可以分到1G
的記憶體給SGA，8G 的記憶體可以分到5G的記憶體給SGA。當然我們這裡是以預設的排序部分
記憶體sort_area_size=64k進行衡量的，假如我們需要調大該引數和hash_area_size等引數，然
後我們應該根據併發的程式的數量，來衡量考慮這個問題。
事實上，通常我們更習慣透過直觀的公式化來表達這樣的問題：
OS使用記憶體+SGA+併發執行程式數*(sort_area_size+hash_ara_size+2M) < 0.7*總記憶體
(公式是死的，系統是活的，實際應用的調整不必框公式，這不過是一個參考建議)
在我們的實際應用中，假如採用的是裸裝置，我們可適當的增大SGA(如果需要的話)。
由於目前幾乎所有的作業系統都使用虛擬快取，所以實際上如果就算SGA 設定的比較大也
不會導致錯誤，而是可能出現頻繁的記憶體頁的換入與換出(page in/out)。在作業系統一級如
果觀察到這個現象，那麼我們就需要調整記憶體的設定。
l SGA內引數設定
Log_buffer
對於日誌緩衝區的大小設定，通常我覺得沒有過多的建議，因為參考LGWR寫的觸發
條件之後，我們會發現通常超過3M意義不是很大。作為一個正式系統，可能考慮先設定這
部分為log_buffer=1—3M 大小，然後針對具體情況再調整。
Large_pool_size
對於大緩衝池的設定，假如不使用MTS，建議在20—30M 足夠了。這部分主要用來保
存並行查詢時候的一些資訊，還有就是RMAN 在備份的時候可能會使用到。如果設定了
MTS，則由於UGA部分要移入這裡，則需要具體根據server process數量和相關會話記憶體參
數的設定來綜合考慮這部分大小的設定。
Java_pool_size
假如資料庫沒有使用JAVA，我們通常認為保留10—20M大小足夠。事實上可以更少，
甚至最少只需要32k，但具體跟安裝資料庫的時候的元件相關(比如http server)。
shared_pool_size
這是迄今為止最具有爭議的一部分記憶體設定。按照很多文件的描述，這部分內容應該
幾乎和資料緩衝區差不多大小。但實際上情況卻不是這樣的。首先我們要考究一個問題，那
就是這部分記憶體的作用，它是為了快取已經被解析過的SQL，而使其能被重用，不再解析。
這樣做的原因是因為，對於一個新的SQL（shared_pool 裡面不存在已經解析的可用的相同
的SQL），資料庫將執行硬解析，這是一個很消耗資源的過程。而若已經存在，則進行的僅
僅是軟分析（在共享池中尋找相同SQL），這樣消耗的資源大大減少。所以我們期望能多共
享一些SQL，並且如果該引數設定不夠大，經常會出現ora-04031錯誤，表示為了解析新的
SQL，沒有可用的足夠大的連續空閒空間，這樣自然我們期望該引數能大一些。但是該引數
的增大，卻也有負面的影響，因為需要維護共享的結構，記憶體的增大也會使得SQL 的老化
的代價更高，帶來大量的管理的開銷，所有這些可能會導致CPU 的嚴重問題。
在一個充分使用繫結變數的比較大的系統中，shared_pool_size 的開銷通常應該維持在
300M 以內。除非系統使用了大量的儲存過程、函式、包，比如oracle erp 這樣的應用，可
能會達到500M甚至更高。於是我們假定一個1G記憶體的系統，可能考慮設定該引數為100M，
2G 的系統考慮設定為150M,8G 的系統可以考慮設定為200—300M。
對於一個沒有充分使用或者沒有使用繫結變數系統，這可能給我們帶來一個嚴重的問
題。所謂沒有使用bind var 的SQL，我們稱為Literal SQL。也就是比如這樣的兩句SQL我
們認為是不同的SQL,需要進行2 次硬解析：
select * from EMP where name = ‘TOM’;
select * from EMP where name = ‘JERRY’;
假如把’TOM’ 和 ‘JERRY’ 換做變數V，那就是使用了bind var，我們可以認為是同樣
的SQL 從而能很好地共享。共享SQL 本來就是shared_pool_size 這部分記憶體存在的本意，
oracle的目的也在於此，而我們不使用bind var 就是違背了oracle 的初衷，這樣將給我們的
系統帶來嚴重的問題。當然，如果透過在作業系統監控，沒有發現嚴重的cpu問題，我們如
果發現該共享池命中率不高可以適當的增加shred_pool_size。但是通常我們不主張這部分內
存超過800M（特殊情況下可以更大）。
事實上，可能的話我們甚至要想辦法避免軟分析，這在不同的程式語言中實現方式有
差異。我們也可能透過設定session_cached_cursors 引數來獲得幫助（這將增大PGA）。
Data buffer
現在我們來談資料緩衝區，在確定了SGA 的大小並分配完了前面部分的記憶體後，其餘
的，都分配給這部分記憶體。通常，在允許的情況下，我們都嘗試使得這部分記憶體更大。這部
分記憶體的作用主要是快取DB BLOCK，減少甚至避免從磁碟上獲取資料，在8i中通常是由
db_block_buffers*db_block_size 來決定大小的。如果我們設定了buffer_pool_keep 和
buffer_pool_recycle，則應該加上後面這兩部分記憶體的大小。
l 9i下引數的變化
oracle的版本的更新，總是伴隨著引數的變化，並且越來越趨向於使得引數的設定更簡
單，因為複雜的引數設定使得DBA們經常焦頭爛額。關於記憶體這部分的變化，我們可以考
察下面的引數。事實上在9i中資料庫本身可以給出一組適合當前執行系統的SGA相關部分
的引數調整值（參考V$DB_CACHE_ADVICE、V$SHARED_POOL_ADVICE），關於PGA
也有相關檢視V$PGA_TARGET_ADVICE 等。
Data buffer
9i 中保留了8i中的引數，如設定了新的引數，則忽略舊的引數。9i中用db_cache_size
來取代db_block_buffers ， 用db_keep_cache_size 取代buffer_pool_keep, 用
db_recycle_cache_size 取代buffer_pool_recycle；這裡要注意9i 中設定的是實際的快取大小
而不再是塊的數量。另外9i新增加了db_nk_cache_size，這是為了支援在同一個資料庫中使
用不同的塊大小而設定的。對於不同的表空間，可以定義不同的資料塊的大小，而緩衝區的
定義則依靠該引數的支援。其中n 可以為2、4、6、8、16 等不同的值。在這裡順便提及的
一個引數就是db_block_lru_latches，該引數在9i中已經成為了保留引數，不推薦手工設定。
PGA
在9i 裡面這部分也有了很大的變化。在獨立模式下，9i已經不再主張使用原來的UGA
相關的引數設定，而代之以新的引數。假如workarea_size_policy=AUTO（預設），則所有的
會話的UGA 共用一大塊記憶體，該記憶體由pga_aggregate_target 設定。在我們根據前面介紹
的方法評估了所有程式可能使用的最大PGA 記憶體之後，我們可以透過在初始化引數中設定
這個引數，從而不再關心其他”*_area_size” 引數。
SGA_MAX_SIZE
在9i中若設定了SGA_MAX_SIZE，則在總和小於等於這個值內，可以動態的調整資料
緩衝區和共享池的大小
SQL> show parameters sga_max_size
NAME TYPE VALUE
------------------------------------ ------- -------------
sga_max_size unknown 193752940
SQL>
SQL> alter system set db_cache_size = 30000000;
System altered.
SQL> alter system set shared_pool_size = 20480000;
System altered.
l Lock_sga = true 的問題
由於幾乎所有的作業系統都支援虛擬記憶體，所以即使我們使用的記憶體小於實體記憶體，也
不能避免作業系統將SGA 換到虛擬記憶體（SWAP）。所以我們可以嘗試使得SGA 鎖定在物
理記憶體中不被換到虛擬記憶體中，這樣減少頁面的換入和換出，從而提高效能。但在這裡遺憾
的是，windows 是無法避免這種情況的。下面我們來參考在不同的幾個系統下怎麼實現
lock_sga
AIX 5L（AIX 4.3.3 以上）
logon aix as root
cd /usr/samples/kernel
./vmtune (資訊如下) v_pingshm已經是1
./vmtune -S 1
然後oracle使用者修改initSID.ora 中 lock_sga = true
重新啟動資料庫
HP UNIX
Root身份登陸
Create the file "/etc/privgroup": vi /etc/privgroup
Add line "dba MLOCK" to file
As root, run the command "/etc/setprivgrp -f /etc/privgroup":
$/etc/setprivgrp -f /etc/privgroup
oracle使用者修改initSID.ora中lock_sga=true
重新啟動資料庫
SOLARIS (solaris2.6以上)
8i版本以上資料庫預設使用隱藏引數 use_ism = true ，自動鎖定SGA於記憶體中,不用設定
lock_sga, 如果設定 lock_sga =true 使用非 root 使用者啟動資料庫將返回錯誤。
WINDOWS
不能設定lock_sga=true,可以透過設定pre_page_sga=true,使得資料庫啟動的時候就把所有內
存頁裝載，這樣可能起到一定的作用。
l 關於記憶體引數的調整
關於引數調整，是oracle的複雜性的一個具體體現。通常來講，我們更傾向於讓客戶做
statspack 報告，然後告訴我們os 監控的狀況，在這些的資訊的基礎上，再向客戶索取具體
的詳細資訊以診斷問題的所在。系統的調整，現在我們通常採用從等待事件入手的方法。因
為一個系統感覺到慢，必然是在某個環節上出現等待，那麼我們從等待最多的事件入手逐步
診斷並解決問題。
對於記憶體的調整，相對來說簡單一些，我們首先可以針對資料緩衝區的大小來看。首先
觀察命中率
資料緩衝區命中率
SQL> select value from v$sysstat where name ='physical reads';
VALUE
----------
14764
SQL> select value from v$sysstat where name ='physical reads direct';
VALUE
----------
50
SQL> select value from v$sysstat where name ='physical reads direct (lob)';
VALUE
----------
0
SQL> select value from v$sysstat where name ='consistent gets';
VALUE
----------
167763
SQL> select value from v$sysstat where name = 'db block gets';
VALUE
----------
14305
這裡命中率的計算應該是
令 x = physical reads direct + physical reads direct (lob)
命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100
通常如果發現命中率低於90%,則應該調整應用可可以考慮是否增大資料緩衝區
共享池的命中率
SQL> select sum(pinhits-reloads)/sum(pins)*100 "hit radio" from v$librarycache;
hit radio
----------
99.809291
假如共享池的命中率低於95%,就要考慮調整應用（通常是沒使用bind var ）或者增加記憶體
關於排序部分
SQL> select name,value from v$sysstat where name like '%sort%';
NAME VALUE
---------------------------------------------------------------- ----------
sorts (memory) 67935
sorts (disk) 1
sorts (rows) 7070
SQL>
假如我們發現sorts (disk)/ (sorts (memory)+ sorts (disk))的比例過高，則通常意味著
sort_area_size 部分記憶體較小，可考慮調整相應的引數。
關於log_buffer
SQL> select name,value from v$sysstat
2 where name in('redo entries','redo buffer allocation retries');
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 2325719
redo buffer allocation retries 10
假如redo buffer allocation retries/ redo entries 的比例超過1%我們就可以考慮增大log_buffer
通常來說，記憶體的調整的焦點就集中在這幾個方面，更多更詳細的內容，建議從statspack
入手來一步一步調整。最後關於記憶體的調整，再強調這一點，一定要結合作業系統來衡量，
任何理論都必須要實踐來檢驗。在作業系統中觀察page in/out 狀況，發現問題嚴重，應
該考慮調小SGA。
l 32bit 和64bit 的問題
對於oracle 來說，存在著32bit與64bit的問題。這個問題影響到的主要是SGA的大小。
在32bit的資料庫下，通常oracle只能使用不超過1.7G的記憶體，即使我們擁有12G的記憶體，
但是我們卻只能使用1.7G，這是一個莫大的遺憾。假如我們安裝64bit的資料庫,我們就可以
使用很大的記憶體，我們幾乎不可能達到上限。但是64bit 的資料庫必須安裝在64bit 的操作
系統上，可惜目前windows 上只能安裝32bit的資料庫，我們透過下面的方式可以檢視資料
庫是32bit 還是64bit：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
但是在特定的作業系統下，可能提供了一定的手段，使得我們可以使用超過1.7G 的內
存，達到2G 以上甚至更多。在這裡我們針對不同的平臺下的具體實現方式做一個總結。