oracle內(nèi)存分配和調(diào)優(yōu)

1、一直都想總結(jié)一下oracle 內(nèi)存調(diào)整方面的知識，最近正好優(yōu)化一個數(shù)據(jù)庫內(nèi)存參數(shù)，查找一些資料并且google 很多下?，F(xiàn)在記錄下來，做下備份。一、概述：oracle 的內(nèi)存可以按照共享和私有的角度分為系統(tǒng)全局區(qū)和進(jìn)程全局區(qū)，也就是sga 和 pga(processglobalareaorprivateglobalarea) 。對于 sga 區(qū)域內(nèi)的內(nèi)存來說，是共享的全局的，在unix 上，必須為 oracle 設(shè)置共享內(nèi)存段（可以是一個或者多個），因為 oracle 在 unix 上是多進(jìn)程；而在windows 上 oracle 是單進(jìn)程（多個線程），所以不用設(shè)置共享內(nèi)存段。pga是屬于進(jìn)程

2、（線程）私有的區(qū)域。在oracle 使用共享 服務(wù)器 模式下（ mts）,pga中的一部分，也就是uga會被放入共享內(nèi)存 large_pool_size中。發(fā)張圖 oracle 內(nèi)存架構(gòu)組成，按照圖上面的顯示可以一目了然關(guān)鍵的參數(shù)和參數(shù)名稱：對于 sga部分，我們通過 sqlplus 中查詢可以看到：sqlselect*fromv$sga; namevalue - fixedsize454032 variablesize109051904 databasebuffers385875968 redobuffers667648 fixedsize: oracle 的不同平臺和不同版本下可能不一樣，

3、但對于確定環(huán)境是一個固定的值，里面存儲了 sga各部分組件的信息，可以看作引導(dǎo)建立sga的區(qū)域。variablesize: 包含了 shared_pool_size、java_pool_size、large_pool_size 等內(nèi)存設(shè)置databasebuffers: 指數(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_

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

5、2.1log_buffer 對于日志緩沖區(qū)的大小設(shè)置，通常我覺得沒有過多的建議，因為參考lgwr 寫的觸發(fā)條件之后，我們會發(fā)現(xiàn)通常超過3m 意義不是很大。作為一個正式系統(tǒng)，可能考慮先設(shè)置這部分為log_buffer=35m 大小，然后針對具體情況再調(diào)整。log_buffer 是 redolog 的 buffer 。因此在這里必須要了解redolog 的觸發(fā)事件（ lgwr）1、當(dāng) redologbuffer 的容量達(dá)到 1/3 2、設(shè)定的寫 redolog 時間間隔到達(dá)，一般為3 秒鐘。3、redologbuffer 中重做日志容量到達(dá)1m 4、在 dbwn 將緩沖區(qū)中的數(shù)據(jù)寫入到數(shù)據(jù)文件之前

6、5、每一次 commit- 提交事務(wù)。上面的結(jié)論可以換句話說1、log_buffer 中的內(nèi)容滿 1/3 ，緩存刷新一次。2、最長間隔 3 秒鐘，緩存刷新一次3、log_buffer 中的數(shù)據(jù)到達(dá) 1m，緩存刷新一次。4、每次提交一個 “ 事務(wù)” ，緩存刷新一次2.2large_pool_size 對于大緩沖池的設(shè)置，假如不使用mts，建議在 2030m 足夠了。這部分主要用來保存并行查詢時候的一些信息，還有就是rman 在備份的時候可能會使用到。如果設(shè)置了 mts，則由于 uga部分要移入這里，則需要具體根據(jù)serverprocess數(shù)量和相關(guān)會話內(nèi)存參數(shù)的設(shè)置來綜合考慮這部分大小的設(shè)置。2

7、.3java_pool_size 假如數(shù)據(jù)庫沒有使用java，我們通常認(rèn)為保留1020m 大小足夠。事實上可以更少，甚至最少只需要32k，但具體跟安裝數(shù)據(jù)庫的時候的組件相關(guān)(比如httpserver) 。2.4shared_pool_size shared_pool_size的開銷通常應(yīng)該維持在300m 以內(nèi)。除非系統(tǒng)使用了大量的存儲過程、函數(shù)、包，比如 oracleerp 這樣的應(yīng)用，可能會達(dá)到500m 甚至更高。于是我們假定一個1g內(nèi)存的系統(tǒng)，可能考慮設(shè)置該參數(shù)為 100m ，2g 的系統(tǒng)考慮設(shè)置為150m,8g 的系統(tǒng)可以考慮設(shè)置為200300m 2.5sga_max_size sga區(qū)

8、包括了各種緩沖區(qū)和內(nèi)存池，而大部分都可以通過特定的參數(shù)來指定他們的大小。但是，作為一個昂貴的資源，一個系統(tǒng)的物理內(nèi)存大小是有限。盡管對于cpu 的內(nèi)存尋址來說，是無需關(guān)系實際的物理內(nèi)存大小的（關(guān)于這一點，后面會做詳細(xì)的介紹），但是過多的使用虛擬內(nèi)存導(dǎo)致pagein/out ，會大大影響系統(tǒng)的性能，甚至可能會導(dǎo)致系統(tǒng)crash。所以需要有一個參數(shù)來控制sga使用虛擬內(nèi)存的最大大小，這個參數(shù)就是sga_max_size 。當(dāng)實例啟動后，各個內(nèi)存區(qū)只分配實例所需要的最小大小，在隨后的運行過程中，再根據(jù)需要擴(kuò)展他們的大小，而他們的總和大小受到了sga_max_size 的限制。對于 oltp 系統(tǒng)，參

9、考 : 系統(tǒng)內(nèi)存sga_max_size值1g400-500m 2g1g 4g2500m 8g5g 2.6pre_page_sga oracle 實例啟動時，會只載入各個內(nèi)存區(qū)最小的大小。而其他sga內(nèi)存只作為虛擬內(nèi)存分配，只有當(dāng)進(jìn)程 touch 到相應(yīng)的頁時，才會置換到物理內(nèi)存中。但我們也許希望實例一啟動后，所有 sga 都分配到物理內(nèi)存。這時就可以通過設(shè)置pre_page_sga 參數(shù)來達(dá)到目的了。這個參數(shù)的默認(rèn)值為 false，即不將全部 sga置入物理內(nèi)存中。當(dāng)設(shè)置為true時，實例啟動會將全部 sga置入物理內(nèi)存中。它可以使實例啟動達(dá)到它的最大性能狀態(tài)，但是，啟動時間也會更長（因為為

10、了使所有 sga 都置入物理內(nèi)存中， oracle 進(jìn)程需要 touch 所有的 sga頁）。2.7lock_sga 為了保證 sga都被鎖定在物理內(nèi)存中，而不必頁入/ 頁出，可以通過參數(shù)lock_sga 來控制。這個參數(shù)默認(rèn)值為false，當(dāng)指定為 true時，可以將全部 sga都鎖定在物理內(nèi)存中。當(dāng)然，有些系統(tǒng)不支持內(nèi)存鎖定，這個參數(shù)也就無效了。2.8sga_target 這里要介紹的時 oracle10g 中引入的一個非常重要的參數(shù)。在10g 之前， sga的各個內(nèi)存區(qū)的大小都需要通過各自的參數(shù)指定，并且都無法超過參數(shù)指定大小的值，盡管他們之和可能并沒有達(dá)到 sga的最大限制。此外，一旦

11、分配后，各個區(qū)的內(nèi)存只能給本區(qū)使用，相互之間是不能共享的。拿 sga中兩個最重要的內(nèi)存區(qū)buffercache 和 sharedpool 來說，它們兩個對實例的性能影響最大，但是就有這樣的矛盾存在：在內(nèi)存資源有限的情況下，某些時候數(shù)據(jù)被cache 的需求非常大，為了提高 bufferhit ，就需要增加 buffercache，但由于 sga有限，只能從其他區(qū)“ 搶” 過來如縮小 sharedpool，增加 buffercache；而有時又有大塊的plsql代碼被解析駐入內(nèi)存中，導(dǎo)致sharedpool 不足，甚至出現(xiàn) 4031 錯誤，又需要擴(kuò)大sharedpool，這時可能又需要人為干預(yù)，從

12、buffercache 中將內(nèi)存奪回來。有了這個新的特性后， sga中的這種內(nèi)存矛盾就迎刃而解了。這一特性被稱為自動共享內(nèi)存管理（automaticsharedmemorymanagementasmm ）。而控制這一特性的，也就僅僅是這一個參數(shù) sga_targe 。設(shè)置這個參數(shù)后，你就不需要為每個內(nèi)存區(qū)來指定大小了。sga_target 指定了sga可以使用的最大內(nèi)存大小，而 sga中各個內(nèi)存的大小由oracle 自行控制，不需要人為指定。oracle 可以隨時調(diào)節(jié)各個區(qū)域的大小，使之達(dá)到系統(tǒng)性能最佳狀態(tài)的個最合理大小，并且控制他們之和在sga_target 指定的值之內(nèi)。一旦給 sga_t

13、arget 指定值后（默認(rèn)為 0，即沒有啟動 asmm），就自動啟動了asmm 特性。三、 oracle 內(nèi)存調(diào)優(yōu)辦法當(dāng)項目的生產(chǎn)環(huán)境出現(xiàn)性能問題，我們?nèi)绾瓮ㄟ^判斷那些參數(shù)需要調(diào)整呢？3.1 檢查 oracle 實例的 librarycache命中率 : 標(biāo)準(zhǔn):一般是大于 99檢查方式 : select1-(sum(reloads)/sum(pins)librarycachehitratiofromv$librarycache; 處理措施 : 如果 librarycachehitratio的值低于 99%，應(yīng)調(diào)高 shared_pool_size 的大小。通過sqlplus 連接數(shù)據(jù)庫執(zhí)行如下

14、命令，調(diào)整shared_pool_size 的大小：sqlaltersystemflushshared_pool; sqlaltersystemsetshared_pool_size= 設(shè)定值 scope=spfile; 3.2 檢查 oracle 實例的 databuffer（數(shù)據(jù)緩沖區(qū)）命中率: 標(biāo)準(zhǔn):一般是大于 90%檢查方式 : select1-(phy.value/(cur.value+con.value)hitratio fromv$sysstatcur,v$sysstatcon,v$sysstatphy =dbblockgets =

15、consistentgets =physicalreads; 處理措施 :如果 hitratio 的值低于 90%，應(yīng)調(diào)高 db_cache_size的大小。通過 sqlplus 連接數(shù)據(jù)庫執(zhí)行如下命令，調(diào)整 db_cache_size的大小sqlaltersystemsetdb_cache_size= 設(shè)定值 scope=spfile 3.3 檢查 oracle 實例的 dictionarycache命中率 : 標(biāo)準(zhǔn):一般是大于 95%檢查方式 : select1-(sum(getmisses)/sum(gets)datadictionaryhitratiofromv$

16、rowcache; 處理措施 :如果 datadictionaryhitratio的值低于 95%，應(yīng)調(diào)高 shared_pool_size的大小。通過 sqlplus 連接數(shù)據(jù)庫執(zhí)行如下命令，調(diào)整shared_pool_size 的大?。簊qlaltersystemflushshared_pool; sqlaltersystemsetshared_pool_size= 設(shè)定值 scope=spfile; 3.4 檢查 oracle 實例的 logbuffer 命中率 : 標(biāo)準(zhǔn):一般是小于 1%檢查方式 : select(req.value*5000)/entries.valueratio f

17、romv$sysstatreq,v$sysstatentries =redologspacerequests =redoentries; 處理措施 :如果 ratio 高于 1，應(yīng)調(diào)高 log_buffer 的大小。通過 sqlplus 連接數(shù)據(jù)庫執(zhí)行如下命令，調(diào)整 log_buffer 的大小：sqlaltersystemsetlog_buffer= 設(shè)定值 scope=spfile; 3.5 檢查 undo_retention: 標(biāo)準(zhǔn):undo_retention的值必須大于 max(maxquerylen)的值檢查方式 : colundo_retentionform