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;