4.1.1  手动PGA内存管理

如果采用手动PGA内存管理，有些参数对PGA大小的影响最大，这是指PGA中除了会话为PL/SQL表和其他变量分配的内存以外的部分，这些参数如下：

q  SORT_AREA_SIZE：在信息换出到磁盘之前，用于对信息排序的RAM总量。

q  SORT_AREA_RETAINED_SIZE：排序完成后用于保存已排序数据的内存总量。也就是说，如果SORT_AREA_SIZE是512 KB，SORT_AREA_RETAINED_SIZE是256 KB，那么服务器进程最初处理查询时会用512 KB的内存对数据排序。等到排序完成时，排序区会“收缩”为256 KB，这256 KB内存中放不下的已排序数据会写出到临时表空间中。

q  HASH_AREA_SIZE：服务器进程在内存中存储散列表所用的内存量。散列联结时会使用这些散列表结构，通常把一个大集合与另一个集合联结时就会用到这些结构。两个集合中较小的一个会散列到内存中，散列区中放不下的部分都会通过联结键存储在临时表空间中。

Oracle将数据写至磁盘（或换出到磁盘）之前，数据排序或散列所用的空间量就由这些参数控制，这些参数还控制着排序完成后会保留多少内存段。SORT_AREA_SIZE～SORT_AREA_ RETAINED_SIZE这部分内存一般从PGA分配，SORT_AREA_RETAINED_SIZE这部分内存会在UGA中分配。通过查询一些特殊的Oracle V$视图，可以看到PGA和UGA内存的当前使用情况，并监视其大小的变化，这些特殊的V$视图也称为动态性能视图（dynamic performance view）。

例如，下面来运行一个小测试，这里会在一个会话中对大量数据排序，在第二个会话中，我们将监视第一个会话中UGA/PGA内存的使用。为了以一种可预测的方式完成这个工作，我们建立了ALL_OBJECTS表的一个副本，其中有大约45 000行，而且没有任何索引（这样就能知道肯定会发生排序）：

为了消除最初硬解析查询所带来的副作用，我们将运行以下脚本，不过现在先不管它的输出。后面还会在一个新会话中再次运行这个脚本，查看受控环境中对内存使用的影响。我们会依次使用大小为64 KB、1 MB和1 GB的排序区：

现在，建议你注销刚才的SQL*Plus会话，紧接着再登录，这样能得到一个一致的环境；也就是说，相对于刚才的环境来讲，还没有做任何其他的工作。

为了确保使用手动内存管理，我们要专门设置，并指定一个很小的排序区大小（64 KB）。另外，还要标识会话ID（SID），以便监视该会话的内存使用情况。

下面需要在第二个单独的会话中测量第一个会话（SID 151）使用的内存。如果使用同一个会话测量自身的内存使用情况，在查询排序所用的内存时，这个查询本身可能会影响我们查看的结果。为了在第二个会话中测量内存，要使用我为此开发的一个SQL*Plus小脚本。实际上这是一对脚本，其中一个脚本名为reset_stat.sql，用于重置一个小表，并将一个SQL*Plus变量设置为SID，这个脚本如下：

另一个脚本是watch_stat.sql，对于这个案例研究，脚本中使用了MERGE SQL语句，这样就能首先插入（INSERT）一个会话的统计值，以后再回过来对其进行更新，而无需单独的INSERT/UPDATE脚本：

这里我强调了“对于这个案例研究”，因为上面粗体显示的行（我们感兴趣的统计名）在不同的示例中会有所不同。在这个例子中，我们感兴趣的是名字里包括ga的统计结果（pga和uga），或者名字里有direct temp的统计结果（在Oracle 10g中，这会显示对临时空间的直接读写，也就是读写临时空间所执行的I/O次数）。

从SQL*Plus命令行运行这个watch_stat.sql脚本时，可以看到会话的PGA和UGA内存统计信息列表，而且列出了对临时空间执行的I/O。在对会话151（也就是使用手动PGA内存管理的会话）做任何工作之前，下面使用以上脚本来看看这个会话当前使用了多少内存，以及对临时空间执行了多少次I/O：

可以看出，开始查询之前，UGA中大约有149 KB（152 176/1 024）的数据，PGA中大约有487 KB的数据。第一个问题是：“在PGA和UGA之间使用了多少内存？”也就是说，用了149 KB + 487 KB的内存吗？还是另外的某个数？这是一个很棘手的问题，除非你了解所监视的会话（SID为151）通过专用服务器还是共享服务器连接数据库，否则这个问题无法回答，而且就算你知道使用的是专用服务器连接还是共享服务器连接，可能也很难得出答案。如果采用专用服务器模式，UGA完全包含在PGA中，在这种情况下，进程或线程就使用487 KB的内存。如果使用共享服务器，UGA将从SGA中分配，PGA则在共享服务器中。所以，在共享服务器模式下，从前面的查询得到最后一行时[1]，共享服务器进程可能会由其他人使用。这个PGA不再是“我们的” 了，所以，从技术上讲，我们使用了149 KB的内存（除非正在运行查询，此时还使用了PGA和UGA之间的487 KB内存）。下面在会话151中运行第一个大查询，这个会话采用专用服务器模式，并使用手动PGA内存管理。需要说明，这里还是使用前面的脚本，SQL文本完全一样，因此可以避免硬解析：

现在，如果在第二个会话中再次运行脚本，会得到下面的结果。注意，这一次session xxx memory和session xxx memory max值并不一样。session xxx memory值表示我们现在使用了多少内存。session xxx memory max值表示会话处理查询时某个时刻所使用内存的峰值。

可以看到，使用的内存增加了，这里对数据做了某种排序。在处理查询期间，UGA临时从149 KB增加到213 KB(增加了64 KB)，然后再收缩回原来的大小。这是因为，为了完成查询和排序，Oracle为会话分配了一个排序区。另外，PGA内存从487 KB增加到551 KB，增加了64 KB。另外可以看到，我们对临时空间执行了2 906次读和写。

如以上结果所示，完成查询并得到结果集之前，UGA内存又退回到原来的大小（从UGA释放了排序区），PGA也会有某种程度的收缩（注意，在Oracle8i 和以前的版本中，可能根本看不到PGA收缩；这是Oracle9i 及以上版本中新增的特性）。

下面再来完成这个操作，不过这一次SORT_AREA_SIZE增加到1 MB。注销所监视的会话，再登录，然后使用reset_stat.sql脚本从头开始。因为开始的统计结果都是一样的，所以这里不再显示，我只给出最后的结果：

下面再在另一个会话中测量内存的使用情况：

可以看到，这一次处理查询期间，PGA大幅增长。它临时增加了大约1 728 KB，但是进行数据排序所必须执行的物理I/O次数则显著下降（使用更多的内存，就会减少与磁盘的交换）。而且，我们还可以避免一种多趟排序（multipass sort），如果有太多很小的排序数据集合要合并（或归并），Oracle最后就要多次将数据写至临时空间，这种情况下就会发生多趟排序。现在，再来看一个极端情况：

从另一个会话进行测量，可以看到迄今为止所使用的内存：

可以观察到，尽管允许SORT_AREA_SIZE有1 GB，但实际上只用了大约6.6 MB。这说明SORT_AREA_SIZE设置只是一个上界，而不是默认的分配大小。还要注意，这里也做了排序，但是这一次完全在内存中进行；而没有使用磁盘上的临时空间，从物理I/O次数为0可以看出这一点。

如果在不同版本的Oracle上运行这个测试，甚至在不同操作系统上运行这个测试，都可能会看到不同的行为，相信你得到的数值肯定和我得到的结果稍有差异。但是一般的行为应该是一样的。换句话说，增加允许的排序区大小并完成大规模排序时，会话使用的内存量会增加。你可能注意到PGA内存上上下下地变化，或者可能一段时间总保持不变（前面已经介绍过这种情况）。例如，如果你在Oracle8i上执行前面的测试，肯定会注意到PGA内存大小根本没有收缩（也就是说，无论什么情况，SESSION PGA MEMORY都等于SESSION PGA MEMORY MAX）。这是可以想见的，因为在8i中，PGA作为堆来管理，并通过malloc()分配内存来创建。在9i和10g中，则使用了新的方法，会根据需要使用操作系统特有的内存分配调用来分配和释放工作区。

在使用*_AREA_SIZE参数时，需要记住以下重要的几点：

q  这些参数控制着SORT、HASH和/或BITMAP MERGE操作所用的最大内存量。

q  一个查询可能有多个操作，这些操作可能都要使用这个内存，这样会创建多个排序/散列区。要记住，可以同时打开多个游标，每个游标都有自己的SORT_AREA_RETAINED需求。所以，如果把排序区大小设置为10 MB，在会话中实际上可以使用10 MB、100 MB、1 000 MB或更多RAM。这些设置并非对会话的限制；它们只是对一个操作的限制。你的会话中，一个查询可以有多个排序，或者多个查询需要一个排序。

q  这些内存区都是根据需要来分配的。如果像我们一样，将排序区大小设置为1 GB，这并不是说你要分配1 GB的RAM，而只是说，你允许Oracle进程为一个排序/散列操作最多分配1 GB的内存。