oracle例項記憶體（SGA和PGA）調整

oracle例項記憶體（SGA和PGA）調整

文章分類:資料庫

一、名詞解釋
(1)SGA：System Global Area是Oracle Instance的基本組成部分，在例項啟動時分配;系統全域性域SGA主要由三部分構成：共享池、資料緩衝區、日誌緩衝區。

(2)共享池：Shared Pool用於快取最近被執行的SQL語句和最近被使用的資料定義，主要包括：Library cache（共享SQL區）和Data dictionary cache（資料字典緩衝區）。  共享SQL區是存放使用者SQL命令的區域，資料字典緩衝區存放資料庫執行的動態資訊。

(3)緩衝區快取記憶體：Database Buffer Cache用於快取從資料檔案中檢索出來的資料塊，可以大大提高查詢和更新資料的效能。

(4)大型池：Large Pool是SGA中一個可選的記憶體區域，它只用於shared server環境。

(5)Java池：Java Pool為Java命令的語法分析提供服務。

(6)PGA：Process Global Area是為每個連線到Oracle database的使用者程式保留的記憶體。

二、分析與調整
(1)系統全域性域：
  SGA與作業系統、記憶體大小、cpu、同時登入的使用者數有關。可佔OS系統實體記憶體的1/3到1/2。
a.共享池：
檢視共享SQL區的使用率：

Sql程式碼

1. select(sum(pins-reloads))/sum(pins) "Library cache" from v$librarycache;--動態效能表  

select(sum(pins-reloads))/sum(pins) "Library cache" from v$librarycache;--動態效能表

這個使用率應該在90％以上，否則需要增加共享池的大小。

檢視資料字典緩衝區的使用率：

Sql程式碼

1. select (sum(gets-getmisses-usage-fixed))/sum(gets) "Data dictionary cache" from v$rowcache;--動態效能表  

select (sum(gets-getmisses-usage-fixed))/sum(gets) "Data dictionary cache" from v$rowcache;--動態效能表

這個使用率也應該在90％以上，否則需要增加共享池的大小。

修改共享池的大小:

Sql程式碼

1. ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;  

ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;

b.緩衝區快取記憶體：
檢視資料庫資料緩衝區的使用情況：

Sql程式碼

1. SELECT name,value FROM v$sysstat order by name WHERE name IN(''DB BLOCK GETS'',''CONSISTENT GETS'',''PHYSICAL READS'');  

SELECT name,value FROM v$sysstat order by name WHERE name IN(''DB BLOCK GETS'',''CONSISTENT GETS'',''PHYSICAL READS'');

計算出來資料緩衝區的使用命中率＝1-(physical reads/(db block gets+consistent gets)),這個命中率應該在90％以上，否則需要增加資料緩衝區的大小。

c.日誌緩衝區
檢視日誌緩衝區的使用情況:

Sql程式碼

1. SELECT name, value  FROM v$sysstat WHERE name IN (''redo entries'',''redo log space requests'');  

SELECT name, value  FROM v$sysstat WHERE name IN (''redo entries'',''redo log space requests'');

查詢出的結果可以計算出日誌緩衝區的申請失敗率：  
申請失敗率＝requests/entries，申請失敗率應該接近於0，否則說明日誌緩衝區開設太小，需要增加ORACLE資料庫的日誌緩衝區。

d.大型池：
    可以減輕共享池的負擔，可以為備份、恢復等操作來使用，不使用LRU演算法來管理。其大小由資料庫的‘共享模式/db模式’如果是共享模式的話，要分配的大一些。
    指定Large Pool的大小：

Sql程式碼

1. ALTER SYSTEM SET LARGE_POOL_SIZE=64M  

ALTER SYSTEM SET LARGE_POOL_SIZE=64M

e.Java池：
    在安裝和使用Java的情況下使用。

(2)PGA調整
a.PGA_AGGREGATE_TARGET初始化設定

    PGA_AGGREGATE_TARGET的值應該基於Oracle例項可利用記憶體的總量來設定，這個引數可以被動態的修改。假設Oracle例項可分配4GB的實體記憶體，剩下的記憶體分配給作業系統和其它應用程式。你也許會分配80％的可用記憶體給Oracle例項，即3.2G。現在必須在記憶體中劃分SGA和PGA區域。

在OLTP(聯機事務處理)系統中，典型PGA記憶體設定應該是總記憶體的較小部分（例如20％），剩下80％分配給SGA。
OLTP：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%

在DSS(資料集)系統中，由於會執行一些很大的查詢，典型的PGA記憶體最多分配70％的記憶體。
DSS：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

在這個例子中，總記憶體4GB，DSS系統，你可以設定PGA_AGGREGATE_TARGET為1600MB，OLTP則為655MB。

b.配置PGA自動管理

不用重啟DB，直接線上修改。

SQL> alter system set workarea_size_policy=auto scope=both;

System altered.

SQL> alter system set pga_aggregate_target=512m scope=both;

System altered.

SQL> show parameter workarea

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO --這個設定成AUTO
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 536870912

SQL>

c.監控自動PGA記憶體管理的效能

V$PGASTAT：這個檢視給出了一個例項級別的PGA記憶體使用和自動分配的統計。

SQL> set lines 256
SQL> set pages 42
SQL> SELECT * FROM V$PGASTAT;

NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                    536870912 bytes    --當前PGA_AGGREGATE_TARGET的值
aggregate PGA auto target                                         477379584 bytes    --當前可用於自動分配了的PGA大小，應該比PGA_AGGREGATE_TARGET 小
global memory bound                                                26843136 bytes    --自動模式下工作區域的最大大小，Oracle根據工作負載自動調整。
total PGA inuse                                                     6448128 bytes
total PGA allocated                                                11598848 bytes    --PGA的最大分配
maximum PGA allocated                                             166175744 bytes
total freeable PGA memory                                            393216 bytes    --PGA的最大空閒大小
PGA memory freed back to OS                                        69074944 bytes
total PGA used for auto workareas                                         0 bytes    --PGA分配給auto workareas的大小
maximum PGA used for auto workareas                                 1049600 bytes
total PGA used for manual workareas                                       0 bytes
maximum PGA used for manual workareas                                530432 bytes
over allocation count                                                  1118    --例項啟動後，發生的分配次數，如果這個值大於0，就要考慮增加pga的值
bytes processed                                                   114895872 bytes
extra bytes read/written                                            4608000 bytes
cache hit percentage                                                  96.14 percent    --命中率

16 rows selected.

--V$PGA_TARGET_ADVICE

SQL> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
   ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
   ESTD_OVERALLOC_COUNT
   FROM v$pga_target_advice;

The output of this query might look like the following:

TARGET_MB   CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
63       23          367
125        24          30
250        30          3
375        39          0
500        58          0
600        59          0
700        59          0
800        60          0
900        60          0
1000    61          0
1500    67          0
2000    76          0
3000    83          0
4000    85          0

可以看出當TARGET_MB 為375M是ESTD_OVERALLOC_COUNT=0，所以可以將PGA_AGGREGATE_TARGET設定成375M。

附：oracle SGA與PGA區別：
SGA：是用於儲存資料庫資訊的記憶體區，該資訊為資料庫程式所共享。它包含Oracle 伺服器的資料和控制資訊,它是在Oracle伺服器所駐留的計算機的實際記憶體中得以分配，如果實際記憶體不夠再往虛擬記憶體中寫。
PGA：包含單個伺服器程式或單個後臺程式的資料和控制資訊，與幾個程式共享的SGA 正相反,PGA 是隻被一個程式使用的區域，PGA 在建立程式時分配,在終止程式時回收。