oracle 增加SGA区和PGA区

一、SGA和PGA初始值到底该设置成多大

1.下面是ORACLE官方的建议

■For OLTP systems, the PGA memory typically accounts for a small fraction of the total memory available (for example, 20%), leaving 80% for the SGA.

■For DSS systems running large, memory-intensive queries, PGA memory can typically use up to 70% of the available memory.

Oracle recommends initially dedicating 50% of the available memory to the PGA, and 50% to the SGA. Therefore, the initial value of the PGA_AGGREGATE_TARGET parameter for a DSS system can be calculated as:

2.初始建库建议计算公式

（1）OLTP系统：

SGA_TARGET = (total_mem * 0.8) * 0.8  ------物理总内存20%保留给操作系统，即（1-0.2）*0.8，剩余内存的80%分配给SGA区。
 PGA_AGGREGATE_TARGET=(total_mem * 0.8) * 0.2----物理总内存20%保留给操作系统，剩余内存的20%分给PGA区。
           

（2）OLAP（DSS）系统：

SGA_TARGET= (total_mem * 0.8) * 0.5
 PGA_AGGREGATE_TARGET =(total_mem * 0.8) * 0.5
           

3.上线后调整值参考

上面的一切初始建议值，都是在上线前的最佳配置建议值，在上线运行一段时间后，系统运行特性真面目就慢慢的体现出来了，这时，就应该根据运行实际需求及时的调整SGA_TARGET与PGA_AGGREGATE_TARGET的值了。

SGA_TARGET的实际需求建议值，可以参考**V$SGA_TARGET_ADVICE**

PGA_AGGREGATE_TARGET的实际需求建议值，可以参考**V$PGA_TARGET_ADVICE视图**

5.简单来说

SGA的大小：一般物理内存20%用作操作系统保留，其他80%用于数据库。

SGA普通数据库可以分配40%-60%之间，PGA可以分配20%-40%之间。

二、修改SGA和PGA步骤：

1、以system的身份登录

并查看SGA信息：

SQL>show parameter sga；
           

查看PGA信息：

SQL>show parameter pga；
           

2、修改sga_target

SQL>alter system set sga_target=20000M scope=spfile;
           

3、修改sga_max_size

SQL> alter system set sga_max_size=5000M scope=spfile;
           

4、重启数据库使其生效：

SQL>shutdown immediate; 
           

注意，重启前一定先完成上述两部操作，且sga_target不得大于sga_max_size，一般保持两者相等。否则可能导致数据库无法启动，如果为PGA保存的虚拟内存太少就会导致ORA－4030的错误。

SQL>startup
           

5.查看SGA是否生效：

SQL>show parameter sga
           

6、如果是RAC环境，需要这样增加sid=’*’：

alter system set sga_target=20G scope=spfile sid='*';
sql>alter system set db_cache_size= 大小M  scope=spfile sid='数据库SID';
           

7，修改PGA的自动管理方式和大小

SQL> alter system set workarea_size_policy=auto scope=both;
SQL> alter system set pga_aggregate_target=3072m scope=both;
           

修改后重启数据库。

–当前可用于自动分配了的PGA大小，应该比PGA_AGGREGATE_TARGET 小

附：oracle SGA与PGA区别：

SGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。

PGA：包含单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA正相反,PGA是只被一个进程使用的区域，PGA在创建进程时分配,在终止进程时回收。

一、名词解释

(1)SGA：SystemGlobal Area是OracleInstance的基本组成部分，在实例启动时分配;系统全局域SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。

(2)共享池：Shared Pool用于缓存最近被执行的SQL语句和最近被使用的数据定义，主要包括：Librarycache（共享SQL区）和Datadictionarycache（数据字典缓冲区）。 共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息。

(3)缓冲区高速缓存：DatabaseBufferCache用于缓存从数据文件中检索出来的数据块，可以大大提高查询和更新数据的性能。

(4)大型池：Large Pool是SGA中一个可选的内存区域，它只用于shared server环境。

(5)Java池：Java Pool为Java命令的语法分析提供服务。

(6)PGA：ProcessGlobal Area是为每个连接到Oracledatabase的用户进程保留的内存。

二、分析与调整

(1)系统全局域：

SGA与操作系统、内存大小、cpu、同时登录的用户数有关。可占OS系统物理内存的1/3到1/2。

a.共享池Shared Pool：

查看共享池大小Sql代码

SQL>show parameter shared_pool_size

查看共享SQL区的使用率：

Sql代码

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

–动态性能表

LIBRARY命中率应该在90％以上，否则需要增加共享池的大小。

查看数据字典缓冲区的使用率：

Sql代码

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

–动态性能表

这个使用率也应该在90％以上，否则需要增加共享池的大小。

修改共享池的大小:

Sql代码

ALTERSYSTEMSET SHARED_POOL_SIZE =64M;
           

b.缓冲区高速缓存DatabaseBufferCache：

查看共享池大小Sql代码

SQL>show parameter db_cache_size
           

查看数据库数据缓冲区的使用情况：

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');
           

计算出来数据缓冲区的使用命中率＝1-(physicalreads/(dbblock gets+consistent gets)),这个命中率应该在90％以上，否则需要增加数据缓冲区的大小。

c.日志缓冲区

查看日志缓冲区的使用情况:

Sql代码

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

查询出的结果可以计算出日志缓冲区的申请失败率：

申请失败率＝requests/entries，申请失败率应该接近于0，否则说明日志缓冲区开设太小，需要增加ORACLE数据库的日志缓冲区。

d.大型池：

可以减轻共享池的负担，可以为备份、恢复等操作来使用，不使用LRU算法来管理。其大小由数据库的’共享模式/db模式’如果是共享模式的话，要分配的大一些。

指定Large Pool的大小：

Sql代码

ALTERSYSTEMSET 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%=2.5G
在DSS(数据集)系统中，由于会运行一些很大的查询，典型的PGA内存最多分配70％的内存。

DSS：PGA_AGGREGATE_TARGET=(total_mem * 80%) * 50%
在这个例子中，总内存4GB，DSS系统，你可以设置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 --这个设置成AUTO

SQL>show parameter pga

NAME TYPE VALUE

pga_aggregate_target big integer 500M

c.监控自动PGA内存管理的性能

V$PGASTAT：这个视图给出了一个实例级别的PGA内存使用和自动分配的统计。

SQL>set lines 256

SQL>set pages 42

SQL>SELECT * FROM V$PGASTAT;
           

NAMEVALUE UNIT

aggregate PGA target parameter536870912 bytes

-当前PGA_AGGREGATE_TARGET的值

aggregate PGAauto target 477379584 bytes

--当前可用于自动分配了的PGA大小，应该比PGA_AGGREGATE_TARGET小

globalmemorybound26843136 bytes

--自动模式下工作区域的最大大小，Oracle根据工作负载自动调整。

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

--实例启动后，发生的分配次数，如果这个值大于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

-----这个值等于参数PGA_AGGREGATE_TARGET的值，如果此值为0，表示禁用了PGA自动管理。

aggregate PGAauto target 75220992 bytes

-----表示PGA还能提供多少内存给自动运行模式，通常这个值接近pga_aggregate_target-total pga inuse.

globalmemorybound20971520 bytes

-----工作区执行的最大值，如果这个值小于1M，马上增加PGA大小

total PGA inuse30167040 bytes

-----当前分配PGA的总大小，这个值有可能大于PGA，如果PGA设置太小.这个值接近select sum(pga_used_mem) from v$process.

total PGA allocated52124672 bytes

-----工作区花费的总大小

maximum PGA allocated67066880 bytes

total freeable PGAmemory0 bytes ----没有了空闲的PGA

processcount23----当前一个有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设置太小，导致PGA有时大于PGA_AGGREGATE_TARGET的值，此处为0，说明PGA没有扩展大于TARGET的值，如果此值出现过，那么增加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;

----当前一个process消耗最大的内存

M

----------

9.12815189

SQL>selectmin(pga_used_mem)/1024/1024Mfrom v$process where pga_used_mem>0;---process消耗最少内存

M

----------

0.19186878

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

----process曾经消耗的最大内存

M

----------

9.12815189

SQL>selectsum(pga_used_mem)/1024/1024from v$process;----当前process一共消耗的PGA

SUM(PGA_USED_MEM)/1024/1024
下面摘抄eygle的关于一个process能够分配的最大内存(串行操作)的规则:

10gR1之前，对于串行操作(非并行)一个process能够分配的最大的内存为min(5%pga_aggregate_target,100m)

10gR2之后，对于串行操作(非并行)一个process能够分配的最大内存有如下规则:

如果pga_aggregate_target<=500m,那么最大的内存为20%*pga_aggregate_target.

如果500m<pga_aggregate_target<=1000m,那么最大内存为100m.

如果1000m<pga_aggregate_target<=2.5G,那么最大内存为10%*pga_aggregate_target.

如果pga_aggregate_target>2.5G,那么最大内存为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的结构如下图：

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：包括了数据库与实例的控制信息、状态信息、字典信息等，启动时就被固定在SGA中，不会改变。

VariableSize：包括了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缓冲区（librarycache）。

将经常访问的过程或包用DBMS_SHARED_POOL.KEEP存储过程将该包pin在共享池中。

手工清除共享池的内容：altersystemflush shard_pool;

共享池相关的几个常用的视图：

V$sqlarea记录了所有sql的统计信息，包括执行次数、物理读、逻辑读、耗费时间等

V$sqltext_with_newline完全显示sql语句，通过hash_value来标示语句，piece排序

V$sql_plan保存了sql的执行计划，通过工具查看

V$shared_pool_advice对共享池的预测，可以做调整SGA的参考

Databuffer：

在OLTP系统中要求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 s y s s t a t p h y s i c a l , v sysstat physical,v sysstatphysical,vsysstat direct,v s y s s t a t l o b s , v sysstat lobs,v sysstatlobs,vsysstat 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中获得的数据库叫cache hit，把从磁盘获得的脚cache miss

数据缓冲区中的数据块通过脏列表（dirtylist）和LRU列表（LRUlist）来管理。

Databuffer可细分为：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对数据缓冲区的预测，可以做调整databuffer的参考

V b h 、 x bh、x bh、xbh记录了数据块在databuffer中缓冲的情况，通过这个视图可以找系统中的热点块。

通过下面语句找系统中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：

用来保存于用户进程相关的内存段。

从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、databuffer、large pool都不用手工指定了，Oracle会自动管理。这一特性就是自动共享内存管理ASMM。如果设置了sga_target=0,就自动关闭自动共享内存管理功能。Sga_target大小不能超过sga_max_size的大小。

手动管理SGA：

Altersystemset sga_target=2000m;

Altersystemset db_cache_size=1000m;

Altersystemsetshared_pool=200m;

Altersystemset sga_target=0---------关闭自动共享内存管理ASMM

11G以后sga+pga整个内存可以自动管理AMM，相关参数memory_max_target memory_target.设置好这两个参数后就不用关心SGA和PGA了

11g手动内存管理：

Altersystemset memory_target=3000m;

Altersystemset sga_target=2000m;

Altersystemset pga_aggregate_target=1000m;

Altersystemset memory_target=0;---------关闭自动内存管理AMM

SGA+PGA最好不要超过总内存的70%