OraclePGA原理及管理

一、PGA概念：

PGA指程序全局区，是服务器进程（ServerProcess）使用的一块包含数据和控制信息的内存区域，PGA是非共享的内存，在服务器进程启动或创建时分配，并为服务器进程所专用。

PGA的分配以及所存储的信息随Oracle的工作状态（专有模式和共享模式）不同而不同，但不管如何PGA通常可分为固定PGA和可变PGA区域，如果服务器工作在专有服务器模式下，那么可变PGA就将分配在PGA自有区域内，如果工作在共享服务器模式下，那么可变PGA将分配在SGA（程序全局区）内，而在PGA自有区域内保留指向可变区域的地址指针。

再细分的话，那么可变PGA区域有可以分为会话内存和私有SQL区，在会话内存中存放会话登录信息以及其他一些信息，对共享服务器模式这部分信息是共享的，而专有服务器模式这部分是私有的。私有SQL区存放绑定变量信息和查询状态以及查询工作区等信息，同样对于共享服务器模式这部分区域分配于SGA中。

私有SQL区的分配Oracle是通过游标（Cursor）来完成，其实PGA的主要内存消耗就来自Cursor，但是游标的打开和释放，Oracle是不负责的，这部分工作完全交由用户程序来处理。但是Oracle通过OPEN_CURSORS参数来控制打开游标的最大值。私有SQL区有可以再细分成永久区域和运行时区域，由于这部分区域完全由用户程序来使用和分配，因此这部分区域也称为UGA（用户全局区）。

在永久区域中包含绑定变量等信息，这部分内存只有在游标被关闭时才会释放。在运行时区域中，存放SQL运行时所需要的信息，它在执行请求时首先创建， 其中包含了查询执行状态信息，SQL WorkAreas（分组，排序，连接操作所使用的内存区域），对于Insert/Update/Delete等操作这部分区域在执行完毕后就会释放，对于查询操作则是在结果集返回后或者查询被取消后返回。

在UGA区域中还存在一个子区域称为CGA（全局调用区），这部分区域用于运行时执行一些低层操作时分配使用，比如SQL分析，Direct I/O Buffer分配等。

二、PGA管理：

在9i之前Oracle通过*_area_size等参数对PGA进行管理，这些参数主要有sort_zrea_size、hash_area_size、bitmap_merge_size、create_bitmap_area_size等。在Oracle中可以通过show parameter area_size命令查看这些设置。

从9i开始Oracle提供了一种新的PGA内存管理方式，即自动内存管理，从而大大简化了Oracle内存管理，通过这个功能Oracle可以在总的内存使用限制下，实现PGA的自动管理与分配。

在9i之后Oracle通过PGA_AGGREGATE_TARGET参数来控制PGA内存自动管理的最大内存使用上限，通过WORKAREA_SIZE_POLICY参数来管理自动内存管理的开启，当该参数设置为AUTO时将开启自动内存管理，设置为MANUAL时将使用手动管理。在9i中PGA_AGGREGATE_TARGET参数只对专有服务器模式有效，对共享服务器模式将会自动失效，但是从10G开始PGA_AGGREGATE_TARGET参数对这两种模式都会生效。

1、 自动PGA管理实现原理：

  Oracle中对自动PGA管理采用反馈环（FeedBack Loop）算法来实现。当进程开始SQL执行时，首先通过Local Memory Manager注册一个ActiveWorkArea Profile，工作区Profile是进城与内存管理器之间通信的唯一接口，活动Profile通过Local Memory Manager来维护，存储在SGA中，有了这些Profile，后台的Global Memory Manager就可以计算出一个在一定上限内并能提供较好性能的Global Memory Bound，这个值用于限制单个进程使用的PGA内存上限，Global Memory Manager每个3秒钟更新一次Global Memory Bound，Local Memory Manager得到Global Memory Bound后会计算出每个Active Statement所要分配的PGA内存，称为Expect Size，然后每个Active Statement将会得到子所分得的Expect Size，并在该内存中进行运算。

2、 参数设置与内存分配：

在启用自动管理的Oracle服务器中，实际内存的分配与PGA_AGGREGATE_TARGET参数以及一些隐含参数的设置有关。主要有两种分配方式（以Oracle10gR2和11G为例）：

（1）、串行操作按照min(5%*PGA_AGGREGATE_TARGET,100M)方式分配；

（2）、并行操作按照50%*PGA_AGGREGATE_TARGET/DOP方式分配，其中DOP为并行度。

对于串行操作5%*PGA_AGGREGATE_TARGET这部分结果值受到_smm_max_szie隐含参数制约，该参数制定了自动管理下最大工作区限制，即5%*PGA_AGGREGATE_TARGET的内存大小不能超过_smm_max_szie值。具体限制如下：

          PGA_AGGREGATE_TARGET<=500M, _smm_max_szie=20%* PGA_AGGREGATE_TARGET

            PGA_AGGREGATE_TARGET[500M,1000M], _smm_max_szie=100M

            PGA_AGGREGATE_TARGET[1001M,2256M] ,_smm_max_szie=10%* PGA_AGGREGATE_TARGET

            PGA_AGGREGATE_TARGET>2560M, _smm_max_szie=250M

        对于并行那个操作，当DOP<=5时_smm_max_szie会生效，当DOP超过5时将取决于另一个参数_smm_px_max_size。

另外对于串行操作分配规则min(5%*PGA_AGGREGATE_TARGET,100M)中的100M，这个值取决于另一个隐含参数_pga_max_size，该参数制定了串行操作PGA的上限值（最大不能超过该值的1/2，其实这个值就是Global Memory Bound），Oracle9iR2中该值缺省为200M，因此在Oracle9i中缺省的串行操作最大将被分配100M。但是在Oracle10GR2中这个参数和PGA_AGGREGATE_TARGET相关，当改变PGA_AGGREGATE_TARGET参数后_pga_max_size将自动改变，但是当PGA_AGGREGATE_TARGET>5G后_pga_max_size参数将不再变化，在1-5G范围内_pga_max_size值将会按照20%*PGA_AGGREGATE_TARGET设置增长。

3、 设置PGA参数：

可以通过alter system set PGA_AGGREGATE_TARGET命令来设置PGA，对于PGA_AGGREGATE_TARGET的设置Oracle提供如下建议对于OLTP系统PGA_AGGREGATE_TARGET<=（总内存*80%）*20%；对于DSS系统PGA_AGGREGATE_TARGET<=（总内存*80%）*50%.

在Oracle中可以通过查询v$pga_target_advice视图来对不同PGA设置进行评估，给出PGA命中率和过载等评估信息，以给出PGA设置建议如：

select a.PGA_TARGET_FOR_ESTIMATE / 1024 / 1024 PGAMB,
       a.PGA_TARGET_FACTOR,
       a.ESTD_PGA_CACHE_HIT_PERCENTAGE,
       a.ESTD_OVERALLOC_COUNT
  from v$pga_target_advice a;

       还可以通过查询v$pga_target_advice_histogram视图来对不同工作区大小采样评估如：

       select a.PGA_TARGET_FACTOR,a.low_optimal_size/1024 low,
         round(a.high_optimal_size/1024) high,
         a.estd_optimal_executions estd_mp,est_total_executions estd_exec
        from v$pga_target_advice_histogram a
        where a.PGA_TARGET_FACTOR=0.25 and a.estd_total_executions>0;

low_optimal_size: 为评估区间的optimal下限（bytes）

high_optimal_size: 为评估区间的optimal上限（bytes）

estd_optimal_executions：为评估区间optimal执行次数

est_total_executions：为评估区间估计执行总次数

PGA中的运行有3种运行方式：

Optimal: 优化方式，所有处理都在内存中完成

Onepass:大部分操作在内存中完成，但需要使用到磁盘操作

Multipass:大量操作需要产生磁盘交互，最差方式

当你需要调整PGA或想要分析当前PGA时可以通过以下SQL产生一个当前系统的PGA指标：

select name,

       value,

       100 *

       (value / decode((select sum(value)

                         from v$sysstat

                        where name like 'workarea executions%'),

                       0,

                       NULL,

                       (select sum(value)

                          from v$sysstat

                         where name like 'workarea executions%'))) pct

  from v$sysstat

 where name like 'workarea executions%';

还可以通过下面的SQL语句查询当前PGA内存消耗在什么地方：

首先通过OS命令：ps –ef|grep LOCAL|head –l查询到当前实例的Oracle进程的进程号，如2803

然后执行如下SQL语句：

select p.PROGRAM,p.pid,pm.category,pm.allocated,pm.used,pm.max_allocated

from v$process p,v$process_memory pm

where p.PID=pm.pid and p.SPID=2803;