對oracle例項的記憶體（SGA和PGA）進行調整，優化資料庫性

二、名詞解釋：

SGA：System Global Area是Oracle Instance的基本組成部分，在例項啟動時分配;

系統全域性域SGA主要由三部分構成：共享池、資料緩衝區、日誌緩衝區。

共享池：Shared Pool用於快取最近被執行的SQL語句和最近被使用的資料定義，

主要包括：Library cache（共享SQL區）和Data dictionary cache（資料字典緩衝區）

共享SQL區是存放使用者SQL命令的區域，資料字典緩衝區存放資料庫執行的動態資訊

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

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

Java池：ava Pool為Java命令的語法分析提供服務

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

三、分析與調整：

1、系統全域性域：

SGA與作業系統、記憶體大小、cpu、同時登入的使用者數有關。可佔OS系統實體記憶體的1/2到1/3，當然，如果伺服器上只有oracle的話，

可以分配的更大一些，如果還有其他服務，如IIS等，那就要分的小一些。

1、共享池：

修改共享池的大小，ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;

檢視共享SQL區的使用率：

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

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

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

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

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

2、緩衝區快取記憶體：

它的大小要根據資料量來決定：

 SGA=((db_block_buffers * block size)+(shared_pool_size+large_pool_size+java_pool_size+log_buffers)+1MB

檢視資料庫資料緩衝區的使用情況：

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％以上，否則需要

增加資料緩衝區的大小。

select sum(pins) "請求存取數",sum(reloads) "不命中數",sum(reloads)/sum(pins) from v$librarycache

其中，pins,顯示在庫快取記憶體中執行的次數;reload,顯示在執行階段庫快取記憶體不命中的數目，一般 sum(reloads)/sum(pins)的

值應接近於零.如果大於1%就應該增加shared_pool_size的值, 來提高資料字典快取記憶體可用的記憶體數量,減少不命中數.

通過動態效能表v$rowcache來查詢資料字典快取記憶體的活動：

select sum(gets) "請求存取數",sum(getmisses) "不命中數" from v$rowcache 

其中，gets,顯示請求相應項的總數; getmisses,顯示造成快取記憶體不命中的資料請求數. 

Hit ratio與wait events：

select value from $pgastat where name =''cache hit percentage''

Hit ratio與wait events是此消彼漲，在執行類似於

select col1,col2,.. from tab1 a where exists (

select 1 from tab2 where a.col3 = b.col3

)

where ....

的語句的時候,如果tab1的記錄很多的話,你會發現系統的hit ratio會有很大的提高,wait events是否會改觀呢.

3、日誌緩衝區

SELECT name, value  FROM v$sysstat WHERE name IN (''redo entries'',''redo log space requests'')檢視日誌緩衝區的使用情況。

查詢出的結果可以計算出日誌緩衝區的申請失敗率：

  申請失敗率＝requests/entries，申請失敗率應該接近於0，否則說明日誌緩衝區開設太小，需要增加ORACLE資料庫的日誌緩衝區。

3、大型池：

可以減輕共享池的負擔

可以為備份、恢復等操作來使用

不使用LRU演算法來管理

 其大小由資料庫的‘共享模式/db模式’如果是共享模式的話，要分配的大一些

指定Large Pool的大小，ALTER SYSTEM SET LARGE_POOL_SIZE=64M

3、大型池：

        可以減輕共享池的負擔

        可以為備份、恢復等操作來使用

        不使用LRU演算法來管理

 其大小由資料庫的‘共享模式/db模式’如果是共享模式的話，要分配的大一些

        指定Large Pool的大小，ALTER SYSTEM SET LARGE_POOL_SIZE=64M

     3、Java池：

        在安裝和使用Java的情況下使用

如何估算PGA，SGA的大小，配置資料庫伺服器的記憶體
ORACLE給的建議是: OLTP系統  PGA=(Total Memory)*80%*20%。DSS系統PGA=(Total Memory)*80%*50%。
ORACLE建議一個資料庫伺服器，分80%的記憶體給資料庫，20%的記憶體給作業系統，那怎麼給一個資料庫伺服器配記憶體呢？
SQL> select * from v$pgastat;
NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                    104857600 bytes 
-----這個值等於引數PGA_AGGREGATE_TARGET的值，如果此值為0，表示禁用了PGA自動管理。
aggregate PGA auto target                                          75220992 bytes     
-----表示PGA還能提供多少記憶體給自動執行模式，通常這個值接近pga_aggregate_target-total pga inuse.
global memory bound                                                20971520 bytes
-----工作區執行的最大值，如果這個值小於1M，馬上增加PGA大小
total PGA inuse                                                    30167040 bytes
-----當前分配PGA的總大小，這個值有可能大於PGA，如果PGA設定太小.這個值接近select sum(pga_used_mem) from v$process.
total PGA allocated                                                52124672 bytes
-----工作區花費的總大小
maximum PGA allocated                                              67066880 bytes
total freeable PGA memory                                                 0 bytes         ----沒有了空閒的PGA
process count                                                            23                    ----當前一個有23個process
max processes count                                                      25
PGA memory freed back to OS                                               0 bytes
total PGA used for auto workareas                                   8891392 bytes
maximum PGA used for auto workareas                                22263808 bytes
total PGA used for manual workareas                                       0 bytes                  ---為0自動管理
maximum PGA used for manual workareas                                     0 bytes            ---為0自動管理
over allocation count                                                     0       
如果PGA設定太小，導致PGA有時大於PGA_AGGREGATE_TARGET的值，此處為0，說明PGA沒有擴充套件大於TARGET的值，如 果此值出現過，那麼增加PGA大小。
bytes processed                                                   124434432 bytes
extra bytes read/written                                                  0 bytes
cache hit percentage                                                    100 percent  ---命中率為100%，如果太小增加PGA
recompute count (total)                                                6651
19 rows selected
SQL> select max(pga_used_mem)/1024/1024 M from v$process;   ----當前一個process消耗最大的記憶體
M
----------
9.12815189
SQL> select min(pga_used_mem)/1024/1024 M from v$process where pga_used_mem>0; ---process消耗最少記憶體
M
----------
0.19186878
SQL> select max(pga_used_mem)/1024/1024 M from v$process ;    ----process曾經消耗的最大記憶體
M
----------
9.12815189
SQL> select sum(pga_used_mem)/1024/1024 from v$process;   ----當前process一共消耗的PGA
SUM(PGA_USED_MEM)/1024/1024
---------------------------
28.8192501068115
如何設定PGA呢？我們可以在壓力測試階段，模擬一下系統的執行，然後執行
select (select sum(pga_used_mem)/1024/1024 from v$process) /(select count(*) from v$process) from dual;得到一個process大約佔用了多少的記憶體，然後估算系統一共會有多少連線，比如一共有500個連線，
那麼Sessions=1.1*process +5=500，那麼processes=450,再乘以一個process需要消耗的記憶體，就能大約估算出PGA需要設定多大。
最好將PGA設定的值比計算出的值大一點，PGA值設定好後，就可以根據系統的性質,如果系統為OLTOP，那麼總的記憶體可以設定為 PGA/0.16,最後也能估算出SGA的大小，建議還是多配點記憶體，反正便宜。
下面摘抄eygle的關於一個process能夠分配的最大記憶體(序列操作)的規則:
10gR1之前，對於序列操作(非並行)一個process能夠分配的最大的記憶體為 min(5%pga_aggregate_target,100m)
10gR2之後，對於序列操作(非並行)一個process能夠分配的最大記憶體有如下規則:
如果pga_aggregate_target<=500m,那麼最大的記憶體為20%*pga_aggregate_target.
如果500m如果1000m如果pga_aggregate_target>2.5G,那麼最大記憶體為2.5G.
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2  FROM SYS.x$ksppi x, SYS.x$ksppcv y
3  WHERE x.inst_id = USERENV ('Instance')
4  AND y.inst_id = USERENV ('Instance')
5  AND x.indx = y.indx
6  AND x.ksppinm LIKE '%&par%'
7  /
NAME                                                                             VALUE                                                                            DESCRIB
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
_smm_max_size                                                                    20480                                                                            maximum work area size in auto mode (serial)
SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 100M
此處我的一個process能夠分配的最大記憶體為20M，因為我的PGA=100M，符合上面的規則。
隱 含引數_smm_max_size表示一個process能夠分配最大的memory.

 

買了piner的《oracle高可用環境》一書，正好趁這段時間學習一下。
把看到的東西總結一下發表於此，今天先發第一章關 於SGA與PGA的內容。

以後會陸續將總結在此發表，與大家共享。

SGA與PGA的結構如下圖：

SGA:
檢視SGA：
Sqlp> show sga
或 select * from v$sga;
Total System Global Area  289406976 bytes
Fixed Size                  1248600 bytes
Variable Size             176161448 bytes
Database Buffers          109051904 bytes
Redo Buffers                2945024 bytes

Fixed Size：包括了資料庫與例項的控制資訊、狀態資訊、字典資訊等，啟動時就被固定在SGA中，不會改變。
Variable Size：包括了shard pool、large pool、java pool、stream pool、遊標區和其他結構
Database Buffers：資料庫中資料塊緩衝的地方，是SGA中最大的地方，決定資料庫效能
Redo Buffers：提供REDO緩衝的地方，在OLAP中不需要太大

V$sgastat記錄了SGA的一些統計資訊
V$sga_dynamic_components 儲存SGA中可以手動調整的區域的一些調整記錄

Shard pool:
Shard_pool_size決定其大小，10g以後 自動管理
Shard_pool中資料字典和控制區結構使用者無法直接控制，與使用者有關的只有sql緩衝區（library cache）。
將 經常訪問的過程或包用DBMS_SHARED_POOL.KEEP儲存過程將該包pin在共享池中。
手工清除共享池的內容：alter system flush shard_pool;

共享池相關的幾個常用的檢視：
V$sqlarea 記錄了所有sql的統計資訊，包括執行次數、物理讀、邏輯讀、耗費時間等
V$sqltext_with_newline 完全顯示sql語句，通過hash_value來標示語句，piece排序
V$sql_plan儲存了sql的執行計劃，通過工具檢視
V$shared_pool_advice 對共享池的預測，可以做調整SGA的參考

Data buffer：

在OLTP系統中要求data buffer 的命中率在95%以上
select sum(pins) "execution",sum(pinhits) "hits",
((sum(pinhits)/sum(pins))*100) "pinhitration",
sum(reloads) "misses",((sum(pins)/(sum(pins)
+sum(reloads)))*100) "relhitratio"
from V$librarycache

PINS NUMBER Number of times a PIN was requested for objects of this namespace
PINHITS NUMBER Number of times all of the metadata pieces of the library object were found in memory
RELOADS NUMBER Any PIN of an object that is not the first PIN performed since the object handle was created, and which requires loading the object from disk

Oracle把從data buffer中獲得的資料庫叫cache hit，把從磁碟獲得的腳cache miss
資料緩衝 區中的資料塊通過髒列表（dirty list）和LRU列表（LRU list）來管理。

Data buffer可細分為：default pool、keep pool、recycle pool對應的引數為db_cache_size、 db_keep_cache_size 、db_recycle_size分別表示緩衝區大小

從9i開始oracle支援不同塊大小的 表空間，相應的可以為不同塊大小的表空間指定不同塊大小的資料緩衝區，不同塊大小的資料緩衝區可以用相應的db_nk_cache_size來指定，其中 n可以是2、4、6、16或32

V$db_cache_advice 對資料緩衝區的預測，可以做調整data buffer的參考

V$bh、 x$bh記錄了資料塊在data buffer中緩衝的情況，通過這個檢視可以找系統中的熱點塊。通過下面語句找系統中top 10 熱點快所在的熱點物件:

Select /*+ rule*/ owner,object_name from dba_objects
Where data_object_id in
(select obj from
(select obj from x$bh order by tch desc)
Where rownum<11);

PGA：
用來儲存於使用者程式相 關的記憶體段。
從9i開始使用PGA自動管理，pga_aggregate_target引數指定session一共使用的最大PGA記憶體的上限。 Workarea_size_policy引數用於開關PGA記憶體自動管理功能，auto/manual

在OLTP環境中，自動PGA 管理只要設定到一定的值，如2G左右就能滿足系統的要求。

自動記憶體管理：
從9i開始，sga_max_size引數設定SGA 的記憶體大小，不能動態修改

從10g開始，指定了sga_target引數後，所有的SGA元件如：shared pool、 data buffer、 large pool都不用手工指定了，Oracle會自動管理。這一特性就是自動共享記憶體管理ASMM。如果設定了sga_target=0,就自動關閉自動共享內 存管理功能。Sga_target大小不能超過sga_max_size的大小。
手動管理SGA：
Alter system set sga_target=2000m;
Alter system set db_cache_size=1000m;
Alter system set shared_pool=200m;
Alter system set sga_target=0---------關閉自動共享記憶體管理ASMM

11G以後sga+pga整個記憶體可以自動管理AMM，相關 引數memory_max_target  memory_target.設定好這兩個引數後就不用關心SGA和PGA了
11g手動記憶體管理：
Alter system set memory_target=3000m;
Alter system set sga_target=2000m;
Alter system set pga_aggregate_target=1000m;
Alter system set memory_target=0;---------關閉自動記憶體管理AMM

SGA+PGA最好不要超過總記憶體的70%

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25736250/viewspace-703616/，如需轉載，請註明出處，否則將追究法律責任。