自动pga管理

自动pga管理—原理及优化

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

在oracle9i以前的版本中,我们可以通过手工修改sort_area_size,hash_area_size等值控制pga的使用率,使用这种分配方法会存在一个弊端,因为数据库中存在成千上万条不同的sql语句,它们之中有的需要很大的内存需要,有的需要的很少,如果指定一个大的sort_area_size或hash_area_size,虽然保证了某些语句的执行速度,但是pga总体内存将会过度分配,严重时会导致操作系统page in/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和hash join,这些语句将会需要很多内存空间去执行.当这些内存分配后,oracle进程将会在其中进行排序或构造hash表,通常的话分配的内存越多sql语句将会执行得越快.我们把这段执行的区域叫做work area,把能进行完全内存操作的work area大小叫做optimal size.与之对应的还有onepass size, multipass size optimal: 所有操作都在内存中进行进行

onepass: 使用最小写磁盘操作,大部分在内存中进行

multipass: 当workarea太小的话将会发生大量磁盘操作,性能急剧下降

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

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

设置.

从上面这张图我们也可以发现pga内存大小对响应时间还是影响很大的,所以我们应该尽量让它们都在内存中进行,一个典型的OLTP环境的话需要 workarea execution - optimal >= 90% workarea execution - multiplass = 0%

现在我们知道了pga内存分配的重要性,也知道pga自动管理的优点,那让我们深入到pga自动管理里面看看内部是怎么实现的.

pga自动管理是采用feedback loop算法来管理内存分配的,来看一张图

我们来把这个图走一遍,当一条语句开始执行,它会通过local memory manager注册一个active workarea profile, profile里面包括了这个workarea的一系列属性, 它的类型 (sort, hash-join,……),它当前执行需要的minimum, one-pass and optimal内存大小,它的并行度,它当前在使用的内存等.由于profile经常被更新,所以所有active profiles基本可以反映出当前内存需要和当前在使用的内存.有了这些profile信息,后台进程global memory manager就可以计算出每个active workarea的memory bound,global memory manager没隔3秒将会更新一下memory bound,local memory manager得到memory bound后将会计算出每个active statement的所要分配的内存大小,在这里被称为expect size.

然后每个active statement将会在自己所分配到的expect size内存里面进行操作.计算expect size的时候还需要符合5个规则

规则1:

expected size 不能小于minimum memory 需求. 规则2:

expected size 不能大于optimal memory 需求.

规则3:

如果bound介于minimum和optimal之间,那么将会使用bound值作为expect size.sort操作除外,因为sort操作并不会从多余内存中得到利益,上面也已经提到,所以当sort操作时将会取onepass做为expect size. 规则4:

如果这个workarea时并行执行的,那么它的expect size和它的并行度成正比. 规则5:

最后,expect size不能超过5% pga_aggregate_target大小或100M,并行操作下不能超过30% pga_aggregate_target大小.

这里面sql memory target,global memory bound,dirft management都运用了一系列机制来保证pga自动内存管理的稳定性,在这里不再详述.

现在大家都了解了pga自动内存管理的原理了,让我们做一些实验来验证一下以上的理论.

SQL> alter system set pga_aggregate_target=10M;

System altered.

SQL> alter system set workarea_size_policy=auto;

System altered.

执行一个排序

select distinct * from a where rownum<500000;

Elapsed: 00:01:00.5

*View SQL workarea SQL> select

sql_text,operation_type,policy,last_memory_used/1024/1024,last_execution, last_tempseg_size from v$sql l,v$sql_workarea a

where l.hash_value=a.hash_value

and sql_text='select distinct * from a where rownum<500000';

SQL_TEXT OPERATION_TYPE ------------------------------------------------ ---------------- select distinct * from a where rownum<500000 GROUP BY (SORT)

POLICY LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE ------ ---------------------------- -------------- ----------------- AUTO .5 30 PASSES 12288000

可以看到这里使用到了0.5M的内存,再把pga_aggregate_target改大为40M

SQL> alter system set pga_aggregate_target=40M;

System altered.

SQL> select distinct * from a where rownum<500000;

Elapsed: 00:00:38.36

SQL> select

sql_text,operation_type,policy,last_memory_used/1024/1024,last_execution, last_tempseg_size from v$sql l,v$sql_workarea a where l.hash_value=a.hash_value

and sql_text='select distinct * from a where rownum<500000';

SQL_TEXT OPERATION_TYPE ------------------------------------------------ ---------------- select distinct * from a where rownum<500000 GROUP BY (SORT)

POLICY LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE ------ ---------------------------- -------------- ----------------- AUTO 2 1 PASSES 12288000

可以看到内存使用达到2M,也正好是5% pga_aggregate_target,这里证明了expect size 不能超过5% pga_aggregate_target的理论.

再把pga_aggregate_target改成10G

SQL> alter system set pga_aggregate_target=10G;

System altered.

SQL> select distinct * from a where rownum<500000;