oracle SGA PGA 内存大小调整
个人归纳总结：SGA 占用总数据库实例内存的30%左右，PGA占用到70%左右。
?
修改简单归纳为：
alter system set sga_max_size=XXXM from spfile;
alter system set sga_target=XXXM from spfile;
alter system set pga_aggregate_target=XXXM from spfile;
?
重启数据库。
在11GR2中修改了sga_max_size，从8G到2G没有生效。设置好PGA和SGA target参数后，oracle自动计算了sga_max_size为4G,不能低于4G.。
?
怎么修改也无法生效了。
?
?
转载自：http://log-cd.iteye.com/blog/562052
一、名词解释
(1)SGA：System Global Area是Oracle Instance的基本组成部分，在实例启动时分配;系统全局域SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。
(2)共享池：Shared Pool用于缓存最近被执行的SQL语句和最近被使用的数据定义，主要包括：Library cache（共享SQL区）和Data dictionary cache（数据字典缓冲区）。? 共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息。
(3)缓冲区高速缓存：Database Buffer Cache用于缓存从数据文件中检索出来的数据块，可以大大提高查询和更新数据的性能。
(4)大型池：Large Pool是SGA中一个可选的内存区域，它只用于shared server环境。
(5)Java池：Java Pool为Java命令的语法分析提供服务。
(6)PGA：Process Global Area是为每个连接到Oracle database的用户进程保留的内存。
二、分析与调整
(1)系统全局域：
? SGA与操作系统、内存大小、cpu、同时登录的用户数有关。可占OS系统物理内存的1/3到1/2。
a.共享池：
查看共享SQL区的使用率：
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%
在DSS(数据集)系统中，由于会运行一些很大的查询，典型的PGA内存最多分配70％的内存。
DSS：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
在这个例子中，总内存4GB，DSS系统，你可以设置PGA_AGGREGATE_TARGET为1600MB，OLTP则为655MB。
b.配置PGA自动管理
不用重启DB，直接在线修改。
SQL> alter system set workarea_size_policy=auto scope=both;
System altered.
SQL> alter system set pga_aggregate_target=512m scope=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 536870912
SQL>
c.监控自动PGA内存管理的性能
V$PGASTAT：这个视图给出了一个实例级别的PGA内存使用和自动分配的统计。
SQL> set lines 256
SQL> set pages 42
SQL> SELECT * FROM V$PGASTAT;
NAME????????????????????????????????????????????????????????????????? VALUE UNIT
---------------------------- ---------- ------------
aggregate PGA target parameter??????????????????????????????????? 536870912 bytes??? --当前PGA_AGGREGATE_TARGET的值
aggregate PGA auto target???????????????????????????????????????? 477379584 bytes??? --当前可用于自动分配了的PGA大小，应该比PGA_AGGREGATE_TARGET 小
global memory bound??????????????????????????????????????????????? 26843136 bytes??? --自动模式下工作区域的最大大小，Oracle根据工作负载自动调整。
total PGA inuse???????????????????????????????????????????????????? 6448128 bytes
total PGA allocated??????????????????????????????????????????????? 11598848 bytes??? --PGA的最大分配
maximum PGA allocated???????????????????????????????????????????? 166175744 bytes
total freeable PGA memory??????????????????????????????????????????? 393216 bytes??? --PGA的最大空闲大小
PGA memory freed back to OS??????????????????????????????????????? 69074944 bytes
total PGA used for auto workareas???????????????????????????????????????? 0 bytes??? --PGA分配给auto workareas的大小
maximum PGA used for auto workareas???????????????????????????????? 1049600 bytes
total PGA used for manual workareas?????????????????????????????????????? 0 bytes
maximum PGA used for manual workareas??????????????????????????????? 530432 bytes
over allocation count????????????????????????????????????????????????? 1118??? --实例启动后，发生的分配次数，如果这个值大于0，就要考虑增加pga的值
bytes processed?????????????????????????????????????????????????? 114895872 bytes
extra bytes read/written??????????????????????????????????????????? 4608000 bytes
cache hit percentage????????????????????????????????????????????????? 96.14 percent??? --命中率
16 rows selected.
--V$PGA_TARGET_ADVICE
SQL> SELECT round(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 like the following:
TARGET_MB?? CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
63?????? 23????????? 367
125??????? 24????????? 30
250??????? 30????????? 3
375??????? 39????????? 0
500??????? 58????????? 0
600??????? 59????????? 0
700??????? 59????????? 0
800??????? 60????????? 0
900??????? 60????????? 0
1000??? 61????????? 0
1500??? 67????????? 0
2000??? 76????????? 0
3000??? 83????????? 0
4000??? 85????????? 0
可以看出当TARGET_MB 为375M是ESTD_OVERALLOC_COUNT=0，所以可以将PGA_AGGREGATE_TARGET设置成375M。
附：oracle SGA与PGA区别：
SGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。
PGA：包含单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA 正相反,PGA 是只被一个进程使用的区域，PGA 在创建进程时分配,在终止进程时回收。
