Oracle 內(nèi)存分配與調(diào)整

1、Oracle 內(nèi)存分配與調(diào)整一、前言對(duì)于oracle 的內(nèi)存的管理,截止到9iR2,都是相當(dāng)重要的環(huán)節(jié),管理不善,將可能給數(shù)據(jù)庫帶來嚴(yán)重的性能問題。下面我們將一步一步就內(nèi)存管理的各個(gè)方面進(jìn)行探討。二、概述oracle 的內(nèi)存可以按照共享和私有的角度分為系統(tǒng)全局區(qū)和進(jìn)程全局區(qū),也就是SGA 和PGA(process global area or private global area 。對(duì)于SGA 區(qū)域內(nèi)的內(nèi)存來說,是共享的全局的,在UNIX 上,必須為oracle 設(shè)置共享內(nèi)存段(可以是一個(gè)或者多個(gè),因?yàn)閛racle 在UNIX 上是多進(jìn)程;而在WINDOWS 上oracle 是單進(jìn)程(多個(gè)線

2、程,所以不用設(shè)置共享內(nèi)存段。PGA 是屬于進(jìn)程(線程私有的區(qū)域。在oracle 使用共享服務(wù)器模式下(MTS,PGA 中的一部分,也就是UGA 會(huì)被放入共享內(nèi)存large_pool_size 中。對(duì)于SGA 部分,我們通過sqlplus 中查詢可以看到:SQL> select * from v$sga;NAME V ALUEFixed Size 454032Variable Size 109051904Database Buffers 385875968Redo Buffers 667648其中:Fixed Size:oracle 的不同平臺(tái)和不同版本下可能不一樣,但對(duì)于確定環(huán)境是一個(gè)固

3、定的值,里面存儲(chǔ)了SGA 各部分組件的信息,可以看作引導(dǎo)建立SGA 的區(qū)域。Variable Size:包含了shared_pool_size 、java_pool_size 、large_pool_size 等內(nèi)存設(shè)置Database Buffers:指數(shù)據(jù)緩沖區(qū),在8i 中包含db_block_buffer*db_block_size 、buffer_pool_keep 、buffer_pool_recycle 三部分內(nèi)存。在9i 中包含db_cache_size 、db_keep_cache_size 、db_recycle_cache_size 、db_nk_cache_size 。R

4、edo Buffers:指日志緩沖區(qū),log_buffer 。在這里要額外說明一點(diǎn)的是,對(duì)于v$parameter 、v$sgastat 、v$sga 查詢值可能不一樣。v$parameter 里面的值,是指用戶在初始化參數(shù)文件里面設(shè)置的值,v$sgastat 是oracle 實(shí)際分配的日志緩沖區(qū)大小(因?yàn)榫彌_區(qū)的分配值實(shí)際上是離散的,也不是以block 為最小單位進(jìn)行分配的, v$sga 里面查詢的值,是在oracle 分配了日志緩沖區(qū)后,為了保護(hù)日志緩沖區(qū),設(shè)置了一些保護(hù)頁,通常我們會(huì)發(fā)現(xiàn)保護(hù)頁大小大約是11k(不同環(huán)境可能不一樣。參考如下內(nèi)容SQL> select substr(n

5、ame,1,10 name,substr(value,1,10 value2 from v$parameter where name = 'log_buffer'NAME V ALUElog_buffer 524288SQL> select * from v$sgastat ;POOL NAME BYTESfixed_sga 454032buffer_cache 385875968log_buffer 656384SQL> select * from v$sga;NAME V ALUEFixed Size 454032Variable Size 109051904D

6、atabase Buffers 385875968Redo Buffers 667648關(guān)于各部分內(nèi)存的作用,參考o(jì)racle 體系結(jié)構(gòu),在此不再敘述。l 、SGA 的大小那么我們現(xiàn)在來考察內(nèi)存參數(shù)的設(shè)置。實(shí)際上,對(duì)于特定的環(huán)境,總是存在著不同的最優(yōu)設(shè)置的,沒有任何一種普遍適用的最優(yōu)方案。但為什么在這里我們還要來談設(shè)置這個(gè)話題呢,那僅僅是出于一個(gè)目的,避免過度的犯錯(cuò)誤。事實(shí)上,在任何一個(gè)生產(chǎn)系統(tǒng)正式投入使用之前,我們不擁有任何系統(tǒng)運(yùn)行信息讓我們?nèi)フ{(diào)整,這樣就只有兩種可能,一是根據(jù)文檔推薦設(shè)置,另外一種就是根據(jù)經(jīng)驗(yàn)設(shè)置。相對(duì)來說,根據(jù)經(jīng)驗(yàn)的設(shè)置比根據(jù)文檔的設(shè)置要可靠一些。尤其是那些24*7 的系

7、統(tǒng),我們更要減少錯(cuò)誤的發(fā)生。那么我們嘗試去了解不同的系統(tǒng)不同的應(yīng)用的具體設(shè)置情況,從而提供一個(gè)參照信息給大家。為了得出一個(gè)參照設(shè)置,我們就必須假定一個(gè)參照環(huán)境。以下所有設(shè)置我們基于這樣一個(gè)假定,那就是硬件服務(wù)器上只考慮存在操作系統(tǒng)和數(shù)據(jù)庫,在這個(gè)單一的環(huán)境中,我們來考慮內(nèi)存的設(shè)置。在設(shè)置參數(shù)之前呢,我們首先要問自己幾個(gè)問題1物理內(nèi)存多大2操作系統(tǒng)估計(jì)需要使用多少內(nèi)存3數(shù)據(jù)庫是使用文件系統(tǒng)還是裸設(shè)備4有多少并發(fā)連接5應(yīng)用是OLTP 類型還是OLAP 類型根據(jù)這幾個(gè)問題的答案,我們可以粗略地為系統(tǒng)估計(jì)一下內(nèi)存設(shè)置。那我們現(xiàn)在來逐個(gè)問題地討論,首先物理內(nèi)存多大是最容易回答的一個(gè)問題,然后操作系統(tǒng)估計(jì)

8、使用多少內(nèi)存呢?從經(jīng)驗(yàn)上看,不會(huì)太多,通常應(yīng)該在200M 以內(nèi)(不包含大量進(jìn)程PCB。接下來我們要探討一個(gè)重要的問題,那就是關(guān)于文件系統(tǒng)和裸設(shè)備的問題,這往往容易被我們所忽略。操作系統(tǒng)對(duì)于文件系統(tǒng),使用了大量的buffer 來緩存操作系統(tǒng)塊。這樣當(dāng)數(shù)據(jù)庫獲取數(shù)據(jù)塊的時(shí)候,雖然SGA 中沒有命中,但卻實(shí)際上可能是從操作系統(tǒng)的文件緩存中獲取的。而假如數(shù)據(jù)庫和操作系統(tǒng)支持異步IO,則實(shí)際上當(dāng)數(shù)據(jù)庫寫進(jìn)程DBWR 寫磁盤時(shí),操作系統(tǒng)在文件緩存中標(biāo)記該塊為延遲寫,等到真正地寫入磁盤之后,操作系統(tǒng)才通知DBWR 寫磁盤完成。對(duì)于這部分文件緩存,所需要的內(nèi)存可能比較大,作為保守的估計(jì),我們應(yīng)該考慮在0.20

9、.3 倍內(nèi)存大小。但是如果我們使用的是裸設(shè)備,則不考慮這部分緩存的問題。這樣的情況下SGA 就有調(diào)大的機(jī)會(huì)。關(guān)于數(shù)據(jù)庫有多少并發(fā)連接,這實(shí)際上關(guān)系到PGA 的大小(MTS 下還有l(wèi)arge_pool_size。事實(shí)上這個(gè)問題應(yīng)該說還跟OLTP 類型或者OLAP 類型相關(guān)。對(duì)于OLTP 類型oracle 傾向于可使用MTS,對(duì)于OLAP 類型使用獨(dú)立模式,同時(shí)OLAP 還可能涉及到大量的排序操作的查詢,這些都影響到我們內(nèi)存的使用。那么所有的問題綜合起來,實(shí)際上主要反映在UGA 的大小上。UGA 主要包含以下部分內(nèi)存設(shè)置SQL> show parameters area_sizeNAME T

10、YPE V ALUEbitmap_merge_area_size integer 1048576create_bitmap_area_size integer 8388608hash_area_size integer 131072sort_area_size integer 65536SQL>在這部分內(nèi)存中我們最關(guān)注的通常是sort_area_size,這是當(dāng)查詢需要排序的時(shí)候,數(shù)據(jù)庫會(huì)話將使用這部分內(nèi)存進(jìn)行排序,當(dāng)內(nèi)存大小不足的時(shí)候,使用臨時(shí)表空間進(jìn)行磁盤排序。由于磁盤排序效率和內(nèi)存排序效率相差好幾個(gè)數(shù)量級(jí),所以這個(gè)參數(shù)的設(shè)置很重要。這四個(gè)參數(shù)都是針對(duì)會(huì)話進(jìn)行設(shè)置的,是單個(gè)會(huì)話使用的

11、內(nèi)存的大小,而不是整個(gè)數(shù)據(jù)庫使用的。偶爾會(huì)看見有人誤解了這個(gè)參數(shù)以為是整個(gè)數(shù)據(jù)庫使用的大小,這是極其嚴(yán)重的錯(cuò)誤。假如設(shè)置了MTS,則UGA 被分配在large_pool_size,也就是說放在了共享內(nèi)存里面,不同進(jìn)程(線程之間可以共享這部分內(nèi)存。在這個(gè)基礎(chǔ)上,我們假設(shè)數(shù)據(jù)庫存在并發(fā)執(zhí)行server process 為100 個(gè),根據(jù)上面我們4 個(gè)參數(shù)在oracle8.1.7 下的默認(rèn)值,我們來計(jì)算獨(dú)立模式下PGA 的大致大小。由于會(huì)話并不會(huì)經(jīng)常使用create_bitmap_area_size 、bitmap_merge_area_size,所以我們通常不對(duì)四個(gè)參數(shù)求和。在考慮到除這四個(gè)參數(shù)外

12、會(huì)話所保存的變量、堆棧等信息,我們估計(jì)為2M,則200 個(gè)進(jìn)程最大可能使用200M 的PGA ?，F(xiàn)在,根據(jù)上面這些假定,我們來看SGA 實(shí)際能達(dá)到多少內(nèi)存。在1G 的內(nèi)存的服務(wù)器上,我們能分配給SGA 的內(nèi)存大約為400500M 。若是2G 的內(nèi)存,大約可以分到1G 的內(nèi)存給SGA,8G 的內(nèi)存可以分到5G 的內(nèi)存給SGA 。當(dāng)然我們這里是以默認(rèn)的排序部分內(nèi)存sort_area_size=64k 進(jìn)行衡量的,假如我們需要調(diào)大該參數(shù)和hash_area_size 等參數(shù),然后我們應(yīng)該根據(jù)并發(fā)的進(jìn)程的數(shù)量,來衡量考慮這個(gè)問題。事實(shí)上,通常我們更習(xí)慣通過直觀的公式化來表達(dá)這樣的問題:OS 使用內(nèi)存+

13、SGA+并發(fā)執(zhí)行進(jìn)程數(shù)*(sort_area_size+hash_ara_size+2M <0.7* 總內(nèi)存(公式是死的,系統(tǒng)是活的,實(shí)際應(yīng)用的調(diào)整不必框公式,這不過是一個(gè)參考建議 在我們的實(shí)際應(yīng)用中,假如采用的是裸設(shè)備,我們可適當(dāng)?shù)脑龃骃GA( 如果需要的話。由于目前幾乎所有的操作系統(tǒng)都使用虛擬緩存,所以實(shí)際上如果就算SGA 設(shè)置的比較大也不會(huì)導(dǎo)致錯(cuò)誤,而是可能出現(xiàn)頻繁的內(nèi)存頁的換入與換出(page in/out 。在操作系統(tǒng)一級(jí)如果觀察到這個(gè)現(xiàn)象,那么我們就需要調(diào)整內(nèi)存的設(shè)置。l SGA 內(nèi)參數(shù)設(shè)置Log_buffer對(duì)于日志緩沖區(qū)的大小設(shè)置,通常我覺得沒有過多的建議,因?yàn)閰⒖糒GW

14、R 寫的觸發(fā)條件之后,我們會(huì)發(fā)現(xiàn)通常超過3M 意義不是很大。作為一個(gè)正式系統(tǒng),可能考慮先設(shè)置這部分為log_buffer=13M 大小,然后針對(duì)具體情況再調(diào)整。Large_pool_size 對(duì)于大緩沖池的設(shè)置,假如不使用MTS,建議在2030M 足夠了。這部分主要用來保存并行查詢時(shí)候的一些信息,還有就是RMAN 在備份的時(shí)候可能會(huì)使用到。如果設(shè)置了MTS,則由于UGA 部分要移入這里,則需要具體根據(jù)server process 數(shù)量和相關(guān)會(huì)話內(nèi)存參數(shù)的設(shè)置來綜合考慮這部分大小的設(shè)置。Java_pool_size假如數(shù)據(jù)庫沒有使用JA V A,我們通常認(rèn)為保留1020M 大小足夠。事實(shí)上可以更

15、少,甚至最少只需要32k,但具體跟安裝數(shù)據(jù)庫的時(shí)候的組件相關(guān)(比如http server 。shared_pool_size這是迄今為止最具有爭(zhēng)議的一部分內(nèi)存設(shè)置。按照很多文檔的描述,這部分內(nèi)容應(yīng)該幾乎和數(shù)據(jù)緩沖區(qū)差不多大小。但實(shí)際上情況卻不是這樣的。首先我們要考究一個(gè)問題,那就是這部分內(nèi)存的作用,它是為了緩存已經(jīng)被解析過的SQL,而使其能被重用,不再解析。這樣做的原因是因?yàn)?對(duì)于一個(gè)新的SQL (shared_pool 里面不存在已經(jīng)解析的可用的相同的SQL,數(shù)據(jù)庫將執(zhí)行硬解析,這是一個(gè)很消耗資源的過程。而若已經(jīng)存在,則進(jìn)行的僅僅是軟分析(在共享池中尋找相同SQL,這樣消耗的資源大大減少。所

16、以我們期望能多共享一些SQL,并且如果該參數(shù)設(shè)置不夠大,經(jīng)常會(huì)出現(xiàn)ora-04031 錯(cuò)誤,表示為了解析新的SQL,沒有可用的足夠大的連續(xù)空閑空間,這樣自然我們期望該參數(shù)能大一些。但是該參數(shù)的增大,卻也有負(fù)面的影響,因?yàn)樾枰S護(hù)共享的結(jié)構(gòu),內(nèi)存的增大也會(huì)使得SQL 的老化的代價(jià)更高,帶來大量的管理的開銷,所有這些可能會(huì)導(dǎo)致CPU 的嚴(yán)重問題。在一個(gè)充分使用綁定變量的比較大的系統(tǒng)中,shared_pool_size 的開銷通常應(yīng)該維持在300M 以內(nèi)。除非系統(tǒng)使用了大量的存儲(chǔ)過程、函數(shù)、包,比如oracle erp 這樣的應(yīng)用,可能會(huì)達(dá)到500M 甚至更高。于是我們假定一個(gè)1G 內(nèi)存的系統(tǒng),可能

17、考慮設(shè)置該參數(shù)為100M,2G 的系統(tǒng)考慮設(shè)置為150M,8G 的系統(tǒng)可以考慮設(shè)置為200300M。對(duì)于一個(gè)沒有充分使用或者沒有使用綁定變量系統(tǒng),這可能給我們帶來一個(gè)嚴(yán)重的問題。所謂沒有使用bind var 的SQL,我們稱為L(zhǎng)iteral SQL 。也就是比如這樣的兩句SQL 我們認(rèn)為是不同的SQL,需要進(jìn)行2 次硬解析:select * from EMP where name = TOM;select * from EMP where name = JERRY;假如把TOM和JERRY換做變量V,那就是使用了bind var,我們可以認(rèn)為是同樣的SQL 從而能很好地共享。共享SQL 本來就

18、是shared_pool_size 這部分內(nèi)存存在的本意,oracle 的目的也在于此,而我們不使用bind var 就是違背了oracle 的初衷,這樣將給我們的系統(tǒng)帶來嚴(yán)重的問題。當(dāng)然,如果通過在操作系統(tǒng)監(jiān)控,沒有發(fā)現(xiàn)嚴(yán)重的cpu 問題,我們?nèi)绻l(fā)現(xiàn)該共享池命中率不高可以適當(dāng)?shù)脑黾觭hred_pool_size 。但是通常我們不主張這部分內(nèi)存超過800M (特殊情況下可以更大。事實(shí)上,可能的話我們甚至要想辦法避免軟分析,這在不同的程序語言中實(shí)現(xiàn)方式有差異。我們也可能通過設(shè)置session_cached_cursors 參數(shù)來獲得幫助(這將增大PGA。Data buffer現(xiàn)在我們來談數(shù)據(jù)緩

19、沖區(qū),在確定了SGA 的大小并分配完了前面部分的內(nèi)存后,其余的,都分配給這部分內(nèi)存。通常,在允許的情況下,我們都嘗試使得這部分內(nèi)存更大。這部分內(nèi)存的作用主要是緩存DB BLOCK,減少甚至避免從磁盤上獲取數(shù)據(jù),在8i 中通常是由db_block_buffers*db_block_size 來決定大小的。如果我們?cè)O(shè)置了buffer_pool_keep 和buffer_pool_recycle,則應(yīng)該加上后面這兩部分內(nèi)存的大小。9i 下參數(shù)的變化oracle 的版本的更新,總是伴隨著參數(shù)的變化,并且越來越趨向于使得參數(shù)的設(shè)置更簡(jiǎn)單,因?yàn)閺?fù)雜的參數(shù)設(shè)置使得DBA 們經(jīng)常焦頭爛額。關(guān)于內(nèi)存這部分的變化

20、,我們可以考察下面的參數(shù)。事實(shí)上在9i 中數(shù)據(jù)庫本身可以給出一組適合當(dāng)前運(yùn)行系統(tǒng)的SGA 相關(guān)部分的參數(shù)調(diào)整值(參考V$DB_CACHE_ADVICE 、V$SHARED_POOL_ADVICE,關(guān)于PGA 也有相關(guān)視圖V$PGA_TARGET_ADVICE 等。Data buffer9i 中保留了8i 中的參數(shù),如設(shè)置了新的參數(shù),則忽略舊的參數(shù)。9i 中用db_cache_size 來取代db_block_buffers ,用db_keep_cache_size 取代buffer_pool_keep, 用db_recycle_cache_size 取代buffer_pool_recycle;

21、這里要注意9i 中設(shè)置的是實(shí)際的緩存大小而不再是塊的數(shù)量。另外9i 新增加了db_nk_cache_size,這是為了支持在同一個(gè)數(shù)據(jù)庫中使用不同的塊大小而設(shè)置的。對(duì)于不同的表空間,可以定義不同的數(shù)據(jù)塊的大小,而緩沖區(qū)的定義則依靠該參數(shù)的支持。其中n 可以為2、4、6、8、16 等不同的值。在這里順便提及的一個(gè)參數(shù)就是db_block_lru_latches,該參數(shù)在9i 中已經(jīng)成為了保留參數(shù),不推薦手工設(shè)置。PGA在9i 里面這部分也有了很大的變化。在獨(dú)立模式下,9i 已經(jīng)不再主張使用原來的UGA 相關(guān)的參數(shù)設(shè)置,而代之以新的參數(shù)。假如workarea_size_policy=AUTO(缺省

22、,則所有的會(huì)話的UGA 共用一大塊內(nèi)存,該內(nèi)存由pga_aggregate_target 設(shè)置。在我們根據(jù)前面介紹的方法評(píng)估了所有進(jìn)程可能使用的最大PGA 內(nèi)存之后,我們可以通過在初始化參數(shù)中設(shè)置這個(gè)參數(shù),從而不再關(guān)心其他”*_area_size”參數(shù)。SGA_MAX_SIZE在9i 中若設(shè)置了SGA_MAX_SIZE,則在總和小于等于這個(gè)值內(nèi),可以動(dòng)態(tài)的調(diào)整數(shù)據(jù)緩沖區(qū)和共享池的大小SQL> show parameters sga_max_sizeNAME TYPE V ALUEsga_max_size unknown 193752940SQL>SQL> alter syst

23、em set db_cache_size = 30000000;System altered.SQL> alter system set shared_pool_size = 20480000;System altered.Lock_sga = true 的問題由于幾乎所有的操作系統(tǒng)都支持虛擬內(nèi)存,所以即使我們使用的內(nèi)存小于物理內(nèi)存,也不能避免操作系統(tǒng)將SGA 換到虛擬內(nèi)存(SWAP。所以我們可以嘗試使得SGA 鎖定在物理內(nèi)存中不被換到虛擬內(nèi)存中,這樣減少頁面的換入和換出,從而提高性能。但在這里遺憾的是,windows 是無法避免這種情況的。下面我們來參考在不同的幾個(gè)系統(tǒng)下怎么實(shí)現(xiàn)loc

24、k_sgaAIX 5L(AIX 4.3.3 以上logon aix as rootcd /usr/samples/kernel./vmtune (信息如下 v_pingshm已經(jīng)是1./vmtune -S 1然后oracle 用戶修改initSID.ora 中l(wèi)ock_sga = true重新啟動(dòng)數(shù)據(jù)庫HP UNIXRoot 身份登陸Create the file "/etc/privgroup": vi /etc/privgroupAdd line "dba MLOCK" to fileAs root, run the command "/e

25、tc/setprivgrp -f /etc/privgroup":$/etc/setprivgrp -f /etc/privgrouporacle 用戶修改initSID.ora 中l(wèi)ock_sga=true重新啟動(dòng)數(shù)據(jù)庫SOLARIS (solaris2.6 以上8i 版本以上數(shù)據(jù)庫默認(rèn)使用隱藏參數(shù)use_ism = true ,自動(dòng)鎖定SGA 于內(nèi)存中,不用設(shè)置lock_sga, 如果設(shè)置lock_sga =true 使用非root 用戶啟動(dòng)數(shù)據(jù)庫將返回錯(cuò)誤。WINDOWS 不能設(shè)置lock_sga=true, 可以通過設(shè)置pre_page_sga=true, 使得數(shù)據(jù)庫啟動(dòng)的時(shí)

26、候就把所有內(nèi)存頁裝載,這樣可能起到一定的作用。關(guān)于內(nèi)存參數(shù)的調(diào)整關(guān)于參數(shù)調(diào)整,是oracle 的復(fù)雜性的一個(gè)具體體現(xiàn)。通常來講,我們更傾向于讓客戶做statspack 報(bào)告,然后告訴我們os 監(jiān)控的狀況,在這些的信息的基礎(chǔ)上,再向客戶索取具體的詳細(xì)信息以診斷問題的所在。系統(tǒng)的調(diào)整,現(xiàn)在我們通常采用從等待事件入手的方法。因?yàn)橐粋€(gè)系統(tǒng)感覺到慢,必然是在某個(gè)環(huán)節(jié)上出現(xiàn)等待,那么我們從等待最多的事件入手逐步診斷并解決問題。對(duì)于內(nèi)存的調(diào)整,相對(duì)來說簡(jiǎn)單一些,我們首先可以針對(duì)數(shù)據(jù)緩沖區(qū)的大小來看。首先觀察命中率數(shù)據(jù)緩沖區(qū)命中率SQL> select value from v$sysstat wher

27、e name ='physical reads'V ALUE14764SQL> select value from v$sysstat where name ='physical reads direct'V ALUE50SQL> select value from v$sysstat where name ='physical reads direct (lob'V ALUESQL> select value from v$sysstat where name ='consistent gets'V ALUE1

28、67763SQL> select value from v$sysstat where name = 'db block gets'V ALUE14305這里命中率的計(jì)算應(yīng)該是令x = physical reads direct + physical reads direct (lob 命中率=100 -( physical reads -x / (consistent gets + db block gets -x*100 通常如果發(fā)現(xiàn)命中率低于90%,則應(yīng)該調(diào)整應(yīng)用可可以考慮是否增大數(shù)據(jù)緩沖區(qū)共享池的命中率SQL> select sum(pinhits-relo

29、ads/sum(pins*100 "hit radio" from v$librarycache;hit radio99.809291假如共享池的命中率低于95%,就要考慮調(diào)整應(yīng)用(通常是沒使用bind var 或者增加內(nèi)存關(guān)于排序部分SQL> select name,value from v$sysstat where name like '%sort%'NAME V ALUEsorts (memory 67935sorts (disk 1sorts (rows 7070SQL>假如我們發(fā)現(xiàn)sorts (disk/ (sorts (memory+ sorts (disk 的比例過高,則通常意味著sort_area_size 部分內(nèi)存較小,可考慮調(diào)整相應(yīng)的參數(shù)。關(guān)于log_bufferSQL> select name,value from v$sysstatwhere name in(