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

ORACLE给的建议是: OLTP系统  PGA=(Total Memory)*80%*20%。DSS系统PGA=(Total Memory)*80%*50%。

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

SQL> select * from v$pgastat;

NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                    104857600 bytes 

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

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

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

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

-----工作区花费的总大小
maximum PGA allocated                                              67066880 bytes
total freeable PGA memory                                                 0 bytes         ----没有了空闲的PGA
process count                                                            23                    ----当前一个有23个process
max processes count                                                      25
PGA memory freed back to OS                                               0 bytes
total PGA used for auto workareas                                   8891392 bytes
maximum PGA used for auto workareas                                22263808 bytes
total PGA used for manual workareas                                       0 bytes                  ---为0自动管理
maximum PGA used for manual workareas                                     0 bytes            ---为0自动管理
over allocation count                                                     0        

如果PGA设置太小，导致PGA有时大于PGA_AGGREGATE_TARGET的值，此处为0，说明PGA没有扩展大于TARGET的值，如果此值出现过，那么增加PGA大小。
bytes processed                                                   124434432 bytes
extra bytes read/written                                                  0 bytes
cache hit percentage                                                    100 percent  ---命中率为100%，如果太小增加PGA

recompute count (total)                                                6651

19 rows selected

SQL> select max(pga_used_mem)/1024/1024 M from v$process;   ----当前一个process消耗最大的内存

         M
----------
9.12815189

SQL> select min(pga_used_mem)/1024/1024 M from v$process where pga_used_mem>0; ---process消耗最少内存

         M
----------
0.19186878

SQL> select max(pga_used_mem)/1024/1024 M from v$process ;    ----process曾经消耗的最大内存

         M
----------
9.12815189

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

SUM(PGA_USED_MEM)/1024/1024
---------------------------
           28.8192501068115

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

select (select sum(pga_used_mem)/1024/1024 from v$process) /(select count(*) from v$process) from dual;得到一个process大约占用了多少的内存，然后估算系统一共会有多少连接，比如一共有500个连接，

那么Sessions=1.1*process +5=500，那么processes=450,再乘以一个process需要消耗的内存，就能大约估算出PGA需要设置多大。

最好将PGA设置的值比计算出的值大一点，PGA值设定好后，就可以根据系统的性质,如果系统为OLTOP，那么总的内存可以设置为PGA/0.16,最后也能估算出SGA的大小，建议还是多配点内存，反正便宜。

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

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

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

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

如果500m

如果1000m

如果pga_aggregate_target>2.5G,那么最大内存为2.5G.

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  2  FROM SYS.x$ksppi x, SYS.x$ksppcv y
  3  WHERE x.inst_id = USERENV ('Instance')
  4  AND y.inst_id = USERENV ('Instance')
  5  AND x.indx = y.indx
  6  AND x.ksppinm LIKE '%&par%'
  7  /

NAME                                                                             VALUE                                                                            DESCRIB
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
_smm_max_size                                                                    20480                                                                            maximum work area size in auto mode (serial)

SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 100M
此处我的一个process能够分配的最大内存为20M，因为我的PGA=100M，符合上面的规则。

隐含参数_smm_max_size表示一个process能够分配最大的memory.






买了piner的《oracle高可用环境》一书，正好趁这段时间学习一下。

把看到的东西总结一下发表于此，今天先发第一章关于SGA与PGA的内容。

 

以后会陆续将总结在此发表，与大家共享。

 

SGA与PGA的结构如下图：

 

SGA:

查看SGA：

Sqlp> show sga

或 select * from v$sga;

Total System Global Area  289406976 bytes

Fixed Size                  1248600 bytes

Variable Size             176161448 bytes

Database Buffers          109051904 bytes

Redo Buffers                2945024 bytes

 

Fixed Size：包括了数据库与实例的控制信息、状态信息、字典信息等，启动时就被固定在SGA中，不会改变。

Variable Size：包括了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缓冲区（library cache）。

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

手工清除共享池的内容：alter system flush shard_pool;

 

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

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

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

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

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

 

Data buffer：

 

在OLTP系统中要求data buffer 的命中率在95%以上

select sum(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

 

PINS	NUMBER	Number of times a PIN was requested for objects of this namespace
PINHITS	NUMBER	Number of times all of the metadata pieces of the library object were found in memory
RELOADS	NUMBER	Any PIN of an object that is not the first PIN performed since the object handle was created, and which requires loading the object from disk

 

Oracle把从data buffer中获得的数据库叫cache hit，把从磁盘获得的脚cache miss

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

 

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

 

V$bh、x$bh记录了数据块在data buffer中缓冲的情况，通过这个视图可以找系统中的热点块。通过下面语句找系统中top 10 热点快所在的热点对象:

 

Select /*+ rule*/ owner,object_name from dba_objects

Where data_object_id in

   (select obj from

   (select obj from x$bh order by tch desc)

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

手动管理SGA：

Alter system set sga_target=2000m;

Alter system set db_cache_size=1000m;

Alter system set shared_pool=200m;

Alter system set sga_target=0---------关闭自动共享内存管理ASMM

 

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

11g手动内存管理：

Alter system set memory_target=3000m;

Alter system set sga_target=2000m;

Alter system set pga_aggregate_target=1000m;

Alter system set memory_target=0;---------关闭自动内存管理AMM

 

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