認識Oracle資料庫System Global Area （SGA）

　1 概述

　　Oracle SGA區的監控和管理，是資料庫日常維護的重要內容。本文詳細介紹SGA的基本概念，SGA運行情況檢查，以及SGA的參數設定原則，希望對大家的有所協助。

　　2 SGA的基本概念

　　當啟動Oracle資料庫時，系統會先在記憶體內規劃一個固定地區，用來儲存使用者需要的資料，以及Oracle運行時必備的系統資訊。我們稱此地區為系統全域區(System Global Area)，簡稱SGA。

　　SGA 包含數個重要地區，分別是：

　　■ Database Buffer Cache (資料庫緩衝區)

　　■ Redo Log Buffer (重做日誌緩衝區)

　　■ Shared Pool (共用區)

　　■ 其它，如Java pool， Large pool

　　2.1 Database Buffer Cache (資料庫緩衝區)

　　資料庫緩衝區的作用主要是在記憶體中緩衝從資料庫中讀取的資料區塊。資料庫緩衝區越大，為使用者已經在記憶體裡的共用資料提供的記憶體就越大，這樣可以減少所需要的磁碟物理讀。

　　在9i以前資料庫緩衝區的大小是由db_block_buffers*db_block_size 來決定大小的。db_block_size參數是在建立資料庫時設定的，OLTP系統的db_block_size一般設定為8k。

　　在9i中，資料庫緩衝區的大小由db_cache_size決定，8i中的db_block_buffers被取消。db_cache_size的單位是位元組，它直接決定了資料庫緩衝區的大小，而不再是塊的數量。

　　2.2 Shared Pool (共用區)

　　共用區由三部分組成，分別是Dictionary cache(包括資料字典的定義，如表結構、許可權等)，Library
cache(包括共用的sql遊標，sql原代碼以及執行計畫、預存程序和會話資訊)和Control
structure。它的大小由初始化參數shared_pool_size控制，它的作用是緩衝已經被解析過的SQL，使其能被重用，不用再解析。
SQL的解析非常消耗CPU的資源，如果一條SQL在Shared pool中已經存在，則進行的僅是軟解析(在Shared
pool中尋找相同SQL)，這將大大提高資料庫的運行效率。當然，這部分記憶體也並非越大越好，太大的Shared
pool，oracle為了維護共用結構，將付出更大的管理開銷。建議在150M-500M之間。如果系統記憶體為1G，該值可設為150M-200M;如
果為2G，該值設為250M-300M;每增加1G記憶體，該值增加100M;但該值最大不應超過500M。

　　2.3 Redo Log Buffer (重做日誌緩衝區)

　　Log_buffer是重做日誌緩衝區，對資料庫的任何修改都按順序被記錄在該緩衝，然後由LGWR
進程將它寫入磁碟。LGWR的寫入條件是：使用者提交、有1/3 重做日誌緩衝區未被寫入磁碟、有大於1M
重做日誌緩衝區未被寫入磁碟、逾時、DBWR需要寫入的資料的SCN 號大於LGWR 記錄的SCN 號，DBWR 觸發LGWR寫入。

　　從中可以看出，大於1M的log buffer值意義並不大。重做日誌緩衝區的大小由初始化參數log_buffer設定。

2.4 Large pool和Java pool

　　Large pool：用於MTS、並行查詢和RMAN。如果使用了MTS或RMAN，large_pool特別有用，它可以降低使用者對share pool的爭用。

　　如果應用系統不使用MTS，也不使用RMAN，large_pool_size的大小可設為1M。

　　Java
pool：Oracle8I以後，oracle資料庫內建了對java的支援，如果資料庫安裝時選擇了JServer組件，則這個值可以設定為20M-
30M之間。可以查詢v$option動態視圖，如果java那一項值為true，則表示安裝了JServer，為false，表示未安裝。

　　如果應用系統不使用PORTAL，APPS，也不使用RMAN，Java pool值的大小可設為1M。如果系統需要安裝補丁，建議先把Java pool值的大小臨時增加到50-150M。

　　3 SGA運行情況檢查

　　3.1 SGA整體檢查

　　對於SGA部分，我們通過sqlplus查詢：

　　

SQL> show sga 　　Total System Global Area 316899848 bytes 　　Fixed Size 741896 bytes 　　Variable Size 150994944 bytes 　　Database Buffers 163840000 bytes 　　Redo Buffers 1323008 bytes

　　或者

　　

SQL> select * from v$sga; 　　NAME VALUE 　　-------------------- ---------- 　　Fixed Size 741896 　　Variable Size 150994944 　　Database Buffers 163840000 　　Redo Buffers 1323008

　　Show sga的結果和SGA的初始化參數配置並不是完全一一對應的，下面是每一個部分的具體解釋：

　　Fixed Size

　　oracle 的不同平台和不同版本下可能不一樣，但對於確定環境是一個固定的值，裡面儲存了SGA各部分組件的資訊，可以看作引導建立SGA的地區。

　　Variable Size

　　包含了shared_pool_size、java_pool_size、large_pool_size等記憶體設定和用於管理資料緩衝區等記憶體結構的hash table、塊頭資訊等。

Database Buffers

　　指資料緩衝區，在8i中包含default
pool、buffer_pool_keep、buffer_pool_recycle三部分記憶體。在9i中包含db_cache_size、
db_keep_cache_size、db_recycle_cache_size、db_nk_cache_size。這裡要注意在8i中三部分記憶體
總和為db_block_buffers*db_block_size。

　　Redo Buffers

　　指重做日誌緩衝區的實際大小，和log_buffer的值可能稍有不同。

　　3.2 Data buffer 命中率檢查

　　檢查語句：

　　

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

　　調整原則：

　　命中率不應該低於90%.

　　如果該值過低，應該調高初始化參數。8i調整db_block_buffer，9i調整db_cache_size。

　　3.3 Dictionary cache命中率檢查

　　檢查語句：

　　

SQL>SELECT 1 - (SUM(getmisses)/SUM(gets)) "Data Dictionary Hit Ratio" FROM v$rowcache;

　　調整原則：

　　命中率不應該低於95%.

　　如果該值過低，應調高shared_pool_size的大小。

　　3.4 Library cache命中率檢查

　　檢查語句：

　　

SQL>SELECT 1-(SUM(reloads)/SUM(pins)) "Library cache Hit Ratio" FROM v$librarycache;

　　調整原則：

　　命中率不應該低於99%.

　　如果該值過低，應調高shared_pool_size的大小。

　3.5 Log buffer命中率檢查

　　檢查語句：

　　

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

　　調整原則：

　　命中率不應該高於1.

　　如果該值過高，應調高log_buffer的大小。

　　4 SGA 的參數設定原則

　　在 Oracle8i 時，SGA
的大小是由初始化參數檔案內的某些參數所設定，最麻煩的是每次調整參數之後必須等重新啟用資料庫才生效。從Oracle9i 開始，DBA
可以動態配置記憶體的大小;這樣的技術我們稱為「dynamicSGA」。有了dynamic SGA
，SGA的各組成地區都可以動態地進行規劃與調整，而不需先關閉資料庫。

　　在Oracle9i中，SGA_MAX_SIZE可設定 SGA 所佔用的最大記憶體空間。需要注意的是：SGA_MAX_SIZE
盡量不要超過實體記憶體大小，否則將會使用到硬碟上的虛擬記憶體，反而導致效能低下。如果 SGA_MAX_SIZE 之設定值小於其它 SGA
相關參數設定值的總和，或是小於各相關參數預設值的總和，則 SGA_MAX_SIZE 之設定值無效。

　　在SGA的基本概念部分，已經介紹了各個部分大小設定的原則，這裡再用一句話來總結：

　　1)資料庫緩衝區建議占實體記憶體的20%-30%。實體記憶體在1G以內，可以佔20%，1G-2G之間可佔25%，實體記憶體在2G以上，可以佔30%，甚至更高。

　　2)共用區建議在150M-500M之間。如果系統記憶體為1G，該值可設為150M-200M;如果為2G，該值設為250M-300M;每增加1G記憶體，該值增加100M;但該值最大不應超過500M。

　　3)重做日誌緩衝區建議設為1M。

　　4)如果應用系統不使用MTS，也不使用RMAN，Large pool的大小可設為1M。否則設為16M。

　　5)如果應用系統不使用RMAN，Java pool值的大小可設為1M。否則設為50M。如果系統需要安裝補丁，建議先把Java pool值的大小臨時增加到50-150M。

　　調整完上述參數後，一定要計算SGA的尺寸，總原則是SGA的尺寸應小於實體記憶體的一半。SGA的計算方法如
下：SGA=DB_BLOCK_BUFFERS*DB_BLOCK_SIZE+SHARED_POOL_SIZE+LOG_BUFFER。同時還要保證
(SGA+sort_area_size*sesson數量)不大於實體記憶體的70%。