对oracle实例的内存（SGA和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;

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

可以看出当TARGET_MB为375M是ESTD_OVERALLOC_COUNT=0，所以可以将PGA_AGGREGATE_TARGET设置成375M。

 

附：oracle SGA与PGA区别：

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

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

另一篇文章中也这样写了相关的调整记录

如何估算PGA，SGA的大小，配置数据库服务器的内存

ORACLE给的建议是: OLTP系统PGA=(TotalMemory)*80%*20%。

DSS系统PGA=(TotalMemory)*80%*50%。

ORACLE建议一个数据库服务器，分80%的内存给数据库，20%的内存给操作系统，那怎么给一个数据库服务器配内存呢？

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

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

28.8192501068115

如何设置PGA呢？我们可以在压力测试阶段，模拟一下系统的运行，然后运行

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

得到一个process