一、SGA和PGA初始值到底該設(shè)置成多大
1.下面是官方的建議
■For OLTP , the PGA for a small of the total (for , 20%), 80% for the SGA.
■For DSS large, - , PGA can use up to 70% of the .
50% of the to the PGA, and 50% to the SGA. , the value of the for a DSS can be as:
2.初始建庫(kù)建議計(jì)算公式
（1）OLTP系統(tǒng)：
SGA_TARGET = (total_mem * 0.8) * 0.8 ------物理總內(nèi)存20%保留給操作系統(tǒng)，即（1-0.2）*0.8，剩余內(nèi)存的80%分配給SGA區(qū)。
PGA_AGGREGATE_TARGET=(total_mem * 0.8) * 0.2----物理總內(nèi)存20%保留給操作系統(tǒng)，剩余內(nèi)存的20%分給PGA區(qū)。
（2）OLAP（DSS）系統(tǒng)：
SGA_TARGET= (total_mem * 0.8) * 0.5
PGA_AGGREGATE_TARGET =(total_mem * 0.8) * 0.5
3.上線后調(diào)整值參考
上面的一切初始建議值，都是在上線前的最佳配置建議值，在上線運(yùn)行一段時(shí)間后，系統(tǒng)運(yùn)行特性真面目就慢慢的體現(xiàn)出來了，這時(shí)，就應(yīng)該根據(jù)運(yùn)行實(shí)際需求及時(shí)的調(diào)整與的值了。
的實(shí)際需求建議值，可以參考**V$**
的實(shí)際需求建議值，可以參考**V$視圖**
5.簡(jiǎn)單來說
SGA的大小：一般物理內(nèi)存20%用作操作系統(tǒng)保留，其他80%用于數(shù)據(jù)庫(kù)。
SGA普通數(shù)據(jù)庫(kù)可以分配40%-60%之間，PGA可以分配20%-40%之間。
二、修改SGA和PGA步驟：
1、以的身份登錄
并查看SGA信息：
SQL>show parameter sga；
查看PGA信息：
SQL>show parameter pga；
2、修改
SQL>alter system set sga_target=20000M scope=spfile;
3、修改
SQL> alter system set sga_max_size=5000M scope=spfile;
4、重啟數(shù)據(jù)庫(kù)使其生效：
SQL>shutdown immediate;
注意，重啟前一定先完成上述兩部操作，且不得大于，一般保持兩者相等。否則可能導(dǎo)致數(shù)據(jù)庫(kù)無法啟動(dòng)，如果為PGA保存的虛擬內(nèi)存太少就會(huì)導(dǎo)致ORA－4030的錯(cuò)誤。
SQL>startup
5.查看SGA是否生效：
SQL>show parameter sga
6、如果是RAC環(huán)境，需要這樣增加sid=’*’：
alter system set sga_target=20G scope=spfile sid='*';
sql>alter system set db_cache_size= 大小M scope=spfile sid='數(shù)據(jù)庫(kù)SID';
7，修改PGA的自動(dòng)管理方式和大小
SQL> alter system set workarea_size_policy=auto scope=both;
SQL> alter system set pga_aggregate_target=3072m scope=both;
修改后重啟數(shù)據(jù)庫(kù)。
–當(dāng)前可用于自動(dòng)分配了的PGA大小，應(yīng)該比 小
附： SGA與PGA區(qū)別：
SGA：是用于存儲(chǔ)數(shù)據(jù)庫(kù)信息的內(nèi)存區(qū)，該信息為數(shù)據(jù)庫(kù)進(jìn)程所共享。它包含服務(wù)器的數(shù)據(jù)和控制信息,它是在服務(wù)器所駐留的計(jì)算機(jī)的實(shí)際內(nèi)存中得以分配，如果實(shí)際內(nèi)存不夠再往虛擬內(nèi)存中寫。
PGA：包含單個(gè)服務(wù)器進(jìn)程或單個(gè)后臺(tái)進(jìn)程的數(shù)據(jù)和控制信息，與幾個(gè)進(jìn)程共享的SGA正相反,PGA是只被一個(gè)進(jìn)程使用的區(qū)域，PGA在創(chuàng)建進(jìn)程時(shí)分配,在終止進(jìn)程時(shí)回收。
一、名詞解釋
(1)SGA： Area是的基本組成部分，在實(shí)例啟動(dòng)時(shí)分配;系統(tǒng)全局域SGA主要由三部分構(gòu)成：共享池、數(shù)據(jù)緩沖區(qū)、日志緩沖區(qū)。
(2)共享池： Pool用于緩存最近被執(zhí)行的SQL語句和最近被使用的數(shù)據(jù)定義，主要包括：（共享SQL區(qū)）和（數(shù)據(jù)字典緩沖區(qū)）。 共享SQL區(qū)是存放用戶SQL命令的區(qū)域，數(shù)據(jù)字典緩沖區(qū)存放數(shù)據(jù)庫(kù)運(yùn)行的動(dòng)態(tài)信息。
(3)緩沖區(qū)高速緩存：用于緩存從數(shù)據(jù)文件中檢索出來的數(shù)據(jù)塊日志記錄緩沖區(qū)多大好，可以大大提高查詢和更新數(shù)據(jù)的性能。
(4)大型池：Large Pool是SGA中一個(gè)可選的內(nèi)存區(qū)域，它只用于 環(huán)境。
(5)Java池：Java Pool為Java命令的語法分析提供服務(wù)。
(6)PGA： Area是為每個(gè)連接到的用戶進(jìn)程保留的內(nèi)存。
二、分析與調(diào)整
(1)系統(tǒng)全局域：
SGA與操作系統(tǒng)、內(nèi)存大小、cpu、同時(shí)登錄的用戶數(shù)有關(guān)。可占OS系統(tǒng)物理內(nèi)存的1/3到1/2。
a.共享池 Pool：
查看共享池大小Sql代碼
SQL>show
查看共享SQL區(qū)的使用率：
Sql代碼
select(sum(pins-reloads))/sum(pins)"Library cache"from v$librarycache;
–動(dòng)態(tài)性能表
命中率應(yīng)該在90％以上，否則需要增加共享池的大小。
查看數(shù)據(jù)字典緩沖區(qū)的使用率：
Sql代碼
select(sum(gets-getmisses-usage-fixed))/sum(gets)"Data dictionary cache"from v$rowcache;
–動(dòng)態(tài)性能表
這個(gè)使用率也應(yīng)該在90％以上，否則需要增加共享池的大小。
修改共享池的大小:
Sql代碼
ALTERSYSTEMSET SHARED_POOL_SIZE =64M;
b.緩沖區(qū)高速緩存：
查看共享池大小Sql代碼
SQL>show parameter db_cache_size
查看數(shù)據(jù)庫(kù)數(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');
計(jì)算出來數(shù)據(jù)緩沖區(qū)的使用命中率＝1-(/( gets+ gets)),這個(gè)命中率應(yīng)該在90％以上日志記錄緩沖區(qū)多大好，否則需要增加數(shù)據(jù)緩沖區(qū)的大小。
c.日志緩沖區(qū)
查看日志緩沖區(qū)的使用情況:
Sql代碼
SELECTname,valueFROM v$sysstat WHEREnameIN('redo entries','redo log space requests')
查詢出的結(jié)果可以計(jì)算出日志緩沖區(qū)的申請(qǐng)失敗率：
申請(qǐng)失敗率＝/，申請(qǐng)失敗率應(yīng)該接近于0，否則說明日志緩沖區(qū)開設(shè)太小，需要增加數(shù)據(jù)庫(kù)的日志緩沖區(qū)。
d.大型池：
可以減輕共享池的負(fù)擔(dān)，可以為備份、恢復(fù)等操作來使用，不使用LRU算法來管理。其大小由數(shù)據(jù)庫(kù)的’共享模式/db模式’如果是共享模式的話，要分配的大一些。
指定Large Pool的大小：
Sql代碼
ALTERSYSTEMSET LARGE_POOL_SIZE=64M
e.Java池：
在安裝和使用Java的情況下使用。
(2)PGA調(diào)整
a.初始化設(shè)置
的值應(yīng)該基于實(shí)例可利用內(nèi)存的總量來設(shè)置，這個(gè)參數(shù)可以被動(dòng)態(tài)的修改。
假設(shè)Oracle實(shí)例可分配4GB的物理內(nèi)存，剩下的內(nèi)存分配給操作系統(tǒng)和其它應(yīng)用程序。你也許會(huì)分配80％的可用內(nèi)存給Oracle實(shí)例，即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)中，由于會(huì)運(yùn)行一些很大的查詢，典型的PGA內(nèi)存最多分配70％的內(nèi)存。
DSS：PGA_AGGREGATE_TARGET=(total_mem * 80%) * 50%
在這個(gè)例子中，總內(nèi)存4GB，DSS系統(tǒng)，你可以設(shè)置PGA_AGGREGATE_TARGET為1600MB，OLTP則為655MB。
b.配置PGA自動(dòng)管理
不用重啟DB，直接在線修改。
SQL>altersystemset workarea_size_policy=autoscope=both;
System altered.
SQL>altersystemset pga_aggregate_target=512mscope=both;
System altered.
SQL>show
NAME TYPE VALUE
AUTO --這個(gè)設(shè)置成AUTO
SQL>show pga
NAME TYPE VALUE
big 500M
c.監(jiān)控自動(dòng)PGA內(nèi)存管理的性能
V$：這個(gè)視圖給出了一個(gè)實(shí)例級(jí)別的PGA內(nèi)存使用和自動(dòng)分配的統(tǒng)計(jì)。
SQL>set lines 256
SQL>set pages 42
SQL>SELECT * FROM V$PGASTAT;
UNIT
PGA bytes
-當(dāng)前PGA_AGGREGATE_TARGET的值
aggregate PGAauto target 477379584 bytes
--當(dāng)前可用于自動(dòng)分配了的PGA大小，應(yīng)該比PGA_AGGREGATE_TARGET小
globalmemorybound26843136 bytes
--自動(dòng)模式下工作區(qū)域的最大大小，Oracle根據(jù)工作負(fù)載自動(dòng)調(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
--實(shí)例啟動(dòng)后，發(fā)生的分配次數(shù)，如果這個(gè)值大于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;
SQL>select * from v$pgastat;
NAMEVALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter104857600 bytes
-----這個(gè)值等于參數(shù)PGA_AGGREGATE_TARGET的值，如果此值為0，表示禁用了PGA自動(dòng)管理。
aggregate PGAauto target 75220992 bytes
-----表示PGA還能提供多少內(nèi)存給自動(dòng)運(yùn)行模式，通常這個(gè)值接近pga_aggregate_target-total pga inuse.
globalmemorybound20971520 bytes
-----工作區(qū)執(zhí)行的最大值，如果這個(gè)值小于1M，馬上增加PGA大小
total PGA inuse30167040 bytes
-----當(dāng)前分配PGA的總大小，這個(gè)值有可能大于PGA，如果PGA設(shè)置太小.這個(gè)值接近select sum(pga_used_mem) from v$process.
total PGA allocated52124672 bytes
-----工作區(qū)花費(fèi)的總大小
maximum PGA allocated67066880 bytes
total freeable PGAmemory0 bytes ----沒有了空閑的PGA
processcount23----當(dāng)前一個(gè)有23個(gè)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自動(dòng)管理
maximum PGA usedformanual workareas 0 bytes ---為0自動(dòng)管理
over allocation count0
--如果PGA設(shè)置太小，導(dǎo)致PGA有時(shí)大于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)前一個(gè)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
下面摘抄eygle的關(guān)于一個(gè)process能夠分配的最大內(nèi)存(串行操作)的規(guī)則:
10gR1之前，對(duì)于串行操作(非并行)一個(gè)process能夠分配的最大的內(nèi)存為min(5%pga_aggregate_target,100m)
10gR2之后，對(duì)于串行操作(非并行)一個(gè)process能夠分配的最大內(nèi)存有如下規(guī)則:
如果pga_aggregate_target<=500m,那么最大的內(nèi)存為20%*pga_aggregate_target.
如果500m2.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%'
SGA與PGA的結(jié)構(gòu)如下圖：
SGA:
查看SGA：
Sqlp>show sga
或select * from v$sga;
Area bytes
bytes
8 bytes
bytes
Redo bytes
：包括了數(shù)據(jù)庫(kù)與實(shí)例的控制信息、狀態(tài)信息、字典信息等，啟動(dòng)時(shí)就被固定在SGA中，不會(huì)改變。
：包括了shard pool、large pool、java pool、 pool、游標(biāo)區(qū)和其他結(jié)構(gòu)
：數(shù)據(jù)庫(kù)中數(shù)據(jù)塊緩沖的地方，是SGA中最大的地方，決定數(shù)據(jù)庫(kù)性能
Redo ：提供REDO緩沖的地方，在OLAP中不需要太大
V$記錄了SGA的一些統(tǒng)計(jì)信息
V$ts保存SGA中可以手動(dòng)調(diào)整的區(qū)域的一些調(diào)整記錄
Shard pool:
決定其大小，10g以后自動(dòng)管理
中數(shù)據(jù)字典和控制區(qū)結(jié)構(gòu)用戶無法直接控制，與用戶有關(guān)的只有sql緩沖區(qū)（）。
將經(jīng)常訪問的過程或包用.KEEP存儲(chǔ)過程將該包pin在共享池中。
手工清除共享池的內(nèi)容： ;
共享池相關(guān)的幾個(gè)常用的視圖：
V$記錄了所有sql的統(tǒng)計(jì)信息，包括執(zhí)行次數(shù)、物理讀、邏輯讀、耗費(fèi)時(shí)間等
V$完全顯示sql語句，通過來標(biāo)示語句，piece排序
V$保存了sql的執(zhí)行計(jì)劃，通過工具查看
V$對(duì)共享池的預(yù)測(cè)，可以做調(diào)整SGA的參考
：
在OLTP系統(tǒng)中要求的命中率在95%以上
(pins)“”,sum()“hits”,
((sum()/sum(pins))*100)“”,
sum()“”,((sum(pins)/(sum(pins)
+sum()))*100)“”
from V$
計(jì)算命中率的語句
round((1 - (.value - .value - lobs.value)/.value)*100,2) “ Cache Hit Ratio”
from , ,, ,, lobs,,
where .name =‘ reads’
and .name =‘ reads ’
and lobs.name =‘ reads (lob)’
and .name =‘ reads’;
times a PIN was for of this
f times were
PIN of an that PIN since was ,and which disk
把從中獲得的數(shù)據(jù)庫(kù)叫cache hit，把從磁盤獲得的腳cache miss
數(shù)據(jù)緩沖區(qū)中的數(shù)據(jù)塊通過臟列表（）和LRU列表（）來管理。
可細(xì)分為： pool、keep pool、 pool對(duì)應(yīng)的參數(shù)為、、分別表示緩沖區(qū)大小
從9i開始支持不同塊大小的表空間，相應(yīng)的可以為不同塊大小的表空間指定不同塊大小的數(shù)據(jù)緩沖區(qū)，不同塊大小的數(shù)據(jù)緩沖區(qū)可以用相應(yīng)的來指定，其中n可以是2、4、6、16或32
V$對(duì)數(shù)據(jù)緩沖區(qū)的預(yù)測(cè)，可以做調(diào)整的參考
Vbh、xbh、xbh、xbh記錄了數(shù)據(jù)塊在中緩沖的情況，通過這個(gè)視圖可以找系統(tǒng)中的熱點(diǎn)塊。
通過下面語句找系統(tǒng)中top10熱點(diǎn)快所在的熱點(diǎn)對(duì)象:
/+ rule/ owner,
Where in
( obj from
( obj from x$bh tch desc)
PGA：
用來保存于用戶進(jìn)程相關(guān)的內(nèi)存段。
從9i開始使用PGA自動(dòng)管理，參數(shù)指定一共使用的最大PGA內(nèi)存的上限。參數(shù)用于開關(guān)PGA內(nèi)存自動(dòng)管理功能，auto/
在OLTP環(huán)境中，自動(dòng)PGA管理只要設(shè)置到一定的值，如2G左右就能滿足系統(tǒng)的要求。
自動(dòng)內(nèi)存管理：
從9i開始，參數(shù)設(shè)置SGA的內(nèi)存大小，不能動(dòng)態(tài)修改
從10g開始，指定了參數(shù)后，所有的SGA組件如： pool、、large pool都不用手工指定了，會(huì)自動(dòng)管理。這一特性就是自動(dòng)共享內(nèi)存管理ASMM。如果設(shè)置了=0,就自動(dòng)關(guān)閉自動(dòng)共享內(nèi)存管理功能。大小不能超過的大小。
手動(dòng)管理SGA：
=2000m;
=1000m;
_pool=200m;
=0---------關(guān)閉自動(dòng)共享內(nèi)存管理ASMM
11G以后sga+pga整個(gè)內(nèi)存可以自動(dòng)管理AMM，相關(guān)參數(shù) .設(shè)置好這兩個(gè)參數(shù)后就不用關(guān)心SGA和PGA了
11g手動(dòng)內(nèi)存管理：
=3000m;
=2000m;
=1000m;
=0;---------關(guān)閉自動(dòng)內(nèi)存管理AMM
SGA+PGA最好不要超過總內(nèi)存的70%