oracle記憶體配置與調整__oracle

l       前言<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

對於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等記憶體設定和用於管理資料緩衝區等記憶體結構的hash table、塊頭資訊(比如x$bh消耗記憶體)等

 

Database Buffers

    指資料緩衝區，在8i中包含default pool、buffer_pool_keep、buffer_pool_recycle三部分記憶體。在9i中包含db_cache_size、db_keep_cache_size、db_recycle_cache_size、db_nk_cache_size。這裡要注意在8i中三部分記憶體總和為db_block_buffers*db_block_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，則100個進程最大可能使用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，我們稱為<