Oracle PGA自动管理在OLAP系统中的应用

碧秋8249

说明：
OLTP：联机事务处理系统，也就是我们常用的业务系统；
OLAP：联机分析处理系统，主要功能是数据查询、分析、统计、挖掘等等

Oracle从9i开始增加了PGA自动管理的功能，可以说是打开了ORACLE内存自动管理的新篇章。PGA自动管理把以前的sort_area_size,hash_area_size比较难配置的问题全解决了。一般建议OLTP系统PGA的大小为系统内存的20%左右,如一个系统2G的内存，那么通常数据库的PGA设置在400M左右。
ORACLE缺省每个session可使用的内存并不是全部PGA大小，实际上比这个小得多，普通会话可使用只有PGA的5%，并且不能超过100M，也就是说如果PGA设成400M，那一个会话可使用的排序加HASH的内存最大为400*0.05=20M。
有些OLAP或者是OLTP、OLAP混合数据库这个配置不一定能满足实际需求，因为OLAP系统一般session比较少，但一个查询要求的HASH及SORT的空间非常大，如果采用标准的5%的话，那么会有许多查询SORT及HASH内存不够，导至过多的磁盘排序或多回HASH连接的问题，这将严重影响系统性能。对于这个问题可以用四种方法处理。
1、增加PGA大小
ORACLE数据库的内存可以说主要由SGA+PGA两块组成。对于OLAP系统数据块缓冲命中率一般不会大于80%，即使增加BUFFER_SIZE作用也不明显，因为物量数据量明显大于内存。OLAP系统的共享池也不需要太大，虽然OLAP的一条SQL会比较复杂，但OLAP的SQL数量一般比OLTP系统少得多，并且SQL解析的时间不是性能的主要瓶颈。综上所述可以将减小的SGA大小用于PGA中
PGA的增大在OLAP系统中性能提升会比较明显一些，特别是发现磁盘排序及HASH多次读写的情况下。
2.对特殊会话采用手动PGA管理
这个方法就是将某个会话的PGA改为手动管理，再设置会话的sort_area_size和hash_area_size，然后再执行SQL，如下所示：
alter session set workarea_size_policy=MANUAL;
--50M
alter session set sort_area_size=52428800;
--200M
alter session set hash_area_size=209715200;
--执行处理
处理完后，根据需要将会话改为自动PGA管理
alter session set workarea_size_policy=AUTO;

这种方法一般用于OLAP系统的ETL中，ETL一般SORT及HASH比较多，并且ETL的时候一般只有一两个会话，所以手动管理不会对其它会话产生影响。

3.对查询或表设置并行处理
上面提到普通会话可使用只有PGA的5%，但是并行事务会话可使用的内存是PGA的30%，所以如果在表上设置并行度或在查询中设置并行提示可以一次使用比较多的PGA内存,如PGA=800M，那么就可以使用400*0.3=120M。
4.调整ORACLE内部隐藏参数
如果系统的并行性能不好或者调节了并行度会有其它影响的情况下，我们可以调整ORACLE内部隐藏参数来达到效果。前面提到的5%和30%两个数字其它是ORACLE的一个缺省值，我们可以通过调整ORACLE内部隐藏参数来改变这个缺省值。
这个两个隐藏参数分别为:
"_smm_max_size":PGA的默认每个用户使用的内存大小，单位为K
"_smm_px_max_size":PGA的默认并行用户使用的内存大小，单位为K

如下所示，设置"_smm_max_size"=100M,"_smm_px_max_size"=200M

alter system set "_smm_max_size"=102400 scope=both;
alter system set "_smm_px_max_size"=204800 scope=both;

注：
1.Oracle隐藏参数一般以下划线开头，所以修改时需要加上引号
2.如果调整过参数pga_aggregate_target后以上两个隐藏参数会重置为默认值
_smm_max_size=pga_aggregate_target*0.05
_smm__px_max_size=pga_aggregate_target*0.3