自动pga管理

自动pga管理

在一个复杂的数据库系统中会同时运行着许多sql语句,这些语句当中会有进行sort,hash_join等等很多操作,它们都必须分配一些内存进行这些操作,
在oracle的dedicatedserver环境中,这些内存将会被分配到pga中,如果pga的内存分配管理不当将会导致系统内存不足,
操作系统将会频繁pgae in/out,会降低数据库的整体性能,所以正确管理pga内存分配是一件相当重要的事情.

在oracle9i以前的版本中,我们可以通过手工修改sort_area_size,hash_area_size等值控制pga的使用率,使用这种分配方法会存在一个弊端,因为数据库中存在成千上万条不同的sql语句,它们之中有的需要很大的内存需要,有的需要的很少,如果指定一个大的sort_area_size或hash_area_size,虽然保证了某些语句的执行速度,但是pga总体内存将会过度分配,严重时会导致操作系统pagein/out,降低整个数据的性能.
如果sort_area_size或hash_area_size设置的太小了,那么某些语句将会执行相当长的时间,会影响应用程序的响应速度.所以如何手工设置合适的pga参数在oracle9i推出之前一直是很多dba心头的痛.不过这种情况在oracle9i推出后已经得到了比较好的解决,9i的pga自动管理功能很好得解决了这个问题.下面让我们开始学习pga自动管理.

什么是pga内存自动管理

Oracle8i或更早版本中用SORT_AREA_SIZE 和 HASH_AREA_SIZE参数来指定每个session可用到的最大排序和hash内存大小.
在Oracle9i里面,用PGA_AGGREGATE_TARGET参数来指定所有session一共使用最大pga内存的上限.这个参数可以被动态的更改,赋值范围从10M~ （4096G -1）bytes。

9i里还提供了WORKAREA_SIZE_POLICY参数用于开关pga内存自动管理功能.
AUTO: 自动管理
MANUAL:手工管理

当WORKAREA_SIZE_POLICY设置为manual时,pga的内存分配还是使用sort_area_size等参数来分配,设置为auto时,sort_area_size等参数将被忽略.

注意,9i里WORKAREA_SIZE_POLICY默认被设置为auto.

在OLAP和DSS系统中通常会存在很多相当复杂的语句,它们会进行多表关联,处理很大数据量,会存在很多sort和hashjoin,
这些语句将会需要很多内存空间去执行.当这些内存分配后,oracle进程将会在其中进行排序或构造hash表,通常的话分配的内存越多sql语句将会执行得越快.
我们把这段执行的区域叫做work area,把能进行完全内存操作的work area大小叫做optimalsize.与之对应的还有onepass size, multipass size

optimal: 所有操作都在内存中进行进行
onepass: 使用最小写磁盘操作,大部分在内存中进行
multipass: 当workarea太小的话将会发生大量磁盘操作,性能急剧下降

这里针对sort和hash join还是有点区别,当进行sort操作的话,当分配的内存介于optimal 与onepass之间的话语句的响应时间是不会随着内存的增加而减少,但是如果是hashjoin,如果增加内存,它的响应时间将会随之降低,这是和sort,hashjoin内步执行方式有关,有兴趣的话可以参考sort,hash相关文档.

注意：当使用shared server时,workarea将在sga的large pool中分配,
同时pga自动内存管理将被忽略,所有workarea内存管理还是来自于sort_area_size,hash_area_size等参数的设置.

注意二：在Oracle9i中，PGA_AGGREGATE_TARGET参数仅对专用服务器模式下的专属连接有效。但是对共享服务器连接无效；从Oracle10g开始，PGA_AGGREGATE_TARGET对专用服务器连接和共享服务器连接同时生效。

注意三：对于单纯的数据库服务器，通常需要保留20%的内存给操作系统使用，剩余80%给Oracle使用。Oracle使用的内存又分为两部分PGA和SGA，那么PGA可以占用Oracle消耗总内存的20%（OLTP系统）~50%（DSS系统）。