一急波、名詞解釋

(1)SGA：SystemGlobal?Area是OracleInstance的基本組成部分煎楣，在實例啟動時分配;系統(tǒng)全局域SGA主要由三部分構(gòu)成：共享池砂吞、數(shù)據(jù)緩沖區(qū)预茄、日志緩沖區(qū)铃将。

(2)共享池：Shared?Pool用于緩存最近被執(zhí)行的SQL語句和最近被使用的數(shù)據(jù)定義项鬼，主要包括：Librarycache（共享SQL區(qū)）和Datadictionarycache（數(shù)據(jù)字典緩沖區(qū)）。 共享SQL區(qū)是存放用戶SQL命令的區(qū)域劲阎，數(shù)據(jù)字典緩沖區(qū)存放數(shù)據(jù)庫運行的動態(tài)信息绘盟。

(3)緩沖區(qū)高速緩存：DatabaseBufferCache用于緩存從數(shù)據(jù)文件中檢索出來的數(shù)據(jù)塊，可以大大提高查詢和更新數(shù)據(jù)的性能悯仙。

(4)大型池：Large Pool是SGA中一個可選的內(nèi)存區(qū)域龄毡，它只用于shared?server環(huán)境。

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

(6)PGA：ProcessGlobal?Area是為每個連接到Oracledatabase的用戶進(jìn)程保留的內(nèi)存沦零。

二、分析與調(diào)整

(1)系統(tǒng)全局域：

SGA與操作系統(tǒng)货岭、內(nèi)存大小路操、cpu疾渴、同時登錄的用戶數(shù)有關(guān)⊥驼蹋可占OS系統(tǒng)物理內(nèi)存的1/3到1/2搞坝。

a.共享池Shared?Pool：

查看共享池大小Sql代碼

SQL>show?parameter shared_pool_size

查看共享SQL區(qū)的使用率：

Sql代碼

select(sum(pins-reloads))/sum(pins)"Library cache"from?v$librarycache;

--動態(tài)性能表

LIBRARY命中率應(yīng)該在90％以上，否則需要增加共享池的大小祭钉。

查看數(shù)據(jù)字典緩沖區(qū)的使用率：

Sql代碼

select(sum(gets-getmisses-usage-fixed))/sum(gets)"Data dictionary cache"from?v$rowcache;

--動態(tài)性能表

這個使用率也應(yīng)該在90％以上瞄沙，否則需要增加共享池的大小。

修改共享池的大小:

Sql代碼

ALTERSYSTEMSET?SHARED_POOL_SIZE?=64M;

b.緩沖區(qū)高速緩存DatabaseBufferCache：

查看共享池大小Sql代碼

SQL>show?parameter db_cache_size

查看數(shù)據(jù)庫數(shù)據(jù)緩沖區(qū)的使用情況：

Sql代碼

SELECTname,valueFROM?v$sysstat?orderbynameWHEREnameIN(''DBBLOCK?GETS'',''CONSISTENT?GETS'',''PHYSICALREADS'');

SELECT?*?FROM?V$SYSSTAT?WHERENAMEIN('parse_time_cpu','parse_time_elapsed','parse_count_ hard');

計算出來數(shù)據(jù)緩沖區(qū)的使用命中率＝1-(physicalreads/(dbblock?gets+consistent?gets)),這個命中率應(yīng)該在90％以上慌核，否則需要增加數(shù)據(jù)緩沖區(qū)的大小。

c.日志緩沖區(qū)

查看日志緩沖區(qū)的使用情況:

Sql代碼

SELECTname,valueFROM?v$sysstat?WHEREnameIN('redo entries','redo log space requests')

查詢出的結(jié)果可以計算出日志緩沖區(qū)的申請失敗率：

申請失敗率＝requests/entries申尼，申請失敗率應(yīng)該接近于0垮卓，否則說明日志緩沖區(qū)開設(shè)太小，需要增加ORACLE數(shù)據(jù)庫的日志緩沖區(qū)师幕。

d.大型池：

可以減輕共享池的負(fù)擔(dān)粟按，可以為備份、恢復(fù)等操作來使用霹粥，不使用LRU算法來管理灭将。其大小由數(shù)據(jù)庫的'共享模式/db模式'如果是共享模式的話，要分配的大一些后控。

指定Large Pool的大忻硎铩：

Sql代碼

ALTERSYSTEMSET?LARGE_POOL_SIZE=64M

e.Java池：

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

(2)PGA調(diào)整

a.PGA_AGGREGATE_TARGET初始化設(shè)置

PGA_AGGREGATE_TARGET的值應(yīng)該基于Oracle實例可利用內(nèi)存的總量來設(shè)置浩淘，這個參數(shù)可以被動態(tài)的修改捌朴。假設(shè)Oracle實例可分配4GB的物理內(nèi)存，剩下的內(nèi)存分配給操作系統(tǒng)和其它應(yīng)用程序张抄。你也許會分配80％的可用內(nèi)存給Oracle實例砂蔽，即3.2G。現(xiàn)在必須在內(nèi)存中劃分SGA和PGA區(qū)域署惯。

在OLTP(聯(lián)機(jī)事務(wù)處理)系統(tǒng)中左驾，典型PGA內(nèi)存設(shè)置應(yīng)該是總內(nèi)存的較小部分（例如20％），剩下80％分配給SGA极谊。

OLTP：PGA_AGGREGATE_TARGET=(total_mem *?80%)?*?20%=2.5G

在DSS(數(shù)據(jù)集)系統(tǒng)中诡右，由于會運行一些很大的查詢，典型的PGA內(nèi)存最多分配70％的內(nèi)存怀酷。

DSS：PGA_AGGREGATE_TARGET=(total_mem *?80%)?*?50%

在這個例子中稻爬，總內(nèi)存4GB，DSS系統(tǒng)蜕依，你可以設(shè)置PGA_AGGREGATE_TARGET為1600MB桅锄，OLTP則為655MB琉雳。

b.配置PGA自動管理

不用重啟DB，直接在線修改友瘤。

SQL>altersystemset?workarea_size_policy=autoscope=both;

System?altered.

SQL>altersystemset?pga_aggregate_target=512mscope=both;

System?altered.

SQL>show?parameter workarea

NAME???????????????????????????????? TYPE??????? VALUE

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

workarea_size_policy???????????????? string????? AUTO--這個設(shè)置成AUTO

SQL>show?parameter pga

NAME???????????????????????????????? TYPE??????? VALUE

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

pga_aggregate_target???????????????? big integer 500M

c.監(jiān)控自動PGA內(nèi)存管理的性能

V$PGASTAT：這個視圖給出了一個實例級別的PGA內(nèi)存使用和自動分配的統(tǒng)計翠肘。

SQL>set?lines?256

SQL>set?pages?42

SQL>SELECT?*?FROM?V$PGASTAT;

NAMEVALUE?UNIT

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

aggregate PGA target parameter536870912?bytes

--當(dāng)前PGA_AGGREGATE_TARGET的值

aggregate PGAauto?target?477379584?bytes

--當(dāng)前可用于自動分配了的PGA大小，應(yīng)該比PGA_AGGREGATE_TARGET小

globalmemorybound26843136?bytes

--自動模式下工作區(qū)域的最大大小辫秧，Oracle根據(jù)工作負(fù)載自動調(diào)整束倍。

total PGA inuse6448128?bytes

total PGA allocated11598848?bytes

--PGA的最大分配

maximum PGA allocated166175744?bytes

total freeable PGAmemory393216?bytes

--PGA的最大空閑大小

PGAmemory?freed back?to?OS?69074944?bytes

total PGA usedforauto?workareas?0?bytes

--PGA分配給auto workareas的大小

maximum PGA usedforauto?workareas?1049600?bytes

total PGA usedformanual?workareas?0?bytes

maximum PGA usedformanual?workareas?530432?bytes

over?allocation?count1118

--實例啟動后，發(fā)生的分配次數(shù)盟戏，如果這個值大于0绪妹，就要考慮增加pga的值

bytes processed114895872?bytes

extra bytesread/written4608000?bytes

cache?hit percentage?96.14percent

--命中率

16rows?selected.

--V$PGA_TARGET_ADVICE

SQL>SELECTround(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?likethefollowing:

TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT

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

6323367

1252430

250303

375390

500580

600590

700590

800600

900600

1000610

1500670

2000760

3000830

4000850

可以看出當(dāng)TARGET_MB為375M是ESTD_OVERALLOC_COUNT=0，所以可以將PGA_AGGREGATE_TARGET設(shè)置成375M柿究。

附：oracle SGA與PGA區(qū)別：

SGA：是用于存儲數(shù)據(jù)庫信息的內(nèi)存區(qū)邮旷，該信息為數(shù)據(jù)庫進(jìn)程所共享。它包含Oracle服務(wù)器的數(shù)據(jù)和控制信息,它是在Oracle服務(wù)器所駐留的計算機(jī)的實際內(nèi)存中得以分配蝇摸，如果實際內(nèi)存不夠再往虛擬內(nèi)存中寫婶肩。

PGA：包含單個服務(wù)器進(jìn)程或單個后臺進(jìn)程的數(shù)據(jù)和控制信息，與幾個進(jìn)程共享的SGA正相反,PGA是只被一個進(jìn)程使用的區(qū)域貌夕，PGA在創(chuàng)建進(jìn)程時分配,在終止進(jìn)程時回收律歼。

另一篇文章中也這樣寫了相關(guān)的調(diào)整記錄

如何估算PGA，SGA的大小啡专，配置數(shù)據(jù)庫服務(wù)器的內(nèi)存

ORACLE給的建議是:?OLTP系統(tǒng)PGA=(TotalMemory)*80%*20%险毁。

DSS系統(tǒng)PGA=(TotalMemory)*80%*50%。

ORACLE建議一個數(shù)據(jù)庫服務(wù)器植旧，分80%的內(nèi)存給數(shù)據(jù)庫辱揭，20%的內(nèi)存給操作系統(tǒng)，那怎么給一個數(shù)據(jù)庫服務(wù)器配內(nèi)存呢病附？

SQL>select?*?from?v$pgastat;

NAMEVALUE?UNIT

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

aggregate PGA target parameter104857600?bytes

-----這個值等于參數(shù)PGA_AGGREGATE_TARGET的值问窃，如果此值為0，表示禁用了PGA自動管理完沪。

aggregate PGAauto?target?75220992?bytes

-----表示PGA還能提供多少內(nèi)存給自動運行模式域庇，通常這個值接近pga_aggregate_target-total pga inuse.

globalmemorybound20971520?bytes

-----工作區(qū)執(zhí)行的最大值，如果這個值小于1M覆积，馬上增加PGA大小

total PGA inuse30167040?bytes

-----當(dāng)前分配PGA的總大小听皿，這個值有可能大于PGA，如果PGA設(shè)置太小.這個值接近select sum(pga_used_mem) from v$process.

total PGA allocated52124672?bytes

-----工作區(qū)花費的總大小

maximum PGA allocated67066880?bytes

total freeable PGAmemory0?bytes?----沒有了空閑的PGA

processcount23----當(dāng)前一個有23個process

max?processes?count25

PGAmemory?freed back?to?OS?0?bytes

total PGA usedforauto?workareas?8891392?bytes

maximum PGA usedforauto?workareas?22263808?bytes

total PGA usedformanual?workareas?0?bytes?---為0自動管理

maximum PGA usedformanual?workareas?0?bytes?---為0自動管理

over?allocation?count0

--如果PGA設(shè)置太小宽档，導(dǎo)致PGA有時大于PGA_AGGREGATE_TARGET的值尉姨，此處為0，說明PGA沒有擴(kuò)展大于TARGET的值吗冤，如果此值出現(xiàn)過又厉，那么增加PGA大小九府。

bytes processed124434432?bytes

extra bytesread/written0?bytes

cache?hit percentage?100percent

---命中率為100%，如果太小增加PGA

recomputecount(total)6651

19rows?selected

SQL>selectmax(pga_used_mem)/1024/1024Mfrom?v$process;

----當(dāng)前一個process消耗最大的內(nèi)存

M

----------

9.12815189

SQL>selectmin(pga_used_mem)/1024/1024Mfrom?v$process?where?pga_used_mem>0;---process消耗最少內(nèi)存

M

----------

0.19186878

SQL>selectmax(pga_used_mem)/1024/1024Mfrom?v$process?;

----process曾經(jīng)消耗的最大內(nèi)存

M

----------

9.12815189

SQL>selectsum(pga_used_mem)/1024/1024from?v$process;----當(dāng)前process一共消耗的PGA

SUM(PGA_USED_MEM)/1024/1024

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

28.8192501068115

如何設(shè)置PGA呢覆致？我們可以在壓力測試階段侄旬，模擬一下系統(tǒng)的運行，然后運行

select(selectsum(pga_used_mem)/1024/1024from?v$process)/(selectcount(*)from?v$process)fromdual;

得到一個process大約占用了多少的內(nèi)存煌妈，然后估算系統(tǒng)一共會有多少連接儡羔，比如一共有500個連接，

那么sessions=1.1*process+5=500璧诵，那么processes=450,

再乘以一個process需要消耗的內(nèi)存汰蜘，就能大約估算出PGA需要設(shè)置多大。

EG = 1.1 * 450 = 495M估算的大一點550M就OK樂

最好將PGA設(shè)置的值比計算出的值大一點腮猖，PGA值設(shè)定好后鉴扫，就可以根據(jù)系統(tǒng)的性質(zhì),如果系統(tǒng)為OLTOP，那么總的內(nèi)存可以設(shè)置為PGA/0.16,最后也能估算出SGA的大小澈缺，建議還是多配點內(nèi)存，反正便宜炕婶。

下面摘抄eygle的關(guān)于一個process能夠分配的最大內(nèi)存(串行操作)的規(guī)則:

10gR1之前姐赡，對于串行操作(非并行)一個process能夠分配的最大的內(nèi)存為min(5%pga_aggregate_target,100m)

10gR2之后，對于串行操作(非并行)一個process能夠分配的最大內(nèi)存有如下規(guī)則:

如果pga_aggregate_target<=500m,那么最大的內(nèi)存為20%*pga_aggregate_target.

如果500m

如果1000m

如果pga_aggregate_target>2.5G,那么最大內(nèi)存為2.5G.

SQL>SELECT?x.ksppinmNAME,?y.ksppstvlVALUE,?x.ksppdesc describ

FROM?SYS.x$ksppi x,?SYS.x$ksppcv y?WHERE?x.inst_id=USERENV('Instance')

AND?y.inst_id=USERENV('Instance')AND?x.indx=?y.indxAND?x.ksppinmLIKE'%&par%'

NAMEVALUE?DESCRIB

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

_smm_max_size20480?maximum?work?area?sizeinautomode(serial)

SQL>show?parameter pga

NAMETYPEVALUE

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

pga_aggregate_target biginteger100M

此處我的一個process能夠分配的最大內(nèi)存為20M柠掂，因為我的PGA=100M项滑，符合上面的規(guī)則。

隱含參數(shù)_smm_max_size表示一個process能夠分配最大的memory.

買了piner的《oracle高可用環(huán)境》一書涯贞，正好趁這段時間學(xué)習(xí)一下枪狂。

把看到的東西總結(jié)一下發(fā)表于此，今天先發(fā)第一章關(guān)于SGA與PGA的內(nèi)容宋渔。

以后會陸續(xù)將總結(jié)在此發(fā)表州疾，與大家共享。

SGA與PGA的結(jié)構(gòu)如下圖：

SGA:

查看SGA：

Sqlp>show?sga

或select?*?from?v$sga;

TotalSystemGlobal?Area?289406976?bytes

FixedSize1248600?bytes

VariableSize176161448?bytes

Database?Buffers?109051904?bytes

Redo Buffers2945024?bytes

FixedSize：包括了數(shù)據(jù)庫與實例的控制信息皇拣、狀態(tài)信息严蓖、字典信息等，啟動時就被固定在SGA中氧急，不會改變颗胡。

VariableSize：包括了shard pool、large pool吩坝、java?pool毒姨、stream pool、游標(biāo)區(qū)和其他結(jié)構(gòu)

Database?Buffers：數(shù)據(jù)庫中數(shù)據(jù)塊緩沖的地方钉寝，是SGA中最大的地方弧呐，決定數(shù)據(jù)庫性能

Redo Buffers：提供REDO緩沖的地方闸迷，在OLAP中不需要太大

V$sgastat記錄了SGA的一些統(tǒng)計信息

V$sga_dynamic_components保存SGA中可以手動調(diào)整的區(qū)域的一些調(diào)整記錄

Shard pool:

Shard_pool_size決定其大小，10g以后自動管理

Shard_pool中數(shù)據(jù)字典和控制區(qū)結(jié)構(gòu)用戶無法直接控制泉懦，與用戶有關(guān)的只有sql緩沖區(qū)（librarycache）稿黍。

將經(jīng)常訪問的過程或包用DBMS_SHARED_POOL.KEEP存儲過程將該包pin在共享池中。

手工清除共享池的內(nèi)容：altersystemflush?shard_pool;

共享池相關(guān)的幾個常用的視圖：

V$sqlarea記錄了所有sql的統(tǒng)計信息崩哩，包括執(zhí)行次數(shù)巡球、物理讀、邏輯讀邓嘹、耗費時間等

V$sqltext_with_newline完全顯示sql語句酣栈，通過hash_value來標(biāo)示語句，piece排序

V$sql_plan保存了sql的執(zhí)行計劃汹押，通過工具查看

V$shared_pool_advice對共享池的預(yù)測矿筝，可以做調(diào)整SGA的參考

Databuffer：

在OLTP系統(tǒng)中要求databuffer的命中率在95%以上

selectsum(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

計算命中率的語句

select?round((1?- (physical.value - direct.value - lobs.value)/logical.value)*100,2) "Buffer Cache Hit Ratio"

from?v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical

where?physical.name ='physical reads'

and?direct.name ='physical reads direct'

and?lobs.name ='physical reads direct (lob)'

and?logical.name ='session logical reads';

PINSNUMBERNumberof?times?a?PIN was requested?for?objects?of?this namespace

PINHITSNUMBERNumberof?times?allofthe?metadata pieces?ofthelibraryobject?were?foundinmemory

RELOADSNUMBERAny?PIN?of?an?object?that?isnotthefirst?PIN performed since?theobject?handle was created,and?which requires loadingtheobjectfromdisk

Oracle把從databuffer中獲得的數(shù)據(jù)庫叫cache?hit，把從磁盤獲得的腳cache?miss

數(shù)據(jù)緩沖區(qū)中的數(shù)據(jù)塊通過臟列表（dirtylist）和LRU列表（LRUlist）來管理棚贾。

Databuffer可細(xì)分為：default?pool窖维、keep?pool、recycle?pool對應(yīng)的參數(shù)為db_cache_size妙痹、db_keep_cache_size铸史、db_recycle_size分別表示緩沖區(qū)大小

從9i開始o(jì)racle支持不同塊大小的表空間，相應(yīng)的可以為不同塊大小的表空間指定不同塊大小的數(shù)據(jù)緩沖區(qū)怯伊，不同塊大小的數(shù)據(jù)緩沖區(qū)可以用相應(yīng)的db_nk_cache_size來指定琳轿，其中n可以是2、4耿芹、6崭篡、16或32

V$db_cache_advice對數(shù)據(jù)緩沖區(qū)的預(yù)測，可以做調(diào)整databuffer的參考

V$bh吧秕、x$bh記錄了數(shù)據(jù)塊在databuffer中緩沖的情況琉闪，通過這個視圖可以找系統(tǒng)中的熱點塊。

通過下面語句找系統(tǒng)中top10熱點快所在的熱點對象:

Select/*+ rule*/?owner,object_namefrom?dba_objects

Where?data_object_id?in

(select?obj?from

(select?obj?from?x$bh?orderby?tch?desc)

Whererownum<11);

PGA：

用來保存于用戶進(jìn)程相關(guān)的內(nèi)存段寇甸。

從9i開始使用PGA自動管理塘偎，pga_aggregate_target參數(shù)指定session一共使用的最大PGA內(nèi)存的上限。Workarea_size_policy參數(shù)用于開關(guān)PGA內(nèi)存自動管理功能拿霉，auto/manual

在OLTP環(huán)境中吟秩，自動PGA管理只要設(shè)置到一定的值，如2G左右就能滿足系統(tǒng)的要求绽淘。

自動內(nèi)存管理：

從9i開始涵防，sga_max_size參數(shù)設(shè)置SGA的內(nèi)存大小，不能動態(tài)修改

從10g開始，指定了sga_target參數(shù)后壮池，所有的SGA組件如：shared?pool偏瓤、databuffer、large pool都不用手工指定了椰憋，Oracle會自動管理厅克。這一特性就是自動共享內(nèi)存管理ASMM。如果設(shè)置了sga_target=0,就自動關(guān)閉自動共享內(nèi)存管理功能橙依。Sga_target大小不能超過sga_max_size的大小证舟。

手動管理SGA：

Altersystemset?sga_target=2000m;

Altersystemset?db_cache_size=1000m;

Altersystemsetshared_pool=200m;

Altersystemset?sga_target=0---------關(guān)閉自動共享內(nèi)存管理ASMM

11G以后sga+pga整個內(nèi)存可以自動管理AMM，相關(guān)參數(shù)memory_max_target memory_target.設(shè)置好這兩個參數(shù)后就不用關(guān)心SGA和PGA了

11g手動內(nèi)存管理：

Altersystemset?memory_target=3000m;

Altersystemset?sga_target=2000m;

Altersystemset?pga_aggregate_target=1000m;

Altersystemset?memory_target=0;---------關(guān)閉自動內(nèi)存管理AMM

SGA+PGA最好不要超過總內(nèi)存的70%

轉(zhuǎn)自：http://blog.csdn.net/xiaobluesky/article/details/6569038