Oracle 内存设置 SGA PGA

oracle的内存一直是DBA比较关注的部分。今天总结一下设置及调优原则。

Oracle使用两种类型的内存结构，一种为共享的，而另一种为进程专有的。SGA（系统全局区）是所有服务器进程（包括后台进程）可共享的内存部分；进程专有的内存部分称为PGA（程序全局区）。

当启动Oracle数据库时，系统会先在内存内规划一个固定区域，用来储存用户需要的数据，以及Oracle运行时必备的系统信息。我们称此区域为系统全局区(System Global Area)，简称SGA。SGA是Oracle实例中最重要的内存部件。SGA的目的是提高查询性能，允许大量的并发数据库活动。

调整SGA并不总是很容易。在Oracle 11g中，用户可以使用自动内存管理（Automatic Memory Management）来将共享内存管理问题完全自动化。使用AMM，Oracle将根据变化的数据库负荷为SGA和PGA自动分配内存或回收内存，重新分配，Oracle使用内部视图和统计数据来决定为SGA组件中分配内存的最好办法。

为了切换到AMM，必须设置MEMORY_TARGET参数。设置MEMORY_TARGET参数后，数据库将根据数据库负荷的要求，把内存分配给SGA和PGA的组件。MEMORY_TARGET参数是动态的，因此在数据库运行时改变内存数量很容易。及时在启动自动内存分配后，也可以通过设置MEMORY_TARGET参数设置SGA_TARGET和或PAG_AGGREGATE_TARGET参数。

事实上，除了MEMORY_TARGET参数，还有MEMORY_MAX_TARGET参数，它是静态参数，代表内存（SGA+PGA）的最大值。如果指定了MEMORY_TARGET而没有指定MEMORY_MAX_TARGET，重启后MEMORY_MAX_TARGET=MEMORY_TARGET；如果指定了MEMORY_MAX_TARGET而没有指定MEMORY_TARGET，重启后MEMORY_TARGET为0，这种情况下表示未启用自动内存管理。

如果手动设置SGA和PGA的大小，对于OLTP系统，因为要处理的事务都很短，PGA的使用率低，Oracle推荐分配给oracle实例的内存为物理内存的80%。例如10GB的物理内存，按照oracle的推荐，分配给oracle实例的内存大约为8GB（10×80%），其中分配给SGA的值即SGA_MAX_SIZE的值大约为6.4GB（10×80%×80%），PGA的值即PGA_AGGREGATE_TARGET大约为1.6GB（10×80%×20%）。这都是针对新的数据库的初始值，具体还需要在使用过程中进行调整。对于复杂且运行时间长的查询来说，如在更典型的OLAP环境下，就需要大量的PGA内存。

此例中，如果设置MEMORY_TARGET参数，可以将它设置为10GB*80%即8GB。

 

当指定了MEMORY_TARGET参数（即启用AMM）时：

如果不设置SGA_TARGET和PAG_AGGREGATE_TARGET参数，数据库将自动调优它们而不需要设置SGA或PGA的最小值；

如果设置了SGA_TARGET和PAG_AGGREGATE_TARGET参数，则将其值作为SGA和PGA的最小值进行调优；

 

当不指定MEMORY_TARGET参数时，它默认为0（即未启用AMM）：

如果设置了SGA_TARGET参数，则数据库自动调优SGA的子组件；

无论是否设置PAG_AGGREGATE_TARGET参数，数据库都自动调优PGA。

 

总结：Oracle建议使用自动内存管理，即设置MEMORY_TARGET参数。同时也可以指定SGA_TARGET和PAG_AGGREGATE_TARGET参数以设置SGA和PGA的最小值。

 

另外，几个比较有用的查询：

如果启用了AMM，可以根据视图v$memory_target_advice查看系统建议的memory_target大小设置；

如果没有启用AMM，但是指定了SGA_TARGET，可以根据视图v$sga_target_advice查看系统建议的SGA大小设置；

查询数据库为PGA分配的最大值：select value from v$PGASTAT where name=’maximum PGA allocated’;此结果表示数据库自实例启动以来分配给PGA的最大值。可以用来估算MEMORY_MAX_TARGET参数（SGA+实例使用的最大PGA）。