Oracle PGA内存管理 PGA Memory Management

一、简介
        Program Global Area(PGA)是一个和服务器进程关联的包含数据和控制信息的私有内存区域。
对于一个复杂的查询来说，需要在PAG中的SQL工作区（work area）中分配不同的内存区域来完成一些操作：
    {排序，比如order by，group by，rollup，窗口函数
    Hash join
    位图merge
    位图create

    批量加载时的write buffer}

    比如说一个排序使用排序区（sort area）完成数据在内存中的排序，一个hash join操作使用hash区（hash area）来创建一个hash表。涉及主要工作区的几个参数（SORT_AREA_SIZE，HASH_AREA_SIZE,BITMAP_MERGE_AREA_SIZE,CREATE_BITMAP_AREA_SIZE）。
    工作区的大小可以调整，一般情况下越大的工作区能明显提升某些操作的性能，但同时也使用更多的内存。
    理想情况是工作区足够大，能满足某个操作（比如排序操作），输入的排序数据和一些辅助的内存结构数据（排序需要维护一个二叉树）。这个时候我们称这个工作区是optimal size。
    如果工作区小于optimal size，响应时间会变长，因为输入数据中的部分数据经历了1次extra pass（比如排序的输入数据比较大，无法在sort area中完成，我们读取一点数据排序好，然后写到temp表空间，接着再次读取数据排序好，写到temp表空间，直到处理完所有数据，因为之前的数据都是部分排序好的，所以还需要读取每一部分排序好的数据中的一些控制信息，进行1次merge，就能返回结果了），这个时候的工作区是one-pass size。
    如果这个以上说的merge不能一次完成，需要多次merge（先读取几个部分的控制信息进行merge，再读取几个部分的控制信息进行merge，如此往复，最后把以上结果进行merge），就会大大增加响应时间和cpu的使用，那么就是multiple-pass size。

   

解释的可能不够准确，可以参考ASKTom的回答

    https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1308127400346936009
    比如排序10G的数据，需要10G多一点的内存使排序运行在optimal的状态，至少40M，才能满足one-pass，如果小于40M，就是multiple-pass。
目标就是让绝大部分的工作区是optimal size（对于纯OLTP系统来说，90%或者更高），然后少量是one-pass size，multiple-pass size是需要尽量避免的。即使是在DSS环境中，大的排序和hash-join也只需要很小的内存就能达到one-pass size，所以需要避免multiple-pass size。

    自动PGA内存管理简化和提升了PGA分配内存的方式。默认情况下PGA内存管理是开启的。在这种模式下，Oracle动态的调整PGA中不同部分的工作区大小。为了后向兼容，自动PGA内存管理可以被禁用（设置PAG_AGGREGATE_TARGET=0），这个时候可以指定通过设置参数*_AREA_SIZE来设置某个工作区的最大值，比如SORT_AREA_SIZE，还有HASH_AREA_SIZE,BITMAP_MERGE_AREA_SIZE,CREATE_BITMAP_AREA_SIZE等。

二、配置自动PGA内存管理
        自动管理PGA内存时，每个session的工作区都自动分配大小，所有*_AREA_SIZE都被忽略。任何时候，活动的工作区可用内存是PGA_AGGREGATE_TARGET参数减去被其他部分已经使用了的PGA内存。PGA内存自动管理是，数据库主要是参考PGA_AGGREGATE_TARGET的值，动态的给SQL工作区分配内存。尽可能使工作区都是optimal size，少量的one-pass size，避免multiple-pass size。
当你配置一个新的实例，刚开始很难准确的设置PGA_AGGREGATE_TARGET参数，我们可以按照以下步骤来设置：
1.初始化，估计一个值，通常是SGA的20%。这个对于DSS系统来说可能太低了。
2.运行一个有代表性的工作量负载，监控性能，使用PGA的一些统计数据，看设置的PGA_AGGREGATE_TARGET是大了还是小了。
3.调整PGA_AGGREGATE_TARGET，使用PGA advice statistics。
下面从以上3个方面展开讨论。

三、初始化设置PGA_AGGREGATE_TARGET
        设置PGA_AGGREGATE_TARGET首先是要基于数据库实例可用的物理内存，比如在有4G物理内存的主机上面，部分内存需要留给操作系统和其他非数据库应用。你可能只分配80%的内存给数据使用，其次，你还要决定这个可用内存在SGA和PGA之间的分配。
对于OLTP系统来说，一般20%分配给PGA,80%分配给SGA。
对DSS系统来说，可能要运行需要大的内存支持的查询，可以分配70%给PGA。
PGA_AGGREGATE_TARGET初始化设置可以参考以下公式:
For OLTP:PGA_AGGREGATE_TARGET=(total_mem*80%)*20%
For DSS:PGA_AGGREGATE_TARGET=(total_mem*80%)*50%

四、监控PGA内存自动管理的性能
        在调优之前，你需要监控PGA的性能，查看关键的性能数据来决定下一步如何调整。主要包括以下动态性能视图：
■ V$PGASTAT
■ V$PROCESS
■ V$PROCESS_MEMORY
■ V$SQL_WORKAREA_HISTOGRAM
■ V$SQL_WORKAREA_ACTIVE
■ V$SQL_WORKAREA

--1、 V$PGASTAT包含的是实例级别的PGA内存使用数据
SQL>  select * from v$pgastat;

NAME                                                    VALUE UNIT
-------------------------------------------------- ---------- ------------
aggregate PGA target parameter                      339738624 bytes
aggregate PGA auto target                           200521728 bytes
global memory bound                                  67947520 bytes
total PGA inuse                                     117057536 bytes
total PGA allocated                                 135889920 bytes
maximum PGA allocated                               158515200 bytes
total freeable PGA memory                             5439488 bytes
process count                                              30
max processes count                                        36
PGA memory freed back to OS                          34013184 bytes
total PGA used for auto workareas                           0 bytes
maximum PGA used for auto workareas                    528384 bytes
total PGA used for manual workareas                         0 bytes
maximum PGA used for manual workareas                       0 bytes
over allocation count                                       0
bytes proces