oracle 記憶體分配和調優總結

http://blog.itpub.net/12272958/viewspace-696834/
http://lqding.blog.51cto.com/9123978/1693659

一、概述：

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系統，參考:

系統記憶體	SGA_MAX_SIZE值
1G	400-500M
2G	1G
4G	2500M
8G	5G

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 的資料庫. 但是在特定的作業系統下，可能提供了一定的手段，使得我們可以使用超過 1.7G 的記憶體，達到 2G 以上甚至更多。

=================補充=======================
Oracle資料庫包含了如下基本記憶體元件

System global area (SGA)

The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.

Program global area (PGA)

A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. The PGA is created by Oracle Database when an Oracle process is started.

One PGA exists for each and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.

User Global Area (UGA)

The UGA is memory associated with a user session.

Software code areas

Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from user programs—a more exclusive or protected location.

記憶體管理

Oracle依賴於記憶體相關的初始化引數來控制記憶體的管理。

記憶體管理有如下三個選項

Automatic memory management

You specify the target size for instance memory. The database instance automatically tunes to the target memory size, redistributing memory as needed between the SGA and the instance PGA.

Automatic shared memory management

This management mode is partially automated. You set a target size for the SGA and then have the option of setting an aggregate target size for the PGA or managing PGA work areas individually.

Manual memory management

Instead of setting the total memory size, you set many initialization parameters to manage components of the SGA and instance PGA individually.

UGA概覽

UGA是會話記憶體，用來儲存會話變數例如登入資訊，已經資料庫會話需要的其他資訊。

當PL/SQL包載入進記憶體時，UGA中包含了package state，也就是呼叫PL/SQL時指定的變數值。

PGA概覽

PGA緩衝區，則主要是為了某個使用者程式所服務的。這個記憶體區不是共享的，只有這個使用者的服務程式本身才能夠訪問它自己的PGA區。做個形象的比喻，SGA就好像是作業系統上的一個共享資料夾，不同使用者可以以此為平臺進行資料方面的交流。而PGA就好像是作業系統上的一個私有資料夾，只有這個資料夾的所有者才能夠進行訪問，其他使用者都不能夠訪問。雖然程式快取區不向其他使用者的程式開放，但是這個記憶體區仍然肩負著一些重要的使命，如資料排序、許可權控制等等都離不開這個記憶體區。

PGA元件

私有SQL區包含了繫結變數值和執行時期記憶體結構資訊等資料。每一個執行SQL語句的會話都有一個塊私有SQL區。一個遊標的私有SQL區又分為兩個生命週期不同的區：

永久區，包含繫結變數資訊。當遊標關閉時被釋放。

執行區，當執行結束時釋放。

Cursor

A cursor is a name or handle to a specific private SQL area

SGA包含如下元件

Database Buffer Cache

Redo Log Buffer

Shared Pool

Large Pool

Java Pool

Streams Pool

Fixed SGA

Buffer cache

Buffer Cache是SGA區中專門用於存放從資料檔案中讀取的的資料塊的區域。Oracle程式如果發現需要訪問的資料塊已經在buffer cache中，就直接讀寫記憶體中的相應區域，而無需讀取資料檔案，從而大大提高效能。Buffer cache對於所有oracle程式都是共享的，即能被所有oracle程式訪問。

Buffer的大小和資料塊一樣。

Buffer cache按照型別分為3個池

Default pool

This pool is the location where blocks are normally cached. Unless you manually configure separate pools, the default pool is the only buffer pool.

Keep pool

This pool is intended for blocks that were accessed frequently, but which aged out of the default pool because of lack of space. The goal of the keep buffer pool is to retain objects in memory, thus avoiding I/O operations.

Recycle pool

This pool is intended for blocks that are used infrequently. A recycle pool prevent objects from consuming unnecessary space in the cache.

Oracle還提供了非標準塊大小的buffer cache。如果你建立的表空間指定的塊大小為非資料庫塊大小，那麼將使用這些buffer cache來快取資料塊。

首先Oracle 以每個資料塊的檔案號、塊號、型別做hash運算，得到hash值。

對於hash值相同的塊，放在一個Hash Bucket中。

因為buffer的大小畢竟有限，buffer中的資料塊需要根據一定的規則提出記憶體。

Oracle採用了LRU演算法維護一個LRU連結串列，來決定哪些資料塊被淘汰。

通用的淘汰演算法如下

Oracle改進了LRU演算法，引入了Touch count概念、以及LRU連結串列分為熱端頭和冷端頭。

Touch count：

用來記錄資料塊訪問的頻繁度，此數值在記憶體中不受保護，多個程式可以同時修改它。這個值並不是精準的表示塊被訪問的次數，只是一種趨勢。3秒內無論多少使用者，訪問多少次塊。此值加1.

當資料塊第一次被放到buffer中，Oracle將其放置在冷端的頭部。

如果buffer已經沒有空閒空間，那麼如何淘汰資料塊呢？Oracle從LRU的冷端尾部掃描資料塊，當發現資料塊的Touch count大於等於2時，將資料塊移動到熱端頭部，並將Touch count置為0 。當Oracle發現Touch count小於2時，則淘汰該資料塊。

當資料塊被修改了，我們把這個塊稱之為髒塊。髒塊在寫入磁碟前，是不會被踢出buffer的。

如果LRU中的髒塊比較多，每次申請新的空間時，都要掃描很多髒塊，但是又不能被淘汰。效率很低。

為此Oracle因為了髒LRU連結串列。專門用來記錄髒資料塊。

當塊被修改，並不會馬上從LRU連結串列中移動到LRUW中。只有當Oracle需要淘汰資料塊時，才會去掃描LRU連結串列，此時發現塊為髒塊，將資料塊移動到LRUW連結串列中。

檢查點連結串列

透過上面的描述，我們知道髒塊在LRU和LRUW連結串列中都有。那麼當dbwr寫資料時，這兩個連結串列都要掃描。首先效率比較低，並且無法保證先修改的資料塊先被寫入磁碟。

為此Oracle引入了檢查點佇列，該佇列按照資料塊第一次被修改順序將髒塊連結到一起。

dbwr寫髒塊時，只需讀取檢查點佇列即可。

並且每個資料塊與記錄了日誌條目的位置

redo log buffer

使用者程式將redo entries 複製到redo log buffer中。LGWR負責將其寫到磁碟中。

Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by DML or DDL operations. Database recovery applies redo entries to data files to reconstruct lost changes.

共享池

Library Cache：

主要存放shared curosr（SQL）和PLSQL物件（function，procedure，trigger）的資訊，以及這些物件所依賴的table，index，view等物件的資訊。

Private SQL Areas與Shared SQL Area的關係

資料字典快取

用來快取系統資料字典表的內容，與普通表的快取不同，普通表以塊為單位快取到buffer cache中。而資料字典快取以行為單位，快取到shared pool中的data dictionary cache中。

Server result cache

用來快取sql或者plsql的執行結果。

大池：

The large pool can provide large memory allocations for the following:

UGA for the shared server and the interface (used where transactions interact with multiple databases)

Message buffers used in the parallel execution of statements

Buffers for Recovery Manager (RMAN) I/O slaves

配置記憶體

Oracle提供了兩個初始化引數用來配置記憶體自動管理

MEMORY_TARGET：sga+pga記憶體之和，Oracle自動分配SGA和PGA的大小。

MEMORY_MAX_TARGET：MEMORY_TARGET可以設定大小的上限。

SGA自動記憶體管理

設定初始化引數SGA_TARGET為非0值，並且將STATISTICS_LEVEL的值設定為TYPICAL或者ALL.

PGA自動記憶體管理

PGA_AGGREGATE_TARGET設定為非0值。

如果workarea_size_policy為auto則sort_area_size,hash_area_size等引數設定被忽略，如果workarea_size_policy為manual，則sort_area_size,hash_area_size等引數設定生效。

也可以手工配置其他各個記憶體池的大小。當配置了記憶體自動管理時，有配置了具體池的大小，那麼該配置為自動記憶體分配時的最小大小。

檢視記憶體情況

The following views provide information about dynamic resize operations:

V$MEMORY_CURRENT_RESIZE_OPS displays information about memory resize operations (both automatic and manual) which are currently in progress.

V$MEMORY_DYNAMIC_COMPONENTS displays information about the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.

V$MEMORY_RESIZE_OPS displays information about the last 800 completed memory resize operations (both automatic and manual). This does not include in-progress operations.

V$MEMORY_TARGET_ADVICE displays tuning advice for the MEMORY_TARGET initialization parameter.

V$SGA_CURRENT_RESIZE_OPS displays information about SGA resize operations that are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component.

V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations. This does not include any operations currently in progress.

V$SGA_DYNAMIC_COMPONENTS displays information about the dynamic components in SGA. This view summarizes information based on all completed SGA resize operations that occurred after startup.

V$SGA_DYNAMIC_FREE_MEMORY displays information about the amount of SGA memory available for future dynamic SGA resize operations.

oracle 記憶體分配和調優 總結

相關文章

oracle 記憶體分配和調優總結