Oracle記憶體全面分析

http://blog.csdn.net/wanglipo/article/details/5733957

的記憶體配置與oracle效能息息相關。而且關於記憶體的錯誤（如4030、4031錯誤）都是十分令人頭疼的問題。可以說，關於記憶體的配置，是最影響Oracle效能的配置。記憶體還直接影響到其他兩個重要資源的消耗：CPU和IO。

首先，看看Oracle記憶體儲存的主要內容是什麼：
   *程式程式碼（PLSQL、Java）；
   *關於已經連線的會話的資訊，包括當前所有活動和非活動會話；
   *程式執行時必須的相關資訊，例如查詢計劃；
   *Oracle程式之間共享的資訊和相互交流的資訊，例如鎖；
   *那些被永久儲存在外圍儲存介質上，被cache在記憶體中的資料（如redo log條目，資料塊）。

此外，需要記住的一點是，Oracle的記憶體是與例項對應的。也就是說，一個例項就有一個獨立的記憶體結構。

先從Oracle記憶體的組成介紹。

1. Oracle的記憶體架構組成

Oracle的記憶體，從總體上講，可以分為兩大塊：共享部分（主要是SGA）和程式獨享部分（主要是PGA和UGA）。而這兩部分記憶體裡面，根據功能不同，還分為不同記憶體池（Pool）和記憶體區（Area）。下面就是Oracle記憶體構成框架圖：

下面分別介紹這兩塊記憶體區。

1.1. SGA（System Global Area）

SGA（System Global Area 系統全域性區域）是一組包含一個Oracle例項的資料和控制資訊的共享記憶體結構。這句話可以說是SGA的定義。雖然簡單，但其中闡述了SGA幾個很重要的特性：1、SGA的構成——資料和控制資訊，我們下面會詳細介紹；2、SGA是共享的，即當有多個使用者同時登入了這個例項，SGA中的資訊可以被它們同時訪問（當涉及到互斥的問題時，由latch和enquence控制）；3、一個SGA只服務於一個例項，也就是說，當一臺機器上有多個例項執行時，每個例項都有一個自己的SGA，儘管SGA來自於OS的共享記憶體區，但例項之間不能相互訪問對方的SGA區。

Oracle程式和一個SGA就構成了一個Oracle例項。當例項啟動時，Oracle會自動從系統中分配記憶體給SGA，而例項關閉時，會回收這些記憶體。下面就是當例項啟動後，顯示已經分配了SGA：

SQL> startup

ORACLE instance started.

Total System Global Area  289406976 bytes

Fixed Size                  1248576 bytes

Variable Size             117441216 bytes

Database Buffers          163577856 bytes

Redo Buffers                7139328 bytes

Database mounted.

Database opened.

SQL>

SGA區是可讀寫的。所有登入到例項的使用者都能讀取SGA中的資訊，而在oracle做執行操作時，服務程式會將修改的資訊寫入SGA區。

SGA主要包括了以下的：
*資料緩衝（Buffer Cache）
*重做日誌緩衝（Redo Log Buffer）
*共享池（Shared Pool）
*Java池（Java Pool）
*大池（Large Pool）
*流池（Streams Pool --- 10g以後才有）
*資料字典快取（Data Dictionary Cache）
*其他資訊（如資料庫和例項的狀態資訊）

最後的兩種記憶體資訊會被例項的後臺程式所訪問，它們在例項啟動後就固定在SGA中了，而且不會改變，所以這部分又稱為固定SGA（Fixed SGA）。這部分割槽域的大小一般小於100K。

此外，用於併發程式控制的鎖（latch）的資訊也包含在SGA區中。

Shared Pool、 Pool、Large Pool和Streams Pool這幾塊記憶體區的大小是隨著相應系統引數設定而改變的，所以有通稱為可變SGA（Variable SGA）。

1.1.1. SGA的重要引數和特性

在設定SGA時，有一些很重要的引數，它們設定正確與否，會直接影響到系統的整體效能。下面一一介紹他們：

· SGA_MAX_SIZE

SGA區包括了各種緩衝區和記憶體池，而大部分都可以透過特定的引數來指定他們的大小。但是，作為一個昂貴的資源，一個系統的實體記憶體大小是有限。儘管對於CPU的記憶體定址來說，是無需關係實際的實體記憶體大小的（關於這一點，後面會做詳細的介紹），但是過多的使用虛擬記憶體導致page in/out，會大大影響系統的效能，甚至可能會導致系統crash。所以需要有一個引數來控制SGA使用虛擬記憶體的最大大小，這個引數就是SGA_MAX_SIZE。

當例項啟動後，各個記憶體區只分配例項所需要的最小大小，在隨後的執行過程中，再根據需要擴充套件他們的大小，而他們的總和大小受到了SGA_MAX_SIZE的限制。

當試圖增加一個記憶體的大小，並且如果這個值導致所有記憶體區大小總和大於SGA_MAX_SIZE時，oracle會提示錯誤，不允許修改。

當然，如果在設定引數時，指定區域為spfile時（包括修改SGA_MAX_SIZE本身），是不會受到這個限制的。這樣就可能出現這樣的情況，在spfile中，SGA各個記憶體區設定大小總和大於SGA_MAX_SIZE。這時，oracle會如下處理：當例項再次啟動時，如果發現SGA各個記憶體總和大於SGA_MAX_SIZE，它會將SGA_MAX_SIZE的值修改為SGA各個記憶體區總和的值。

SGA所分配的是虛擬記憶體，但是，在我們配置SGA時，一定要使整個SGA區都在實體記憶體中，否則，會導致SGA頻繁的頁入/頁出，會極大影響系統效能。

對於OLTP系統，我個人建議可以如下配置SGA_MAX_SIZE（一般有經驗的DBA都會有自己的預設配置大小，你也可以透過一段時間的觀察、調整自己的系統來得到適合本系統的引數配置）：

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

SGA的實際大小可以透過以下公式估算：

SGA實際大小 = DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + STREAMS_POOL_SIZE（10g中的新記憶體池） + LOG_BUFFERS+11K(Redo Log Buffer的保護頁) + 1MB + 16M(SGA內部記憶體消耗，適合於9i及之前版本)

公式種涉及到的引數在下面的內容種會一一介紹。

· PRE_PAGE_SGA

我們前面提到，oracle例項啟動時，會只載入各個記憶體區最小的大小。而其他SGA記憶體只作為虛擬記憶體分配，只有當程式touch到相應的頁時，才會置換到實體記憶體中。但我們也許希望例項一啟動後，所有SGA都分配到實體記憶體。這時就可以透過設定PRE_PAGE_SGA引數來達到目的了。

這個引數的預設值為FALSE，即不將全部SGA置入實體記憶體中。當設定為TRUE時，例項啟動會將全部SGA置入實體記憶體中。它可以使例項啟動達到它的最大效能狀態，但是，啟動時間也會更長（因為為了使所有SGA都置入實體記憶體中，oracle程式需要touch所有的SGA頁）。

我們可以透過TopShow工具（本站原創工具，可在中下載）來觀察windows（Unix下的記憶體監控比較複雜，這裡暫不舉例）下引數修改前後的對比。

PRE_PAGE_SGA為FALSE：

SQL> show parameter sga

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 276M

sga_target                           big integer 276M

SQL> startup force

ORACLE instance started.

Total System Global Area  289406976 bytes

Fixed Size                  1248576 bytes

Variable Size             117441216 bytes

Database Buffers          163577856 bytes

Redo Buffers                7139328 bytes

Database mounted.

Database opened.

SQL>

啟動後，Oracle的記憶體情況

可以看到，例項啟動後，oracle佔用的實體記憶體只有168M，遠小於SGA的最大值288M（實際上，這部分實體記憶體中還有一部分程式的PGA和Oracle Service佔用的記憶體），而虛擬記憶體則為340M。

將PRE_PAGE_SGA修改為TRUE，重啟例項：

SQL> alter system set pre_page_sga=true scope=spfile;

System altered.

SQL> startup force

ORACLE instance started.

Total System Global Area  289406976 bytes

Fixed Size                  1248576 bytes

Variable Size             117441216 bytes

Database Buffers          163577856 bytes

Redo Buffers                7139328 bytes

Database mounted.

Database opened.

再觀察啟動後Oracle的記憶體分配情況：

這時看到，例項啟動後實體記憶體達到了最大343M，於虛擬記憶體相當。這時，oracle例項已經將所有SGA分配到實體記憶體。

當引數設定為TRUE時，不僅在例項啟動時，需要touch所有的SGA頁，並且由於每個oracle程式都會訪問SGA區，所以每當一個新程式啟動時（在Dedicated Server方式中，每個會話都會啟動一個Oracle程式），都會touch一遍該程式需要訪問的所有頁。因此，每個程式的啟動時間頁增長了。所以，這個引數的設定需要根據系統的應用情況來設定。

在這種情況下，程式啟動時間的長短就由系統記憶體的頁的大小來決定了。例如，SGA大小為100M，當頁的大小為4K時，程式啟動時需要訪問100000/4=25000個頁，而如果頁大小為4M時，程式只需要訪問100/4=25個頁。頁的大小是由作業系統指定的，並且是無法修改的。

但是，要記住一點：PRE_PAGA_SGA只是在啟動時將實體記憶體分配給SGA，但並不能保證系統在以後的執行過程不會將SGA中的某些頁置換到虛擬記憶體中，也就是說，儘管設定了這個引數，還是可能出現Page In/Out。如果需要保障SGA不被換出，就需要由另外一個引數LOCK_SGA來控制了。

· LOCK_SGA

上面提到，為了保證SGA都被鎖定在實體記憶體中，而不必頁入/頁出，可以透過引數LOCK_SGA來控制。這個引數默認值為FALSE，當指定為TRUE時，可以將全部SGA都鎖定在實體記憶體中。當然，有些系統不支援記憶體鎖定，這個引數也就無效了。

· 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特性。

設定了SGA_TARGET後，以下的SGA記憶體區就可以由ASMM來自動調整：

*共享池（Shared Pool）
*Java池（Java Pool）
*大池（Large Pool）
*資料快取區（Buffer Cache）
*流池（Streams Pool）

對於SGA_TARGET的限制，它的大小是不能超過SGA_MAX_SIZE的大小的。

SQL> show parameter sga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 276M

sga_target                           big integer 276M

SQL>

SQL> alter system set sga_target=280M;

alter system set sga_target=280M

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00823: Specified value of sga_target greater than sga_max_size

另外，當指定SGA_TARGET小於SGA_MAX_SIZE，例項重啟後，SGA_MAX_SIZE就自動變為和SGA_TARGET一樣的值了。

SQL> show parameter sga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 276M

sga_target                           big integer 276M

SQL> alter system set sga_target=252M;

System altered.

SQL> show parameter sga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 276M

sga_target                           big integer 252M

SQL> startup force

ORACLE instance started.

Total System Global Area  264241152 bytes

Fixed Size                  1248428 bytes

Variable Size             117441364 bytes

Database Buffers          138412032 bytes

Redo Buffers                7139328 bytes

Database mounted.

Database opened.

SQL> show parameter sga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 252M

sga_target                           big integer 252M

SQL>

對於SGA_TARGET，還有重要一點就是，它的值可以動態修改（在SGA_MAX_SIZE範圍內）。在10g之前，如果需要修改SGA的大小（即修改SGA_MAX_SIZE的值）需要重啟例項才能生效。當然，在10g中，修改SGA_MAX_SIZE的值還是需要重啟的。但是有了SGA_TARGET後，可以將SGA_MAX_SIZE設定偏大，再根據實際需要調整SGA_TARGET的值（我個人不推薦頻繁修改SGA的大小，SGA_TARGET在例項啟動時設定好，以後不要再修改）。

SGA_TARGET帶來一個重要的好處就是，能使SGA的利用率達到最佳，從而節省記憶體成本。因為ASMM啟動後，Oracle會自動根據需要調整各個區域的大小，大大減少了某些區域記憶體緊張，而某些區域又有記憶體空閒的矛盾情況出現。這也同時大大降低了出現4031錯誤的機率。

· use_indirect_data_buffers

這個引數是32位平臺使用擴充套件緩衝快取基址，以支援支援4GB多實體記憶體。設定此引數，可以使SGA突破在32位系統中的2G最大限制。64位平臺中，這個引數被忽略。

二.

1.1.2. 關於SGA的重要檢視

要了解和觀察SGA的使用情況，並且根據統計資料來處理問題和調整效能，主要有以下的幾個系統檢視。

· v$sga

這個檢視包括了SGA的的總體情況，只包含兩個欄位：name（SGA記憶體區名字）和value（記憶體區的值，單位為位元組）。它的結果和show sga的結果一致，顯示了SGA各個區的大小：

SQL> select * from v$sga;

NAME                      VALUE

-------------------- ----------

Fixed Size              1248428

Variable Size         117441364

Database Buffers      138412032

Redo Buffers            7139328

4 rows selected.

SQL> show sga

Total System Global Area  264241152 bytes

Fixed Size                  1248428 bytes

Variable Size             117441364 bytes

Database Buffers          138412032 bytes

Redo Buffers                7139328 bytes

SQL>

· v$sgastat

這個檢視比較重要。它記錄了關於sga的統計資訊。包含三個欄位：Name（SGA記憶體區的名字）；Bytes（記憶體區的大小，單位為位元組）；Pool（這段記憶體所屬的記憶體池）。

這個檢視尤其重要的是，它詳細記錄了個各個池（Pool）記憶體分配情況，對於定位4031錯誤有重要參考價值。

以下語句可以查詢Shared Pool空閒率：

 select to_number(v$parameter.value) value, v$sgastat.BYTES,
        (v$sgastat.bytes/v$parameter.value)*100 "percent free"
     from v$sgastat, v$parameter
     where v$sgastat.name= 'free memory'
     and v$parameter.name = 'shared_pool_size'
     and v$sgastat.pool='shared pool';

     VALUE      BYTES percent free

---------- ---------- ------------

 503316480  141096368 28.033329645

SQL>

· v$sga_dynamic_components

這個檢視記錄了SGA各個動態記憶體區的情況，它的統計資訊是基於已經完成了的，針對SGA動態記憶體區大小調整的操作，欄位組成如下：

欄位	資料型別	描述
COMPONENT	VARCHAR2(64)	記憶體區名稱
CURRENT_SIZE	NUMBER	當前大小
MIN_SIZE	NUMBER	自從例項啟動後的最小值
MAX_SIZE	NUMBER	自從例項啟動後的最大值
OPER_COUNT	NUMBER	自從例項啟動後的調整次數
LAST_OPER_TYPE	VARCHAR2(6)	最後一次完成的調整動作，值包括： GROW（增加） SHRINK（縮小）
LAST_OPER_MODE	VARCHAR2(6)	最後一次完成的調整動作的模式，包括： MANUAL（手動） AUTO（自動）
LAST_OPER_TIME	DATE	最後一次完成的調整動作的開始時間
GRANULE_SIZE	NUMBER	GRANULE大小（關於granule後面詳細介紹）

· V$SGA_DYNAMIC_FREE_MEMORY

這個檢視只有一個欄位，一條記錄：當前SGA可用於動態調整SGA記憶體區的空閒區域大小。它的值相當於（SGA_MAX_SIZE – SGA各個區域設定大小的總和)。當設定了SGA_TARGET後，它的值一定為0（為什麼就不需要我再講了吧^_^）。

下面的例子可以很清楚的看到這個檢視的作用：

SQL> select * from v$sga_dynamic_free_memory;

CURRENT_SIZE

--------------

SQL> show parameter shared_pool

NAME                                TYPE        VALUE

----------------------------------- ----------- ----------

shared_pool_size                    big integer 50331648

SQL> alter system set shared_pool_size=38M;

system altered.

SQL> show parameter shared_pool

NAME                                TYPE        VALUE

----------------------------------- ----------- ----------

shared_pool_size                    big integer 41943040

SQL> select * from v$sga_dynamic_free_memory;

CURRENT_SIZE

--------------

1.1.3. 資料庫緩衝區（Database Buffers）

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

和Shared Pool一樣，buffer cache被分為多個集合，這樣能夠大大降低多CPU系統中的爭用問題。

1.1.3.1. Buffer cache的管理

Oracle對於buffer cache的管理，是透過兩個重要的連結串列實現的：寫連結串列和最近最少使用連結串列（the Least Recently Used LRU）。寫連結串列所指向的是所有髒資料塊快取（即被程式修改過，但還沒有被回寫到資料檔案中去的資料塊，此時緩衝中的資料和資料檔案中的資料不一致）。而LRU連結串列指向的是所有空閒的快取、pin住的快取以及還沒有來的及移入寫連結串列的髒快取。空閒快取中沒有任何有用的資料，隨時可以使用。而pin住的快取是當前正在被訪問的快取。LRU連結串列的兩端就分別叫做最近使用端（the Most Recently Used MRU）和最近最少使用端（LRU）。

· Buffer cache的資料塊訪問

當一個Oracle程式訪問一個快取時，這個程式會將這塊快取移到LRU連結串列中的MRU。而當越來越多的緩衝塊被移到MRU端，那些已經過時的髒緩衝（即資料改動已經被寫入資料檔案中，此時緩衝中的資料和資料檔案中的資料已經一致）則被移到LRU連結串列中LRU端。

當一個Oracle使用者程式第一次訪問一個資料塊時，它會先查詢buffer cache中是否存在這個資料塊的複製。如果發現這個資料塊已經存在於buffer cache（即命中cache hit），它就直接讀從記憶體中取該資料塊。如果在buffer cache中沒有發現該資料塊（即未命中cache miss），它就需要先從資料檔案中讀取該資料塊到buffer cache中，然後才訪問該資料塊。命中次數與程式讀取次數之比就是我們一個衡量效能的重要指標：buffer hit ratio（buffer命中率），
可以透過以下語句獲得自例項啟動至今的buffer命中率：

 select 1-(sum(decode(name, 'physical reads', value, 0))/
          (sum(decode(name, 'db block gets', value, 0))+
          (sum(decode(name, 'consistent gets', value, 0))))) "Buffer Hit Ratio"
 from v$sysstat;

Buffer Hit Ratio

----------------

      .926185625

1 row selected.

根據經驗，一個良好效能的系統，這一值一般保持在95%左右。

上面提到，如果未命中（missed），則需要先將資料塊讀取到快取中去。這時，oracle程式需要從空閒列表中找到一個適合大小的空閒快取。如果空閒列表中沒有適合大小的空閒buffer，它就會從LRU端開始查詢LRU連結串列，直到找到一個可重用的快取塊或者達到最大查詢塊數限制。在查詢過程中，如果程式找到一個髒快取塊，它將這個快取塊移到寫連結串列中去，然後繼續查詢。當它找到一個空閒塊後，就從磁碟中讀取資料塊到快取塊中，並將這個快取塊移到LRU連結串列的MRU端。

當有新的物件需要請求分配buffer時，會透過記憶體管理模組請求分配空閒的或者可重用的buffer。“free buffer requested”就是產生這種請求的次數；

當請求分配buffer時，已經沒有適合大小的空閒buffer時，需要從LRU連結串列上獲取到可重用的buffer。但是，LRU連結串列上的buffer並非都是立即可重用的，還會存在一些塊正在被讀寫或者已經被別的使用者所等待。根據LRU，查詢可重用的buffer是從連結串列的LRU端開始查詢的，如果這一段的前面存在這種不能理解被重用的buffer，則需要跳過去，查詢連結串列中的下一個buffer。“free buffer inspected”就是被跳過去的buffer的數目。

如果Oracle使用者程式達到查詢塊數限制後還沒有找到空閒快取，它就停止查詢LRU連結串列，並且透過訊號同志DBW0程式將髒快取寫入磁碟去。

下面就是oracle使用者程式訪問一個資料塊的虛擬碼：

user_process_access_block(block)

    if (search_lru(block))

        g_cache_hit++;

        return read_block_from_buffer_cache(block);

    else

        g_cache_missed++;

        search_count = 1;

        searched = FALSE;

        set_lru_latch_context();

        buffer_block = get_lru_from_lru();

do

            if (block == buffer_block)

                set_buffer_block(buffer_block, read_block_from_datafile(block);

                move_buffer_block_to_mru(buffer_block);

                searched = TRUE;

            search_count++;

            buffer_block = get_next_from_lru(buffer_block);

        }while(!searched && search_count < BUFFER_SEARCH_THRESHOLD)

        free_lru_latch_context();

        if (!searched)

            buffer_block = signal_dbw0_write_dirty_buffer();

            set_buffer_block(buffer_block, read_block_from_datafile(block);

            move_buffer_block_to_mru(buffer_block);

        return buffer_block;

· 全表掃描

當發生全表掃描（Full Table Scan）時，使用者程式讀取表的資料塊，並將他們放在LRU連結串列的LRU端（和上面不同，不是放在MRU端）。這樣做的目的是為了使全表掃描的資料儘快被移出。因為全表掃描一般發生的頻率較低，並且全表掃描的資料塊大部分在以後都不會被經常使用到。

而如果你希望全表掃描的資料能被cache住，使之在掃描時放在MRU端，可以透過在建立或修改表（或簇）時，指定CACHE引數。

· Flush Buffer

回顧一下前面一個使用者程式訪問一個資料塊的過程，如果訪問的資料塊不在buffer cache中，就需要掃描LRU連結串列，當達到掃描塊數限制後還沒有找到空閒buffer，就需要通知DBW0將髒快取回寫到磁碟。分析一下虛擬碼，在這種情況下，使用者程式訪問一個資料塊的過程是最長的，也就是效率最低的。如果一個系統中存在大量的髒緩衝，那麼就可能導致使用者程式訪問資料效能下降。

我們可以透過人工干預將所有髒緩衝回寫到磁碟去，這就是flush buffer。

在9i，可以用以下語句：

alter system set events = 'immediate trace name flush_cache'; --9i

在10g，可以用以下方式（9i的方式在10g仍然有效）：

alter system flush buffer_cache; -- 10g

另外，9i的設定事件的方式可以是針對系統全部的，也可以是對會話的（即將該會話造成的髒緩衝回寫）。

1.1.3.2. Buffer Cache的重要引數配置

Oracle提供了一些引數用於控制Buffer Cache的大小等特性。下面介紹一下這些引數。

· Buffer Cache的大小配置

由於Buffer Cache中存放的是從資料檔案中來的資料塊的複製，因此，它的大小的計算也是以塊的尺寸為基數的。而資料塊的大小是由引數db_block_size指定的。9i以後，塊的大小預設是8K，它的值一般設定為和作業系統的塊尺寸相同或者它的倍數。

而引數db_block_buffers則指定了Buffer Cache中快取塊數。因此，buffer cache的大小就等於db_block_buffers * db_block_size。

在9i以後，Oracle引入了一個新引數：db_cache_size。這個引數可以直接指定Buffer Cache的大小，而不需要透過上面的方式計算出。它的預設值48M，這個數對於一個系統來說一般是不夠用的。

注意：db_cache_size和db_block_buffers是不能同時設定的，否則例項啟動時會報錯。

SQL> alter system set db_block_buffers=16384 scope=spfile;

system altered.

SQL> alter system set db_cache_size=128M scope=spfile;

system altered.

SQL> startup force

ORA-00381: cannot use both new and old parameters for buffer cache size specification

9i以後，推薦使用db_cache_size來指定buffer cache的大小。

在OLTP系統中，對於DB_CACHE_SIZE的設定，我的推薦配置是：

DB_CACHE_SIZE = SGA_MAX_SIZE/2 ～ SGA_MAX_SIZE*2/3

最後，DB_CACHE_SIZE是可以聯機修改的，即例項無需重啟，除非增大Buffer Cache導致SGA實際大小大於SGA_MAX_SIZE。

· 多種塊尺寸系統中的Buffer Cache的配置

從9i開始，Oracle支援建立不同塊尺寸的表空間，並且可以為不同塊尺寸的資料塊指定不同大小的buffer cache。

9i以後，除了SYSTEM表空間和TEMPORARY表空間必須使用標準塊尺寸外，所有其他表空間都可以最多指定四種不同的塊尺寸。而標準塊尺寸還是由上面的所說的引數db_block_size來指定。而db_cache_size則是標緻塊尺寸的buffer cache的大小。

非標準塊尺寸的塊大小可以在建立表空間（CREATE TABLESPACE）是透過BLOCKSIZE引數指定。而不同塊尺寸的buffer cache的大小就由相應引數DB_nK_CACHE_SZIE來指定，其中n可以是2，4，8，16或者32。例如，你建立了一個塊大小為16K的非標準塊尺寸的表空間，你就可以透過設定DB_16K_CACHE_SIZE為來指定快取這個表空間資料塊的buffer cache的大小。

任何一個尺寸的Buffer Cache都是不可以快取其他尺寸的資料塊的。因此，如果你打算使用多種塊尺寸用於你的資料庫的儲存，你必須最少設定DB_CACHE_SIZE和DB_nK_CACHE_SIZE中的一個引數（10g後，指定了SGA_TARGET就可以不需要指定Buffer Cache的大小）。並且，你需要給你要用到的非標準塊尺寸的資料塊指定相應的Buffer Cache大小。這些引數使你可以為系統指定多達4種不同塊尺寸的Buffer Cache。

另外，請注意一點，DB_nK_CACHE_SIZE 引數不能設定標準塊尺寸的緩衝區大小。舉例來說，如果 DB_BLOCK_SIZE 設定為 4K，就不能再設定 DB_4K_CACHE_SIZE 引數。

· 多緩衝池

你可以配置不同的buffer cache，可以達到不同的cache資料的目的。比如，可以設定一部分buffer cache快取過的資料在使用後後馬上釋放，使後來的資料可以立即使用緩衝池；還可以設定資料進入緩衝池後就被keep住不再釋放。部分資料庫物件（表、簇、索引以及分割槽）可以控制他們的資料快取的行為，而這些不同的快取行為就使用不同緩衝池。

o 保持緩衝池（Keep Buffer Pool）用於快取那些永久駐入記憶體的資料塊。它的大小由引數DB_KEEP_CACHE_SZIE控制；

o 回收緩衝池（Recycle Buffer Pool）會立即清除那些不在使用的資料快取塊。它的大小由引數DB_RECYLE_CACHE_SIZE指定；

o 預設的標準快取池，也就是上面所說的DB_CACHE_SIZE指定。

這三個引數相互之間是獨立的。並且他們都只適用於標準塊尺寸的資料塊。與8i相容引數DB_BLOCK_BUFFERS相應的，DB_KEEP_CACHE_SIZE對應有BUFFER_POOL_KEEP，DB_RECYLE_CACHE_SIZE對應有BUFFER_POOL_RECYCLE。同樣，這些引數之間是互斥的，即DB_KEEP_CACHE_SIZE和BUFFER_POOL_KEEP之間只能設定一個。

· 緩衝池建議器

從9i開始，Oracle提供了一些自動最佳化工具，用於調整系統配置，提高系統效能。建議器就是其中一種。建議器的作用就是在系統執行過程中，透過監視相關統計資料，給相關配置在不同情況下的效能效果，提供給DBA做決策，以選取最佳的配置。

9i中，Buffer Cache就有了相應的建議器。引數db_cache_advice用於該建議器的開關，預設值為FALSE（即關）。當設定它為TRUE後，在系統執行一段時間後，就可以查詢檢視v$db_cache_advice來決定如何使之DB_CACHE_SIZE了。關於這個建議器和檢視，我們會在下面的內容中介紹。

· 其他相關引數

DB_BLOCK_LRU_LATCHES

LRU連結串列作為一個記憶體物件，對它的訪問是需要進行鎖(latch)控制的，以防止多個使用者程式同時使用一個空閒快取塊。DB_BLOCK_LRU_LATCHES設定了LUR latch的數量範圍。Oracle透過一系列的內部檢測來決定是否使用這個引數值。如果這個引數沒有設定，Oracle會自動為它計算出一個值。一般來說，oracle計算出來的值是比較合理，無需再去修改。

9i以後這個引數是隱含引數。對於隱含引數，我建議在沒有得到Oracle支援的情況下不要做修改，否則，如果修改了，Oracle是可以拒絕為你做支援的。

DB_WRITER_PROCESSES

在前面分析Oracle讀取Buffer Cache時，提到一個Oracle重要的後臺程式DBW0，這個（或這些）程式負責將髒快取塊寫回到資料檔案種去，稱為資料庫書寫器程式（Database Writer Process）。DB_WRITER_PROCESSES引數配置寫程式的個數，各個程式以DBWn區分，其中n>=0，是程式序號。一般情況下，DB_WRITER_PROCESSES = MAX(1, TRUNC(CPU數/8))。也就是說，CPU數小於8時，DB_WRITER_PROCESSES為1，即只有一個寫程式DBW0。這對於一般的系統來說也是足夠用。當你的系統的修改資料的任務很重，並且已經影響到效能時，可以調整這個引數。這個引數不要超過CPU數，否則多出的程式也不會起作用，另外，它的最大值不能超過20。

DBWn程式除了上面提到的在使用者程式讀取buffer cache時會被觸發，還能被Checkpoint觸發（Checkpoint是例項從redo log中做恢復的起始點）。

1.1.3.3. Buffer Cache的重要檢視

關於Buffer Cache，oracle提供一些重要檢視，用於查詢關於Buffer Cache的重要資訊，為調整Buffer Cache、提高效能提供參考。下面一一介紹它們

· v$db_cache_advice

上面我們提到了Oracle的建議器，其中有一個針對Buffer Cache的建議器。在我們設定了引數db_cache_advice為TRUE後，經過一段時間的系統執行，Oracle收集到相關統計資料，並根據一定的數學模型，預測出DB_CACHE_SIZE在不同大小情況的效能資料。我們就可以由檢視V$DB_CACHE_ADVICE查出這些資料，並根據這些資料調整DB_CACHE_SZIE，使系統效能最優。

下面是關於這個檢視的結構描述：

欄位	資料型別	描述
ID	NUMBER	緩衝池標識號（從1到8，1-6對應於DB_nK_CACHE_SIZE，DB_CACHE_SIZE與系統標準塊尺寸的序號相關，如DB_BLOCK_SIZE為8K，則DB_CACHE_SIZE的標識號為3（2,4,8…）。7是DB_KEEP_CACHE_SIZE，8是DB_RECYCLE_CACHE_SIZE）
NAME	VARCHAR2(20)	緩衝池名稱
BLOCK_SIZE	NUMBER	緩衝池塊尺寸（位元組為單位）
ADVICE_STATUS	VARCHAR2(3)	建議器狀態：ON表示建議器在執行；OFF表示建議器已經關閉。當建議器關閉了，檢視中的資料是上一次開啟所統計得出的。
SIZE_FOR_ESTIMATE	NUMBER	預測效能資料的Cache大小（M為單位）
SIZE_FACTOR	NUMBER	預測的Cache大小因子（即與當前大小的比例）
BUFFERS_FOR_ESTIMATE	NUMBER	預測效能資料的Cache大小（緩衝塊數）
ESTD_PHYSICAL_READ_FACTOR	NUMBER	這一緩衝大小時，物理讀因子，它是如果緩衝大小為SIZE_FOR_ESTIMATE時，建議器預測物理讀數與當前實際物理讀數的比率值。如果當前物理讀數為0，這個值為空。
ESTD_PHYSICAL_READS	NUMBER	如果緩衝大小為SIZE_FOR_ESTIMATE時，建議器預測物理讀數。

下面是從這個檢視中查詢出來的資料：

 select size_for_estimate, estd_physical_read_factor, estd_physical_reads
 from v$db_cache_advice
 where name = 'DEFAULT';

SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS

----------------- ------------------------- -------------------

               16                    2.0176             6514226

               32                    1.7403             5619048

               48                    1.5232             4917909

               64                    1.3528             4367839

               80                    1.2698             4099816

               96                    1.1933             3852847

              112                    1.1443             3694709

              128                    1.1007             3553685

              144                    1.0694             3452805

              160                    1.0416             3362964

              176                    1.0175             3285085

              192                         1             3228693

              208                    0.9802             3164754

              224                    0.9632             3109920

              240                    0.9395             3033427

              256                    0.8383             2706631

              272                    0.7363             2377209

              288                     0.682             2202116

              304                    0.6714             2167888

              320                    0.6516             2103876

20 rows selected

當前我們的DB_CACHE_SIZE為192M，可以看到，它的物理讀因子為1，物理讀數為3228693。那麼如何根據這些資料調整DB_CACHE_SIZE呢？給出一個方法，找到變化率較平緩的點作為採用值。因為建議器做預測是，DB_CACHE_SIZE的預測值的增長步長是相同的，是16M。我們按照這一步長增加DB_CACHE_SIZE，如果每次增加物理讀降低都很明顯，就可以繼續增加，直到物理讀降低不明顯，說明繼續增加DB_CACHE_SIZE沒有太大作用。當然，效能和可用資源是天平的兩端，你需要根據自己系統的實際情況調整。

上面的例子中，我們可以考慮將DB_CACHE_SIZE調整到288M。因為在288M之前，物理讀因子變化都比較大，而從288M到304M以後，這個因子變化趨緩。用一個二維圖可以更容易看出這個變化來：

這一檢視作為調整DB_CACHE_SIZE以提高效能有很大參考價值。但衡量Buffer Cache是否合適的重要指標還是我們前面提到的快取命中率（Buffer Hit），而影響快取命中率往往還有其他因素，如效能極差的SQL語句。

· V$BUFFER_POOL

這一檢視顯示了當前例項中所有緩衝池的資訊。它的結構如下：

欄位	資料型別	描述
ID	NUMBER	緩衝池ID，和上面檢視描述相同。
NAME	VARCHAR2(20)	緩衝池名稱
BLOCK_SIZE	NUMBER	緩衝池塊尺寸（位元組為單位）
RESIZE_STATE	VARCHAR2(10)	緩衝池當前狀態。 STATIC：沒有被正在調整大小 ALLOCATING：正在分配記憶體給緩衝池（不能被使用者取消） ACTIVATING：正在建立新的快取塊（不能被使用者取消） SHRINKING：正在刪除快取塊（能被使用者取消）
CURRENT_SIZE	NUMBER	緩衝池大小（M為單位）
BUFFERS	NUMBER	當前快取塊數
TARGET_SIZE	NUMBER	如果正在調整緩衝池大小（即狀態不為STATIC），這記錄了調整後的大小（M為單位）。如果狀態為STATIC，這個值和當前大小值相同。
TARGET_BUFFERS	NUMBER	如果正在調整緩衝池大小（即狀態不為STATIC），這記錄了調整後的快取塊數。否則，這個值和當前快取塊數相同。
PREV_SIZE	NUMBER	前一次調整的緩衝池大小。如果從來沒有調整過，則為0。
PREV_BUFFERS	NUMBER	前一次調整的快取塊數。如果從來沒有調整過，則為0。
LO_BNUM	NUMBER	9i後已經廢棄欄位
HI_BNUM	NUMBER	9i後已經廢棄欄位
LO_SETID	NUMBER	9i後已經廢棄欄位
HI_SETID	NUMBER	9i後已經廢棄欄位
SET_COUNT	NUMBER	9i後已經廢棄欄位

· v$buffer_pool_statistics

V$BUFFER_POOL_STATISTICS檢視記錄了所有緩衝池的統計資料。它的結構如下：

欄位	資料型別	描述
ID	NUMBER	緩衝池ID，和上面檢視描述相同。
NAME	VARCHAR2(20)	緩衝池名稱
SET_MSIZE	NUMBER	緩衝池中快取塊的最大數
CNUM_REPL	NUMBER	在置換列表中的快取塊數
CNUM_WRITE	NUMBER	在寫列表中的快取塊數
CNUM_SET	NUMBER	當前的快取塊數
BUF_GOT	NUMBER	讀取過的快取塊數
SUM_WRITE	NUMBER	被寫過的快取塊數
SUM_SCAN	NUMBER	被掃描過的快取塊數
FREE_BUFFER_WAIT	NUMBER	等待空閒塊統計數
WRITE_COMPLETE_WAIT	NUMBER	等待完成寫統計數
BUFFER_BUSY_WAIT	NUMBER	忙（正在被使用）等待統計數
FREE_BUFFER_INSPECTED	NUMBER	確認了的空閒快取塊數（即可用的）
DIRTY_BUFFERS_INSPECTED	NUMBER	確認了的髒快取塊數
DB_BLOCK_CHANGE	NUMBER	被修改過的資料塊數
DB_BLOCK_GETS	NUMBER	讀取過的資料塊數
CONSISTENT_GETS	NUMBER	一致性讀統計數
PHYSICAL_READS	NUMBER	物理讀統計數
PHYSICAL_WRITES	NUMBER	物理寫統計數

檢視當前的Buffer Cache命中率：

 select 1-(physical_reads)/(consistent_gets+db_block_gets)
 from v$buffer_pool_statistics;

1-(PHYSICAL_READS)/(CONSISTENT

------------------------------

             0.967658520581074

SQL>

· v$bh

這一檢視在深入定位緩衝區問題時很有用。它記錄了緩衝區中所有資料塊物件。粒度非常細。這個檢視最初的目的是用於OPS（Oracle Parallel Server Oracle平行伺服器，9i後稱為RAC）的，是用於保證RAC中各個節點的資料一致性的。但是，我們可以透過它來查詢Buffer Cache的使用情況，找出大量消耗Buffer Cache的物件。下面的語句就可以完成這一工作：

 column c0 heading 'Owner'                     format a15
 column c1 heading 'Object|Name'               format a30
 column c2 heading 'Number|of|Buffers'         format 999,999
 column c3 heading 'Percentage|ofData|Buffer' format 999,999,999
 select
    owner                        c0,
    object_name                  c1,
    count(1)                     c2,
    (count(1)/(select count(*) from v$bh)) *100  c3
 from
    dba_objects o,
    v$bh        bh
 where
    o.object_id  = bh.objd
 and
    o.owner not in ('SYS','SYSTEM')
 group by
    owner,
    object_name
 order by
    count(1) desc;

C0              C1                                     C2         C3

--------------- ------------------------------ ---------- ----------

PLSQLDEV        STANDARD_CITY                  17290      72.5860621

DBOWNER         MSG_LOG                        2          0.00839630

DBOWNER         COUNTRY_PK                     1          0.00419815

DBOWNER         PARAMETER                      1          0.00419815

DBOWNER         PARAMETER_PK                   1          0.00419815

DBOWNER         MSG_LOG_IDX1                   1          0.00419815

6 rows selected

SQL>

更重要的是，這個檢視記錄的粒度非常細，一條記錄對應了一個資料塊。這對於我們做記憶體問題分析或分析Oracle行為時很有幫助。

下面是這個檢視的結構：

欄位	資料型別	說明
FILE#	NUMBER	快取塊對應的資料塊所在的資料檔案號。可以透過檢視DBA_DATA_FILES或V$DBFILES查詢
BLOCK#	NUMBER	快取塊對應的資料塊編號
CLASS#	NUMBER	分類編號
STATUS	VARCHAR2(1)	快取塊的狀態 FREE：空閒，沒有被使用 XCUR：排斥（正在被使用） SCUR：可被共享 CR：一致性讀 READ：正在從磁碟讀入 MREC：處於從儲存介質恢復狀態 IREC：處於例項恢復狀態
XNC	NUMBER	快取塊上由於和其他例項爭用導致的PCM（Parallel Cache Management並行快取管理）x to null鎖的數量。這一欄位已經被廢棄。
LOCK_ELEMENT_ADDR	RAW(4 \| 8)	快取塊上PCM鎖的地址。如果多個快取塊的PCM鎖地址相同，說明他們被同一鎖鎖住。
LOCK_ELEMENT_NAME	NUMBER	快取塊上PCM鎖的地址。如果多個快取塊的PCM鎖地址相同，說明他們被同一鎖鎖住。
LOCK_ELEMENT_CLASS	NUMBER	快取塊上PCM鎖的地址。如果多個快取塊的PCM鎖地址相同，說明他們被同一鎖鎖住。
FORCED_READS	NUMBER	由於其他例項的PCM鎖鎖住了該快取塊，導致當前例項嘗試重新請求讀該緩衝塊的次數。
FORCED_WRITES	NUMBER	由於其他例項的PCM鎖鎖住了該快取塊，導致當前例項嘗試重新請求寫該緩衝塊的次數。
DIRTY	VARCHAR2(1)	髒標誌：Y – 塊被修改過，是髒塊；N – 不是髒塊
TEMP	VARCHAR2(1)	是否為臨時塊：Y – 是；N – 否。
PING	VARCHAR2(1)	是否被ping住：Y – 是；N – 否。
STALE	VARCHAR2(1)	是否是陳舊塊：Y – 是；N – 否。
DIRECT	VARCHAR2(1)	是否為直接讀寫塊：Y – 是；N – 否。
NEW	VARCHAR2(1)	欄位被廢棄，始終為N
OBJD	NUMBER	資料塊所屬物件的物件標號，可以查詢dba_objects
TS#	NUMBER	資料塊所在的表空間號，可以查詢v$tablespaces

1.1.4. 共享池（Shared pool）

SGA中的共享池由庫快取（Library Cache）、字典快取（Dictionary Cache）、用於並行執行訊息的緩衝以及控制結構組成。

Shared Pool的大小由引數SHARED_POOL_SIZE決定。在32位系統中，這個引數的預設值是8M，而64位系統中的預設值位64M。最大為4G。

對於Shared Pool的記憶體管理，是透過修正過的LRU演算法表來實現的。

下面分別介紹Shared Pool的幾個組成部分。

1.1.4.1. 庫快取（Library Cache）

Library Cache中包括共享SQL區（Shared SQL Areas）、PL/SQL儲存過程和包以及控制結構（如鎖、庫快取控制程式碼）。

任何使用者都可以訪問共享SQL區（可以透過v$sqlarea訪問，隨後會介紹這個重要檢視）。因此庫快取存在於SGA的共享池中。

· 共享SQL區和私有SQL區

Oracle會為每一條SQL語句執行（每執行一條語句Oracle都會開啟一個遊標）提供一個共享SQL區（Shared SQL Areas）和私有SQL區（Private SQL Areas屬於PGA）。當發現兩個（或多個）使用者都在執行同一SQL語句時，Oracle會重新組織SQL區，使這些使用者能重用共享SQL區。但他們還會在私有SQL區中儲存一份這條SQL語句的複製。

一個共享SQL區中儲存了一條語句的解析樹和查詢計劃。在多使用者系統中，Oracle透過為SQL語句使用同一共享SQL區多次執行來節省記憶體。

當一條新的SQL語句被解析時，Oracle從共享池中分配一塊記憶體來儲存共享SQL區。這塊記憶體的大小與這條語句的複雜性相關。如果Shared Pool不夠空間分配給共享SQL區，Oracle將釋放從LRU連結串列中查詢到最近最少使用的記憶體塊，直到有足夠空間給新的語句的共享SQL區。如果Oracle釋放的是一個共享SQL區的記憶體，那麼相應的語句在下次執行時需要再次解析並重新分配共享SQL區。而從解析語句到分配共享SQL區是一個比較消耗CPU的工程。這就是為什麼我們提倡使用繫結變數的原因了。在沒有使用繫結變數時，語句中的變數的數值不同，oracle就視為一條新的語句（9i後可以透過cursor_sharing來控制），重複上面的解析、記憶體分配的動作，將大大消耗系統資源，降低系統效能。

· PL/SQL程式單元

Oracle對於PL/SQL程式單元（儲存過程、函式、包、匿名PL/SQL塊和觸發器）的處理過程和對單個的SQL語句的處理過程相似。它會分配一個共享區來儲存被解析、編譯過的程式單元。同時分配一個私有區域來存放執行程式單元的會話所指定的程式單元的引數值（包括本地變數、全域性變數和包變數——這也叫做包的例項化）和用於執行程式所需的記憶體。如果多個使用者執行同一個程式單元，則他們共享同一個共享區域，並且各自保持一份私有區域，用於使用者會話中指定的變數值。

而一個PL/SQL程式單元中的每條單個SQL語句的處理過程則和上面描述的SQL語句的處理過程相同。要注意一點，儘管這些語句是從PL/SQL程式單元中來的，但是Oracle還是會為這些語句分配一塊共享SQL區，同時為每個使用者分配一個相應的私有SQL區。

1.1.4.2. 字典快取（Dictionary Cache）

資料字典是有關於資料庫的參考資訊、資料庫的結構資訊和資料庫中的使用者資訊的一組表和檢視的集合，如我們常用到的V$檢視、DBA_檢視都屬於資料字典。在SQL語句解析的過程中，Oracle可以非常迅速的訪問（如果需要的話）這些資料字典，在SQL Trace中，這種對資料字典的訪問就被統計為回撥（recursive calls）。看下面例子：

第一、呼叫語句，需要做硬解析：

SQL> select * from T_COMPANY;

9999 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3356521258

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           | 10000 |   156K|     9   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T_COMPANY | 10000 |   156K|     9   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

        355  recursive calls

          0  db block gets

        764  consistent gets

         39  physical reads

        116  redo size

     305479  bytes sent via SQL*Net to client

       7711  bytes received via SQL*Net from client

        668  SQL*Net roundtrips to/from client

          5  sorts (memory)

          0  sorts (disk)

       9999  rows processed

可以看到，Recursive Calls高達355。第二次呼叫，無需解析，直接使用共享SQL區中快取：

SQL> /

9999 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3356521258

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           | 10000 |   156K|     9   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T_COMPANY | 10000 |   156K|     9   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        705  consistent gets

          0  physical reads

          0  redo size

     305479  bytes sent via SQL*Net to client

       7711  bytes received via SQL*Net from client

        668  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       9999  rows processed

由於沒做解析，這時recursive calls為0。

當然，recursive calls並不僅僅發生在解析的時候。由於資料字典記錄了所有物件的結構、資料資訊，因此在物件結構、資料發生變化時都會訪問資料字典：

SQL> delete from t_company where rownum=1;

1 row deleted.

Statistics

----------------------------------------------------------

        360  recursive calls

...

SQL> /

1 row deleted.

Statistics

----------------------------------------------------------

          4  recursive calls

...

SQL> /

Statistics

----------------------------------------------------------

          4  recursive calls

...

可以看到，上面的delete語句在第一次執行時，包括因解析和資料改動導致對資料字典的訪問，因此recursive calls較高，為360。在隨後的執行中，因為沒有做解析，所以recursive calls大大減少，只有4，而這4個recursive calls是因為資料改變而需要對資料字典的訪問。

因為Oracle對資料字典訪問如此頻繁，因此記憶體中有兩處地方被專門用於存放資料字典。一個地方就是資料字典快取（Data Dictionary Cache）。資料字典快取也被稱為行快取（Row Cache），因為它是以記錄行為單元儲存資料的，而不像Buffer Cache是以資料塊為單元儲存資料。
記憶體中另外一個儲存資料字典的地方是庫快取。所有Oracle的使用者都可以訪問這兩個地方以獲取資料字典資訊。

1.1.4.3. 共享池的記憶體管理

通常來說，共享池是根據修正過的LRU演算法來是否其中的物件（共享SQL區和資料自動記錄行）的，否則這些物件就一直保持在共享池中。如果共享池需要為一個新物件分配記憶體，並且共享池中沒有足夠記憶體時，記憶體中那些不經常使用的物件就被釋放掉。一個被許多會話使用過的共享池物件，即使最初建立它的程式已經結束，只要它是有用的，都會被修正過的LRU演算法一直保持在共享池中。這樣就使一個多使用者的Oracle系統對SQL語句的處理和記憶體消耗最小。

注意，即使一個共享SQL區與一個開啟的遊標相關，但如果它長時間沒有被使用，它還是可能會被從共享池中釋放出來。而此時如果開啟的遊標還需要執行它的相關語句，Oracle就會重新解析語句，並分配新的共享SQL區。

當一條SQL語句被提交給Oracle執行，Oracle會自動執行以下的記憶體分配步驟：

1．Oracle檢查共享池，看是否已經存在關於這條語句的共享SQL區。如果存在，這個共享SQL區就被用於執行這條語句。而如果不存在，Oracle就從共享池中分配一塊新的共享SQL區給這條語句。同時，無論共享SQL區存在與否，Oracle都會為使用者分配一塊私有SQL區以儲存這條語句相關資訊（如變數值）。

2．Oracle為會話分配一個私有SQL區。私有SQL區的所在與會話的連線方式相關。

下面是Oracle執行一條語句時共享池記憶體分配過程的虛擬碼：

execute_sql(statement)

    if ((shared_sql_area = find_shared_sql_area(statement)) == NULL)

    if (!allocate_from_shared_pool(&new_buffer))

        if (new_buffer = find_age_area_by_lru(size_of(statement)) == NULL)

            raise(4031);

            return 0;

    shared_sql_area = set_shared_sql_area(new_buffer);

    parse_sql(statement, shared_sql_area);

private_sql_area = allocate_private_sql_area(statement);

run_sql(statement, shared_sql_area, private_sql_area);

return 1;

在以下情況下，Oracle也會將共享SQL區從共享池中釋放出來：

· 當使用ANALYZE語句更新或刪除表、簇或索引的統計資訊時，所有與被分析物件相關的共享SQL區都被從共享池中釋放掉。當下一次被釋放掉的語句被執行時，又重新在一個新的共享SQL區中根據被更新過的統計資訊重新解析。

· 當物件結構被修改過後，與該物件相關的所有共SQL區都被標識為無效（invalid）。在下一次執行語句時再重新解析語句。

· 如果資料庫的全域性資料庫名（Global Database Name）被修改了，共享池中的所有資訊都會被清空掉。

· DBA透過手工方式清空共享池：

 ALTER SYSTEM FLUSH SHARED_POOL;

Shared Pool能被分成幾個區域，分別被不同的latch（latch數最大為7，可以透過隱含引數_kghdsidx_count設定）保護。

表x$kghlu可以檢視shared pool中的LRU列表。當滿足以下條件之一時，shared pool會分為多個區，分別有不同的LRU連結串列管理：

· 在10g之前版本，如果shared pool大於128M、CPU數量大於4；

· Oracle資料庫版本為10g

這時，在x$kghlu中就會對應不同記錄。

1.1.4.4. 保留共享池

前面提到，如果Oracle解析一個 PL/SQL程式單元，也需要從共享池中分配記憶體給這些程式單元物件。由於這些物件本一般比較大（如包），所以分配的記憶體空間也相對較大。系統經過長時間執行後，共享池可能存在大量記憶體碎片，導致無法滿足對於大塊記憶體段的分配。

為了使有足夠空間快取大程式塊，Oracle專門從共享池內建出一塊區域來來分配記憶體保持這些大塊。這個保留共享池的預設大小是共享池的5%。它的大小也可以透過引數SHARED_POOL_RESERVED_SIZE來調整。保留區是從共享池中分配，不是直接從SGA中分配的，它是共享池的保留部分，用於儲存大塊段。

Shared Pool中記憶體大於5000位元組的大段就會被存放在共享池的保留部分。而這個大小限制是透過隱含引數_SHARED_POOL_RESERVED_MIN_ALLOC來設定的（如前面所說，隱含引數不要去修改它）。除了在例項啟動過程中，所有小於這個數的記憶體段永遠都不會放到保留部分中，而大於這個值的大記憶體段也永遠不會存放到非保留區中，即使共享池的空間不夠用的情況下也是如此。

保留區的空閒記憶體也不會被包含在普通共享池的空閒列表中。它會維護一個單獨的空閒列表。保留池也不會在它的LRU列表中存放可重建（Recreatable 關於記憶體段的各種狀態我們在後面的內容中再介紹）段。當釋放普通共享池空閒列表上的記憶體時是不會清除這些大段的，同樣，在釋放保留池的空閒列表上的大記憶體段時也不會清除普通共享池中記憶體。

透過檢視V$SHARED_POOL_RESERVED可以查到保留池的統計資訊。其中欄位REQUEST_MISSES記錄了沒有立即從空閒列表中得到可用的大記憶體段請求次數。這個值要為0。因為保留區必須要有足夠個空閒記憶體來適應那些短期的記憶體請求，而無需將那些需要長期cache住的沒被pin住的可重建的段清除。否則就需要考慮增大SHARED_POOL_RESERVED_SIZE了。

你可以透過觀察檢視V$SHARED_POOL_RESERVED的MAX_USED_SPACE欄位來判斷保留池的大小是否合適。大多數情況下，你會觀察到保留池是很少被使用的，也就是說5%的保留池空間可能有些浪費。但這需要經過長期觀察來決定是否需要調整保留池大小。

保留區使用shared pool的LRU連結串列來管理記憶體塊，但是在做掃描時，相互是不受影響的。例如，記憶體管理器掃描shared pool的LRU連結串列，清出空間以分配給一個小於5000位元組的記憶體請求，是不會清出保留區的記憶體塊的，相反亦然。

1.1.4.5. 將重要、常用物件保持（Keep）在共享池中

前面提到，根據LRU演算法，一些一段時間沒有使用到的記憶體塊會被情況釋放。這就可能導致一些重要的物件（如一個含有大量通用演算法函式的包、被cache的序列）被從記憶體中清除掉。這些物件可能只是間歇被使用，但是因為他們的處理過程複雜（不僅包本身重新分配記憶體、解析，還要檢查裡面的所有語句），要在記憶體中重建他們的代價非常大。

我們可以透過呼叫儲存過程DBMS_SHARED_POOL.KEEP將這些物件保持在共享池中來降低這種風險。這個儲存過程立即將物件及其從事物件載入library cache中，並將他們都標記為保持（Keeping）狀態。對於這種物件，我們建議在例項啟動時就Keep住，以減少記憶體碎片的機率。

有一種觀點認為那些大物件（如包）是沒有必要被Keep住的，因為他們會被保持在共享池的保留區（如前所述，這個區通常使用率很低），所以一般不可能被清出。這個觀點是錯誤滴！因為大多數大物件實際上是被分為多個小的記憶體段被載入共享池的，因此根本不會因為物件的大小而受到特別的保護。

另外，也不要透過頻繁呼叫某些物件以防止他們被從共享池中清出。如果共享池大小設定合理，在系統執行的高峰時期，LRU連結串列會相對較短，那些沒有被pin住的物件會很快被清出，除非他們被keep住了。

1.1.4.6. 關於Shared Pool的重要引數

這裡再介紹與共享池相關的一些重要引數。

· SHARED_POOL_SIZE

這個引數我們前面已經提到，它指定了Shared Pool的大小。在32位系統中，這個引數的預設值是8M，而64位系統中的預設值位64M。

但是，在SGA中還存在一塊叫內部SGA消耗（Internal SGA Overhead）的記憶體被放置在共享池中。在9i及之前版本，共享池的統計大小（透過v$sgastat檢視統計）為SHARED_POOL_SIZE + 內部SGA消耗大小。而10g以後，SHARED_POOL_SIZE就已經包含了這部分記憶體大小。因此在10g中，共享池的實際使用大小就是SHARED_POOL_SIZE - 內部SGA消耗大小，這在配置共享池大小時需要考慮進去，否則，扶過SHARED_POOL_SIZE設定過小，在例項啟動時就會報ORA-00371錯誤。

看9i中的結果：

SQL> show parameter shared_pool_size

NAME                         TYPE        VALUE

----------------------------- ----------- --------------

shared_pool_size             big integer 41943040

SQL> select sum(bytes) from v$sgastat where pool = 'shared pool';

SUM(BYTES)

----------

58720256

SQL>

· SHARED_POOL_RESERVED_SIZE

這個引數前面已經提到，指定了共享池中快取大記憶體物件的保留區的大小。這裡不再贅述。

· _SHARED_POOL_RESERVED_MIN_ALLOC

這個引數前面也已經介紹，設定了進入保留區的物件大小的閥值。

1.1.4.7. 共享池的重要檢視

最後，我們再介紹關於共享池的一些重要檢視

· v$shared_pool_advice

這個檢視與Oracle的另外一個最佳化建議器——共享池建議器——相關。我們可以根據這個檢視裡面oracle所做的預測資料來調整共享池大小。它的預測範圍是從當前值的10%到200%之間。檢視的結構如下

欄位	資料型別	描述
SHARED_POOL_SIZE_FOR_ESTIMATE	NUMBER	估算的共享池大小（M為單位）
SHARED_POOL_SIZE_FACTOR	NUMBER	估算的共享池大小與當前大小比
ESTD_LC_SIZE	NUMBER	估算共享池中用於庫快取的大小（M為單位）
ESTD_LC_MEMORY_OBJECTS	NUMBER	估算共享池中庫快取的記憶體物件數
ESTD_LC_TIME_SAVED	NUMBER	估算將可以節省的解析時間。這些節省的時間來自於請求處理一個物件時，重新將它載入共享池的時間消耗和直接從庫快取中讀取的時間消耗的差值。
ESTD_LC_TIME_SAVED_FACTOR	NUMBER	估算的節省的解析時間與當前節省解析時間的比。
ESTD_LC_MEMORY_OBJECT_HITS	NUMBER	估算的可以直接從共享池中命中庫快取的記憶體物件的命中次數。

關於如何根據建議器採用合理的共享池大小的方法，和前面提到的緩衝區建議器的使用方法類似，不再贅述。

· V$SHARED_POOL_RESERVED

前面提到了這個檢視。這個檢視存放了共享池保留區的統計資訊。可以根據這些資訊來調整保留區。檢視結構如下：

Column	Datatype	Description
以下欄位只有當引數SHARED_POOL_RESERVED_SIZE設定了才有效。
FREE_SPACE	NUMBER	保留區的空閒空間數。
AVG_FREE_SIZE	NUMBER	保留區的空閒空間平均數。
FREE_COUNT	NUMBER	保留區的空閒記憶體塊數
MAX_FREE_SIZE	NUMBER	最大的保留區空閒空間數。
USED_SPACE	NUMBER	保留區使用空間數。
AVG_USED_SIZE	NUMBER	保留區使用空間平均數。
USED_COUNT	NUMBER	保留區使用記憶體塊數。
MAX_USED_SIZE	NUMBER	最大保留區使用空間數
REQUESTS	NUMBER	請求再保留區查詢空閒記憶體塊的次數。
REQUEST_MISSES	NUMBER	無法滿足查詢保留區空閒記憶體塊請求，需要從LRU列表中清出物件的次數。
LAST_MISS_SIZE	NUMBER	請求的記憶體大小，這次請求是最後一次需要從LRU列表清出物件來滿足的請求。
MAX_MISS_SIZE	NUMBER	所有需要從LRU列表清出物件來滿足的請求中的記憶體最大大小
以下欄位無論引數SHARED_POOL_RESERVED_SIZE是否設定了都有效。
REQUEST_FAILURES	NUMBER	沒有記憶體能滿足的請求次數（導致4031錯誤的請求）
LAST_FAILURE_SIZE	NUMBER	沒有記憶體能滿足的請求所需的記憶體大小（導致4031錯誤的請求）
ABORTED_REQUEST_THRESHOLD	NUMBER	不清出物件的情況下，導致4031錯誤的最小請求大小。
ABORTED_REQUESTS	NUMBER	不清出物件的情況下，導致4031錯誤的請求次數。。
LAST_ABORTED_SIZE	NUMBER	不清出物件的情況下，最後一次導致4031錯誤的請求大小。

我們可以根據後面4個欄位值來決定如何設定保留區的大小以避免4031錯誤的發生。

· v$db_object_cache

這一檢視顯示了所有被快取在library cache中的物件，包括表、索引、簇、同義詞、PL/SQL儲存過程和包以及觸發器。

欄位	資料型別	說明
OWNER	VARCHAR2(64)	物件所有者
NAME	VARCHAR2(1000)	物件名稱
DB_LINK	VARCHAR2(64)	如果物件存在db link的話，db link的名稱
NAMESPACE	VARCHAR2(28)	庫快取的物件名稱空間，包括： TABLE/PROCEDURE, BODY, TRIGGER, INDEX, CLUSTER, OBJECT, CURSOR, INVALID NAMESPACE, JAVA SHARED DATA, PUB_SUB, RSRC CONSUMER GROUP
TYPE	VARCHAR2(28)	物件型別，包括：INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK, CURSOR, JAVA CLASS, JAVA SHARED DATA, NON-EXISTENT, NOT LOADED, PUB_SUB, REPLICATION OBJECT GROUP, TYPE
SHARABLE_MEM	NUMBER	物件消耗的共享池中的共享記憶體
LOADS	NUMBER	物件被載入次數。即使物件被置為無效了，這個數字還是會增長。
EXECUTIONS	NUMBER	物件執行次數，但本檢視中沒有被使用。可以參考檢視v$sqlarea中執行次數。
LOCKS	NUMBER	當前鎖住這個物件的使用者數（如正在呼叫、執行物件）。
PINS	NUMBER	當前pin住這個物件的使用者數（如正在編譯、解析物件）。
KEPT	VARCHAR2(3)	物件是否被保持，即呼叫了DBMS_SHARED_POOL.KEEP來永久將物件pin在記憶體中。 (YES \| NO)
CHILD_LATCH	NUMBER	正在保護該物件的子latch的數量。

· v$sql、v$sqlarea 、v$sqltext

這三個檢視都可以用於查詢共享池中已經解析過的SQL語句及其相關資訊。

V$SQL中列出了共享SQL區中所有語句的資訊，它不包含GROUP BY字句，並且為每一條SQL語句中單獨存放一條記錄；

V$SQLAREA中一條記錄顯示了一條共享SQL區中的統計資訊。它提供了有在記憶體中、解析過的和準備執行的SQL語句的統計資訊；

V$SQLTEXT包含了庫快取中所有共享遊標對應的SQL語句。它將SQL語句分片顯示。

下面介紹一下我常用的V$SQLAREA的結構：

欄位	資料型別	說明
SQL_TEXT	VARCHAR2(1000)	遊標中SQL語句的前1000個字元。
SHARABLE_MEM	NUMBER	被遊標佔用的共享記憶體大小。如果存在多個子遊標，則包含所有子游標占用的共享記憶體大小。
PERSISTENT_MEM	NUMBER	用於一個開啟這條語句的遊標的生命過程中的固定記憶體大小。如果存在多個子遊標，則包含所有子游標生命過程中的固定記憶體大小。
RUNTIME_MEM	NUMBER	一個開啟這條語句的遊標的執行過程中的固定記憶體大小。如果存在多個子遊標，則包含所有子游標執行過程中的固定記憶體大小。
SORTS	NUMBER	所有子游標執行語句所導致的排序次數。
VERSION_COUNT	NUMBER	快取中關聯這條語句的子游標數。
LOADED_VERSIONS	NUMBER	快取中載入了這條語句上下文堆（KGL heap 6）的子游標數。
OPEN_VERSIONS	NUMBER	開啟語句的子游標數。
USERS_OPENING	NUMBER	開啟這些子游標的使用者數。
FETCHES	NUMBER	SQL語句的fetch數。
EXECUTIONS	NUMBER	所有子游標的執行這條語句次數。
USERS_EXECUTING	NUMBER	透過子游標執行這條語句的使用者數。
LOADS	NUMBER	語句被載入和過載入的次數
FIRST_LOAD_TIME	VARCHAR2(19)	語句被第一次載入的時間戳。
INVALIDATIONS	NUMBER	所以子游標的非法次數。
PARSE_CALLS	NUMBER	所有子游標對這條語句的解析呼叫次數。
DISK_READS	NUMBER	所有子游標執行這條語句導致的讀磁碟次數。
BUFFER_GETS	NUMBER	所有子游標執行這條語句導致的讀記憶體次數。
ROWS_PROCESSED	NUMBER	這條語句處理的總記錄行數。
COMMAND_TYPE	NUMBER	Oracle命令型別代號。
OPTIMIZER_MODE	VARCHAR2(10)	執行這條的最佳化器模型。
PARSING_USER_ID	NUMBER	第一次解析這條語句的使用者的ID。
PARSING_SCHEMA_ID	NUMBER	第一次解析這條語句所用的schema的ID。
KEPT_VERSIONS	NUMBER	所有被DBMS_SHARED_POOL包標識為保持（Keep）狀態的子游標數。
ADDRESS	RAW(4 \| 8)	指向語句的地址
HASH_VALUE	NUMBER	這條語句在library cache中hash值。
MODULE	VARCHAR2(64)	在第一次解析這條語句是透過呼叫DBMS_APPLICATION_INFO.SET_MODULE設定的模組名稱。
MODULE_HASH	NUMBER	模組的Hash值
ACTION	VARCHAR2(64)	在第一次解析這條語句是透過呼叫DBMS_APPLICATION_INFO.SET_ACTION設定的動作名稱。
ACTION_HASH	NUMBER	動作的Hash值
SERIALIZABLE_ABORTS	NUMBER	所有子游標的事務無法序列化的次數，這會導致ORA-08177錯誤。
IS_OBSOLETE	VARCHAR2(1)	遊標是否被廢除（Y或N）。當子游標數太多了時可能會發生。
CHILD_LATCH	NUMBER	為了包含此遊標的子latch數。

檢視當前會話所執行的語句以及會話相關資訊：

 col A.SID||'.'||A.SERIAL#  for a25
 col USERNAME for a10
 col program for a10
 col TERMINAL for a10
 col sql_text for a100
 
 select a.sid||'.'||a.SERIAL#, a.username, a.TERMINAL, a.program, s.sql_text
 from v$session a, v$sqlarea s
 where a.sql_address = s.address(+)
 and a.sql_hash_value = s.hash_value(+)
 order by a.username, a.sid;

... ...

SQL>

· v$sql_plan

檢視V$SQL_PLAN包含了library cache中所有遊標的執行計劃。透過結合v$sqlarea可以查出library cache中所有語句的查詢計劃。先從v$sqlarea中得到語句的地址，然後在由v$sql_plan查出它的查詢計劃：

 select lpad(' ', 2*(level-1))||operation "Operation",
        options "Options",
        decode(to_char(id), '0', 'Cost='||nvl(to_char(position), 'n/a'), object_name) "Object Name",
        substr(optimizer, 1, 6) "Optimizer"
  from v$sql_plan a
 start with address = 'C0000000FCCDEDA0'
 and id = 0
 connect by prior id = a.parent_id
 and prior a.address = a.address
 and prior a.hash_value = a.hash_value;

Operation           Options              Object Name          Optimizer

------------------- -------------------- -------------------- ---------

SELECT STATEMENT                         Cost=0               CHOOSE

  NESTED LOOPS

    INDEX           RANGE SCAN           CSS_BL_CNTR_IDX1     ANALYZ

    INDEX           RANGE SCAN           CSS_BKG_BL_ASSN_UQ1  ANALYZ

SQL>

· v$librarycache

這個檢視包含了關於library cache的效能統計資訊，對於共享池的效能調優很有幫助。它是按照名稱空間分組統計的，結構如下：

欄位	資料型別	說明
NAMESPACE	VARCHAR2(15)	library cache的名稱空間
GETS	NUMBER	請求GET該名稱空間中物件的次數。
GETHITS	NUMBER	請求GET並在記憶體中找到了物件控制程式碼的次數（鎖定命中）。
GETHITRATIO	NUMBER	請求GET的命中率。
PINS	NUMBER	請求pin住該命名中物件的次數。
PINHITS	NUMBER	庫物件的所有後設資料在記憶體中被找到的次數（pin命中）。
PINHITRATIO	NUMBER	Pin命中率。
RELOADS	NUMBER	Pin請求需要從磁碟中載入物件的次數。
INVALIDATIONS	NUMBER	名稱空間中的非法物件（由於依賴的物件被修改所導致）數。
DLM_LOCK_REQUESTS	NUMBER	GET請求導致的例項鎖的數量。
DLM_PIN_REQUESTS	NUMBER	PIN請求導致的例項鎖的數量.
DLM_PIN_RELEASES	NUMBER	請求釋放PIN鎖的次數。
DLM_INVALIDATION_REQUESTS	NUMBER	GET請求非法例項鎖的次數。
DLM_INVALIDATIONS	NUMBER	從其他例項那的得到的非法pin數。

其中PIN的命中率（或未命中率）是我們系統調優的一個重要依據：

 select sum(pins) "hits",
        sum(reloads) "misses",
        sum(pins)/(sum(pins)+sum(reloads)) "Hits Ratio"
 from v$librarycache;

      hits     misses Hits Ratio

---------- ---------- ----------

  84962803        288 0.99999661

SQL>

 select sum(pins) "hits",
        sum(reloads) "misses",
        ((sum(reloads)/sum(pins))*100) "Reload%"
 from v$librarycache;

      hits     misses    Reload%

---------- ---------- ----------

  84963808        288 0.00033896

SQL>

當命中率小於99%或未命中率大於1%時，說明系統中硬解析過多，要做系統最佳化（增加Shared Pool、使用繫結變數、修改cursor_sharing等措施，效能最佳化不是本文重點，不再贅述）。

· v$library_cache_memory
這個檢視顯示了各個名稱空間中的庫快取記憶體物件的記憶體分配情況。一個記憶體物件是為了高效管理而組織在一起的一組內部記憶體。一個庫物件可能包含多個記憶體物件。

欄位	資料型別	說明
LC_NAMESPACE	VARCHAR2(15)	Library cache名稱空間
LC_INUSE_MEMORY_OBJECTS	NUMBER	屬於名稱空間並正被在共享池使用的記憶體物件數。
LC_INUSE_MEMORY_SIZE	NUMBER	正在使用的記憶體物件的大小總（M未單位）。
LC_FREEABLE_MEMORY_OBJECTS	NUMBER	共享池中空閒的記憶體物件數。
LC_FREEABLE_MEMORY_SIZE	NUMBER	空閒記憶體物件的大小總和（M為單位）。

· v$sgastat

這個檢視前面介紹過，是關於SGA使用情況的統計。其中，關於Shared Pool有詳細的統計資料。

1.1.5. 重做日誌快取（Redo Log Buffer）

Redo Log Buffer是SGA中一段儲存資料庫修改資訊的快取。這些資訊被儲存在重做條目(Redo Entry)中.重做條目中包含了由於INSERT、UPDATE、DELETE、CREATE、ALTER或DROP所做的修改操作而需要對資料庫重新組織或重做的必須資訊。在必要時，重做條目還可以用於資料庫恢復。

重做條目是Oracle資料庫程式從使用者記憶體中複製到Redo Log Buffer中去的。重做條目在記憶體中是連續相連的。後臺程式LGWR負責將Redo Log Buffer中的資訊寫入到磁碟上活動的重做日誌檔案（Redo Log File）或檔案組中去的。

引數LOG_BUFFER決定了Redo Log Buffer的大小。它的預設值是512K（一般這個大小都是足夠的），最大可以到4G。當系統中存在很多的大事務或者事務數量非常多時，可能會導致日誌檔案IO增加，降低效能。這時就可以考慮增加LOG_BUFFER。

但是，Redo Log Buffer的實際大小並不是LOB_BUFFER的設定大小。為了保護Redo Log Buffer，oracle為它增加了保護頁（一般為11K）：

SQL> select * from v$sgastat where name = 'log_buffer';

POOL         NAME                            BYTES

------------ -------------------------- ----------

             log_buffer                    7139328

1 row selected.

SQL> show parameter log_buffer

NAME                                 TYPE        VALUE

------------------------------------ ----------- -------------------

log_buffer                           integer     7028736

SQL>

1.1.6. 大池（large pool）

大池是SGA中的一塊可選記憶體池，根據需要時配置。在以下情況下需要配置大池：

o * 用於共享服務（Shared Server MTS方式中）的會話記憶體和Oracle分散式事務處理的Oracle XA介面

o * 使用並行查詢（Parallel Query Option PQO）時

o * IO服務程式

o * Oracle備份和恢復操作（啟用了RMAN時）

透過從大池中分配會話記憶體給共享服務、Oracle XA或並行查詢，oracle可以使用共享池主要來快取共享SQL，以防止由於共享SQL快取收縮導致的效能消耗。此外，為Oracle備份和恢復操作、IO服務程式和並行查詢分配的記憶體一般都是幾百K，這麼大的記憶體段從大池比從共享池更容易分配得到（所以叫“大”池嘛^_^）。

引數LARGE_POOL_SIZE設定大池的大小。大池是屬於SGA的可變區（Variable Area）的，它不屬於共享池。對於大池的訪問，是受到large memory latch保護的。大池中只有兩種記憶體段：空閒（free）和可空閒（freeable）記憶體段（關於不同型別記憶體段我們在後面介紹）。它沒有可重建（recreatable）記憶體段，因此也不用LRU連結串列來管理（這和其他記憶體區的管理不同）。大池最大大小為4G。

為了防止大池中產生碎片，隱含引數_LARGE_POOL_MIN_ALLOC設定了大池中記憶體段的最小大小，預設值是16K（同樣，不建議修改隱含引數）。

此外，large pool是沒有LRU連結串列的。

1.1.7. Java池（Java Pool）

Java池也是SGA中的一塊可選記憶體區，它也屬於SGA中的可變區。

Java池的記憶體是用於儲存所有會話中特定Java程式碼和JVM中資料。Java池的使用方式依賴於Oracle服務的執行模式。

Java池的大小由引數JAVA_POOL_SIZE設定。Java Pool最大可到1G。

在Oracle 10g以後，提供了一個新的建議器——Java池建議器——來輔助DBA調整Java池大小。建議器的統計資料可以透過檢視V$JAVA_POOL_ADVICE來查詢。如何藉助建議器調整Java池的方法和使用Buffer Cache建議器類似，可以參考Buffer Cache中關於建議器部分。

1.1.8. 流池（Streams Pool）

流池是Oracle 10g中新增加的。是為了增加對流（流複製是Oracle 9iR2中引入的一個非常吸引人的特性，支援異構資料庫之間的複製。10g中得到了完善）的支援。

流池也是可選記憶體區，屬於SGA中的可變區。它的大小可以透過引數STREAMS_POOL_SIZE來指定。如果沒有被指定，oracle會在第一次使用流時自動建立。如果設定了SGA_TARGET引數，Oracle會從SGA中分配記憶體給流池；如果沒有指定SGA_TARGET，則從buffer cache中轉換一部分記憶體過來給流池。轉換的大小是共享池大小的10%。

Oracle同樣為流池提供了一個建議器——流池建議器。建議器的統計資料可以透過檢視V$STREAMS_POOL_ADVICE查詢。使用方法參看Buffer Cache中關於最佳化器部分。

1.2. PGA (The Process Global Area)

PGA（Program Global Area程式全域性區）是一塊包含一個服務程式的資料和控制資訊的記憶體區域。它是Oracle在一個服務程式啟動是建立的，是非共享的。一個Oracle程式擁有一個PGA記憶體區。一個PGA也只能被擁有它的那個服務程式所訪問，只有這個程式中的Oracle程式碼才能讀寫它。因此，PGA中的結構是不需要Latch保護的。

我們可以設定所有服務程式的PGA記憶體總數受到例項分配的總體PGA（Aggregated PGA）限制。

在專有伺服器（Dedicated Server）模式下，Oracle會為每個會話啟動一個Oracle程式；而在多執行緒服務（Multi-Thread Server MTS）模式下，由多個會話共享通一個Oracle服務程式。

PGA中包含了關於程式使用到的作業系統資源的資訊，以及一些關於程式狀態的資訊。而關於程式使用的Oracle共享資源的資訊則是在SGA中。這樣做可以使在程式以外中止時，能夠及時釋放和清除這些資源。

1.2.1. PGA的組成

PGA由兩組區域組成：固定PGA和可變PGA（或者叫PGA堆，PGA Heap【堆——Heap就是一個受管理的記憶體區】）。固定PGA和固定SGA類似，它的大小時固定的，包含了大量原子變數、小的資料結構和指向可變PGA的指標。

可變PGA時一個記憶體堆。它的記憶體段可以透過檢視X$KSMPP（另外一個檢視X$KSMSP可以查到可變SGA的記憶體段資訊，他們的結構相同）查到。PGA堆包含用於存放X$表的的記憶體（依賴與引數設定，包括DB_FILES、CONTROL_FILES）。

總的來說，PGA的可變區中主要分為以下三部分內容：

o * 私有SQL區；

o * 遊標和SQL區

o * 會話記憶體

1.2.1.1. 私有SQL區（Private SQL Area）

前面已經說過，私有SQL區包含了繫結變數值和執行時期記憶體結構資訊等資料。每一個執行SQL語句的會話都有一個塊私有SQL區。所有提交了相同SQL語句的使用者都有各自的私有SQL區，並且他們共享一個共享SQL區。因此，一個共享SQL區可能和多個私有共享區相關聯。

一個遊標的私有SQL區又分為兩個生命週期不同的區：

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

o * 執行區。當執行結束時釋放。

建立執行區是一次執行請求的第一步。對於INSERT、UPDATE和DELETE語句，Oracle在語句執行結束時釋放執行區。對於查詢操作，Oracle只有在所有記錄被fetch到或者查詢被取消時釋放執行區。

1.2.1.2. 遊標和SQL區（Cursors and SQL Areas）

一個Oracle預編譯程式或OCI程式的應用開發人員能夠很明確的開啟一個遊標，或者控制一塊特定的私有SQL區，將他們作為程式執行的命名資源。另外，oracle隱含的為一些SQL語句產生的遞迴呼叫（前面有介紹，讀取資料字典資訊）也使用共享SQL區。

私有SQL區是由使用者程式管理的，如何分配和釋放私有SQL區極大的依賴與你所使用的應用工具。而使用者程式可以分配的私有SQL區的數量是由引數OPEN_CURSORS控制的，它的預設值是50。

在遊標關閉前或者語句控制程式碼被釋放前，私有SQL區將一直存在（但其中的執行區是在語句執行結束時被釋放，只有永久區一直存在）下去。應用開發人員可以透過將所有開啟的不再使用的遊標都關閉來釋放永久區，以減少使用者程式所佔用的記憶體。

1.2.1.3. 會話記憶體（Session Memory）UGA

會話記憶體是一段用於儲存會話變數（如登入資訊）和其他預會話相關資訊的記憶體。對於共享伺服器模式下，會話記憶體是共享的，而不是私有的。

對於複雜的查詢（如決策支援系統中的查詢），執行區的很大一部分被那些記憶體需求很大的操作分配給SQL工作區（SQL Work Area）。這些操作包括：

o * 基於排序的操作（ORDER BY、GROUP BY、ROLLUP、視窗函式）；

o * Hash Join

o * Bitmap merge

o * Bitmap create

例如，一個排序操作使用工作區（這時也可叫排序區Sort Area）來將一部分資料行在記憶體排序；而一個Hash Join操作則使用工作區（這時也可以叫做Hash區 Hash Area）來建立Hash表。如果這兩種操作所處理的資料量比工作區大，那就會將輸入的資料分成一些更小的資料片，使一些資料片能夠在記憶體中處理，而其他的就在臨時表空間的磁碟上稍後處理。儘管工作區太小時，Bitmap操作不會將資料放到磁碟上處理，但是他們的複雜性是和工作區大小成反比的。因此，總的來說，工作區越大，這些操作就執行越快。

工作區的大小是可以調整的。一般來說，大的工作區能讓一些特定的操作效能更佳，但也會消耗更多的記憶體。工作區的大小足夠適應輸入的資料和相關的SQL操作所需的輔助的記憶體就是最優的。如果不滿足，因為需要將一部分資料放到臨時表空間磁碟上處理，操作的響應時間會增長。

1.2.2. PGA記憶體自動管理

SQL工作區可以是自動的、全域性的管理。DBA只要設定引數PGA_AGGREGATE_TARGET給一個例項的PGA記憶體指定總的大小。設定這個引數後，Oracle將它作為一個總的全侷限制值，儘量使所有Oracle服務程式的PGA記憶體總數不超過這個值。

在這個引數出現之前，DBA要調整引數SORT_AREA_SIZE、 HASH_AREA_SIZE,、BITMAP_MERGE_AREA_SIZE 和CREATE_BITMAP_AREA_SIZE（關於這些引數，我們會在後面介紹），使效能和PGA記憶體消耗最佳。對這些引數的調整是非常麻煩的，因為即要考慮所有相關的操作，使工作區適合它們輸入資料大小，又要使PGA記憶體不消耗過大導致系統整體效能下降。

9i以後，透過設定了引數PGA_AGGREGATE_TARGET，使所有會話的工作區的大小都是自動分配。同時，所有*_AREA_SIZE引數都會失效。在任何時候，例項中可用於工作區的PGA記憶體總數都是基於引數PGA_AGGREGATE_TARGET的。工作區記憶體總數等於引數PGA_AGGREGATE_TARGET的值減去系統其他元件（如分配給會話的PGA記憶體）的記憶體消耗。分配給Oracle程式的PGA記憶體大小是根據它們對記憶體的需求情況來的。

引數WORKAREA_SIZE_POLICY決定是否使用PGA_AGGREGATE_TARGET來管理PGA記憶體。它有兩個值：AUTO和MANUAL。預設是AUTO，即使PGA_AGGREGATE_TARGET來管理PGA記憶體。其實，從引數WORKAREA_SIZE_POLICY的名字上可以看出，Oracle的PGA記憶體自動管理只會調整工作區部分，而非工作區部分（固定PGA區）則不會受影響。

還有注意一點就是：10g之前，PGA_AGGREGATE_TARGET只在專用服務模式下生效。而10g以後，PGA記憶體自動管理在專有服務模式（Dedicated Server）和MTS下都有效。另外，9i在OpenVMS系統上還不支援PGA記憶體自動管理，但10g支援。

設定了PGA_AGGREGATE_TARGET以後，每個程式PGA記憶體的大小也是受限制的：

o * 序列操作時，每個程式可用的PGA記憶體為MIN(PGA_AGGREGATE_TARGET * 5%, _pga_max_size/2)，其中隱含引數_pga_max_size的預設值是200M，同樣不建議修改它。

o * 並行操作時，並行語句可用的PGA記憶體為PGA_AGGREGATE_TARGET * 30% / DOP （Degree Of Parallelism 並行度）。

1.2.3. 專有服務（Dedicated Server）和共享服務（Shared Server）

對PGA記憶體的管理和分配，很大程度上依賴與服務模式。下面這張表顯示了在不同模式下，PGA記憶體不同部分的分配的異同：

記憶體區	專有服務	共享服務
會話記憶體UGA	私有的	共享的
永久區所在區域	PGA	SGA
SELECT語句的執行區所在區域	PGA	PGA
DML/DDL語句的執行區所在區域	PGA	PGA

1.2.4. 重要引數

PGA的管理和分配是由多個系統引數控制的，下面介紹一下這些引數：

1.2.4.1.

這個引數前面介紹了。它控制了所有程式PGA記憶體的總的大小。

在專有服務模式下，推薦使用

的取值範圍是10M～（4096G - 1 ）bytes。

對於

o * 對於OLTP系統，

o * 對於DSS系統，

例如，你的系統是一個OLTP系統，實體記憶體為8G，那麼推薦

1.2.4.2. WORKAREA_SIZE_POLICY

引數WORKAREA_SIZE_POLICY決定是否使用PGA_AGGREGATE_TARGET來管理PGA記憶體。它有兩個值：AUTO和MANUAL。預設是AUTO，即使用PGA_AGGREGATE_TARGET來管理PGA記憶體。

1.2.4.3. sort_area_size

Oracle在做排序操作（ORDER BY、GROUP BY、ROLLUP、視窗函式）時，需要從工作區中分配一定記憶體區域對資料記錄做記憶體排序。在排序完成後，資料返回之前，Oracle會釋放這部分記憶體，。SORT_AREA_SIZE指定了這部分記憶體的大小。設定了

除非在共享服務模式下，一般不推薦設定這個引數，而推薦使用

Oracle也許會為一個查詢分配多個排序區。通常情況下，一條語句只有1、2個排序操作，但是對於複雜語句，可能存在多個排序操作，每個排序操作都有自己的排序區。因此，語句的複雜性也影響到每個程式PGA記憶體的大小。

1.2.4.4. sort_area_retained_size

這個引數與SORT_AREA_SIZE配合使用。它指定了在排序操作完成後，繼續保留使用者全域性區（User Global Area UGA，關於UGA與PGA、SGA關係在UGA部分介紹）記憶體的最大大小，以維護記憶體中的排序，直到所有資料行被返回後才釋放（上面提到，SORT_AREA_SIZE的記憶體在排序完成、資料行返回之前被釋放）回UGA（注意：是釋放回UGA，而不會被作業系統回收）。

SORT_AREA_RETAINED_SIZE在共享服務中是從SGA中分配的（因為此時UGA從SGA中分配），在專有服務模式中是從PGA中分配的。而SORT_AREA_SIZE無論在那種模式下都從PGA中分配。

同樣，設定了

1.2.4.5. hash_area_size

HASH_AREA_SIZE設定了在做Hash Join時，hash記憶體表可佔用的記憶體空間。同樣，設定了

此外，由於Hash Join只有在最佳化器為CBO（Cost-Base Optimizer）模式下才有效，因此這個引數也只有CBO模式下才有意義。

1.2.4.6. hash_join_enable

這個引數決定是否啟用Hash Join。預設為TRUE。

由於Hash Join只有在最佳化器為CBO（Cost-Base Optimizer）模式下才有效，因此這個引數也只有CBO模式下才有意義。

10g中，這個引數是隱含引數。

1.2.4.7. bitmap_merge_area_size

在使用點陣圖索引（Bitmap Index）時，oracle為索引點陣圖段建立一張點陣圖。在進行點陣圖索引掃描時，需要將掃描到的點陣圖索引排序後與點陣圖合併（Merge），Oracle會在PGA中開闢一片區域用於排序和合並。引數BITMAP_MERGE_AREA_SIZE指定了這篇區域的大小。預設值是1M。

同樣，設定了

1.2.4.8. create_bitmap_area_size

在欄位的集的勢（Cardinality 參照記錄行數，欄位的不同值的一個因子。記錄數越多，不同值越少，則集的勢越小）很小，並且表的資料變化不大時，可以考慮為欄位建立點陣圖索引以提高對該欄位的檢索效率。這個引數指定可在建立點陣圖索引時的記憶體空間佔用大小。它的預設大小是8M。

同樣，設定了

1.2.4.9. open_cursors

這個引數設定一個會話可以同時開啟的遊標數。由於每開啟一個遊標，都需要一部分PGA記憶體分配出來作為私有SQL區。因此這個引數也影響了每個程式的PGA記憶體的佔用大小。

1.2.4.10. _pga_max_size

這是一個隱含引數。它規定了一個PGA的最大大小。可參見1.2.2。

1.2.5. 重要檢視

1.2.5.1. V$PGASTAT

V$PGASTAT提供了PGA記憶體使用情況的統計資訊和當自動PGA記憶體管理啟動時的統計資訊。檢視裡面的累加資料是自從例項啟動後開始累加的。

欄位	資料型別	說明
NAME	VARCHAR2(64)	統計的名稱，包括： aggregate PGA target parameter – 當前引數PGA_AGGREGATE_TARGET的值。如果引數沒有設定，則值為0並且PGA記憶體自動管理被關閉。 aggregate PGA auto target – 在自動管理模式下，可用於工作區的總的PGA記憶體數。這個數值是動態的，和PGA_AGGREGATE_TARGET 的值以及當前工作區的負載有關，Oracle會動態調整它。這個值相對與PGA_AGGREGATE_TARGET來說很小。其他很大一部分的PGA記憶體都被用於系統的其他元件（如PLSQL和Java的記憶體）。DBA必須保證在自動模式下有足夠的PGA記憶體用於工作區。 global memory bound – 自動模式下可用的工作區的最大大小。Oracle根據當前工作區的負載動態調整這個值。當系統中活動的工作區數量增加時，global memory bound一般會下降。如果global bound 降到低於1M，則要考慮增加PGA_AGGREGATE_TARGET了。 total PGA allocated – 當前例項分配的總的PGA記憶體大小。Oracle會試圖保持這個值在PGA_AGGREGATE_TARGET以內。然而，當工作區負載增加得非常快或者PGA_AGGREGATE_TARGET被設定得很小時，這個值也許會在一段時間內超過PGA_AGGREGATE_TARGET。 total PGA used – 當前被工作區消耗得PGA記憶體。這個數值也可以用於計算有多少PGA記憶體被其他元件（如PLSQL或Java）所消耗。 total PGA used for auto workareas – 自動模式下，當前多少PGA記憶體被工作區所消耗。這個數值也可以用於計算有多少PGA記憶體被其他元件（如PLSQL或Java）所消耗。 total PGA used for manual workareas –手動模式下，當前多少PGA記憶體被工作區所消耗。這個數值也可以用於計算有多少PGA記憶體被其他元件（如PLSQL或Java）所消耗。 over allocation count – 這個數值是自從例項啟動後累加的。當PGA_AGGREGATE_TARGET設定非常小或工作區負載增長很快時，會超額分配PGA記憶體（分配的值大於PGA_AGGREGATE_TARGET）。這種情況發生時，Oracle不能限制PGA記憶體小於PGA_AGGREGATE_TARGET，只能分配實際需要的PGA記憶體。此時，建議透過建議器檢視V$PGA_TARGET_ADVICE來增加PGA_AGGREGATE_TARGET的大小。 bytes processed – 自從例項啟動後，被記憶體SQL操作處理的位元組數。 extra bytes read/written – 自從例項啟動後，需要額外輸入資料所處理的位元組數。當工作區無法在最佳狀態下執行時，就需要進行這個額外處理。 cache hit percentage – Oracle計算出來的一個與PGA記憶體元件效能相關的資料，是自從例項啟動後累加的。如果這個值是100%，則表示例項啟動後，所有系統使用到的工作區都分配了最佳的PGA記憶體。當工作區無法在最佳狀態下執行，就需要進行額外的資料輸入處理，這將會降低cache hit percentage。
VALUE	NUMBER	統計資料
UNITS	VARCHAR2(12)	資料的單位 (microseconds, bytes, or percent)

1.2.5.2. V$PGA_TARGET_ADVICE

這個檢視是可以顯示PGA最佳化建議器的估算預測結果，它顯示了在各種

只有當建議器開啟（隱含引數_smm_advice_enabled為TRUE），並且引數STATISTICS_LEVEL值不是BASIC時，檢視中才會有內容。例項重啟後，所有預測資料都會被重寫。

欄位	資料型別	說明
PGA_TARGET_FOR_ESTIMATE	NUMBER	用於預測的PGA_AGGREGATE_TARGET值。
PGA_TARGET_FACTOR	NUMBER	預測的PGA_AGGREGATE_TARGET與當前PGA_AGGREGATE_TARGET的比。
ADVICE_STATUS	VARCHAR2(3)	建議器狀態（ON或OFF）
BYTES_PROCESSED	NUMBER	預測的被所有工作區處理的位元組數。
ESTD_EXTRA_BYTES_RW	NUMBER	當PGA_AGGREGATE_TARGET設定為預測值時，需要額外讀寫的位元組數。
ESTD_PGA_CACHE_HIT_PERCENTAGE	NUMBER	當PGA_AGGREGATE_TARGET設定為預測值時，快取命中率。這個值等於BYTES_PROCESSED / (BYTES_PROCESSED + ESTD_EXTRA_BYTES_RW)
ESTD_OVERALLOC_COUNT	NUMBER	當PGA_AGGREGATE_TARGET設定為預測值時，需要超額分配的PGA記憶體。如果非0則說明PGA_AGGREGATE_TARGET設定得太小。

1.2.5.3. V$SYSSTAT 、V$SESSTAT

這兩個檢視顯示了系統（會話）的統計資料。他們的統計專案基本相同，但不同之處在於一個是系統級的、一個是會話級的。

透過這兩個檢視我們可以查出像sort這樣操作對工作區的使用情況：

select * from V$SYSSTAT where name like '%sort%';

STATISTIC# NAME                                                                  CLASS      VALUE

---------- ---------------------------------------------------------------- ---------- ----------

       245 sorts (memory)                                                           64    2876455

       246 sorts (disk)                                                             64        483

       247 sorts (rows)                                                             64  116554720

SQL>

1.2.5.4. V$SQL_WORKAREA

這個檢視顯示了被SQL遊標使用的工作區的資訊。儲存在Shared Pool中的每條SQL語句都有一個或多個子遊標，它們能被V$SQL顯示。而V$SQL_WORKAREA顯示需要被這些遊標所使用的工作區資訊。可以將它與V$SQL進行join查詢。

透過這個檢視可以解決以下一些問題：

1、請求最多的工作區；

2、在自動模式下，佔用記憶體最多的工作區。

欄位	資料型別	說明
ADDRESS	RAW(4 \| 8)	遊標控制程式碼的地址。
HASH_VALUE	NUMBER	遊標控制程式碼的Hash值。這個欄位和ADDRESS欄位join V$SQLAREA可以定位出相關語句。
CHILD_NUMBER	NUMBER	使用此工作區的子游標數。
WORKAREA_ADDRESS	RAW(4 \| 8)	工作區控制程式碼的地址。唯一定位了一條記錄
OPERATION_TYPE	VARCHAR2(20)	工作區的操作型別(SORT, HASH JOIN, GROUP BY, BUFFERING, BITMAP MERGE, or BITMAP CREATE)
OPERATION_ID	NUMBER	唯一定位查詢計劃中的一個操作的值，可以和檢視V$SQL_PLAN join。
POLICY	VARCHAR2(10)	工作區的模式(MANUAL或AUTO)
ESTIMATED_OPTIMAL_SIZE	NUMBER	估計需要此工作區來執行記憶體中操作的所需的大小。
ESTIMATED_ONEPASS_SIZE	NUMBER	估計需要此工作區來一次執行記憶體中操作的所需的大小。
LAST_MEMORY_USED	NUMBER	最後一次執行遊標所使用的工作區大小。
LAST_EXECUTION	VARCHAR2(10)	最後一次執行遊標，工作區請求記憶體的方式，OPTIMAL, ONE PASS, ONE PASS 或MULTI-PASS。
LAST_DEGREE	NUMBER	最後一次執行並行操作的並行度（Degree of parallelism DOP）。
TOTAL_EXECUTIONS	NUMBER	此工作區啟用的次數。
OPTIMAL_EXECUTIONS	NUMBER	此工作區執行於optimal模式的次數
ONEPASS_EXECUTIONS	NUMBER	此工作區執行於one-pass 模式的次數
MULTIPASSES_EXECUTIONS	NUMBER	此工作區執行於one-pass記憶體請求情況下的次數
ACTIVE_TIME	NUMBER	此工作區啟用的評價時間數。
MAX_TEMPSEG_SIZE	NUMBER	例項化此工作區所建立的臨時段的最大大小。
LAST_TEMPSEG_SIZE	NUMBER	最後一次例項化此工作區所建立的臨時段的大小。

1.2.5.5. V$SQL_WORKAREA_ACTIVE

這個檢視包含了系統當前分配的工作區的瞬間資訊。可以透過欄位WORKAREA_ADDRESS join V$SQL_WORKAREA來查詢工作區資訊。如果工作區溢位到磁碟，則這個檢視就包含了這個工作區所溢位的臨時段的資訊。透過與檢視V$TEMPSEG_USAGE join，可以得到更多的臨時段資訊。

這個檢視可以解決以下問題：

1、當前系統分配最大的工作區；

2、超額分配記憶體的百分比（EXPECTED_SIZE < ACTUAL_MEM_USED），和未超額分配記憶體的百分比（EXPECTED_SIZE > ACTUAL_MEM_USED）；

3、哪個活動的工作區使用了超出記憶體管理預期的記憶體大小；

4、哪個活動的工作區溢位到磁碟了。

欄位	資料型別	說明
WORKAREA_ADDRESS	RAW(4 \| 8)	工作區控制程式碼的地址。唯一定位了一條記錄。
OPERATION_TYPE	VARCHAR2(20)	使用此工作區的操作 (SORT, HASH JOIN, GROUP BY, BUFFERING, BITMAP MERGE, 或 BITMAP CREATE)
OPERATION_ID	NUMBER	唯一定位查詢計劃中的一個操作的值，可以和檢視V$SQL_PLAN join。
POLICY	VARCHAR2(6)	工作區的模式(MANUAL或AUTO)
SID	NUMBER	會話ID
QCINST_ID	NUMBER	查詢協調（查詢協調在並行查詢中出現）者例項ID。
QCSID	NUMBER	查詢協調者的會話ID。
ACTIVE_TIME	NUMBER	此工作區啟用的平均時間（釐秒為單位）
WORK_AREA_SIZE	NUMBER	被當前操作使用的最大工作區大小。
EXPECTED_SIZE	NUMBER	工作區的預期大小。預期大小是記憶體管理器設定的。當WORK_AREA_SIZE 大於EXPECTED_SIZE 時，記憶體會超額分配。
ACTUAL_MEM_USED	NUMBER	當前分配個工作區的PGA記憶體大小（KB）。這個值在0和 WORK_AREA_SIZE之間。
MAX_MEM_USED	NUMBER	這個工作區使用的最大大小（KB）。
NUMBER_PASSES	NUMBER	這個工作區的通道數(如果在OPTIMAL模式下為0)
TEMPSEG_SIZE	NUMBER	用於此工作區的臨時段大小。
TABLESPACE	VARCHAR2(31)	建立臨時段給這個工作區的表空間名字。
SEGRFNO#	NUMBER	建立臨時段的表空間的檔案ID。
SEGBLK#	NUMBER	給工作區建立臨時段的block數。

1.2.5.6. V$PROCESS

這個檢視顯示了所有Oracle程式的資訊。其中以下幾個欄位則說明了程式PGA記憶體的使用情況。

PGA_USED_MEM：程式使用的PGA記憶體

PGA_ALLOCATED_MEM：分配給程式的PGA記憶體

PGA_MAX_MEM：程式使用的最大的PGA記憶體。

1.3. UGA (The User Global Area)

PGA是一段包含一個Oracle服務或後臺程式的資料和控制資訊的記憶體。PGA的大小依賴與系統的配置。在專用服務（Dedicated Server）模式下，一個服務程式與一個使用者程式相關，PGA就包括了堆空間和UGA。而UGA（User Global Area 使用者全域性區）由使用者會話資料、遊標狀態和索引區組成。在共享服務（MTS）模式下，一個共享服務程式被多個使用者程式共享，此時UGA是Shared Pool或Large Pool的一部分（依賴與配置）。

許多DBA都不理解PGA和UGA之間的區別。其實這種區別可以簡單的理解為程式和會話直接的區別。在專用服務模式下，程式和會話是一對一的；而在MTS模式下，程式和會話是一對多的關係。PGA是服務於程式的，它包含的是程式的資訊；而UGA是服務於會話的，它包含的是會話的資訊。因此，MTS模式下，PGA和UGA之間的關係也是一對多的。

UGA中包含了一個會話的資訊，包括：

   o        開啟遊標的永久區和執行區；
o        包的狀態資訊，特別是包的變數；
o        Java會話的資訊；
o        啟用的角色；
o        啟用的跟蹤事件（ALTER SESSION SET EVENT …）；
o        起作用的NLS引數（SELECT * FROM NLS_SESSION_PARAMETERS;）；
o        所有開啟的db link；
o        會話對於信任的Oracle的託管訪問標記（mandatory access control (MAC）

和PGA一樣，UGA也由兩組區組成，固定UGA和可變UGA（或者說UGA堆）。固定UGA包含了大概70個原子變數、小的資料結構以及指向UGA堆的指標。

UGA heap中的段可以透過表X$KSMUP查到（它的結構和X$KSMSP相同）。UGA堆包含了儲存一些固定表（X$表）的永久記憶體（依賴與特定引數的設定，如OPEN_CURSORS，OPEN_LINKS和MAX_ENABLED_ROLES）。除此以外，大部分的UGA用於私有SQL區。UGA記憶體的所在依賴於會話的設定。在專用服務模式下，會話和程式是一對一的關係，UGA位於PGA中。固定UGA是PGA中的一段記憶體段，而UGA堆是PGA的子堆。在MTS模式下，固定UGA是shared pool中的一段記憶體段，而UGA堆是Large Pool的子堆，如果從large pool分配失敗，則從shared pool中分配。

MTS模式下，可以透過Profile中的PRIVATE_SGA項（透過dba_profiles檢視）來控制每個UGA佔用的SGA的總的大小，但是不建議這樣做。

Oracle 9.2以後，有一個新的隱含引數：_use_realfree_heap。當設定這個引數為true時，Oracle會為CGA、UGA單獨分配堆，而不從PGA中分配。它的預設值為false，而當設定了pga_aggregate_target後，它的值自動被改為true。

1.4. CGA (The Call Global Area)

與其他的全域性區不同，CGA（Call Global Area 呼叫全域性區）的存在是瞬間的。它只存在於一個呼叫過程中。對於例項的一些低層次的呼叫需要CGA，包括：

o 解析一條SQL語句；

o 執行一條SQL語句；

o 取一條SELECT語句的輸出值。

如果語句產生了遞迴呼叫，則需要為每個遞迴呼叫分配一個CGA。如上所述，遞迴呼叫是在語句解析、最佳化器產生語句查詢計劃、DML操作時需要查詢或修改資料字典資訊的呼叫。

無論UGA存在於PGA還是SGA，CGA都是PGA的subheap。因為無論那種模式，會話在做呼叫時總需要一個程式進行處理。這一點很重要，特別是在MTS模式下時，如果發現一次呼叫很久沒有響應，則可能需要增加PGA的大小。

當然，呼叫並不是只透過CGA中的資料結構來工作。實際上，呼叫所需要的大部分的重要資料結構都來自於UGA。例如私有SQL區和排序區都存放在UGA中，因為呼叫結束後，它們是被保留的。CGA中只包含了那些呼叫結束後可以被釋放的資料。例如，CGA中包含了直接IO快取、關於遞迴呼叫的資訊、用於表示式評估（產生查詢計劃時）的的堆空間和其他一些臨時資料。

Java呼叫記憶體也分配在CGA中。它被分為三部分空間：堆空間、新空間和老空間。在呼叫期間（呼叫長短依賴於使用期長短和大小），在新空間和老空間中的記憶體段不再使用的記憶體段將被垃圾收集器回收。

1.5. 軟體程式碼區（Software Code Area）

軟體程式碼區是一部分用於存放那些正在執行和可以被執行的程式碼（Oracle自身的程式碼）的記憶體區。Oracle程式碼一般儲存在一個不同於使用者程式儲存區的軟體程式碼區，而使用者程式儲存區是排他的、受保護的區域。

軟體區的大小一般是固定的，只有Oracle軟體升級或重灌後才會改變。在不同作業系統下，這部分割槽域所要求的大小也不同。

軟體區是隻讀的，可以被安裝成共享的或非共享的。可能的情況下，Oracle程式碼是共享的，這樣所有Oracle使用者都可以直接訪問這些程式碼，而不需要各自儲存一份複製在自己的記憶體中。這樣可以節省大量記憶體並提高整體效能。

而使用者程式也可以是共享的或非共享的。一些Oracle工具（如SQL Plus）能被安裝成共享的，但有些不能。如果一臺機器執行多個例項，這些例項可以使用同一個Oracle程式碼區。

另外要注意的是：並不是所有作業系統都能將軟體區安裝成共享的，如Windows。

2. Oracle的記憶體管理

在這部分章節中，我們將從更底層的角度來了解Oracle的記憶體管理。當然，涉及到記憶體管理，就不可避免的要了解OS對記憶體的管理，在這以章節中，將談到不少關於OS記憶體的管理知識。如果概念記得不清除了，可以找一本《作業系統》的書看看先。

2.1. Oracle記憶體管理基礎

要了解記憶體管理，首先需要知道虛擬記憶體。而要了解虛擬記憶體，就先要知道CPU定址。我們知道，目前主流的CPU都是32位或者64位的。那麼這個位數指的是什麼呢？就是指CPU的最大定址能力。在CPU中，所有一切都是二進位制表示的，那麼一個32位CPU的定址範圍就是2^32=4G。但有可能一臺機器的實際實體記憶體沒有這麼大，比如說只有2G。而程式設計師在程式設計的時候根本不用考慮實際實體記憶體多大，還是按照4G的記憶體來分配。這時，OS就提出了一個虛擬記憶體的概念，如果所定址的資料實際上不在實體記憶體中，那就從“虛擬記憶體”中來獲取。這個虛擬記憶體可以是一個專門檔案格式的磁碟分割槽（比如UNIX下的swap分割槽），也可以是硬碟上的某個足夠大的檔案（比如win下的那個i386檔案，好像是這個名字）。實體記憶體中長期不用的資料，也可以轉移到虛擬記憶體中。這樣的交換由OS來控制，使用者看起來就好像實體記憶體大了一樣。

虛擬記憶體定址的一個好處就是可以使程式使用很大的虛擬記憶體地址，而無需考慮實際的實體記憶體的大小。這使得程式記憶體可以基於使用需要，從邏輯上分為幾個不同段。這些段可能對映到不連續的虛擬記憶體地址上，以使記憶體能夠夠增加。

為了共享RAM，需要有一個叫做交換磁碟（swap disk）的特殊磁碟空間。交換磁碟的重要目的是儲存程式的透過LRU演算法換出的記憶體，這樣可以使很多程式能夠共享有限的RAM。一旦非活動程式的RAM頁被寫入交換磁碟（Page Out），作業系統可以使空出來的記憶體用於其他活動的程式。如果非活動程式稍後又繼續執行，被page out的RAM頁又重新從交換磁碟中載入RAM（Page in）。這種重新載入RAM頁的動作就叫交換，而交換是非常消耗時間的，並且會降低相關程式的效能。

儘管交換磁碟確保併發的RAM使用量能大於實際的RAM，但是為了確保最佳效能，交換空間絕不要被活動程式所使用。這是因為從交換磁碟上讀取page out的RAM頁要比直接從RAM中讀取記憶體頁要慢14000倍。磁碟訪問都是以毫秒記的，而RAM訪問是以10億份之一秒記的。

2.1.1. Oracle的記憶體段型別

段（Segement）在OS上是對不同記憶體的使用目的和存放位置不同的區分。和一般的程式一樣，Oracle使用以下幾種段型別：

o 程式文字（Pragram Text）

文字段包括了程式本身的可執行的機器程式碼（除動態連結庫以外）。文字段一般標識為只讀，因此它能被多個程式共享來跑同一個程式。

o 初始化全域性資料（Initialized Global Data）

這一段包括了被編譯器初始化的全域性資料，比如用於跟蹤資料的字串。初始化資料能被修改，因此它不能被執行同一程式的多個程式共享。Oracle很少使用這個段。

o 未初始化全域性資料（Uninitialized Global Data）

未初始化全域性資料一般稱為BSS（Block Started by Symbol 以符號開始的塊）段。這一段包括了靜態分配的全域性資料，這些資料在程式執行時被程式初始化。Oracle也很少使用這個段。

o 資料堆（Data Heap）

資料堆被用於程式在執行時，透過使用系統呼叫malloc()或sbrk()動態分配記憶體。Oracle將資料heap用於PGA。

o 執行堆疊（Execution Stack）

無論什麼時候一個函式被呼叫，它的引數和返回上下文被push到一個執行堆疊中。返回上下文實際上是一組CPU註冊值，這些註冊值描述了程式在呼叫函式時那一刻的狀態。當呼叫結束後，堆疊被POP而上下文被保留，以使執行能從函式呼叫時的結構狀態立即執行下去。堆疊同時還保留了程式碼塊的本地變數。堆疊大小依賴於函式巢狀或遞迴呼叫的深度、引數和本地變數所需的記憶體大小。

o 共享庫（Shared Libraries）

共享庫是一個與位置無關的可執行程式碼集，這個集合實現了許多程式——特別是系統呼叫功能——所需要的功能。共享庫段也是隻讀的，它被所有的程式（包括Oracle程式）共享。共享庫無需儲存一份在記憶體中。當呼叫了共享庫中的一個函式後，程式需要開啟共享庫檔案，然後透過系統呼叫mmap()將它對映到它的地址空間去。

使用共享庫的另外一種方法是在程式文字段本身將需要的系統呼叫include進去。在那些不支援共享庫的作業系統中或用上面方式有問題時就需要這樣做。在大多數作業系統中，Oracle使用共享庫作為來實現系統呼叫而不是實現Oracle程式碼本身。然而，Java類庫都是編譯好的，並且作為共享庫動態連結的。

o 共享記憶體段（Shared Memory Segment）

共享記憶體允許關聯的程式共同讀寫記憶體中的同樣資料。每個需要在共享記憶體段中定址的程式都需要先將這段記憶體附到它自己的虛擬記憶體地址中去（一般透過shmat()系統呼叫實現）。Oracle SGA就是使用的共享記憶體段。

2.1.2. Oracle的記憶體管理模組

Oracle中有兩個記憶體管理模組。一個是核心服務記憶體管理模組（Kernel Service Memory KSM）；一個是核心通用堆管理模組（Kernel Generic Heap KGH）

在X$表中，有兩種用於這兩個模組的表，它們就是以KSM和KGH開頭的表。這兩個模組相互非常緊密。記憶體管理模組是負責與作業系統進行介面以獲取用於Oracle的記憶體，同時還負責靜態記憶體的分配。這個模組中比較重要的X$表是X$ksmfs，它記錄了固定 sga、buffer cache、log buffer在核心服務記憶體中的分配。而堆管理模組則負責動態記憶體的管理。這也就是為什麼SGA和PGA中堆又叫可變記憶體區了。Shared Pool、Library cache和PGA的堆都是由這個模組管理的。

一個堆（Heap）包括一個堆描述符和一個或多個記憶體擴充套件段（extent）。一個堆還可以包含子堆（Subheap）。這種情況下，堆描述符和子堆的擴充套件段可以被視為其父堆的大塊（chunk）.堆描述符的大小依賴於堆的型別和堆的空閒列表和LRU列表所包含的列表（Header）頭的多少。一個擴充套件段又一個包含指向前一個和後一個擴充套件段指標（Pointer）的小的頭部，擴充套件段的其他記憶體就是堆可用於動態分配的記憶體。

除了還包含一個保留列表的這一特性外，Shared Pool中的子堆具有與Shared Pool本身相同的結構。記憶體是以Chunk為單位分配的。空閒的chunk按照大小來組織在相應的空閒列表（Free List）中。而未pin住的、可重建（unpinned recreatable）的chuck被維護在兩個分別用於週期性chunk和短期chunk的LRU連結串列中。子堆還有一個包含少許空閒記憶體的主永久記憶體chunk。子堆也許還包含子堆，一共可以巢狀到四層。

子堆的概念非常重要，因為大多數被快取在shared pool中的物件實際上都被儲存在子堆中，而不是儲存在最上一層的堆中。在一個子堆中尋找空閒空間就相當於在shared pool中尋找空閒空間。它們的不同處就在於子堆可以透過分配新的擴充套件段（extent）來增長，而shared pool具有固定的擴充套件段數（10g引入SGA自動管理特性後，shared pool的擴充套件段數也是可變的）。為子堆分配新的擴充套件段受到擴充套件段大小的限制，因此會存在這樣的可能，因為沒有任何一個父堆可以分配一個所需最小擴充套件段大小的chunk導致在子堆中查詢一個小的chunk失敗（拋4031錯誤）。

為了減少記憶體錯誤，在10g中，引入了多個隱含引數對擴充套件段進行控制，

_bct_buffer_allocation_min_extents －每次buffer cache分配時的最小擴充套件段數（1）。

_compilation_call_heap_extent_size 編譯呼叫時分配堆的擴充套件段的大小（16384）10gR2引入。

_kgl_fixed_extents library cache記憶體分配時是否使用固定的擴充套件段大小（TRUE），10gR2引入。

_mem_std_extent_size 固定擴充套件段大小的堆的標準擴充套件段大小（4096），10gR2引入。

_minimum_extents_to_shrink 當記憶體收縮時，收縮的最少擴充套件段數（1）

_total_large_extent_memory 分配大擴充套件段的記憶體數（0），10gR1引入，10gR2中已經廢除。

_pga_large_extent_size（1048576）和_uga_cga_large_extent_size（262144）控制了當使用系統函式mmap()初始化時，PGA、UGA和CGA擴張段的最大大小。

2.1.3. 記憶體分配顆粒Granule

在Oracle的記憶體分配和管理中，有一個重要的單位：Granule（顆粒）。granule是連續虛擬記憶體分配的單位。Granule的大小依賴於SGA的總的大小（即SGA_MAX_SIZE大小）。當SGA小於128M時，granule為4M，SGA大於128M時，granule為16M。

Buffer Cache、Shared Pool、Large Pool和Java Pool（在10g中，還包括Streams Pool、KEEP buffer cache、RECYCLE buffer cache、nK Buffer Cache、ASM Buffer Cache）的增長和收縮都是以granule為單位的。SGA的各個元件的大小增長、收縮情況可以透過檢視v$sga_dynamic_components（這個檢視在1.1.2中有介紹）來觀察。

在例項啟動時， Oracle先分配granule條目（Entry），使所有granule能支援到SGA_MAX_SIZE的空間大小。如果沒有設定PRE_PAGE_SGA和LOCK_SGA，在例項啟動時，每個元件請求它所需要的最少granule。

因此最小SGA（佔用實體記憶體）是3個granule，包括：

o 固定SGA（包括redo buffer）一個granule

o Buffer Cache一個granule

o Shared Pool一個granule

我們可以透過“ALTER SYSTEM”命令來修改分配給各個元件的granule數。當DBA想要給元件增加granule時，需要考慮例項中是否還有足夠的granule來分配給新增加的元件大小（即增大後，各個元件之和小於SGA_MAX_SIZE）。有一點要注意，ALERT SYSTEM指定的是新的元件大小，是記憶體的大小，不是granule數。而Oracle在分配記憶體時，會以granule為單位，如果新分配的大小不是granule大小的倍數，則會使用最接近且大於分配數的granule的倍數值。例如，Unix中，當granule為16M，新分配元件大小為120M（不是16的倍數），Oracle則會實際分配128M（16×8）；32位windows下，SGA小於1G時granule是4M，大於1G時granule是8M。

執行ALERT SYSTEM擴充套件元件大小後，前臺程式（即執行ALTER SYSTEM命令）的程式會將SGA中的可用的granule（按照擴充套件新增大小計算）先保留下來。當這些granule被保留後，前臺程式將後續處理交給後臺程式。後臺程式負責將這些保留的granule加到指定的元件的granule列表中去。這就完成了一次對SGA元件的擴充套件。

Granule的預設大小是根據以上規則規定的，但也可以透過隱含引數_ksm_granule_size來修改（強烈不建議修改）。另外，隱含引數_ksmg_granule_locking_status可以設定記憶體分配是否強制按照granule為單位進行分配。

2.1.4. SGA記憶體

當一個Oracle例項啟動後，主要的SGA區的大小一開始基於初始化引數計算得出。這些大小可以透過show sga顯示（例項啟動後也會顯示出這些資訊）。但是，在共享記憶體段分配之前，每個區（Area）的大小都只有大概一個記憶體頁大小。當需要時，這些區被分為一些子區（sub-area），因此沒有一個子區會大於作業系統所限制的共享記憶體段（UNIX下受SHMMAX限制）的大小。對於可變區，有一個作業系統規定的最小子區大小，因此可變區的大小是最小子區大小的倍數。

如果可能，Oracle會為整個SGA分配一個單獨的共享記憶體段。然而，如果SGA大於作業系統限制的單個共享記憶體段的大小時，Oracle會使用最佳的演算法來將所有子區組織在多個共享段中，並且不超過SGA最大大小的限制。

嚴重的頁入/頁出會導致很嚴重的系統效能問題。然而，大記憶體消耗導致的間斷的頁入/頁出是沒有影響的。大多數系統都有大量的非活動記憶體被page out而沒有什麼效能影響。但少量的頁出也是有問題的，因為這會導致SGA中那些中度活性的頁會經常page out。大多數作業系統提供了一個讓Oracle鎖住SGA（設定lock_sga引數為TRUE）到實體記憶體中的機制以防止page out。在某些作業系統中，oracle需要有特定的系統許可權來使用這一特性。

當共享池分配了一個chunk，程式碼會返回給執行分配的函式一個註釋。這些註釋可以透過表X$KSMSP的欄位KSMCHCOM查到。它們同時描述了這塊記憶體分配的目的。如以下語句：

select ksmchcom, ksmchcls, ksmchsiz from x$ksmsp;

2.1.5. 程式記憶體

程式包括程式程式碼（文字）、本地資料域（程式堆疊、程式堆【主要是PGA】和程式BSS【未初始化的全域性資料】）和SGA。程式程式碼的大小由基本核心、核心、聯機的網路情況以及所使用的作業系統決定的。SGA大小是由Oracle初始化引數決定的。而這兩部分是共享。隨著使用者的增加，它們與單個Oracle服務程式的關係越來越小。它們是可以透過修改Oracle配置引數來改變的。

本地資料域中的堆疊是根據需要來增大、縮小的。然而，堆就不會釋放記憶體了。堆的主要組成部分是PGA。而影響PGA的主要因素是sort_area_size（如果沒有配置

因此，總的來說，可以有以下方法來限制程式記憶體大小：

1、降低相關的核心引數；

2、透過Oracle引數來降低SGA；

3、透過減小sort_area_size來降低PGA。

在UNIX平臺中，一般透過作業系統命令如“ps”或“top”來定位一個經常的記憶體大小。這些工具可以預警那些大量佔用記憶體的程式。但是，這些工具統計出來的Oracle程式的記憶體情況往往是是實際PGA記憶體是有出入的。

這是為什麼呢？有兩種原因會導致錯誤的程式記憶體報告錯誤：將那些非程式私有的（如共享記憶體）計算進去了；作業系統沒有回收空閒記憶體。下面詳細解釋它們。

· 統計了非私有記憶體

一個記憶體中的程式包含以下幾個部分：

o 共享記憶體（SGA）

o 共享庫（包括公用和私有的）

o 私有資料（指資料段【DATA】或堆）

o 可執行部分（指文字【TEXT】段）

而SGA和TEXT部分是被所有Oracle程式共享的。它們只會被對映到記憶體中一次，而不會為每個程式做對映。因此，這些記憶體不是一個新的Oracle程式導致的記憶體增加部分。

· 作業系統沒有回收空閒記憶體

通常，一部分記憶體被一個程式釋放了後，並沒有立即返回到作業系統的空閒池中，而是繼續保持與程式的關聯，直到作業系統記憶體不足時，才會回收這些空閒頁。所以作業系統工具報告的程式記憶體大小可能會被實際大。

從Oracle的角度來看，一個服務程式的私有記憶體包括多個Oracle“堆（heap）”。在Oracle的術語中，堆就是一個受管理的記憶體區。而對於作業系統來說，這僅僅是分配給一個應用程式的另外一塊記憶體而已。PGA和UGA中都關聯到堆。

Oracle當前或者曾經在這些堆中擁有的記憶體總數可以透過以下語句統計出來：

 select statistic#, name, value
 from v$sysstat
 where name like '%ga memory%';

STATISTIC# NAME                       VALUE

---------- -------------------------- ------------------------------

        20 session uga memory         8650004156

        21 session uga memory max     778811244

        25 session pga memory         50609488

        26 session pga memory max     58007200

查詢所有會話的堆大小可以用以下語句實現：

  select value, n.name|| '('||s.statistic#||')' , sid
  from v$sesstat s , v$statname n 
  where s.statistic# = n.statistic# 
  and n.name like '%ga memory%' 
  order by value;

但是，查詢出來大的PGA或UGA並不一定說明有問題。它們的大小受到以下引數影響：

o SORT_AREA_SIZE

o SORT_AREA_RETAINED_SIZE

o HASH_AREA_SIZE

另外，過多的使用PL/SQL結構體（如PL/SQL TABLE、ARRAY）也會導致會話記憶體增大。

2.2. Oracle的記憶體的分配、回收

Oracle中的共享記憶體區的分配都是以chunk為最小單位的。Chunk不是一個固定值，它是一個擴充套件段（extent）中一塊連續的記憶體。而Oracle的記憶體（其他儲存，如磁碟也是）的增長是以擴充套件段為基礎的。

2.2.1. 空閒列表和LRU連結串列

空閒的chunk按照大小來組織在相應的空閒列表（Free List）中。而未pin住的、可重建（unpinned recreatable）的chuck被維護在兩個分別用於週期性chunk和短期chunk的LRU連結串列中。子堆還有一個包含少許空閒記憶體的主永久記憶體chunk。

2.2.2. 空閒記憶體分配和回收

空閒記憶體都是由空閒列表（free list）統一管理、分配的。每個空閒的chunk（大塊）都會屬於也只屬於一個空閒列表。空閒列表上的chunk的大小範圍是由bucket來劃分的。Bucket直譯為“桶”，在西方，往往用桶來盛裝一定品質範圍的物品，以便查詢。比如，在採礦時，用不同的桶來裝不同純度的礦石，在桶上標明礦石的純度範圍，以便在提煉時可以採用不同工藝。在這裡，我們也可以把bucket視為一種索引，使Oracle在查詢空閒塊時，先定位所需的空閒塊在哪個bucket的範圍內，然後在相應的空閒列表中查詢。

一次記憶體的分配過程如下：當一個程式需要一個記憶體的大塊（chunk）時，它會先掃描目標空閒列表（每個空閒列表對應有一個bucket，bucket是這個空閒列表的中chunk的大小範圍）以查詢最適合大小的chunk。如果找不到一個大小正好合適的chunk，則繼續掃描空閒列表中更大的chunk。如果找到的可用chunk比所需的大小大24位元組或者更多，則這個chunk就會被分裂，剩餘的空閒chunk又被加到空閒列表的合適位置。如果空閒列表中沒有一個chunk能滿足要求的大小，則會從非空的相鄰bucket的空閒列表中取最小的chunk。如果所有空閒列表都是空的，就需要掃描LRU連結串列釋放最近最少使用的記憶體。當chunk空閒時，如果相鄰的chunk也是空閒的，它們可能會結合（coalesce）起來。

當所有空閒列表都沒有合適的空閒chunk可用時，就開始掃描LRU連結串列，將最近最少使用的chunk釋放掉（如有空閒的相鄰chunk，則結合），放到相應的空閒列表中去，直到找到合適的chunk或者達到最大查詢數限制。如果在釋放了LRU連結串列中最近最少使用的chunk後沒沒有找到合適空閒chunk，系統就拋4031錯誤。

如果找到了可用空閒chunk，就將它從空閒列表中移出，放到LRU連結串列中去。

下面介紹一下Shared Pool的分配和回收。

2.2.3. Shared Pool的分配和回收

在Shared Pool中，空閒列表掃描、管理和chunk分配的操作都是受到shared pool latch保護的。顯然，如果shared pool含有大量的非常小的空閒chunk，則掃描空閒列表時間將很長，而shared pool latch則會要保持很久。這就是導致shared pool latch爭用的主要原因了。有些DBA透過增加shared pool來減少shared pool latch爭用，這種方法是沒有用的，可能反倒會加劇爭用（作為短期解決方法，可以flush shared pool；而要真正解決問題，可以採取在例項啟動時keep住那些可能會斷斷續續使用的物件【這種物件最容易導致shared pool碎片】）。

只有執行了ALTER SYSTEM FLUSH SHARED_POOL才會使shared pool的空閒chunk全結合起來。因此，即使shared pool空閒記憶體之和足夠大，也可能出現記憶體請求失敗（空閒記憶體都是一些很小的碎片chunk）。

實際上，oracle例項啟動時，會保留大概一半的Shared Pool，當有記憶體壓力時逐漸釋放它們。Oracle透過這種方法限制碎片的產生。Oracle的少量空閒記憶體（spare free memory）和X$表即其他持久記憶體結構一起，隱含在shared pool的主要持久記憶體chunk中。這些記憶體不在shared pool的空閒列表中，因此能夠立即被分配。但是，卻包含在檢視V$SGASTAT的free memory的統計資料中。

 select * from v$sgastat
 where pool = 'shared pool'
 and name = 'free memory';

POOL         NAME                            BYTES

------------ -------------------------- ----------

shared pool  free memory                  18334468

SQL>

而spare free memory可以用以下方式查出：

select
  avg(v.value)  shared_pool_size,
  greatest(avg(s.ksmsslen) - sum(p.ksmchsiz), 0)  spare_free,
  to_char(
    100 * greatest(avg(s.ksmsslen) - sum(p.ksmchsiz), 0) / avg(v.value),
    '99999'
  ) || '%' wastage
from
  sys.x$ksmss s,
  sys.x$ksmsp p,
  sys.v$parameter v
where
  s.inst_id = userenv('Instance') and
  p.inst_id = userenv('Instance') and
  p.ksmchcom = 'free memory' and
  s.ksmssnam = 'free memory' and
  v.name = 'shared_pool_size';

注意：如果10g中用了SGA記憶體自動管理。以上語句可能無法查出。

當需要時，少量的空閒記憶體chunk會被釋放到shared pool中。除非所有這些少量空閒記憶體倍耗盡了，否則不會報4031錯誤。如果例項在負載高峰執行了一段時期之後還有大量的少量空閒記憶體，這就說明shared pool太大了。

而未pin住的、可重建（unpinned recreatable）的chuck被維護在兩個分別用於週期性chunk和短期chunk的LRU連結串列中。這兩個LRU連結串列的長度可以透過表X$KGHLU查到，同時還能查到被flush的chunk數、由於pin和unpin而加到和從LRU連結串列中移出的chunk數。X$KGHLU還能顯示LRU連結串列沒有被成功flush的次數，以及最近一次這樣的請求失敗的請求大小。

column kghlurcr heading "RECURRENT|CHUNKS"
column kghlutrn heading "TRANSIENT|CHUNKS"
column kghlufsh heading "FLUSHED|CHUNKS"
column kghluops heading "PINS AND|RELEASES"
column kghlunfu heading "ORA-4031|ERRORS"
column kghlunfs heading "LAST ERROR|SIZE"
select
kghlurcr "RECURRENT_CHUNKS",
kghlutrn "TRANSIENT_CHUNKS",
kghlufsh "FLUSHED_CHUNKS",
kghluops "PINS AND_RELEASES",
kghlunfu "ORA-4031_ERRORS",
kghlunfs "LAST ERROR_SIZE"
from
sys.x$kghlu
where
inst_id = userenv('Instance');

RECURRENT_CHUNKS TRANSIENT_CHUNKS FLUSHED_CHUNKS PINS AND_RELEASES ORA-4031_ERRORS LAST ERROR_SIZE

---------------- ---------------- -------------- ----------------- --------------- ---------------

             327              368              0              7965               0               0

             865              963           2960            102138               0               0

            1473             5657             96             20546               1             540

               0                0              0                 0               0               0

如果短期連結串列的長度大於週期連結串列的長度3倍以上，說明Shared Pool太大，如果chunk flash對LRU 操作的比例大於1/20，則說明Shared Pool可能太小。

2.3. Oracle在UNIX下的記憶體管理

在UNIX下，Oracle是以多程式的方式執行的。除了幾個後臺程式外，Oracle為每個連線起一個程式（程式名稱中，LOCAL = NO）。而UNIX下的記憶體分為兩類，一種叫共享記憶體，即可以被多個程式共享；還有一種就是私有記憶體，只能被所分配的程式訪問。根據Oracle記憶體區的不同特性，SGA記憶體可以被所有會話程式共享，因此是從共享記憶體中分配的；而PGA是程式私有的，因而是從私有記憶體中分配的。

2.3.1. 共享記憶體和訊號量

在Unix下，Oracle的SGA是儲存在共享記憶體中的（因為共享記憶體是可以被多個程式共享的，而SGA正是需要能被所有Oracle程式所共享）。因此，系統中必須要有足夠的共享記憶體用於分配SGA。而訊號量則是在共享記憶體被多個程式共享時，防止發生記憶體衝突的一種鎖機制。

UNIX下，對於記憶體的管理配置，是由許多核心引數控制，在安裝使用Oracle時，這些引數一定要配置正確，否則可能導致嚴重的效能問題，甚至Oracle無法安裝、啟動。涉及共享記憶體段和訊號量的引數：

引數名稱	建議大小（各個平臺的Oracle建議值可以去metalink上找）	引數描述
SHMMAX	可以得到的實體記憶體 (0.5*實體記憶體)	定義了單個共享記憶體段能分配的最大數. SHMMAX 必須足夠大,以在一個共享記憶體段中能足夠分配Oracle的SGA空間.這個引數設定過低會導致建立多個共享記憶體段,這會導致Oracle效能降低.
SHMMIN		定義了單個共享記憶體段能分配的最小數.
SHMMNI	512	定義了整個系統共享記憶體段的最大數。
NPROC	4096	系統的程式總數
SHMSEG	32	定義了一個程式能獲取的共享記憶體段的最大數.
SEMMNS	*(NPROC 2) * 2**	設定系統中的訊號數. SEMMNS的預設值是128
SEMMNI	*(NPROC 2)**	定義了系統中訊號集的最大數
SEMMSL	與Oracle中引數processes大小相同	一個訊號集中的訊號最大數
SEMMAP	*((NPROC 2) + 2)**	定義了訊號對映入口最大數
SEMMNU	(NPROC - 4)	定義了訊號回滾結構數
SEMVMX	32768	定義了訊號的最大值

訊號量(Semaphore)：對於每個相關的process都給予一個訊號來表示其目前的狀態。主要的目地在於確保process間能同步，避免process存取shared data時產生碰撞( collisions)的情況。可以把訊號量視為作業系統級的用於管理共享記憶體的鑰匙，每個程式需要有一把，一個訊號量集就是一組鑰匙，這組鑰匙可以開啟共同一個共享記憶體段上的鎖。當一個程式需要從共享記憶體段中獲取共享資料時，使用它自己的鑰匙開啟鎖，進入後，再反鎖以防止其他程式進來。使用完畢後，將鎖釋放，這樣其他程式就可以存取共享資料了。

共享記憶體（Shared Memory）是指同一塊記錄記憶體段被一個以上的程式所共享。這是我們所知速度最快的程式間通訊方式。使用共享記憶體，再使用多CPU的機器上，會使機器發揮較佳的效能。

可以透過以下命令檢查你當前的共享記憶體和訊號量的設定：

$ sysdef | more

當Oracle異常中止，如果懷疑共享記憶體沒有被釋放，可以用以下命令檢視：

$ipcs -mop

IPC status from /dev/kmem as of Thu Jul  6 14:41:43 2006

T      ID     KEY        MODE        OWNER     GROUP NATTCH  CPID  LPID

Shared Memory:

m       0 0x411c29d6 --rw-rw-rw-      root      root      0   899   899

m       1 0x4e0c0002 --rw-rw-rw-      root      root      2   899   901

m       2 0x4120007a --rw-rw-rw-      root      root      2   899   901

m  458755 0x0c6629c9 --rw-r-----      root       sys      2  9113 17065

m       4 0x06347849 --rw-rw-rw-      root      root      1  1661  9150

m   65541 0xffffffff --rw-r--r--      root      root      0  1659  1659

m  524294 0x5e100011 --rw-------      root      root      1  1811  1811

m  851975 0x5fe48aa4 --rw-r-----    oracle  oinstall     66  2017 25076

然後它ID號清除共享記憶體段：

$ipcrm –m 851975

對於訊號量，可以用以下命令檢視：

$ ipcs -sop

IPC status from /dev/kmem as of Thu Jul  6 14:44:16 2006

T      ID     KEY        MODE        OWNER     GROUP

Semaphores:

s       0 0x4f1c0139 --ra-------      root      root

... ...

s      14 0x6c200ad8 --ra-ra-ra-      root      root

s      15 0x6d200ad8 --ra-ra-ra-      root      root

s      16 0x6f200ad8 --ra-ra-ra-      root      root

s      17 0xffffffff --ra-r--r--      root      root

s      18 0x410c05c7 --ra-ra-ra-      root      root

s      19 0x00446f6e --ra-r--r--      root      root

s      20 0x00446f6d --ra-r--r--      root      root

s      21 0x00000001 --ra-ra-ra-      root      root

s   45078 0x67e72b58 --ra-r-----    oracle  oinstall

當Oracle異常中止，可以根據訊號量ID，用以下命令清除訊號量：

$ipcrm -s 45078

一個共享記憶體段可以可以分別由以下兩個屬性來定位：

o Key：一個32位的整數

o 共享記憶體ID：系統分配的一個記憶體

$ ipcs -mop

IPC status from /dev/kmem as of Thu Jul  6 10:32:12 2006

T      ID     KEY        MODE        OWNER     GROUP NATTCH  CPID  LPID

Shared Memory:

m       0 0x411c29d6 --rw-rw-rw-      root      root      0   899   899

m       1 0x4e0c0002 --rw-rw-rw-      root      root      2   899   901

m       2 0x4120007a --rw-rw-rw-      root      root      2   899   901

m  458755 0x0c6629c9 --rw-r-----      root       sys      2  9113 17065

m       4 0x06347849 --rw-rw-rw-      root      root      1  1661  9150

m   65541 0xffffffff --rw-r--r--      root      root      0  1659  1659

m  524294 0x5e100011 --rw-------      root      root      1  1811  1811

m  851975 0x5fe48aa4 --rw-r-----    oracle  oinstall     66  2017 25076

2.3.2. 私有記憶體

對於PGA，由於是分配的私有記憶體，不存在爭用問題，因而OS也沒有相應的訊號量來控制（同理，PGA中也沒有latch）。在10g之前，PGA的私有記憶體是透過函式malloc()和sbrk()進行分配擴充套件的，10g之後，私有記憶體是透過函mmap()進行初始化的。

另外，還有一點要注意的是，和SGA不同，PGA記憶體的大小不是固定的，是可以擴充套件的（前者的大小是固定的，無法增長的）。因而程式的私有記憶體是會增長的。因此，一個規劃好的系統發生記憶體不足的情況通常是由於程式的私有記憶體或程式數量突然增長造成的（

隱含引數_pga_large_extent_size（1048576）和_uga_cga_large_extent_size（262144）就控制了當初始化時，PGA、UGA和CGA擴張段的最大大小。

2.3.3. SWAP的保留區

swap（交換）區，是UNIX中用來分配虛擬記憶體的一塊特殊的磁碟分割槽。UNIX啟動每一個程式，都需要在swap區預留一塊和記憶體一樣大小的區域，以防記憶體不夠時作資料交換。當預留的swap區用完時，系統就不能再啟動新的程式。比如，系統實體記憶體是4G，而設定的交換區只有1G，那麼可以計算得出大概3G的記憶體會浪費（Buffer Cache除外，可能有2G浪費）。

在HP-UX中，引數swapmen_on可以讓系統建立一個pseudo-swap（偽交換區），大小為系統實體記憶體的3/4，但是這個偽交換區並不佔用任何記憶體和硬碟資源。只是說，讓系統認為，交換區的大小是1+4*3/4=4G，而不是1G，就是說可以啟動更多的程式，避免記憶體的浪費。

一般系統實體記憶體不大的時候，設定交換區是實體記憶體的2-4倍，swapmen_on設定為1或0都沒什麼影響，但是當系統記憶體很大如8G時，因為swap一般不設為16G-32G，這時開啟swapmen_on就很必要了。

hp建議，即使設定了swapmen_on，也將你的swap為實體記憶體的1-1.5倍。

swap大小設定不當，也同樣會造成系統的效能問題。因為，swap中首先會為各個程式留出一個保留區，這部分割槽去掉後，swap的可用大小就比較小了（這就是為什麼用swapinfo可能看到Total PCT USED為100%而dev PCT USED為0％）。當swap可用區不足，而由記憶體需要被page out到swap區中，就需要先將swap區中一些頁被page in到實體記憶體中去，因而導致發生交換，產生效能問題。

swap的使用情況可以透過swapinfo檢視：

> swapinfo -mt

             Mb      Mb      Mb   PCT  START/      Mb

TYPE      AVAIL    USED    FREE  USED   LIMIT RESERVE  PRI  NAME

dev        4096       0    4096    0%       0       -    1  /dev/vg00/lvol2

dev        8000       0    8000    0%       0       -    1  /dev/vg00/swap2

reserve       -   12026  -12026

memory    20468   13387    7081   65%

total     32564   25413    7151   78%       -       0    -

2.4. Oracle在windows下的記憶體管理

2.4.1. Windows記憶體系統概述

Windows NT使用一個以頁為基礎的虛擬記憶體系統，該系統使用32位線性地址。在內部，系統管理被稱為頁的4096位元組段中的所有記憶體。每頁的實體記憶體都被備份。對於臨時的記憶體頁使用頁檔案(pagefile)，而對於只讀的記憶體頁，則使用磁碟檔案。在同一時刻，最多可以有16個不同的頁檔案。程式碼、資源和其它只讀資料都是透過它們建立的檔案直接備份。

Windows NT為系統中的每一個應用程式（程式）提供一個獨立的、2 GB的使用者地址空間。對於應用程式來說，好象是有2 GB的可用記憶體，而不用考慮實際可用的實體記憶體的量。如果某個應用程式要求的記憶體比可用的記憶體更多時，Windows NT是這樣滿足這種要求的，它從這個和/或其他的程式把非關鍵記憶體分頁(paging)到一個頁檔案，並且釋放這些實體記憶體頁。結果，在Windows NT中，全域性堆不再存在。相反，每一個程式都有其自己的32位地址空間，在其中，該程式的所有記憶體被分配, 包括程式碼、資源、資料、DLL（動態連結庫），和動態記憶體。實際上，系統仍然要受到可用的硬體資源的限制，但是實現了與系統中應用程式無關的、對於可用資源的管理。

Windows NT在記憶體和地址空間之間作出了區分。每個程式分配到2 GB的使用者地址空間，而不管對於該程式的實際可用實體記憶體有多少。而且，所有程式都使用相同範圍的線性32位地址，範圍從0000000016-7FFFFFFF16，而不考慮可用記憶體的地址。Windows NT負責在適當的時間把記憶體頁對映(paging)到磁碟以及從磁碟頁對映回記憶體，使得每個程式都確保能夠定址到它所需要的記憶體。儘管有可能出現兩個程式試圖同時訪問同一虛擬地址上的記憶體，但是，實際上Windows NT虛擬記憶體管理程式是在不同的物理位置描述這兩個記憶體的位置。而且這兩個地址都不見得與原始的虛擬地址一致。這就是虛擬記憶體。

Win32環境下，32位的地址空間轉化為4GB的虛擬記憶體。預設情況下，將一半（2GB）分配給使用者程式（因而一個程式的最大可用虛擬記憶體為2G，oracle程式同樣受此限制），另一半（2GB）分配給作業系統。

因為虛擬記憶體的存在，一個應用程式能夠管理它自己的地址空間，而不必考慮在系統中對於其它程式的影響。在Windows NT中的記憶體管理程式負責檢視在任何給定的時間裡，所有的應用程式是否有足夠的實體記憶體進行有效的操作。Windows NT作業系統下的應用程式不必考慮和其它應用程式共享系統記憶體這個問題。並且，即使在應用程式自己的地址空間內，它們仍能夠與其它的應用程式共享記憶體。

區分記憶體和地址空間的一個好處是，為應用程式提供了將非常大的檔案載入到記憶體的能力。不必將一個大的檔案讀進記憶體中，Windows NT為應用程式保留該檔案所需的地址範圍提供了支援。然後，在需要的時候，該檔案部分就可以被瀏覽了（物理性地讀進記憶體）。透過虛擬記憶體的支援，對於大段的動態記憶體的分配同樣可以做到這一點。

在任意給定的時間，程式中每個地址都可以被當作是空閒的（free）、保留的（reserved）或已提交的（committed）。程式開始時，所有地址的都是空閒的，意味著它們都是自由空間並且可以被提交到記憶體，或者為將來使用而保留起來。在任何空閒的地址能夠被使用前，它必須首先被分配為保留的或已提交的。試圖訪問一個保留的或已提交的地址都將產生一個訪問衝突異常(access violation exception)。

一個程式中的所有2 GB的地址要麼為了使用而是空閒的、要麼為了將來的使用而是保留的、要麼已提交到特定的記憶體（在使用的）。

一旦地址被以保留的或者已提交的形式分配，VirtualFree是唯一可以釋放它們的方法棗那就是，將它們返回到自由的地址。VirtualFree還可以用來對已提交的頁解除提交，同時，返回這些地址到保留狀態。當解除地址的提交時，所有與該地址相關的實體記憶體和頁檔案空間都被釋放。

在Windows NT中的程式有一個被稱為工作組（working set）的最小頁，是為了程式能夠順利地執行，在執行時在記憶體中必須被提供。Windows NT在啟動時為一個程式分配了預設數量的頁數，並且逐漸地調整該數，使得系統中所有啟用的程式的效能達到一種平衡的最優。當一個程式正在執行時（實際上是，是一個程式的執行緒正在執行時），Windows NT“儘量”確保該程式的工作組頁總是駐留在實體記憶體中。工作集即在實體記憶體中保持的虛擬頁面的子集，分程式工作集和系統工作集。

2.4.2. Windows下Oracle的記憶體配置

在windows下，Oracle例項作為一個單獨的程式執行，這個程式是一個標準的Win32應用程式，它能夠申請最大為2G的虛擬記憶體地址空間，所有使用者連線後後臺執行緒的分配記憶體（包括像buffer cache那些全域性記憶體）必須小於2G（64位平臺中無此限制，32位平臺中可以透過設定引數use_indirect_data_buffers來突破這一限制，不詳述）。

Oracle可以執行於windows NT下的任何版本，但是Oracle一般不推薦將Oracle資料庫執行在PDC（主域控伺服器）或BDC（備域控伺服器）下。這是因為域控伺服器會需要大量的檔案快取（這會消耗大量記憶體，影響到Oracle）和網路資源。

而檔案快取帶來的另外一個問題就是，這個機器到底是作為一個專用的資料庫伺服器還是一個混合伺服器。因為Oracle資料庫不會用到檔案快取（log buffer就相當於Oracle自己的檔案快取），它透過直接寫磁碟來避免檔案緩衝。

在專用資料庫伺服器系統上，使用者一定要確保沒有使用到頁檔案（pagefile即虛擬記憶體檔案）。否則，可以透過修改Oracle引數或者增大實體記憶體來避免。如果大量額頁被持續的移入、移出到虛擬記憶體中，會嚴重影響到效能。

如果是專用伺服器系統，有以下建議：

o 如果分配給Oracle的總記憶體能保證不會超過實體記憶體，則虛擬記憶體頁可以被設定位實體記憶體的50％，並且可以增長到實體記憶體的100％大小（在my computer => properties => Advanced => Performance => Settings => Advanced => Virtual Memory => Change中設定，設定Initial size為實體記憶體的50％，Maximum Size和實體記憶體大小相同）；

o 對於主要是執行純Oracle資料庫的系統（但不是專用），一般推薦虛擬記憶體頁大小在1倍到1.5倍於實體記憶體大小之間；

o 對於實體記憶體大於2G的機器，要求虛擬記憶體頁最少為2G。

一個機器上能被用於分配的總記憶體等於實體記憶體加上擴充套件前的虛擬記憶體頁大小。你一定要避免設定引數如buffer_cache_size或其他相關引數導致Oracle要求分配記憶體大於實體記憶體。儘管Oracle的分配記憶體大小是限制在總記憶體（實體記憶體＋最小虛擬記憶體）之內，但是，對虛擬記憶體頁的訪問是非常慢的，會直接影響到系統效能，因此Oracle分配記憶體要小於實體記憶體大小以避免發生記憶體交換。

如果系統是混合應用，除了Oracle資料庫外還執行了其他程式，這時就需要考慮設定虛擬記憶體頁大於實體記憶體了。那些當前不活動的程式可以減少它們的工作區（working set 即實體記憶體）以使活動程式能增加工作區。如果Oracle資料庫執行在這樣的機器上，則推薦虛擬內頁最少1.5到2倍的實體記憶體大小，特別是在記憶體大於2G時。

在Oracle 8.1.x之前，啟動Oracle服務時不會啟動Oracle例項。此時（即只啟動了Oracle服務，沒有啟動例項），分配給Oracle.EXE的主要記憶體是給相關DLL的，大概20M（各個版本的DLL不同，因此佔用記憶體情況也不同）。9i之後，Oracle例項會隨著服務啟動，不過我們可以在服務啟動後再關閉例項，這時就可以觀察出Oracle服務所佔用的記憶體大小了：

這是windows下一個Oracle 10g的例項關閉後記憶體佔用情況。我們看到此時Oracle服務佔用的記憶體是32M。

Windows下，可以使用以下語句來計算Oracle佔用的虛擬記憶體大小：

select sum(bytes)/1024/1024 + 22/*DLL佔用記憶體*/ Mb
from (select bytes from v$sgastat – SGA記憶體         union
        select value bytes from – 會話記憶體              v$sesstat s,
             v$statname n
        where
             n.STATISTIC# = s.STATISTIC# and
             n.name = 'session pga memory'
        union
        select 1024*1024*count(*) bytes – 執行緒堆疊         from v$process
       );

在例項啟動時，所有全域性記憶體頁都被保留和提交（所有共享全域性區、Buffer Cache和Redo Buffer）——可以透過觀察到例項啟動後，所需要的Page File都已經被分配。但只有一小部分記憶體頁（如果沒有設定PRE_PAGE_SGA的話；這小部分記憶體以granule為單位，固定SGA【包括redo buffer】一個、Buffer Cache一個、Shared Pool一個）被觸及（touch）而已經分配到工作組(working set)中，而其他更多的頁需要使用時才分配到工作組中。

透過設定登錄檔可以設定Oracle程式的最小工作組大小和最大工作組大小：

o ORA_WORKINGSETMIN或ORA_%SID%_WORKINGSETMIN：Oracle.EXE程式的最小工作組大小（M為單位）

o ORA_WORKINGSETMAX或ORA_%SID%_WORKINGSETMAX：Oracle.EXE程式的最大工作組大小（M為單位）

這些註冊項需要加在HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE或者HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE -> HOMEn下。

在混合伺服器下，這種設定能防止Oracle程式的記憶體被其他程式爭用。設定這些註冊項時，需要考慮PRE_PAGE_SGA的設定。如前所述，PRE_PAGE_SGA使Oracle例項啟動時“觸及”所有的SGA記憶體頁，使它們都置入工作組中，但同時會增長例項啟動時間。

ORA_WORKINGSETMIN是一個非常有用的引數，它能防止Oracle程式的工作組被縮小到這一限制值之下：

o 如果設定了PRE_PAGE_SGA，例項啟動後，工作組就大於這個限制值。在例項關閉之前，就不會低於這個值；

o 如果沒有PRE_PAGE_SGA，當例項的工作組一旦達到這個值後，就不會再低於這個值。

另外，在10g之前，存在一個Bug(642267)，導致在windows系統中設定了LOCK_SGA後，例項啟動報ORA-27102錯誤。可以透過設定ORA_WORKINGSETMIN最小為2M來解決這個問題。但是，windows中，LOCK_SGA隻影響Oracle程式中的SGA部分，而分配給使用者會話的記憶體不會受影響，這部分記憶體還是可能會產生記憶體交換。

2.4.3. SGA的分配

當例項啟動時，oracle在虛擬記憶體地址空間中建立一段連續的記憶體區，這個記憶體區的大小與SGA所有區相關引數有關。透過呼叫Win32 API介面“VirtualAlloc”，在介面函式的引數中指定MEM_RESERVE | MEM_COMMIT記憶體分配標識和PAGE_READWRITE保護標識，這部分記憶體始終會被保留和提交。這就保證所有執行緒都能訪問這塊記憶體（SGA是被所有執行緒共享的），並且這塊記憶體區始終有物理儲存（記憶體或磁碟）所支援。

VirtualAlloc函式不會觸及這塊區域內的記憶體頁，這就是說分配給SGA元件（如buffer cache）的記憶體在沒有觸及之前是不會到工作組中去的。

VirtualAlloc

VirtualAlloc函式保留或提交呼叫此函式的程式的虛擬記憶體地址空間中的一段記憶體頁。如果沒有指定MEM_RESET，被這個函式分配的記憶體自動初始化為0，

Buffer Cache通常被建立為一個單一的連續記憶體區。但這並不是必須的，特別是當使用了非常大的Buffer Cache時。因為dll記憶體和執行緒分配的記憶體會導致虛擬地址空間產生碎片。

當一個程式建立後，windows NT會在程式的地址空間中建立一個堆（heap），這個堆被稱為程式的預設堆。許多Win32 API呼叫介面和C執行呼叫介面（如malloc、localalloc）都會使用這個預設堆。當需要時，程式能在虛擬記憶體地址空間中建立另外的命名堆。預設堆建立為1M大小（被保留和提交的）的記憶體區，當執行分配或釋放這個堆的操作時，堆管理器提交或撤銷這個區。而訪問這個區是透過臨界區來序列訪問的，所以多個執行緒不能同時訪問這個區。

當程式建立了一個執行緒後，windows NT會為執行緒堆疊（每個現場都有自己的堆疊）保留一塊地址空間區域，並提交一部分這些保留區。當一個程式連線到標準區時，系統為堆疊保留一個1M大小的虛擬地址空間並提交這個區頂部的兩個頁。當一個執行緒分配了一個靜態或者全域性變數時，多個執行緒可以同時訪問這個變數，因此存在變數內容被破壞的潛在可能。本地和自動變數被建立線上程的堆疊中，因而變數被破壞的可能性很小。堆疊的分配是從上至下的。例如，一個地址空間從0x08000000到0x080FF000的堆疊的分配是從0x080FF000到0x08001000來提交記憶體頁，如果訪問在0x08001000的頁就會導致堆疊溢位異常。堆疊不能增長，任何試圖訪問堆疊以外的地址的操作都可能會導致程式被中止的致命錯誤。

2.4.4. 會話記憶體的分配

當監聽建立了一個使用者會話（執行緒和堆疊）時，Oracle服務程式就透過呼叫Win32 API函式建立使用者連線所必須的記憶體結構，並且指定MEM_RESERVE | MEM_COMMIT標識，以保持和提交給執行緒私有的地址空間區域。

當呼叫了VirtualAlloc來在指定地址保留記憶體，它會返回一個虛擬地址空間到下一個64K大塊（chunk，windows記憶體分配最小單位）的地址。Oracle呼叫VirtualAlloc時不指定地址，只傳一個NULL在相應引數中，這會返回到下一個64K大塊的地址。因此使用者會話在分配PGA、UGA和CGA時同時也遵循64K的最小粒度，來提交倍數於這個粒度值的記憶體頁。許多使用者會話經常分配許多小於64K的記憶體，這就導致地址空間出現碎片，因為許多64K區只有兩個頁被提交。

一旦地址空間被使用者會話佔滿了後，如果要再建立一個新會話，就會存在無法分配到地址空間的危險。將可能報以下錯誤：

ORA-12500 / TNS-12500

TNS:listener failed to start a dedicated server process

也可能報這些錯誤：

o ORA-12540 / TNS-12540 TNS:internal limit restriction exceeded

o NT-8 Not enough storage is available to process this command

o skgpspawn failed:category = ....

o ORA-27142 could not create new process

o ORA-27143 OS system call failure

o ORA-4030 out of process memory when trying to allocate ....

因為地址空間碎片問題和DLL被載入了oracle服務程式的地址空間，這些錯誤很可能發生再當Oracle程式佔用大概1.6G～1.7G（可以透過工作管理員或檢視）時。

2.4.4.1. 會話記憶體大小設定

我們前面說了，一個程式的全部記憶體大小被限制在2G以內。因此，對於一個有許多使用者同時訪問的系統，要考慮這些會話總記憶體小於2G – SGA的大小。

下列引數會影響每個會話的記憶體大小（這些引數前面都有介紹）：

o bitmap_merge_area_size

o create_bitmap_area_size

o hash_area_size

o open_cursors

o sort_area_size (sort_area_retained_size)

在沒有設定PGA_AGGREGATE_TARGE（這個引數能儘量但不一定使所有會話PGA之和在指定範圍內）引數時，需要調整這些引數，以使所有會話佔用記憶體與SGA之和小於2G。過多的使用PL/SQL結構體（如PL/SQL TABLE、ARRAY）也會導致會話記憶體增大。

2.4.4.2. ORASTACK修改執行緒堆疊大小

Oracle提供了ORASTACK工具讓使用者內修改Oracle執行程式建立會話、執行緒時的預設堆疊大小。當ORASTACK應用於一個可執行程式時，它會修改程式頭部的、定義使用建立執行緒API函式所指定預設堆疊大小的二進位制區，以修改預設堆疊的大小。沒有必要區修改執行緒提交頁數的預設值，因為它們時從堆疊中請求到的。當使用者非常多時，透過減少每個建立在Oracle中會話堆疊大小，可以節省大量記憶體。比如，一個1000使用者的系統，將堆疊從1M降為500K後，能節省出1000 * 500K = 500M的地址空間。

在需要使用ORASTACK來降低現場堆疊大小時，你需要你的系統以保證新的堆疊大小能確保系統正常執行。如果堆疊大小被縮小到Oracle服務端所必須的堆疊大小以下，就會產生堆疊溢位錯誤，使用者程式就失敗（通常報ORA-3113錯誤），並且在alert log中不會有報錯而且頁不產生trace檔案。Oracle一般不推薦將堆疊該到500K以下（儘管不少系統在300K時也能正常執行）。

ORASTACK必須修改所有能在oracle中建立執行緒的程式，使用語法如下：

  orastack  executable_name  new_stack_size_in_bytes

下面的例子將堆疊改為500K：

  orastack oracle.exe  500000

  orastack tnslsnr.exe 500000

  orastack svrmgrl.exe 500000

  orastack sqlplus.exe 500000

在使用ORASTACK之前必須保證沒有任何oracle程式正在執行（可以通工作管理員或者）。

此外，如果有程式在本地連線（沒有透過SQL*NET）了Oracle，也要先停止（如在本地執行sqlplus連線了例項）。

2.4.4.3. 會話記憶體如何釋放、執行緒如何結束

當會話成功結束後，它會按用Win32 API函式VirtualFree來釋放它的記憶體，呼叫此函式時，需要指定MEM_DECOMMIT | MEM_RELEASE標識。當所有記憶體被釋放後，堆疊也被釋放，將Oracle程式中指向完成的會話的地址空間空閒出來。

如果一個使用者會話被異常結束，它將不會釋放它所分配的記憶體，這些記憶體頁會被繼續保留在Oracle程式的地址空間中，知道程式結束。會話的異常結束可能由以下原因導致的：

o Shutdown abort.

o Alter session kill session.

o orakill殺掉的會話.

o Oracle管理助手for Windows：kill session.

o 其他殺執行緒的工具（工具提供了殺執行緒的功能）

Oracle建議儘量少使用以上方式（命令），特別是shutdown abort（這種停止例項的方法所帶來的問題還不止這個）。當呼叫shutdown abort時，Oracle會呼叫Win32 API函式TerminateThread來中止每個使用者會話，這個命令將直接殺掉執行緒而不釋放它們的記憶體。如果系統已經接近2G地址空間的限制，Oracle例項再次啟動要分配記憶體時就會產生問題。唯一釋放Oracle全部記憶體的方法是停止和啟動Oracle服務（服務OracleService）。

如果windows NT下的系統需要能被許多使用者訪問，可以透過以下措施來最佳化記憶體的使用：

o 降低相關的PGA、UGA記憶體引數（如SORT_AREA_SIZE）；

o 降低SGA的引數；

o 使資料庫作業佇列數（引數job_queue_processes控制）和並行查詢slave（引數parallel_max_servers控制）最小，因為它們也會導致Oracle程式建立執行緒；

o 使用ORASTACK降低會話、執行緒堆疊大小到500K；

o 考慮使用MTS模式；

o 考慮將Windows NT升級到Windows NT企業版；

o 考慮升級硬體以支援Intel ESMA（Extended. Server Memory Architecture，擴展服務記憶體架構）

3. 記憶體錯誤處理

Oracle中最常見的記憶體錯誤就是4030和4031錯誤。這兩個錯誤分別是在分配PGA和SGA時，沒有足夠記憶體分配導致的。經過我們以上對Oracle記憶體的瞭解以及對記憶體管理機制的淺析，可以總結在這兩種錯誤發生時，我們該如何分析和處理。

3.1. 分析、定位ORA-4030

4030錯誤是由於oracle程式在記憶體擴充套件時，無法從OS獲取到所需的記憶體而產生的報錯。在專有服務模式下，Oracle程式的記憶體包括堆疊、PGA、UGA（從PGA中分配）和有些程式資訊；而MTS下，UGA是從SGA中分配，不包括在程式的記憶體範圍內。

3.1.1. 4030錯誤產生的原因

PGA的大小不是固定的，是可以擴充套件的。PGA透過系統呼叫擴充套件堆資料段時，作業系統分配新的虛擬記憶體給程式作為PGA擴充套件段。這些擴充套件段一般是幾個KB。只要需要，oracle會分配幾千個擴充套件段。然而，作業系統限制了一個程式的堆資料段的增長。在UNIX中，這個限制一般受到OS核心引數MAXDSIZ限制，這是限制單個程式的。還有一個堆所有程式的虛擬記憶體的總的大小的限制。這個限制和swap交換空間（虛擬記憶體）大小有關。如果在擴充套件PGA記憶體時達到這些限制，就會拋4030錯誤。

3.1.2. 4030錯誤分析

既然知道了4030錯誤產生的可能原因，我們在分析4030錯誤時，就可以從這幾個方面分別收集資訊進行分析，並結合Oracle程式記憶體的使用情況來解決問題。

3.1.2.1. 作業系統是否由足夠的記憶體

在不同的作業系統下，我們可以使用相應的工具來收集系統的記憶體使用情況，以判斷記憶體是否足夠：

· OpenVMS systems

可以使用show memory檢視實體記憶體和虛擬記憶體頁的使用情況

Physical Memory Usage (pages):   Total      Free      In Use    Modified
  Main Memory (256.00Mb)                       32768      24849      7500     419


 .....


Paging File Usage (blocks):                     Free   Reservable  Total

 DISK$BOBBIEAXPSYS:[SYS0.SYSEXE]SWAPFILE.SYS    30720   30720      39936
 DISK$BOBBIEAXPSYS:[SYS0.SYSEXE]PAGEFILE.SYS    226160  201088    249984
 DISK$BOBBIE_USER3:[SYS0.PAGEFILE]PAGEFILE.SYS  462224  405296    499968

一般情況下，空閒pagefile的之和不能小於它的總數之和的一半。而且SWAPFILE必須是始終沒有被使用的，它的空閒頁必須和總頁數相同。

· Windows

Windows下可以透過工作管理員的效能頁來檢視記憶體的使用情況，也可以使用來觀察系統程式的記憶體使用情況

· UNIX

不同廠商的UNIX下，各種工具的使用和統計結果可能有所不同。常用的對記憶體的檢視工具主要有：

o TOP —— 檢視實體記憶體和交換空間

o vmstat —— 檢視實體記憶體和交換空間狀況，以及觀察是否有page out/page in

o swapon –s —— 檢視交換空間情況

o swapinfo –mt —— 檢視交換空間使用情況

下面是swapinfo的一個輸出：

> swapinfo -mt

             Mb      Mb      Mb   PCT  START/      Mb

TYPE      AVAIL    USED    FREE  USED   LIMIT RESERVE  PRI  NAME

dev        4096       0    4096    0%       0       -    1  /dev/vg00/lvol2

dev        8000       0    8000    0%       0       -    1  /dev/vg00/swap2

reserve       -   12026  -12026

memory    20468   13387    7081   65%

total     32564   25413    7151   78%       -       0    -

此外，在一些作業系統中，還可以透過Oracle自己提供的工具maxmem來檢查一個程式能夠分配的最大堆資料段的大小。

> maxmem

Memory starts at: 6917529027641212928 (6000000000020000)

Memory ends at:   6917529031936049152 (6000000100000000)

Memory available: 4294836224 (fffe0000)

3.1.2.2. 是否受到系統限制

在作業系統，往往會有對單個程式或者所有程式能夠分配的記憶體大小做了限制。當Oracle分配程式記憶體時，如果達到這些限制，也會導致4030錯誤。在不同作業系統中，可以用不同方式檢查系統是否有做限制。

· OpenVMS systems:

show process/id=/quota可以顯示一個程式的可用配額是多少。

· Windows

如前所述，在window 32位系統中，程式的可用記憶體限制為2G（可以透過其他方式突破此限制）。而windows下，oracle是以一個單獨程式方式執行的，它的記憶體包括了堆疊、SGA、PGA。我們可以透過工作管理員或來檢查Oracle程式是否達到此限制。

· UNIX

可以使用命令ulimit來檢視unix下的限制：

> ulimit -a

time(seconds)        unlimited

file(blocks)         unlimited

data(kbytes)         1048576

stack(kbytes)        131072

memory(kbytes)       unlimited

coredump(blocks)     4194303

3.1.2.3. 哪個Oracle程式請求了過多的記憶體

有些程式會做某些操作時會需要分配大量記憶體，如使用了PL/SQL TABLE或者做排序時。如果這樣的程式在系統中執行一段時間後，就可能導致4030錯誤的產生。我們可以用以下語句來檢視各個程式的記憶體使用情況：

select
   sid,name,value
from
   v$statname n,v$sesstat s
where
   n.STATISTIC# = s.STATISTIC# and
   name like '%ga %'
order by 3 asc;

同時，我們還可以從作業系統的角度來確認這些大量消耗記憶體的程式。

· OpenVMS systems:

show process/continious可以檢視各個程式的物理和虛擬記憶體的使用情況。

· Windows

在windows中，由於Oracle是以一個單獨程式執行的，而由執行緒來服務於會話的。因此無法檢視單個會話的記憶體佔用情況。

· UNIX

UNIX中，可以透過ps –lef|grep ora來檢視oracle程式佔用的記憶體情況。

3.1.2.4. 收集程式正在進行的操作

在解決4030問題時，有一點很重要，丟擲4030錯誤的程式並不一定是導致記憶體不足的程式。只不過在它請求分配記憶體時，記憶體已經不足了。很有可能在此之前就已經有大量消耗記憶體的程式導致記憶體不足。你需要找出記憶體消耗不斷增長的程式，觀察它鎖進行的操作。這條語句可以查出會話程式正在執行的語句：

select sql_text  
from v$sqlarea a, v$session s 
where a.address = s.sql_address and s.sid = ;

另外，可以做一個heapdump，將結果發給Oracle進行分析，

 oradebug unlimit
 oradebug setorapid  (透過v$process查到的pid, 用setospid來設定OS中的PID【或者v$process中的spid】)
 oradebug dump heapdump 7 (1-PGA; 2-Shared Pool; 4-UGA; 8-CGA; 16-top CGA; 32-large pool)

 alter session set events '4030 trace name heapdump level 25';

3.1.3. 解決4030錯誤的建議

如果問題是由於swap空間不足造成的，並且由中度或者嚴重的page in/page out（可以用vmstat檢視），你就需要嘗試降低系統整體的虛擬記憶體的使用（如調整SGA大小），或者降低單個程式記憶體的使用（如調整sort_area_size），或者減少程式數量（如限制processes引數，使用MTS）。而如果page in/page out很少或者根本沒有，就可以考慮增大swap空間。某些系統中，可以考慮使用偽交換區（如hp-ux中，可以考慮設定swapmen_on）。

如果問題和PLSQL操作有關，可以，1、檢查PLSQL中的TABLE，看看其中的資料是否全都必要，是否可以減少資料放入TABLE中；2、最佳化相關語句（比如透過調整最佳化器策略，使查詢計劃走sort比較少的訪問路徑），減少sort操作，或者減少sort_area_size（代價就是一部分sort操作會放在磁碟上進行，降低效能）。

9i以後可以考慮設定PGA記憶體自動管理。即設定PGA_AGGREGATE_TARGET在一定數值範圍內，WORKAREA_SIZE_POLICY設定為AUTO。但是注意，9i在OpenVMS系統上、或者在MTS模式下不支援PGA記憶體自動關聯。

如果是因為程式數過多導致的記憶體大量消耗，首先可以考慮調整客戶端，減少不必要的會話連線，或者採用連線池等方式，以保持系統有穩定的連線數。如果會話非常多，且無法降低的話，可以考慮採用MTS，以減少Oracle程式數。

檢查SGA中的記憶體區是否分配過多（如shared pool、large pool、java pool）等，嘗試減少SGA的記憶體大小。

在windows下，可以嘗試使用ORASTACK來減少執行緒的堆疊大小，以釋放更多的記憶體。

考慮增加實體記憶體。

3.2. 分析、定位ORA-4031

4031錯誤是Oracle在沒有足夠的連續空閒空間分配給Shared Pool或者Large Pool時丟擲的錯誤。

3.2.1. 4031錯誤產生的原因

前面我們描述Shared Pool的空閒空間的請求、分配過程。在受到空閒空間請求時，記憶體管理模組會先查詢空閒列表，看是否有合適的空閒chunk，如果沒有，則嘗試從LRU連結串列中尋找可釋放的chunk，最終還未找到合適的空閒chunk就會丟擲4031錯誤。

在討論4031問題之前，可以先到第一章中找到與shared pool（shared_pool_size、shared_pool_reserved_size、shared_pool_reserved_min_alloc）和large pool（large_pool_size）的引數描述，再瞭解一下這些引數的作用。這對於理解和分析4031錯誤會很有幫助。此外，還需要再回顧以下10g以後的SGA記憶體自動關聯部分（相關引數是SGA_TARGET），因為使用這一特性，能大大減少4031錯誤產生的機率。

3.2.2. 4031錯誤分析

通常，大多數的4031錯誤都是和shared pool相關的。因此，4031錯誤的分析，主要是對shared pool的分析。

3.2.2.1. 對shared pool的分析

當4031錯誤提示是shared pool無足夠連續記憶體可分配時，有可能是由於shared pool不足或者shared pool中嚴重的碎片導致的。

· Shared pool不足分析

檢視V$SHARED_POOL_RESERVED中可以查詢到產生4031的一些統計資料、以及shared pool中保留區（前面說了，保留區是用來快取超過一定大小的物件的shared pool區）的統計資訊。

如果欄位REQUEST_FAILURES >= 0並且欄位LAST_FAILURE_SIZE < _SHARED_POOL_RESERVED_MIN_ALLOC，可以考慮減小_SHARED_POOL_RESERVED_MIN_ALLOC，以使更多的物件能放到保留區中區（當然，你還需要觀察欄位MAX_USED_SPACE以確保保留區足夠大）。如果還沒有效果，就需要考慮增加shared_pool_size了。

· 碎片問題分析

Library cache和shared pool保留區的碎片也會導致4031錯誤的產生。

還是觀察上面的檢視，如果欄位REQUEST_FAILURES > 0並且欄位LAST_FAILURE_SIZE > _SHARED_POOL_RESERVED_MIN_ALLOC，就可以考慮增加_SHARED_POOL_RESERVED_MIN_ALLOC大小以減少放入保留區的物件，或者增加SHARED_POOL_RESERVED_SIZE和shared_pool_size（因為保留區是從shared pool中分配的）的大小。

此外，要注意有一個bug導致REQUEST_FAILURES在9.2.0.7之前所有版本和10.1.0.4之前的10g版本中統計的資料是錯誤的，這時可以觀察最後一次4031報錯資訊中提示的無法分配的記憶體大小。

3.2.2.2. 對large pool的分析

Large pool是在MTS、或並行查詢、或備份恢復中存放某些大物件的。可以透過檢視v$sgastat來觀察large pool的使用情況和空閒情況。

而在MTS模式中，sort_area_retained_size是從large pool中分配的。因此也要檢查和調整這個引數的大小，並找出產生大量sort的會話，調整語句，減少其中的sort操作。

MTS中，UGA也是從large pool中分配的，因此還需要觀察UGA的使用情況。不過要注意一點的是，如果UGA無法從large pool獲取到足夠記憶體，會嘗試從shared pool中去分配。

3.2.3. 解決4031錯誤

根據4031產生的不同原因，採取相應辦法解決問題。

3.2.3.1. bug導致的錯誤

有很多4031錯誤都是由於oracle bug引起的。因此，發生4031錯誤後，先檢查是否你的系統的4031錯誤是否是由bug引起的。下面是已經發現的會引起4031錯誤的bug。相關資訊可以根據bug號或note號到metalink上查詢。

BUG	說明	修正版本
Bug 1397603	ORA-4031 由於快取控制程式碼導致的SGA永久記憶體洩漏	8172, 901
Bug 1640583	ORA-4031 due to leak / 由於查詢計劃中AND-EQUAL訪問路徑導致緩衝記憶體鏈爭用，從而發生記憶體洩漏。	8171, 901
Bug:1318267 （未公佈）	如果設定了TIMED_STATISTICS可能導致INSERT AS SELECT無法被共享。	8171, 8200
Bug:1193003 （未公佈）	Oracle 8.1中，某些遊標不共享。	8162, 8170, 901
Bug 2104071	ORA-4031 太多PIN導致shared pool消耗過大。	8174, 9013, 9201
Note 263791.1	許多與4031相關的錯誤在9205補丁集中修正。	9205

3.2.3.2. Shared pool太小

大多數4031錯誤都是由shared pool不足導致的。可以從以下幾個方面來考慮是否調整shared pool大小：

· Library cache命中率

透過以下語句可以查出系統的library cache命中率：

SELECT SUM(PINS) "EXECUTIONS",
       SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",
       1 - SUM(RELOADS)/SUM(PINS)
FROM V$LIBRARYCACHE;

如果命中率小於99％，就可以考慮增加shared pool以提高library cache的命中率。

· 計算shared pool的大小

以下語句可以檢視shared pool的使用情況

select sum(bytes) from v$sgastat
where pool='shared pool'
and name != 'free memory';

專用服務模式下，以下語句檢視cache在記憶體中的物件的大小，

select sum(sharable_mem) from v$db_object_cache;

專用服務模式下，以下語句檢視SQL佔用的記憶體大小，

select sum(sharable_mem) from v$sqlarea;

Oracle需要為儲存每個開啟的遊標分配大概250位元組的記憶體，以下語句可以計算這部分記憶體的佔用情況，

select sum(250 * users_opening) from v$sqlarea;

此外，在我們文章的前面部分有多處提到了如何分析shared pool是否過大或過小，這裡就不在贅述。

3.2.3.3. Shared pool碎片

每當需要執行一個SQL或者PLSQL語句時，都需要從library cache中分配一塊連續的空閒空間來解析語句。Oracle首先掃描shared pool查詢空閒記憶體，如果沒有發現大小正好合適的空閒chunk，就查詢更大的chunk，如果找到比請求的大小更大的空閒chunk，則將它分裂，多餘部分繼續放到空閒列表中。這樣就產生了碎片問題。系統經過長時間執行後，就會產生大量小的記憶體碎片。當請求分配一個較大的記憶體塊時，儘管shared pool總空閒空間還很大，但是沒有一個單獨的連續空閒塊能滿足需要。這時，就可能產生4031錯誤。

如果檢查發現shared_pool_size足夠大，那4031錯誤一般就是由於碎片太多引起的。

如果4031是由碎片問題導致的，就需要弄清楚導致碎片的原因，採取措施，減少碎片的產生。以下是可能產生碎片的一些潛在因素：

o 沒有使用共享SQL；

o 過多的沒有必要的解析呼叫（軟解析）；

o 沒有使用繫結變數。

以下表/檢視、語句可以查詢shared pool中沒有共享的SQL

· 透過V$SQLAREA檢視

前面我們介紹過這個檢視，它可以檢視到每一個SQL語句的相關資訊。以下語句可以查出沒有共享的語句，

SELECT substr(sql_text,1,40) "SQL",
count(*) ,
sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5 –-語句執行次數 GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30 –-所有未共享的語句的總的執行次數 ORDER BY 2;

· X$KSMLRU表

這張表儲存了對shared pool的分配所導致的shared pool中的物件被清出的記錄。可以透過它來查詢是什麼導致了大的shared pool分配請求。

如果有許多物件定期會被從shared pool中被清出，會導致響應時間太長和library cache latch爭用問題。

不過要注意一點，每當查詢過表X$KSMLRU後，它的內容就會被刪除。因此，最好將查出的資料儲存在一個臨時的表中。以下語句查詢X$KSMLRU中的內容，

SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;

· X$KSMSP表

從這張表中可以查到當前分配了多少空閒空間，這對於分析碎片問題很有幫助。一些語句可以查詢shared pool的空閒列表中chunk的統計資訊，

select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) "From",
count(*) "Count" , max(KSMCHSIZ) "Biggest",
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ<140
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10)
UNION ALL
select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 140 and 267
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20)
UNION ALL
select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 268 and 523
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50)
UNION ALL
select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 524 and 4107
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500)
UNION ALL
select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ >= 4108
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);

如果使用ORADEBUG將shared pool資訊dump出來，就會發現這個查詢結果和trace檔案中空閒列表資訊一直。

如果以上查詢結果顯示大多數空閒chunk都在bucket比較小的空閒列表中，則說明系統存在碎片問題。

3.2.3.4. 編譯java程式碼導致的錯誤

當編譯java（用loadjava或deployjb）程式碼時產生了4031錯誤，錯誤資訊一般如下：

A SQL exception occurred while compiling: :

ORA-04031: unable to allocate bytes of shared memory ("shared pool","unknown object","joxlod: init h", "JOX: ioc_allocate_pal")

這裡提示時shared pool不足，其實是錯誤，實際應該是java pool不足導致的。解決方法將JAVA_POOL_SIZE加大，然後重啟例項。

3.2.3.5. Large pool導致的錯誤

Large pool是在MTS、或並行查詢、或備份恢復中存放某些大物件的。但和shared pool中的保留區（用於存放shared pool的大物件）不同，large pool是沒有LRU連結串列的，而後者使用的是shared pool的LRU連結串列。

在large pool中的物件永遠不會被清出的，因此不存在碎片問題。當由於large pool不足導致4031錯誤時，可以先透過v$sgastat檢視large pool的使用情況，

SELECT pool,name,bytes FROM v$sgastat where pool = 'large pool';

或者做一個dump，看看large pool中空閒chunk的大小情況。

進入large pool的大小條件是由引數LARGE_POOL_MIN_ALLOC決定的，根據以上資訊，可以適當調整LARGE_POOL_MIN_ALLOC的大小。

Large pool的大小是由LARGE_POOL_SIZE控制的，因此當large pool空間不足時，可以調整這個引數。

3.2.4. SGA記憶體自動管理

10g以後，Oracle提供了一個非常有用的特性，即SGA記憶體自動管理。透過設定SGA_TARGET可以指定總的SGA大小，而無需固定每個區的大小。這就是說，當分配shared pool或large pool時，只要SGA區足夠大，就能獲取到足夠記憶體，因而可以大大減少4031錯誤發生的機率。

3.2.5. FLUSH SHARED POOL

使用繫結變數是解決shared pool碎片的最好方法。此外，9i以後，可以設定CURSOR_SHARING為FORCE，強行將沒有使用繫結變數的語句使用繫結變數，從而共享SQL遊標。當採用以上措施後，碎片問題並不會馬上消失，並可能還會長時間存在。這時，可以考慮flush shared pool，將記憶體碎片結合起來。但是，在做flush之前，要考慮以下問題。

· Flush會將所有沒有使用的遊標從library cache中清除出去。因此，這些語句在被再次呼叫時會被重新硬解析，從而提高CPU的佔用率和latch爭用；

· 如果應用沒有使用繫結變數，即使flush了shared pool以後，經過一段時間執行，仍然會出現大量碎片。因此，這種情況下，flush是沒有必要的，需要先考慮最佳化應用系統；

· 如果shared pool非常大，flush操作可能會導致系統被hung住。

因此，如果要flush shared pool，需要在系統不忙的時候去做。Flush的語法為，

alter system flush shared_pool;

3.2.6. TRACE 4031錯誤

如果問題比較複雜（比如由於記憶體洩漏導致），或者你不幸遇上了oracle的bug，這時就需要考慮設定4031事件來trace並dump出相關記憶體資訊。

以下語句在整個系統設定4031事件，

SQL> alter system set events '4031 trace name errorstack level 3';
SQL> alter system set events '4031 trace name HEAPDUMP level 3';

這個事件也可以在會話中設定，只要將以上語句中的“system”改為“session”就行了。

然後將dump出來的trace檔案發給oracle吧。

不過注意一點，9205以後就無需設定這個事件了，因為一旦發生4031錯誤時，oracle會自動dump出trace檔案。

4. Dump記憶體解析

下面以shared pool為例，解釋一下dump出來的記憶體結構。

SQL> conn sys/sys as sysdba

Connected.

SQL> oradebug setmypid

Statement processed.

SQL>  oradebug dump heapdump 2

Statement processed.

SQL>

以下時trace檔案的內容，我們分別解釋各個部分：

Dump file c:"oracle"product"10.2.0"admin"fuyuncat"udump"fuyuncat_ora_4032.trc

Tue Jul 11 16:03:26 2006

ORACLE V10.2.0.1.0 - Production vsnsta=0

vsnsql=14 vsnxtr=3

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Windows XP Version V5.1 Service Pack 2

CPU                 : 2 - type 586

Process Affinity    : 0x00000000

Memory (Avail/Total): Ph:885M/2039M, Ph+PgF:2702M/3890M, VA:1590M/2047M

Instance name: fuyuncat

Redo thread mounted by this instance: 1

Oracle process number: 18

Windows thread id: 4032, image: ORACLE.EXE (SHAD)

*** SERVICE NAME:(SYS$USERS) 2006-07-11 16:03:26.322

*** SESSION ID:(159.7) 2006-07-11 16:03:26.322

這部分是關於trace檔案的基本資訊，oracle版本、資源情況、使用者和會話等。

KGH Latch Directory Information

ldir state: 2  next slot: 75

Slot [  1] Latch: 03C3D280  Index: 1  Flags:  3  State: 2  next:  00000000

Slot [  2] Latch: 1EC9D4B0  Index: 1  Flags:  3  State: 2  next:  00000000

Slot [  3] Latch: 1EC9D540  Index: 1  Flags:  3  State: 2  next:  00000000

Slot [  4] Latch: 03C3E100  Index: 1  Flags:  3  State: 2  next:  00000001

Slot [  5] Latch: 1ED65CE4  Index: 1  Flags:  3  State: 2  next:  00000000

Slot [  6] Latch: 1ED65F14  Index: 1  Flags:  3  State: 2  next:  00000000

... ...

這部分記錄的是shared pool中的latch資訊。每個latch的具體資訊可以透過檢視V$LATCH、V$LATCH_PARENT、V$LATCH_CHILDREN或者表x$ksllt查出

******************************************************

HEAP DUMP heap name="sga heap"  desc=03C38510

 extent sz=0x32c8 alt=108 het=32767 rec=9 flg=-126 opc=0

 parent=00000000 owner=00000000 nex=00000000 xsz=0x10

******************************************************

這是堆dump資訊的頭部，heap name說明了記憶體所述的堆，shared pool是屬於SGA區的，因此，這裡是"sga heap"；

extent sz記錄的是所有擴充套件段的大小。

HEAP DUMP heap name="sga heap(1,0)"  desc=04EC131C

 extent sz=0xfc4 alt=108 het=32767 rec=9 flg=-126 opc=0

 parent=00000000 owner=00000000 nex=00000000 xsz=0x400000

EXTENT 0 addr=1CC00000

  Chunk 1cc00038 sz=       24  R-freeable  "reserved stoppe"

  Chunk 1cc00050 sz=   212888  R-free      "               "

  Chunk 1cc33fe8 sz=       24  R-freeable  "reserved stoppe"

  Chunk 1cc34000 sz=  3977544    perm      "perm           "  alo=3977544

  Chunk 1cfff148 sz=     3768    free      "               "

EXTENT 1 addr=1D000000

  Chunk 1d000038 sz=       24  R-freeable  "reserved stoppe"

  Chunk 1d000050 sz=   212888  R-free      "               "

  Chunk 1d033fe8 sz=       24  R-freeable  "reserved stoppe"

  Chunk 1d034000 sz=  2097168    perm      "perm           "  alo=2097168

這部分資訊是trace檔案中的主要部分，它詳細記錄了shared pool中各個chunk的資訊。

首先看它的頭部資訊，注意到這裡heap name是"sga heap(1,0)"。這是什麼意思呢？我們前面提到，oracle 10g會將shared pool分為幾個區來管理，這裡就是其中的一個區。共有4個區。透過表X$KGHLU可以看到對應的LRU連結串列。

EXTENT 0 addr=1CC00000

這一行說明下面的chunk都屬於這個擴充套件段（extent），0是它的編號，addr是它的起始地址。

  Chunk 1cc00038 sz=       24  R-freeable  "reserved stoppe"

這是一個chunk的資訊，sz是這個chunk的大小（24位元組）。R-freeable是這個chunk的狀態，"reserved stoppe"是這個chunk的用途。Chunk有4種可能狀態，以下是這四種狀態的含義：

free：即空閒chunk，可以隨時分配給適合大小的請求；

freeable：這種狀態的chunk表示它當前正在被使用，但是這種使用是短期的，比如在一次呼叫中或者一個會話中，會話或者呼叫解釋就可以被釋放出來。這種狀態的chunk是不放在LRU連結串列中的，一旦使用結束，自動成為free狀態，放到空閒列表中；

recreatable：這種狀態的chunk正在被使用，但是它所包含的物件是可以被暫時移走、重建，比如解析過的語句。它是被LRU連結串列管理的。

permanent：顧名思義，這種狀態的chunk所包含的物件是永遠不會被釋放的。即使flush shared pool也不會釋放。

我們注意到，這裡還有一些狀態是有字首“R-”的。帶有這種字首的chunk說明是shared pool中的保留區的chunk。

Total heap size    = 41942480

最後是這一shared pool區的總的大小。

FREE LISTS:

 Bucket 0 size=16

 Bucket 1 size=20

  Chunk 166ed050 sz=       20    free      "               "

  Chunk 167de068 sz=       20    free      "               "

  Chunk 164b9c10 sz=       20    free      "               "

  Chunk 1f2776f8 sz=       20    free      "               "

接下來便是這個shared pool區的空閒列表。Bucket是一個空閒列表的範圍，例如Bucket 1，它的最小值是上一個Bucket的最大值，即16，最大值為20。Bucket下面是空閒列表中chunk，後面的資訊和前面解釋chunk的資訊一樣，8位的16進位制數字是它的地址；sz是chunk的大小；free是chunk的狀態，因為是空閒列表中的chunk，這裡只有一個狀態；最後是chunk的用途，因為都是free，所以肯定為空。

Total free space   =  1787936

最後是這塊shared pool區中空閒chunk的總的大小。

RESERVED FREE LISTS:

 Reserved bucket 0 size=16

 Reserved bucket 1 size=4400

 Reserved bucket 2 size=8204

 Reserved bucket 3 size=8460

 Reserved bucket 4 size=8464

 Reserved bucket 5 size=8468

 Reserved bucket 6 size=8472

 Reserved bucket 7 size=9296

 Reserved bucket 8 size=9300

 Reserved bucket 9 size=12320

 Reserved bucket 10 size=12324

 Reserved bucket 11 size=16396

 Reserved bucket 12 size=32780

 Reserved bucket 13 size=65548

  Chunk 1b800050 sz=   212888  R-free      "               "

  Chunk 16c00050 sz=   212888  R-free      "               "

  Chunk 1ac00050 sz=   212888  R-free      "               "

Total reserved free space   =   638664

Shared pool的普通區的空閒列表下面就是關於這塊shared pool區中保留區的空閒列表的描述，其中除了在名字上bucket前面都有一個Reserved標識，和狀態前面有“R-”字首外，含義和普通空閒列表相同。

UNPINNED RECREATABLE CHUNKS (lru first):

  Chunk 1aee99c0 sz=     4096    recreate  "sql area       "  latch=1D8BDD48

  Chunk 1ae4aeec sz=     4096    recreate  "sql area       "  latch=1D8BDDB0

... ...

SEPARATOR

  Chunk 166e8384 sz=      540    recreate  "KQR PO         "  latch=1DD7F138

  Chunk 1f333a5c sz=      284    recreate  "KQR PO         "  latch=1DC7DFC8

  Chunk 166e9340 sz=      540    recreate  "KQR PO         "  latch=1DE00A70

  Chunk 1f0fe058 sz=      284    recreate  "KQR PO         "  latch=1DC7DFC8

  Chunk 1f2116b4 sz=      540    recreate  "KQR PO         "  latch=1DE81910

  Chunk 1f21127c sz=      540    recreate  "KQR PO         "  latch=1DE81910

... ...

Unpinned space     =  1611488  rcr=645 trn=864

空閒列表後面就是LRU連結串列了。LRU連結串列不是按照大小分的，因而沒有Bucket。它的chunk是按照最近最少使用的順序排列。其中chunk的資訊和前面解釋的一樣。但是要注意一點，因為LRU連結串列中的chunk都是使用的，因為每個chunk根據用途不同，都會有一個latch來保護，Chunk資訊最後便是latch的地址。

注意，我們前面提到，shared pool中是有兩種LRU連結串列的，一種迴圈LRU連結串列；另外一種是暫時LRU連結串列。在這裡LRU資訊中前面部分是迴圈LRU連結串列，SEPARATOR後面部分是暫時LRU連結串列資訊。

最後是LRU連結串列中chunk的總的大小，rcr是迴圈LRU連結串列中的chunk數，trn是暫時LRU連結串列中的chunk數。

此外，有一點提示，如果是有多個shared pool區，第一個區是不含LRU連結串列資訊的。

PERMANENT CHUNKS:

  Chunk 1d234010 sz=  1884144    perm      "perm           "  alo=1728440

  Chunk 1cc34000 sz=  3977544    perm      "perm           "  alo=3977544

  Chunk 1d034000 sz=  2097168    perm      "perm           "  alo=2097168

  Chunk 1d434000 sz=  3117112    perm      "perm           "  alo=3117112

... ...

  Chunk 1f434000 sz=  3917704    perm      "perm           "  alo=3917704

Permanent space    = 38937696

最後是永久chunk的資訊。Chunk部分解釋和前面一致。alo表示已經分配的大小。

如果有多個shared pool區，永久chunk資訊則只存在於第一個shared pool區。