前言
對于 oracle 的內存的管理，截止到9iR2，都是相當重要的環(huán)節，管理不善，將可能給數據庫帶來(lái)嚴重的性能問(wèn)題。下面我們將一步一步就內存管理的各個(gè)方面進(jìn)行探討。
概述
Oracle 的內存可以按照共享和私有的角度分為系統全局區和進(jìn)程全局區，也就是SGA和PGA(process global area or private global area)。對于SGA區域內的內存來(lái)說(shuō)，是共享的全局的，在UNIX 上，必須為oracle 設置共享內存段（可以是一個(gè)或者多個(gè)），因為oracle 在UNIX上是多進(jìn)程；而在WINDOWS上oracle是單進(jìn)程（多個(gè)線(xiàn)程），所以不用設置共享內存段。PGA是屬于進(jìn)程（線(xiàn)程）私有的區域。在oracle 使用共享服務(wù)器模式下（MTS）,PGA中的一部分，也就是UGA會(huì )被放入共享內存large_pool_size 中。
對于SGA部分，我們通過(guò)sqlplus 中查詢(xún)可以看到：
SQL> select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 454032
Variable Size 109051904
Database Buffers 385875968
Redo Buffers 667648
Fixed Size
Oracle 的不同平臺和不同版本下可能不一樣，但對于確定環(huán)境是一個(gè)固定的值，里面存儲了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。在這里要額外說(shuō)明一點(diǎn)的是，對于v$parameter、v$sgastat、v$sga查詢(xún)值可能不一樣。v$parameter 里面的值，是指用戶(hù)在初始化參數文件里面設置的值，v$sgastat是oracle 實(shí)際分配的日志緩沖區大?。ㄒ驗榫彌_區的分配值實(shí)際上是離散的，也不是以block 為最小單位進(jìn)行分配的），v$sga 里面查詢(xún)的值，是在oracle 分配了日志緩沖區后，為了保護日志緩沖區，設置了一些保護頁(yè)，通常我們會(huì )發(fā)現保護頁(yè)大小大約是11k(不同環(huán)境可能不一樣)。參考如下內容
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
關(guān)于各部分內存的作用，參考Oracle體系結構，在此不再敘述。
SGA的大小
那么我們現在來(lái)考察內存參數的設置。實(shí)際上，對于特定的環(huán)境，總是存在著(zhù)不同的最優(yōu)設置的，沒(méi)有任何一種普遍適用的最優(yōu)方案。但為什么在這里我們還要來(lái)談設置這個(gè)話(huà)題呢，那僅僅是出于一個(gè)目的，避免過(guò)度的犯錯誤。事實(shí)上，在任何一個(gè)生產(chǎn)系統正式投入使用之前，我們不擁有任何系統運行信息讓我們去調整，這樣就只有兩種可能，一是根據文檔推薦設置，另外一種就是根據經(jīng)驗設置。相對來(lái)說(shuō)，根據經(jīng)驗的設置比根據文檔的設置要可靠一些。尤其是那些24*7 的系統，我們更要減少錯誤的發(fā)生。那么我們嘗試去了解不同的系統不同的應用的具體設置情況，從而提供一個(gè)參照信息給大家。
為了得出一個(gè)參照設置，我們就必須假定一個(gè)參照環(huán)境。以下所有設置我們基于這樣一個(gè)假定，那就是硬件服務(wù)器上只考慮存在操作系統和數據庫，在這個(gè)單一的環(huán)境中，我們來(lái)考慮內存的設置。
在設置參數之前呢，我們首先要問(wèn)自己幾個(gè)問(wèn)題
一：物理內存多大
二：操作系統估計需要使用多少內存
三：數據庫是使用文件系統還是裸設備
四：有多少并發(fā)連接
五：應用是OLTP 類(lèi)型還是OLAP 類(lèi)型
根據這幾個(gè)問(wèn)題的答案，我們可以粗略地為系統估計一下內存設置。那我們現在來(lái)逐個(gè)問(wèn)題地討論，首先物理內存多大是最容易回答的一個(gè)問(wèn)題，然后操作系統估計使用多少內存呢？從經(jīng)驗上看，不會(huì )太多，通常應該在200M 以?xún)龋ú话罅窟M(jìn)程PCB）。
接下來(lái)我們要探討一個(gè)重要的問(wèn)題，那就是關(guān)于文件系統和裸設備的問(wèn)題，這往往容易被我們所忽略。操作系統對于文件系統，使用了大量的buffer 來(lái)緩存操作系統塊。這樣當數據庫獲取數據塊的時(shí)候，雖然SGA 中沒(méi)有命中，但卻實(shí)際上可能是從操作系統的文件緩存中獲取的。而假如數據庫和操作系統支持異步IO，則實(shí)際上當數據庫寫(xiě)進(jìn)程DBWR寫(xiě)磁盤(pán)時(shí)，操作系統在文件緩存中標記該塊為延遲寫(xiě)，等到真正地寫(xiě)入磁盤(pán)之后，操作系統才通知DBWR寫(xiě)磁盤(pán)完成。對于這部分文件緩存，所需要的內存可能比較大，作為保守的估計，我們應該考慮在0.2——0.3 倍內存大小。但是如果我們使用的是裸設備，則不考慮這部分緩存的問(wèn)題。這樣的情況下SGA就有調大的機會(huì )。
本文地址：【伊甸網(wǎng)】http://www.edenw.com/tech/devdeloper/database/2010-12-06/6696.html
關(guān)于數據庫有多少并發(fā)連接，這實(shí)際上關(guān)系到PGA 的大?。∕TS 下還有large_pool_size）。事實(shí)上這個(gè)問(wèn)題應該說(shuō)還跟OLTP 類(lèi)型或者OLAP 類(lèi)型相關(guān)。對于OLTP類(lèi)型oracle 傾向于可使用MTS,對于OLAP 類(lèi)型使用獨立模式，同時(shí)OLAP 還可能涉及到大量的排序操作的查詢(xún)，這些都影響到我們內存的使用。那么所有的問(wèn)題綜合起來(lái)，實(shí)際上主要反映在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
在這部分內存中我們最關(guān)注的通常是sort_area_size，這是當查詢(xún)需要排序的時(shí)候，數據庫會(huì )話(huà)將使用這部分內存進(jìn)行排序，當內存大小不足的時(shí)候，使用臨時(shí)表空間進(jìn)行磁盤(pán)排序。由于磁盤(pán)排序效率和內存排序效率相差好幾個(gè)數量級，所以這個(gè)參數的設置很重要。這四個(gè)參數都是針對會(huì )話(huà)進(jìn)行設置的，是單個(gè)會(huì )話(huà)使用的內存的大小，而不是整個(gè)數據庫使用的。偶爾會(huì )看見(jiàn)有人誤解了這個(gè)參數以為是整個(gè)數據庫使用的大小，這是極其嚴重的錯誤。假如設置了MTS，則UGA被分配在large_pool_size，也就是說(shuō)放在了共享內存里面，不同進(jìn)程（線(xiàn)程）之間可以共享這部分內存。在這個(gè)基礎上，我們假設數據庫存在并發(fā)執行serverprocess 為100 個(gè)，根據上面我們4 個(gè)參數在oracle8.1.7 下的默認值，我們來(lái)計算獨立模式下PGA 的大致大小。由于會(huì )話(huà)并不會(huì )經(jīng)常使用create_bitmap_area_size 、bitmap_merge_area_size，所以我們通常不對四個(gè)參數求和。在考慮到除這四個(gè)參數外會(huì )話(huà)所保存的變量、堆棧等信息，我們估計為2M，則200 個(gè)進(jìn)程最大可能使用200M 的PGA。
現在，根據上面這些假定，我們來(lái)看SGA 實(shí)際能達到多少內存。在1G 的內存的服務(wù)器上，我們能分配給SGA 的內存大約為400—500M。若是2G 的內存，大約可以分到1G的內存給SGA，8G 的內存可以分到5G的內存給SGA。當然我們這里是以默認的排序部分內存sort_area_size=64k進(jìn)行衡量的，假如我們需要調大該參數和hash_area_size等參數，然后我們應該根據并發(fā)的進(jìn)程的數量，來(lái)衡量考慮這個(gè)問(wèn)題。
事實(shí)上，通常我們更習慣通過(guò)直觀(guān)的公式化來(lái)表達這樣的問(wèn)題：
OS使用內存+SGA+并發(fā)執行進(jìn)程數*(sort_area_size+hash_ara_size+2M) < 0.7*總內存
(公式是死的，系統是活的，實(shí)際應用的調整不必框公式，這不過(guò)是一個(gè)參考建議)
在我們的實(shí)際應用中，假如采用的是裸設備，我們可適當的增大SGA(如果需要的話(huà))。由于目前幾乎所有的操作系統都使用虛擬緩存，所以實(shí)際上如果就算SGA 設置的比較大也不會(huì )導致錯誤，而是可能出現頻繁的內存頁(yè)的換入與換出(page in/out)。在操作系統一級如果觀(guān)察到這個(gè)現象，那么我們就需要調整內存的設置。
SGA內參數設置
Log_buffer
對于日志緩沖區的大小設置，通常我覺(jué)得沒(méi)有過(guò)多的建議，因為參考LGWR寫(xiě)的觸發(fā)條件之后，我們會(huì )發(fā)現通常超過(guò)3M意義不是很大。作為一個(gè)正式系統，可能考慮先設置這部分為log_buffer=1—3M 大小，然后針對具體情況再調整。
Large_pool_size
對于大緩沖池的設置，假如不使用MTS，建議在20—30M 足夠了。這部分主要用來(lái)保存并行查詢(xún)時(shí)候的一些信息，還有就是RMAN 在備份的時(shí)候可能會(huì )使用到。如果設置了MTS，則由于UGA部分要移入這里，則需要具體根據server process數量和相關(guān)會(huì )話(huà)內存參數的設置來(lái)綜合考慮這部分大小的設置。
Java_pool_size
假如數據庫沒(méi)有使用JAVA，我們通常認為保留10—20M大小足夠。事實(shí)上可以更少，甚至最少只需要32k，但具體跟安裝數據庫的時(shí)候的組件相關(guān)(比如http server)。
Shared_pool_size
這是迄今為止最具有爭議的一部分內存設置。按照很多文檔的描述，這部分內容應該幾乎和數據緩沖區差不多大小。但實(shí)際上情況卻不是這樣的。首先我們要考究一個(gè)問(wèn)題，那就是這部分內存的作用，它是為了緩存已經(jīng)被解析過(guò)的SQL，而使其能被重用，不再解析。這樣做的原因是因為，對于一個(gè)新的SQL（shared_pool 里面不存在已經(jīng)解析的可用的相同的SQL），數據庫將執行硬解析，這是一個(gè)很消耗資源的過(guò)程。而若已經(jīng)存在，則進(jìn)行的僅僅是軟分析（在共享池中尋找相同SQL），這樣消耗的資源大大減少。所以我們期望能多共享一些SQL，并且如果該參數設置不夠大，經(jīng)常會(huì )出現ora-04031錯誤，表示為了解析新的SQL，沒(méi)有可用的足夠大的連續空閑空間，這樣自然我們期望該參數能大一些。但是該參數的增大，卻也有負面的影響，因為需要維護共享的結構，內存的增大也會(huì )使得SQL 的老化的代價(jià)更高，帶來(lái)大量的管理的開(kāi)銷(xiāo)，所有這些可能會(huì )導致CPU 的嚴重問(wèn)題。
在一個(gè)充分使用綁定變量的比較大的系統中，shared_pool_size 的開(kāi)銷(xiāo)通常應該維持在300M 以?xún)?。除非系統使用了大量的存儲過(guò)程、函數、包，比如oracle erp 這樣的應用，可能會(huì )達到500M甚至更高。于是我們假定一個(gè)1G內存的系統，可能考慮設置該參數為100M，2G 的系統考慮設置為150M,8G 的系統可以考慮設置為200—300M。
對于一個(gè)沒(méi)有充分使用或者沒(méi)有使用綁定變量系統，這可能給我們帶來(lái)一個(gè)嚴重的問(wèn)題。所謂沒(méi)有使用bind var 的SQL，我們稱(chēng)為L(cháng)iteral SQL。也就是比如這樣的兩句SQL我們認為是不同的SQL,需要進(jìn)行2 次硬解析：
select * from EMP where name = ‘TOM’;
select * from EMP where name = ‘JERRY’;
假如把’TOM’ 和 ‘JERRY’ 換做變量V，那就是使用了bind var，我們可以認為是同樣的SQL 從而能很好地共享。共享SQL 本來(lái)就是shared_pool_size 這部分內存存在的本意，oracle的目的也在于此，而我們不使用bind var 就是違背了oracle 的初衷，這樣將給我們的系統帶來(lái)嚴重的問(wèn)題。當然，如果通過(guò)在操作系統監控，沒(méi)有發(fā)現嚴重的cpu問(wèn)題，我們如果發(fā)現該共享池命中率不高可以適當的增加shred_pool_size。但是通常我們不主張這部分內存超過(guò)800M（特殊情況下可以更大）。
事實(shí)上，可能的話(huà)我們甚至要想辦法避免軟分析，這在不同的程序語(yǔ)言中實(shí)現方式有差異。我們也可能通過(guò)設置session_cached_cursors 參數來(lái)獲得幫助（這將增大PGA）。
Data buffer
現在我們來(lái)談數據緩沖區，在確定了SGA 的大小并分配完了前面部分的內存后，其余的，都分配給這部分內存。通常，在允許的情況下，我們都嘗試使得這部分內存更大。這部分內存的作用主要是緩存DB BLOCK，減少甚至避免從磁盤(pán)上獲取數據，在8i中通常是由db_block_buffers*db_block_size 來(lái)決定大小的。如果我們設置了buffer_pool_keep 和buffer_pool_recycle，則應該加上后面這兩部分內存的大小。
9i下參數的變化
Oracle的版本的更新，總是伴隨著(zhù)參數的變化，并且越來(lái)越趨向于使得參數的設置更簡(jiǎn)單，因為復雜的參數設置使得DBA們經(jīng)常焦頭爛額。關(guān)于內存這部分的變化，我們可以考察下面的參數。事實(shí)上在9i中數據庫本身可以給出一組適合當前運行系統的SGA相關(guān)部分的參數調整值（參考V$DB_CACHE_ADVICE、V$SHARED_POOL_ADVICE），關(guān)于PGA也有相關(guān)視圖V$PGA_TARGET_ADVICE 等。
Data buffer
9i 中保留了8i中的參數，如設置了新的參數，則忽略舊的參數。9i中用db_cache_size來(lái)取代db_block_buffers ， 用db_keep_cache_size 取代buffer_pool_keep, 用db_recycle_cache_size 取代buffer_pool_recycle；這里要注意9i 中設置的是實(shí)際的緩存大小而不再是塊的數量。另外9i新增加了db_nk_cache_size，這是為了支持在同一個(gè)數據庫中使用不同的塊大小而設置的。對于不同的表空間，可以定義不同的數據塊的大小，而緩沖區的定義則依靠該參數的支持。其中n 可以為2、4、6、8、16 等不同的值。在這里順便提及的一個(gè)參數就是db_block_lru_latches，該參數在9i中已經(jīng)成為了保留參數，不推薦手工設置。
PGA
在9i 里面這部分也有了很大的變化。在獨立模式下，9i已經(jīng)不再主張使用原來(lái)的UGA相關(guān)的參數設置，而代之以新的參數。假如workarea_size_policy=AUTO（缺?。?，則所有的會(huì )話(huà)的UGA 共用一大塊內存，該內存由pga_aggregate_target 設置。在我們根據前面介紹的方法評估了所有進(jìn)程可能使用的最大PGA 內存之后，我們可以通過(guò)在初始化參數中設置這個(gè)參數，從而不再關(guān)心其他”*_area_size” 參數。
SGA_MAX_SIZE
在9i中若設置了SGA_MAX_SIZE，則在總和小于等于這個(gè)值內，可以動(dòng)態(tài)的調整數據緩沖區和共享池的大小
SQL> show parameters sga_max_size
NAME TYPE VALUE
------------------------------------ ------- -------------
sga_max_size unknown 193752940
SQL> alter system set db_cache_size = 30000000;
System altered.
SQL> alter system set shared_pool_size = 20480000;
System altered.
Lock_SGA = TRUE 的問(wèn)題
由于幾乎所有的操作系統都支持虛擬內存，所以即使我們使用的內存小于物理內存，也
不能避免操作系統將SGA 換到虛擬內存（SWAP）。所以我們可以嘗試使得SGA 鎖定在物理內存中不被換到虛擬內存中，這樣減少頁(yè)面的換入和換出，從而提高性能。但在這里遺憾的是，windows 是無(wú)法避免這種情況的。下面我們來(lái)參考在不同的幾個(gè)系統下怎么實(shí)現lock_sga
AIX 5L（AIX 4.3.3 以上）
logon aix as root
cd /usr/samples/kernel
./vmtune (信息如下) v_pingshm已經(jīng)是1
./vmtune -S 1
然后oracle用戶(hù)修改initSID.ora 中 lock_sga = true
重新啟動(dòng)數據庫
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用戶(hù)修改initSID.ora中lock_sga=true
重新啟動(dòng)數據庫
SOLARIS (solaris2.6以上)
8i版本以上數據庫默認使用隱藏參數 use_ism = true ，自動(dòng)鎖定SGA于內存中,不用設置lock_sga, 如果設置 lock_sga =true 使用非 root 用戶(hù)啟動(dòng)數據庫將返回錯誤。
WINDOWS
不能設置lock_sga=true,可以通過(guò)設置pre_page_sga=true,使得數據庫啟動(dòng)的時(shí)候就把所有內存頁(yè)裝載，這樣可能起到一定的作用。
關(guān)于內存參數的調整
關(guān)于參數調整，是oracle的復雜性的一個(gè)具體體現。通常來(lái)講，我們更傾向于讓客戶(hù)做statspack 報告，然后告訴我們os 監控的狀況，在這些的信息的基礎上，再向客戶(hù)索取具體
的詳細信息以診斷問(wèn)題的所在。系統的調整，現在我們通常采用從等待事件入手的方法。因為一個(gè)系統感覺(jué)到慢，必然是在某個(gè)環(huán)節上出現等待，那么我們從等待最多的事件入手逐步診斷并解決問(wèn)題。
對于內存的調整，相對來(lái)說(shuō)簡(jiǎn)單一些，我們首先可以針對數據緩沖區的大小來(lái)看。首先觀(guān)察命中率。
數據緩沖區命中率
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
通常如果發(fā)現命中率低于90%,則應該調整應用可可以考慮是否增大數據緩沖區共享池的命中率
SQL> select sum(pinhits-reloads)/sum(pins)*100 "hit radio" from v$librarycache;
hit radio
----------
99.809291
假如共享池的命中率低于95%,就要考慮調整應用（通常是沒(méi)使用bind var ）或者增加內存。
關(guān)于排序部分
SQL> select name,value from v$sysstat where name like '%sort%';
NAME VALUE
---------------------------------------------------------------- -------
sorts (memory) 67935
sorts (disk) 1
sorts (rows) 7070
假如我們發(fā)現sorts (disk)/ (sorts (memory)+ sorts (disk))的比例過(guò)高，則通常意味著(zhù)sort_area_size 部分內存較小，可考慮調整相應的參數。
關(guān)于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 的比例超過(guò)1%我們就可以考慮增大log_buffer
通常來(lái)說(shuō)，內存的調整的焦點(diǎn)就集中在這幾個(gè)方面，更多更詳細的內容，建議從statspack入手來(lái)一步一步調整。最后關(guān)于內存的調整，再強調這一點(diǎn)，一定要結合操作系統來(lái)衡量，任何理論都必須要實(shí)踐來(lái)檢驗。在操作系統中觀(guān)察page in/out 狀況，發(fā)現問(wèn)題嚴重，應該考慮調小SGA。
32bit 和64bit 的問(wèn)題
對于oracle 來(lái)說(shuō)，存在著(zhù)32bit與64bit的問(wèn)題。這個(gè)問(wèn)題影響到的主要是SGA的大小。在32bit的數據庫下，通常oracle只能使用不超過(guò)1.7G的內存，即使我們擁有12G的內存，但是我們卻只能使用1.7G，這是一個(gè)莫大的遺憾。假如我們安裝64bit的數據庫,我們就可以使用很大的內存，我們幾乎不可能達到上限。但是64bit 的數據庫必須安裝在64bit 的操作系統上，可惜目前windows 上只能安裝32bit的數據庫，我們通過(guò)下面的方式可以查看數據庫是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
但是在特定的操作系統下，可能提供了一定的手段，使得我們可以使用超過(guò)1.7G 的內存，達到2G 以上甚至更多。在這里我們針對不同的平臺下的具體實(shí)現方式做一個(gè)總結。
聯(lián)系客服
