歸納整理oracle學習之內存分配和調優

本篇文章給大家帶來了關于Oracle的相關知識，其中主要介紹了內存分配和調優的相關問題，oracle 的內存可以按照共享和私有的角度分為系統全局區和進程全局區，也就是SGA和PGA，下面就一起來看一下，希望對大家有幫助。

推薦教程：《Oracle》

一、概述

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 中。??

? ? ? ?發張圖oracle內存架構組成，按照圖上面的顯示可以一目了然關鍵的參數和參數名稱：? ?

? ? ? ?? ?

對于 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(不同環境可能不一樣)。? ? ? ?? ?

二、SGA內參數及設置：??? ? ? ? ? ??? ?

2.1 ?Log_buffer?? ? ?

對于日志緩沖區的大小設置，通常我覺得沒有過多的建議，因為參考 LGWR 寫的觸發條件之后，我們會發現通常超過 3M 意義不是很大。作為一個正式系統， ? ?

可能考慮先設置這部分為 log_buffer=3—5M ?大小，然后針對具體情況再調整。? ? ?

log_buffer是Redo log的buffer。 ? ? ?

因此在這里必須要了解Redo Log的觸發事件（LGWR）? ? ?

1、當redo log buffer的容量達到1/3? ? ?

2、設定的寫redo log時間間隔到達，一般為3秒鐘。? ? ?

3、redo log buffer中重做日志容量到達1M? ? ?

4、在DBWn將緩沖區中的數據寫入到數據文件之前? ? ?

5、每一次commit–提交事務。? ? ?

上面的結論可以換句話說? ? ?

1、log_buffer中的內容滿1/3，緩存刷新一次。? ? ?

2、最長間隔3秒鐘，緩存刷新一次? ? ?

3、log_buffer中的數據到達1M，緩存刷新一次。? ? ?

4、每次提交一個“事務”，緩存刷新一次? ? ?

2.2 Large_pool_size?? ? ?

對于大緩沖池的設置，假如不使用 MTS，建議在 20—30M ?足夠了。這部分主要用來保存并行查詢時候的一些信息，還有就是 RMAN 在備份的時候可能會使用到。 ? ?

如果設置了MTS，則由于 UGA 部分要移入這里，則需要具體根據 server process 數量和相關會話內存參數的設置來綜合考慮這部分大小的設置。? ? ?

2.3 ?Java_pool_size? ? ?

假如數據庫沒有使用 JAVA，我們通常認為保留 10—20M 大小足夠。事實上可以更少，甚至最少只需要 32k，但具體跟安裝數據庫的時候的組件相關(比如 http server)。? ? ?

2.4 ?Shared_pool_size? ?

Shared_pool_size的開銷通常應該維持在300M 以內。除非系統使用了大量的存儲過程、函數、包， ?

比如 oracle erp 這樣的應用，可能會達到 500M 甚至更高。于是我們假定一個 1G 內存的系統，可能考慮 ?

設置該參數為 100M，2G 的系統考慮設置為 150M,8G 的系統可以考慮設置為 200—300M? ?

2.5SGA_MAX_SIZE? ?

SGA區包括了各種緩沖區和內存池，而大部分都可以通過特定的參數來指定他們的大小。但是，作為一個昂貴的資源，一個系統的物理內存大小是有限。 ? ?

盡管對于CPU的內存尋址來說，是無需關系實際的物理內存大小的（關于這一點，后面會做詳細的介紹），但是過多的使用虛擬內存導致page in/out， ? ?

會大大影響系統的性能，甚至可能會導致系統crash。所以需要有一個參數來控制SGA使用虛擬內存的最大大小，這個參數就是SGA_MAX_SIZE。當實例啟動后， ? ?

各個內存區只分配實例所需要的最小大小，在隨后的運行過程中，再根據需要擴展他們的大小，而他們的總和大小受到了SGA_MAX_SIZE的限制。? ? ?

對于OLTP系統，參考:? ?

2.6?PRE_PAGE_SGA? ?

oracle實例啟動時，會只載入各個內存區最小的大小。而其他SGA內存只作為虛擬內存分配， ?

只有當進程touch到相應的頁時，才會置換到物理內存中。但我們也許希望實例一啟動后，所有SGA ?

都分配到物理內存。這時就可以通過設置PRE_PAGE_SGA參數來達到目的了。這個參數的默認值 ?

為FALSE，即不將全部SGA置入物理內存中。當設置為TRUE時，實例啟動會將全部SGA置入物理 ?

內存中。它可以使實例啟動達到它的最大性能狀態，但是，啟動時間也會更長（因為為了使所有SGA ?

都置入物理內存中，oracle進程需要touch所有的SGA頁）。? ?

2.7 LOCK_SGA? ?

為了保證SGA都被鎖定在物理內存中，而不必頁入/頁出，可以通過參數LOCK_SGA來控制。 ?

這個參數默認值為FALSE，當指定為TRUE時，可以將全部SGA都鎖定在物理內存中。當然， ?

有些系統不支持內存鎖定，這個參數也就無效了。? ?

2.8 SGA_TARGET? ?

這里要介紹的時Oracle10g中引入的一個非常重要的參數。在10g之前，SGA的各個內存區 ?

的大小都需要通過各自的參數指定，并且都無法超過參數指定大小的值，盡管他們之和可能并 ?

沒有達到SGA的最大限制。此外，一旦分配后，各個區的內存只能給本區使用，相互之間是不能共享的。 ?

拿SGA中兩個最重要的內存區Buffer Cache和Shared Pool來說，它們兩個對實例的性能影響最大， ?

但是就有這樣的矛盾存在：在內存資源有限的情況下，某些時候數據被cache的需求非常大， ?

為了提高buffer hit，就需要增加Buffer Cache，但由于SGA有限，只能從其他區“搶”過來——如縮小Shared Pool， ?

增加Buffer Cache；而有時又有大塊的PLSQL代碼被解析駐入內存中，導致Shared Pool不足， ?

甚至出現4031錯誤，又需要擴大Shared Pool，這時可能又需要人為干預，從Buffer Cache中將內存奪回來。? ?

? ? ? ??有了這個新的特性后，SGA中的這種內存矛盾就迎刃而解了。這一特性被稱為自動共享內存管理 ?

（Automatic Shared Memory Management ASMM）。而控制這一特性的，也就僅僅是這一個參數SGA_TARGE。 ?

設置這個參數后，你就不需要為每個內存區來指定大小了。SGA_TARGET指定了SGA可以使用的最大內存大小， ?

而SGA中各個內存的大小由Oracle自行控制，不需要人為指定。Oracle可以隨時調節各個區域的大小，使之達到系 ?

統性能最佳狀態的個最合理大小，并且控制他們之和在SGA_TARGET指定的值之內。一旦給SGA_TARGET指定值后 ?

（默認為0，即沒有啟動ASMM），就自動啟動了ASMM特性。? ?

三、oracle 內存調優辦法? ?

當項目的生產環境出現性能問題，我們如何通過判斷那些參數需要調整呢？? ?

3.1 檢查ORACLE實例的Library Cache命中率:? ?

?標準:一般是大于99％ ? ?? ? ? ? ? ? ? ?檢查方式:

select?1-(sum(reloads)/sum(pins))?"Library?cache?Hit?Ratio"?from?v$librarycache;

?處理措施: ? ?如果Library cache Hit Ratio的值低于99%，應調高shared_pool_size的大小。通過sqlplus連接數據庫執行如下命令，調整shared_pool_size的大小： ? ?

SQL>alter?system?flush?shared_pool; ???? SQL>alter?system?set?shared_pool_size=設定值?scope=spfile;

3.2 檢查ORACLE實例的Data Buffer（數據緩沖區）命中率:? ?

?標準:一般是大于90% ? ??檢查方式: ? ?

select?1?-?(phy.value?/?(cur.value?+?con.value))?"HIT?RATIO" ????from?v$sysstat?cur,?v$sysstat?con,?v$sysstat?phy ??? ?where?cur.name?=?'db?block?gets' ????and?con.name?=?'consistent?gets' ?????and?phy.name?=?'physical?reads';

處理措施: ? ?

如果HIT RATIO的值低于90%，應調高db_cache_size的大小。通過sqlplus連接數據庫執行如下命令， ? ?

調整db_cache_size的大小 ? ?

SQL>alter?system?set?db_cache_size=設定值?scope=spfile

3.3?檢查ORACLE實例的Dictionary Cache命中率:? ? ?

標準:一般是大于95%? ? ? ?

檢查方式:? ? ? ?

select?1?-?(sum(getmisses)?/?sum(gets))?"Data?Dictionary?Hit?Ratio"??????? ??from?v$rowcache;

處理措施:? ? ? ?

如果Data Dictionary Hit Ratio的值低于95%，應調高shared_pool_size的大小。通過sqlplus連接數據庫執行如下命令，調整shared_pool_size的大小：? ? ? ?

SQL>alter?system?flush?shared_pool;??????? SQL>alter?system?set?shared_pool_size=設定值?scope=spfile;

3.4 ?檢查ORACLE實例的Log Buffer命中率:? ? ?

標準:一般是小于1%? ? ? ?

檢查方式:? ? ??

select?(req.value?*?5000)?/?entries.value?"Ratio"??????? ??from?v$sysstat?req,?v$sysstat?entries??????? ?where?req.name?=?'redo?log?space?requests'??????? ???and?entries.name?=?'redo?entries';

處理措施:? ? ? ?

如果Ratio高于1％，應調高log_buffer的大小。通過sqlplus連接數據庫執行如下命令，調整log_buffer的大小：? ? ? ?

SQL>alter?system?set?log_buffer=設定值?scope=spfile;

3.5?檢查undo_retention:? ? ?

標準:undo_retention 的值必須大于max(maxquerylen)的值? ? ? ?

檢查方式:? ? ? ?

col?undo_retention?format?a30 ?????? select?value?"undo_retention"?from?v$parameter?where?name='undo_retention'; ?????? select?max(maxquerylen)?From?v$undostat?Where?begin_time>sysdate-(1/4);

處理措施:? ? ? ?

如果不滿足要求，需要調高undo_retention 的值。通過sqlplus 連接數據庫執行如下命令，調整undo_retention 的大小：? ? ? ?

SQL>alter?system?set?undo_retention=?設定值?scope=spfile;

注： ? ?

32bit ?和 64bit ?的問題 ? ?

對于 oracle 來說，存在著 32bit 與 64bit 的問題。這個問題影響到的主要是 SGA 的大小。在 32bit 的數據庫下，通常 oracle 只能使用不超過 1.7G 的內存，即使我們擁有 12G 的內存，但是我們卻只能使用 1.7G，這是一個莫大的遺憾。假如我們安裝 64bit 的數據庫,我們就可以使用很大的內存，我們幾乎不可能達到上限。但是 64bit 的數據庫必須安裝在 64bit 的操作系統上，可惜目前 windows 上只能安裝 32bit 的數據庫，我們通過下面的方式可以查看數據庫是 32bit 還是 64bit? ? ?

? ? 但是在特定的操作系統下，可能提供了一定的手段，使得我們可以使用超過 1.7G 的內存，達到 2G 以上甚至更多。 ? ?

推薦教程：《Oracle》

以上就是歸納整理