oracle pga建议值,浅谈如何管理Oracle PGA

PGA

PGA(Program Global Area程序全局区)是一块包含一个服务进程的数据和控制信息的内存区域。它是Oracle在一个服务进程启动是创建的，是非共享的。一个Oracle进程拥有一个PGA内存区。一个PGA也只能被拥有它的那个服务进程所访问，只有这个进程中的Oracle代码才能读写它。因此，PGA中的结构是不需要Latch保护的。

我们可以设置所有服务进程的PGA内存总数受到实例分配的总体PGA(Aggregated PGA)限制。

在专有服务器(Dedicated Server)模式下，Oracle会为每个会话启动一个Oracle进程；而在多线程服务(Multi-Thread Server MTS)模式下，由多个会话共享通一个Oracle服务进程。

PGA中包含了关于进程使用到的操作系统资源的信息，以及一些关于进程状态的信息。而关于进程使用的Oracle共享资源的信息则是在SGA中。这样做可以使在进程以外中止时，能够及时释放和清除这些资源。

·Stack Space是用来存储用户会话变量和数组的存储区域；

·User Session Data是为用户会话使用的附加存储区。

|--Session Information

|--Sort Area

|--Cursor Information

注意Session information(用户会话信息)在独占服务器中与在共享服务器中所处的内存区域是不同的。

2.1 PGA的组成

PGA由两组区域组成：固定PGA和可变PGA(或者叫PGA堆，PGA Heap【堆——Heap就是一个受管理的内存区】)。固定PGA和固定SGA类似，它的大小时固定的，包含了大量原子变量、小的数据结构和指向可变PGA的指针。

可变PGA是一个内存堆。它的内存段可以通过视图X$KSMPP(另外一个视图X$KSMSP可以查到可变SGA的内存段信息，他们的结构相同)查到。PGA堆包含用于存放X$表的的内存(依赖与参数设置，包括DB_FILES、CONTROL_FILES)。

总的来说，PGA的可变区中主要分为以下三部分内容：

1)私有SQL区；

2)游标和SQL区

3)会话内存

2.1.1 私有SQL区(Private SQL Area)

私有SQL区包含了绑定变量值和运行时期内存结构信息等数据。每一个运行SQL语句的会话都有一个块私有SQL区。所有提交了相同SQL语句的用户都有各自的私有SQL区，并且他们共享一个共享SQL区。因此，一个共享SQL区可能和多个私有共享区相关联。

一个游标的私有SQL区又分为两个生命周期不同的区：

永久区：包含绑定变量信息。当游标关闭时被释放。

运行区：当执行结束时释放。

创建运行区是一次执行请求的第一步。对于INSERT、UPDATE和DELETE语句，Oracle在语句运行结束时释放运行区。对于查询操作，Oracle只有在所有记录被fetch到或者查询被取消时释放运行区。

2.1.2 游标和SQL区(Cursors and SQL Areas)

一个Oracle预编译程序或OCI程序的应用开发人员能够很明确的打开一个游标，或者控制一块特定的私有SQL区，将他们作为程序运行的命名资源。另外，oracle隐含的为一些SQL语句产生的递归调用(前面有介绍，读取数据字典信息)也使用共享SQL区。

私有SQL区是由用户进程管理的。如何分配和释放私有SQL区极大的依赖与你所使用的应用工具。而用户进程可以分配的私有SQL区的数量是由参数OPEN_CURSORS控制的，它的默认值是50。

在游标关闭前或者语句句柄被释放前，私有SQL区将一直存在(但其中的运行区是在语句执行结束时被释放，只有永久区一直存在)下去。应用开发人员可以通过将所有打开的不再使用的游标都关闭来释放永久区，以减少用户程序所占用的内存。

2.1.3 会话内存(Session Memory)

会话内存是一段用于保存会话变量(如登录信息)和其他预会话相关信息的内存。对于共享服务器模式下，会话内存是共享的，而不是私有的。

对于复杂的查询(如决策支持系统中的查询)，运行区的很大一部分被那些内存需求很大的操作分配给SQL工作区(SQL Work Area)。这些操作包括：

基于排序的操作(ORDER BY、GROUP BY、ROLLUP、窗口函数)；

Hash Join

Bitmap merge

Bitmap create

例如，一个排序操作使用工作区(这时也可叫排序区Sort Area)来将一部分数据行在内存排序；而一个Hash Join操作则使用工作区(这时也可以叫做Hash区 Hash Area)来建立Hash表。如果这两种操作所处理的数据量比工作区大，那就会将输入的数据分成一些更小的数据片，使一些数据片能够在内存中处理，而其他的就在临时表空间的磁盘上稍后处理。尽管工作区太小时，Bitmap操作不会将数据放到磁盘上处理，但是他们的复杂性是和工作区大小成反比的。因此，总的来说，工作区越大，这些操作就运行越快。

工作区的大小是可以调整的。一般来说，大的工作区能让一些特定的操作性能更佳，但也会消耗更多的内存。工作区的大小足够适应输入的数据和相关的SQL操作所需的辅助的内存就是最优的。如果不满足，因为需要将一部分数据放到临时表空间磁盘上处理，操作的响应时间会增长。

2.2 PGA内存自动管理

SQL工作区可以是自动的、全局的管理。DBA只要设置参数PGA_AGGREGATE_TARGET给一个实例的PGA内存指定总的大小。设置这个参数后，Oracle将它作为一个总的全局限制值，尽量使所有Oracle服务进程的PGA内存总数不超过这个值。

在这个参数出现之前，DBA要调整参数SORT_AREA_SIZE、 HASH_AREA_SIZE,、BITMAP_MERGE_AREA_SIZE和CREATE_BITMAP_AREA_SIZE(关于这些参数，我们会在后面介绍)，使性能和PGA内存消耗最佳。对这些参数的调整是非常麻烦的，因为即要考虑所有相关的操作，使工作区适合它们输入数据大小，又要使PGA内存不消耗过大导致系统整体性能下降。

9i以后，通过设置了参数PGA_AGGREGATE_TARGET，使所有会话的工作区的大小都是自动分配。同时，所有*_AREA_SIZE参数都会失效。在任何时候，实例中可用于工作区的PGA内存总数都是基于参数PGA_AGGREGATE_TARGET的。工作区内存总数等于参数PGA_AGGREGATE_TARGET的值减去系统其他组件(如分配给会话的PGA内存)的内存消耗。分配给Oracle进程的PGA内存大小是根据它们对内存的需求情况来的。

参数WORKAREA_SIZE_POLICY决定是否使用PGA_AGGREGATE_TARGET来管理PGA内存。它有两个值：AUTO和MANUAL。默认是AUTO，即使用 PGA_AGGREGATE_TARGET来管理PGA内存。其实，从参数WORKAREA_SIZE_POLICY的名字上可以看出，Oracle的PGA内存自动管理只会调整工作区部分，而非工作区部分(固定PGA区)则不会受影响。

还有注意一点就是：10g之前，PGA_AGGREGATE_TARGET只在专用服务模式下生效。而10g以后，PGA内存自动管理在专有服务模式(Dedicated Server)和MTS下都有效。另外，9i在OpenVMS系统上还不支持PGA内存自动管理，但10g支持。

设置了PGA_AGGREGATE_TARGET以后，每个进程PGA内存的大小也是受限制的：

串行操作时，每个进程可用的PGA内存为MIN(PGA_AGGREGATE_TARGET * 5%, _pga_max_size/2)，其中隐含参数_pga_max_size的默认值是200M，同样不建议修改它。

并行操作时，并行语句可用的PGA内存为PGA_AGGREGATE_TARGET * 30% / DOP(Degree Of Parallelism并行度)。

2.3 专有服务(Dedicated Server)和共享服务(Shared Server)

对PGA内存的管理和分配，很大程度上依赖与服务模式。下面这张表显示了在不同模式下，PGA内存不同部分的分配的异同：