Oracle数据库内存体系 - PGA

PGA:Program Global Area（程序全局区）或Process Global Area（进程全局区），PGA是一块包含一个操作系统进程或线程专用的内存，不允许系统中的其他进程或线程访问。

PGA存储了进程所需要访问的数据(Data)和控制信息(Control Information)的内存区域。

PGA的内存管理模式

手动PGA内存管理：这种情况下，你需要告诉Oracle，如果一个特定进程中需要排序或散列，允许使用多少内存来完成这些排序或散列。

自动PGA内存管理：你需要告诉Oracle，在系统范围内可以使用多少内存。

（Oracle 9iR1后开始支持PGA的自动管理。）

简单的说，对于成天在数据库运行的应用，建议使用PGA自动内存管理。手动内存管理，适用于大型批处理作业（特殊时段，它们是数据库中唯一的活动）。

自动PGA内存管理

PGA自动管理下，PGA区域内存可以动态扩大和回收。PGA内存管理模式由WORKAREA_SIZE_POLICY控制。
1) 设为MANUAL，启用手动内存管理。
2) 设为AUTO，并且PGA_AGGREGATE_TARGET不为0时，启用自动内存管理。

SQL> show parameter workarea_size_policy
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO

SQL> show parameter PGA_AGGREGATE_TARGET
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 1G

PGA_AGGREGATE_TARGET是设置PGA自动管理的上限内存，SGA_TARGET用于设置SGA自动管理的上限内存。Oracle 11g则对这两部分进行综合，引入memory_target，可以通过这一个参数即可自动调整所有的内存（SGA+PGA），这就是新引入的自动内存管理特性。推荐阅读：初始化参数之memory_target

手动PGA内存管理

SORT_AREA_SIZE：对信息排序所用的内存总量。

SORT_AREA_RETAINED_SIZE：排序后在内存中保存排序信息的内存总量。
HASH_AREA_SIZE：存储散列列表所用的内存量。

两个例子看下，这几个参数对于排序以及Hash影响（案例来自Oracle内存结构研究-PGA篇 ）

1，排序区：
pga_aggregate_target为100MB，单个查询能用到5%也就是5MB时排序所需时间
SQL> create table sorttable as select * from all_objects;
表已创建。
SQL> insert into sorttable (select * from sorttable);
已创建49735行。
SQL> insert into sorttable (select * from sorttable);
已创建99470行。
SQL> set timing on;
SQL> set autotrace traceonly;
SQL> select * from sorttable order by object_id;
已选择198940行。
已用时间: 00: 00: 50.49
Session级修改排序区为30mb所需时间
SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
会话已更改。
已用时间: 00: 00: 00.02
SQL> ALTER SESSION SET SORT_AREA_SIZE = 30000000;
会话已更改。
已用时间: 00: 00: 00.01
SQL> select * from sorttable order by object_id;
已选择198940行。
已用时间: 00: 00: 10.76
可以看到所需时间从50.49秒减少到10.31秒，速度提升很明显。
2，散列区：
pga_aggregate_target为100MB，单个查询能用到5%也就是5MB时表连接所需时间
SQL> select /*+ use_hash(tb1 tb2)*/ * from sorttable tb1,sorttable tb2 where tb1.object_id=tb2.object_id;
已选择49735行。
已用时间: 00: 00: 40.50
Session级修改散列区为30mb所需时间
SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
会话已更改。
已用时间: 00: 00: 00.01
SQL> ALTER SESSION SET HASH_AREA_SIZE = 30000000;
会话已更改。
已用时间: 00: 00: 00.01
SQL> select /*+ use_hash(tb1 tb2)*/ * from sorttable tb1,sorttable tb2 where tb1.object_id=tb2.object_id;
已选择49735行。
已用时间: 00: 00: 04.47
所需时间由40.50秒提升到4.47秒，效果同样很明显。
备注：以上实验皆执行全表扫描保证相关表读入缓冲区中，避免因数据没读入缓存造成误差。

操作命令
系统级更改：
ALTER SYSTEM SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL}；
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100000000;
ALTER SYSTEM SET SORT_AREA_SIZE = 65536 SCOPE = SPFILE；
ALTER SYSTEM SET HASH_AREA_SIZE = 65536 SCOPE = SPFILE；
会话级更改
ALTER SESSION SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL}；
ALTER SESSION SET SORT_AREA_SIZE = 65536；
ALTER SESSION SET HASH_AREA_SIZE = 65536；

参考：

Oracle内存结构研究-PGA篇