PGA自动管理原理深入分析及性能调整

1. PGA的概念以及所包含的内存结构
作为一个复杂的oracle数据库系统来说，每时每刻都要处理不同的用户所提交的SQL语句，获取数
据并返回数据给用户。众所周知，解析SQL语句的工作是在oracle实例中的shared pool所完成的。那么对于每个session来说，其执行SQL语句时所传入的绑定变量放在哪里？而且，对于那些需要执行比较复杂SQL的session来说，比如需要进行排序（sort）或hash连接（hash-join）时，这时这些session所需要的内存空间又从哪里来？另外，还有与每个session相关的一些管理控制信息又放在哪里？对于诸如此类与每个session相关的一些内存的分配问题，oracle通过引入PGA这个内存组件来进行解决。

1.1 PGA的相关概念
PGA按照oracle官方文档解释，叫做程序全局区（Program Global Area），但也有些资料上说还可以理解为进程全局区（Process Global Area）。这两者没有本质的区别，它首先是一个内存区域，其次，该区域中包含了与某个特定服务器进程相关的数据和控制信息。每个进程都具有自己私有的PGA区，这也就意味着，这块区域只能被其所属的进程进入，而不能被其他进程访问，所以在PGA中不需要latch这样的内存结构来保护其中的信息。笼统的来说，PGA里包含了当前进程所使用的有关操作系统资源的信息（比如打开的文件句柄等）以及一些与当前进程相关的一些私有的状态信息。
每个PGA区都包含两部分：
固定PGA部分（Fixed PGA）：这部分包含一些小的固定尺寸的变量，以及指向变化PGA部分的指针。
变化PGA部分（Variable PGA）：这部分是按照堆（Heap）来进行组织的，所以这部分也叫做PGA堆。可以从X$KSMPP视图中看到有关PGA堆的分布信息。PGA堆中所包含的内存结构包括：
有关一些固定表的永久性内存。
如果session使用的是专用连接方式（dedicated server），则还含有用户全局区（UGA－User Global Area）子堆。如果session使用的是共享连接方式（shared server），则UGA位于SGA中。
调用全局区（CGA－Call Global Area）子堆。

1.2 UGA（用户全局区）的相关概念
UGA是包含与某个特定session相关信息的内存区域，比如session的登录信息以及session私有的SQL区域等。每个UGA也包含两个部分：
固定UGA部分（Fixed UGA）：这部分包含一些小的固定尺寸的变量，以及指向变化UGA部分的指针。
变化UGA部分（Variable UGA）：这部分也是按照堆来进行组织的，可以从X$KSMUP视图中看到有关UGA堆的分布情况。UGA堆的分布与OPEN_CURSORS、OPEN_LINKS等参数有关系。所谓的游标（cursor）就是放在这里的。UGA堆中所包含的内存结构包括：
私有SQL区域（Private SQL Area）：这部分区域包含绑定变量信息以及运行时的内存结构等数据。每一个发出SQL语句的session都有自己的私有SQL区域。这部分区域又可分成两部分：
永久内存区域：这里存放了相同SQL语句多次执行时都需要的一些游标信息，比如绑定变量信息、数据类型转换信息等。这部分内存只有在游标被关闭时才会被释放。
运行时区域：这里存放了当SQL语句运行时所使用的一些信息。这部分区域的大小尺寸依赖于所要执行的SQL语句的类型（sort或hash-join等）和复杂度以及所要处理的数据行的行数以及行的大小。在处理SQL语句时的第一步就是要创建运行时区域，对于DML（INSERT、UPDATE、DELETE）语句来说，SQL语句执行完毕就释放该区域；而对于查询语句（SELECT）来说，则是在所有数据行都被获取并传递给用户以后被释放，或者该查询被取消以后也会被释放。
Session相关的信息。这部分信息包括：
正在使用的包（package）的状态信息。
使用alter session这样的命令所启用的跟踪信息、或者所修改的session级别的优化器参数（optimizer_mode）、排序参数（sort_area_size等）、修改的NLS参数等。
所打开的dblinks。
可使用的角色（roles）等。
从上面可以很明显的看出，我们最需要关注的就是私有SQL区域中的运行时区域了。实际上，从9i
以后，对这部分区域有了一个新的名称：SQL工作区域（SQL Work Area）。SQL工作区域的大小依赖于所要处理的SQL语句的复杂程度而定。如果SF1则，如果没有设置large pool，则UGA只能从shared pool里进行分配了。
如果session是通过专用服务器（dedicated server）方式连到数据库的，则UGA是从进程的PGA中进行分配的。

1.3 CGA（调用全局区）的相关概念
CGA也是一块内存区域，但它是动态的，随着调用（call）的开始而创建，在调用过程中一直存在，直到调用结束时被释放。它存放的是在调用过程中所需要的数据。
我们知道，调用主要包括解析（parse）调用、执行（executive）调用、获取（fetch）调用以及递归SQL调用和PL/SQL调用。从调用的种类可以看出，实际上在调用过程中所需要的数据，比如SQL AREA，PL/SQL AREA和SORT AREA基本都是放在UGA中的，因为这些数据在各个调用之间必须一直存在并可用。而在CGA中只存放了在调用过程中临时需要的数据，比如直接I/O缓存（Direct I/O Buffer）以及堆栈空间等数据结构。因此，没有CGA中的数据结构，调用是无法完成的。
注意，CGA不象UGA可以位于SGA中（以共享服务器模式连接），CGA一定是位于PGA中的。如果当前进程正在运行，则每个PGA中只有一个CGA。如果当前进程没有运行，则该进程的PGA中就没有CGA。
1.4 转储PGA
就象实例中的其他内存结构一样，oracle同样提供了可以将PGA转储到跟踪文件的方法。方法如下：
SQL> alter session set events 'immediate trace name heapdump level n';
其中的level n决定了将哪些内存堆转储到跟踪文件：
Level 1: PGA汇总信息
Level 2: SGA汇总信息
Level 4: UGA汇总信息
Level 8: 当前调用的汇总信息（CGA）
Level 16: 用户调用的汇总信息（CGA）
Level 32: Large pool的汇总信息（LGA）
Level 1025: PGA详细信息
Level 2050: SGA详细信息
Level 5000: UGA 详细信息
Level 8200: 当前调用的详细信息
Level 16400: 用户调用的详细信息
Level 32800: Large pool的详细信息

举例来说，我们转储PGA的汇总信息：
SQL> alter session set events 'immediate trace name heapdump level 1';
到user_dump_dest所定义的目录下，找到跟踪文件并打开，可以看到类似下面的信息：
******************************************************
HEAP DUMP heap name="pga heap" desc=001DB880
extent sz=0x213c alt=84 het=32767 rec=0 flg=2 opc=2
parent=00000000 owner=00000000 nex=00000000 xsz=0x213c
EXTENT 0 addr=03700034
Chunk 370003c sz= 8500 perm "perm " alo=7524
EXTENT 1 addr=0351BC8C
Chunk 351bc94 sz= 9156 freeable "Fixed Uga
"
EXTENT 2 addr=03519B3C
Chunk 3519b44 sz= 3764 perm "perm " alo=3764
Chunk 351a9f8 sz= 4196 free " "
Chunk 351ba5c sz= 540 freeable "kopolal dvoid "
……………
Chunk 45e988c sz= 4144 recreate "Alloc environm " latch=00000000
ds 45eade0 sz= 4144 ct= 1
Chunk 45ea8bc sz= 1484 freeable "kpuinit env han"
我们可以看到，其中的红色部分就是在PGA中所包含的固定UGA部分。同时，我们可以使用如下的命令将PGA的子堆也给转储出来，其中9.2以前使用：
SQL> alter session set events 'immediate trace name heapdump_addr level n';
9.2以后使用：
SQL> alter session set events 'immediate trace name heapdump_addr level 1, addr n';
其中的n表示子堆的地址。而子堆的地址可以在PGA的转储文件中找到。比如上面的例子中，我们可以看到这样的一行：
ds 45eade0 sz= 4144 ct= 1
这里的ds 45eade0就是某个子堆的地址，这是个十六进制的数值，于是我们先将其转换为十进制数值：
SQL> select to_number('45eade0','xxxxxxxx') from dual;
TO_NUMBER('45EADE0','XXXXXXXX')
-------------------------------
73313760
这里的73313760就是转储PGA子堆的命令中的n，所以我们可以执行（我的测试库为9.2.0.5）：
SQL> ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level 1, addr 73313760';

2. PGA自动管理及深入研究
2.1 PGA自动管理概述
在9i之前，我们主要是通过设置sort_area_size、hash_area_size等参数值（通常都叫做*_area_size）来管理PGA的使用，不过严格说来，是对PGA中的UGA中的私有SQL区域进行管理，这块内存区域又有个名称叫做SQL工作区域。但是，这里有个问题，就是这些参数都是针对某个session而言的，也就是说设置的参数值对所有连进来的session都生效。在数据库实际运行过程中，总有些session需要的PGA多，而有些session需要的PGA少。如果都设置一个很小的*_area_size，则会使得某些SQL语句运行时由于需要将临时数据交换到磁盘而导致效率低下。而如果都设置一个很大的值，又有可能一方面浪费空间，另一方面，消耗过多内存可能导致操作系统其他组件所需要的内存短缺，而引起数据库整体性能下降。所以如何设置*_area_size的值一直都是DBA很头疼的一个问题。

而从9i起所引入的一个新的特性可以有效的解决这个问题，这个特性就是自动PGA管理。DBA可以根据数据库的负载情况估计所有session大概需要消耗的PGA的内存总和，然后把该值设置为初始化参数pga_aggregate_target的值即可。Oracle会按照每个session的需要为其分配PGA，同时会尽量维持整个PGA的内存总和不超过该参数所定义的值。这样的话，oracle就能尽量避免整个PGA的内存容量异常增长而影响整个数据库的性能。从而，就有效的解决了设置*_area_size所带来的问题。
不过遗憾的是，9i下的PGA自动管理只对专用连接方式有效，对共享连接方式无效。10g以后对两种连接方式都有效。
启用PGA自动管理是很容易的，只要设置两个初始化参数即可。首先，设置workarea_size_policy参数。该参数为auto（也是缺省值）时，表示启用PGA自动管理；而设置该参数为manual时，则表示禁用PGA自动管理，仍然沿用9i之前的方式，即使用*_area_size对PGA进行管理。其次，就是设置pga_aggregate_target了，该参数可以动态进行调整，范围是从10MB到4096GB – 1个字节。

2.2 PGA自动管理深入
PGA中对性能影响最大的就是SQL工作区了。通常说来，SQL工作区越大则对于SQL语句的执行的效率就高，从而对于用户的响应时间就越少。理想情况下，SQL工作区应该可以容纳SQL执行过程中所涉及到的所有输入数据和控制信息。当然，这只是理想情况，现实往往总是不能尽如人意，很多情况下SQL工作区是不能容纳执行SQL所需要的内存空间的，从而不得不交换到临时表空间里。为了衡量执行SQL所需要的内存与实际分配给该SQL的SQL工作区之间的契合程度，oracle将所分配的SQL工作区大小分成三种类型：
optimal尺寸：SQL语句能够完全在所分配的SQL工作区内完成所有的操作。这时的性能最佳。
onepass尺寸：SQL语句需要与磁盘上的临时表空间交互一次才能够在所分配的SQL工作区中完成所有的操作。 multipass尺寸：由于SQL工作区过小，从而导致SQL语句需要与磁盘上的临时表空间交互多次才能完成所有的操作。这个时候的性能将急剧下降。
当系统整体负载不大时，oracle倾向于为每个session的PGA分配optimal尺寸大小的SQL工作区。
而随着负载上升，比如连接的session逐渐增多导致同时执行的SQL语句越来越多时，oracle就会倾向于为每个session的PGA分配onepass尺寸大小的SQL工作区，甚至是multipass尺寸的SQL工作区了。
那么，PGA自动管理机制在内部到底是如何实现的呢？很遗憾，oracle官方并没有给出说明文档。其实这本身也说明了，PGA自动管理的内部算法会随着版本升级而发生变化。不过，知其然而不知其所以然，总是会让诸如我等之类的技术人员感觉如梗在喉。还好，曾经就有一些专门做oracle优化的公司发布的文档中介绍了PGA内部的实现原理，我想这可能是oracle公司透露给这些公司的。这里就做些简单的介绍，不过记住，这里所描述的PGA自动管理的原理并不一定就是将来版本的原理，只能说是截至到9.2的PGA自动管理的原理。
PGA自动管理是采用名为“循环反馈（feedback loop）”的算法来实现的。如下图所示。
上图中，当开始处理一条SQL时，oracle会使用本地内存管理器（local memory manager）对该SQL语句相关的work area profile进行注册。work area profile是一组元数据，描述了该SQL语句所需要的工作区的所有特征，包括该SQL的类型（sort还是hash-join等）、该SQL语句的并行度、所需要的内存等信息。它是SQL语句操作与内存管理器之间唯一的接口。当SQL语句执行完毕时，其对应的work area profile就会被删除。而在SQL语句执行期间，为了反映SQL语句当前已经消耗的内存以及是否被交换到临时表空间了等状态信息，oracle会不断更新其对应的work area profile。所以说，SQL语句的work area profile是有生命周期的，始终能够体现其对应SQL语句的工作区状态。因此，我们可以说，在任何时间点，所有当前活动的work area profile就能够基本体现当前所有session对PGA内存的需要以及当前正在使用的PGA内存。通过查询视图v$sql_workarea_active，可以显示所有当前活动的work area profile的相关信息。
现在，我们需要引入另外一个后台守护进程（background daemon），叫做全局内存管理器（global memory manager）。这个进程每隔3秒会启动一次。每次启动时，都会根据当前所有活动的work area profile的数量以及其他相关信息计算出这个时候的SQL工作区的“内存限度（memory bound）”，也就是每个工作区最大尽量不能超过多大（不过，注意，严格说来应该是尽量不超过。实际上这个最大值是可以被超过的，后面会用个实例来说明）。然后立即发布这个“内存限度”。
最后，本地内存管理器关闭“反馈循环”，并根据当前的“内存限度”以及当前work area profile，从而计算出当前SQL工作区应该具有的内存大小，并为进程分配该大小的内存以执行SQL语句，这个内存的大小尺寸就叫做“期望尺寸（expect size）”，可以从v$sql_workarea_active的expected_size列看到“期望尺寸”的大小。同时，这个“期望尺寸”会定时更新，并据此对SQL工作区进行调整。
Oracle内部对这个“期望尺寸”的大小有如下规则的限制：
“期望尺寸”不能小于最低的内存需求。
“期望尺寸”不能大于optimal尺寸。
如果“内存限度”介于最低的内存需求和optimal尺寸之间，则使用“内存限度”作为“期望尺寸”的大小，但是排序操作除外。因为排序操作算法的限制，对于分配的内存在optimal尺寸和onepass尺寸之间时，排序操作不会随着内存的增加而更快完成，除非能够为排序操作分配optimal尺寸。所以，如果排序操作的“内存限度”介于onepass尺寸和optimal尺寸之间的话，“期望尺寸”取onepass尺寸。
如果SQL以并行方式运行，则“期望尺寸”为上面三个规则算出的值乘以并行度。
非并行模式下，按照通常的说法是“期望尺寸”不能超过min(5%*pga_aggregate_target,100MB)。但实际上，这是在不修改_pga_max_size和_smm_max_size这两个隐藏参数的前提下，可以简单的这么认为。严格说来，应该是不能超过min(5%*pga_aggregate_target,50%*_pga_max_size,_smm_max_size)。对于并行的情况，就更加复杂，可以简单认为不超过30%*pga_aggregate_target。
下面，我们举例（如下图所示）来说明全局内存管理器是如何计算并应用“内存限度”的。比如，
当前系统中有6个活动的work area profile。WP1所需要的onepass内存为7MB，而optimal内存为27MB。WP3是一个并行度为2的hash-join，它需要11MB的onepass内存，以及67MB的optimal的内存。假设pga_aggregate_target设置为133MB，则可以简单的认为全局内存管理器直接将133除以6，也就是大约20MB作为“内存限度”的值。于是该“内存限度”限制了分配给WP1的工作区只能为7MB，也就是onepass的大小，因为WP1是一个排序操作，如果给它分配20MB也不能使它在以optimal的方式完成。而对于20MB的“内存限度”，WP3可以分到40MB的工作区，因为WP3的并行度为2，所以可以分配20MB×2的大小的工作区。
2.3如何设置新数据库的PGA值
我们一旦设置了pga_aggregate_target以后，所有的*_area_size就将被忽略。那么，我们该如何来设置该参数的值呢？这依赖于数据库的用途，如果数据库为OLTP（联机事务处理）应用的，则其应用一般都是小的短的进程，所需要的PGA也相应较少，所以该值该值通常为总共分配给oracle实例的20%，另外的80%则给了SGA；如果数据库为OLAP（DSS）（数据仓库或决策分析）应用的，则其应用一般都是很大的，运行时间很长的进程，因此需要的PGA就多。所以通常为PGA分配50%的内存。而如果数据库为混合类型的，则情况比较复杂，一般会先分配40%的初始值，而后随着数据库的应用，而不断对PGA进行监控，并进行相应的调整。
比如，对于8GB物理内存的数据库服务器来说，按照oracle推荐的，分配给oracle实例的内存为物理内存的80%。那么对于OLTP应用来说，pga_aggregate_target的值大约就是1310MB ((8192 MB× 80%)×20%)。而对于OLAP来说，则该值大约就是3276MB (8192MB×80%)×50%)。
当然，这里所说的都是对于一个新的数据库来说，初始设置的值。这些值并不一定正确，可能设置过大，也可能设置过小。必须随着系统的不断运行，DBA需要不断监控，从而对其进行调整。

3. PGA监控及调优
我们已经大致了解了有关PGA的相关理论知识，现在我们可以开始动手实践来验证上面的理论，并
可以开始对PGA的使用进行监控以及调优了。以下测试都是在windows XP、oracle 9.2.0.5，以及专用连
接模式下进行的。

3.1准备测试用例
首先，我们先创建一个测试用例。
SQL> create table pga_test as select * from dba_objects;
SQL> select count(*) from pga_test;
COUNT(*)
----------
6243
然后，引入几个监控PGA的脚本。
pga_by_hashvalue.sql，这是一个监控SQL语句所使用的SQL工作区的脚本：
SELECT
b.sql_text,
a.operation_type,
a.policy,
a.last_memory_used/(1024*1024) as "Used MB" ,
a.estimated_optimal_size/(1024*1024) as "Est Opt MB",
a.estimated_onepass_size/(1024*1024) as "Est OnePass MB",
a.last_execution,
a.last_tempseg_size
FROM v$sql_workarea a,v$sql b
WHERE a.hash_value = b.hash_value
and a.hash_value = &hashvalue
/
pga_by_session.sql，第二个脚本是pga_by_session.sql，用来监控session所使用的PGA和UGA的大小：
select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = &sid
and a.name like '%ga %'
order by a.name
/
第三个脚本监控进程所使用的PGA的大小，pga_by_process.sql ：
SELECT
a.pga_used_mem "PGA Used",
a.pga_alloc_mem "PGA Alloc",
a.pga_max_mem "PGA Max"
FROM v$process a,v$session b
where a.addr = b.paddr
and b.sid= &sid
/

3.2单个session对PGA使用情况的监控
我们分别创建5个session，第一个session（sess#1）执行测试语句；第二个session（sess#2）执行pga_by_hashvalue.sql脚本；第三个session（sess#3）执行pga_by_session.sql脚本；第四个session（sess#4）执行pga_by_process.sql脚本；第五个session（sess#5）设置相关参数。以下按照顺序描述整个测试的过程。
Sess#1：
SQL> select sid from v$mystat where rownum=1;
SID
----------
7
Sess#3查询当前sid为7的session的PGA和UGA各为多少，可以看到，即使不执行任何的SQL，只要session连接了，就会消耗大约0.23MB的PGA内存：
SQL> @pga_by_session.sql;
NAME VALUE
------------------------------ ----------
session pga memory 238188
session pga memory max 238188
session uga memory 77008
session uga memory max 77008
Sess#5，我们将pga_aggregate_target设置为60MB：
SQL> alter system set pga_aggregate_target=60M;
Sess#1，执行测试语句：
SQL> set autotrace traceonly stat;
SQL> select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8;
Sess#5，找到sess#1中所执行的SQL语句的hash值：
SQL> select hash_value from v$sql where sql_text='select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8';
HASH_VALUE
----------
2656983355
Sess#2：
SQL> @d:pga_by_hashvalue.sql
输入 hashvalue 的值: 2656983355
原值 12: and a.hash_value = &hashvalue
新值 12: and a.hash_value = 2656983355
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8
SORT AUTO 3
66.1376953 2.75390625 2 PASSES 65011712
我们可以看到，该SQL语句所分配的工作区为3MB，这个值就是5%*pga_aggregate_target（60M*0.05）。符合前面说到的“期望尺寸”为min(5%*pga_aggregate_target,100MB)。
Sess#3：
SQL> @ pga_by_session.sql;
NAME VALUE
------------------------------ ----------
session pga memory 369796
session pga memory max 4956780
session uga memory 77008
session uga memory max 3677528

可以看到，为了执行测试语句，为该session分配的PGA为4956780个字节，其中UGA为3677528个字节，大约3.5M。同时可以看出，执行完测试语句以后，oracle就把该session的PGA空间回收了（PGA从4956780下降到369796，而UGA从3677528下降到77008），顺带提一下，在8i中分配了PGA以后是不会回收的，也就是说session pga memory始终等于session pga memory max，而9i以后的PGA的分配方式发生了改变，从而能够在分配PGA以后还可以再回收一部分内存。结合上面为SQL语句所分配的3M的工作区，可以知道，UGA中的其他空间占用大约0.5M。而SQL工作区占整个PGA大小大约为64%，从这个方面也可以看出，SQL工作区是PGA中最占空间、也是最重要的部分。
Sess#4：
SQL> @d:pga_by_process.sql
输入 sid 的值: 7
原值 7: and b.sid= &sid
新值 7: and b.sid= 7
PGA Used PGA Alloc PGA Max
---------- ---------- ----------
253932 382664 4969648

可以看到，这几个视图查出来的PGA的大小基本都是一致的。
我们继续测试，从sess#2可以看出，如果要让该SQL语句完全在内存中完成，需要大约67MB的PGA空间。根据5%的原理倒算，可以知道这个时候的pga_aggregate_target应该大于1340MB（67/0.05）。于是，我们设置1500MB，来看看是不是确实进行optimal了。顺便提醒一下，并不是说你的电脑得有超过1500MB的物理内存你才可以设置1500M的pga_aggregate_target，事实上pga_aggregate_target是按需分配的，不象SGA，一旦设置就占着内存，不用也得占着。也就是说是PGA是随着对内存需求的增长而不断增长的。我测试的机器上只有1GB的物理内存，但做测试时完全可以将pga_aggregate_target设置5GB，甚至更高的10GB。
Sess#5，我们将pga_aggregate_target设置为1500MB：
SQL> alter system set pga_aggregate_target=1500M;
Sess#1：
SQL> select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8;
Sess#2：
SQL> @d:pga_by_hashvalue.sql
输入 hashvalue 的值: 2656983355
原值 12: and a.hash_value = &hashvalue
新值 12: and a.hash_value = 2656983355
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8
SORT AUTO 65.765625
73.9873047 2.90039063 OPTIMAL
我们可以看到，该SQL语句确实完全在内存里完成了（LAST_EXECUTION为“OPTIMAL”）。同时，实际的“期望尺寸”始终会小于optimal（65.765625<73.9873047），也符合前面说的第二条规则。
我们继续测试，看看SQL工作区的“期望尺寸”是否真的不能超过100MB。为此，需要设置5%*
pga_aggregate_target>100MB，因此pga_aggregate_target最少要大于2G，我们设置5GB。
Sess#5，我们将pga_aggregate_target设置为5GB：
SQL> alter system set pga_aggregate_target=5G;
Sess#1，注意，为了能够占用更多的PGA，这时的SQL语句已经把where条件修改了：
SQL> select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8;
Sess#5，找到该语句的hash值：
SQL> select hash_value from v$sql where sql_text='select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8';
HASH_VALUE
----------
3008669403
Sess#2：
SQL> /
输入 hashvalue 的值: 3008669403
原值 12: and a.hash_value = &hashvalue
新值 12: and a.hash_value = 3008669403
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8
SORT AUTO 87.265625
137.232422 3.87109375 1 PASS 127926272
可以看到，optimal尺寸已经超过100MB很多了，但是实际分配的“期望尺寸”却只有88MB左右。而5G*0.05为250MB，为何该SQL用不了呢？这其实是由两个隐藏参数决定的，分别是_pga_max_size和_smm_max_size。我们来看一下这两个参数的含义和缺省值：
Sess#5：
SQL> select ksppinm, ksppstvl, ksppdesc from x$ksppi x, x$ksppcv y where x.indx = y.indx and ksppinm in ('_pga_max_size','_smm_max_size');
KSPPINM KSPPSTVL KSPPDESC
-------------- ---------- -----------------------------------------------
_pga_max_size 209715200 Maximum size of the PGA memory for one process
_smm_max_size 102400 maximum work area size in auto mode (serial)
我们可以看到_pga_max_size缺省值为200M（209715200/1024/1024），而_smm_max_size缺省值为100MB（上面的查询结果中显示的单位是KB）。而每个session的PGA最多只能使用_pga_max_size的一半，也就是100MB。
当你修改参数pga_aggregate_target的值时，Oracle系统会根据pga_aggregate_target和_pga_max_size这两个值来自动修改参数_smm_max_size。具体修改的规则是：
如果_pga_max_size大于5%*pga_aggregate_target，则_smm_max_size为5%*pga_aggregate_target。
如果_pga_max_size小于等于5%*pga_aggregate_target，则_smm_max_size为50%*_pga_max_size。
有些资料上说，可以通过修改_pga_max_size来突破这个100MB的限制。真的是这样吗？我们来测试。Sess#5，修改参数_pga_max_size为600MB：
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 5368709120
SQL> alter system set "_pga_max_size"=600M;
我们将_pga_max_size的值设置为600M，其一半就是300MB，已经超过5%*pga_aggregate_target（即250MB）了。所以这两者的较小值为250M，如果这时我们在sess#1中再次执行测试语句，应该可以使用超过100MB的SQL工作区了。我们来看测试结果。
Sess#1：
SQL> select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8;
Sess#2：
SQL> /
输入 hashvalue 的值: 3008669403
原值 12: and a.hash_value = &hashvalue
新值 12: and a.hash_value = 3008669403

SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8
SORT AUTO 87.265625
137.232422 3.87109375 1 PASS 127926272

我们看到，“期望尺寸”仍然是大约88MB，并没有突破100MB的限制。其中的问题就在于参数
_smm_max_size 上。我们来看这个时候该参数值是多少：
Sess#5：
SQL> select ksppinm, ksppstvl, ksppdesc from x$ksppi x, x$ksppcv y where x.indx = y.indx and ksppinm in ('_pga_max_size','_smm_max_size');
KSPPINM KSPPSTVL KSPPDESC
-------------- ---------- -----------------------------------------------
_pga_max_size 629145600 Maximum size of the PGA memory for one process
_smm_max_size 102400 maximum work area size in auto mode (serial)
可以看到参数_smm_max_size的值仍然是100MB。实际上，这也是一个对 “期望尺寸”的限制参数。这里可以看到“期望尺寸”不能超过100MB。这时，我们只要简单的执行：
Sess#5：
SQL> alter system set pga_aggregate_target=5G;
SQL> select ksppinm, ksppstvl, ksppdesc from x$ksppi x, x$ksppcv y where x.indx = y.indx and ksppinm in ('_pga_max_size','_smm_max_size');
KSPPINM KSPPSTVL KSPPDESC
-------------- ---------- -----------------------------------------------
_pga_max_size 629145600 Maximum size of the PGA memory for one process
_smm_max_size 262144 maximum work area size in auto mode (serial)
我们可以看到，只要设置一下pga_aggregate_target，就会按照前面所说的规则重新计算并设置_smm_max_size的值，该参数修改后的值为250MB。这个时候我们重复上面的测试：
Sess#1：
SQL> select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8;
Sess#2：
SQL> /
输入 hashvalue 的值: 3008669403
原值 12: and a.hash_value = &hashvalue
新值 12: and a.hash_value = 3008669403
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8
SORT AUTO 137.195313
154.345703 4.09179688 OPTIMAL
这时，我们看到，“期望尺寸”为138MB左右，终于超过了100MB。如果我们再次将参数_smm_max_size人为的降低到100MB，则“期望尺寸”又将不能突破100MB了。我们来看试验。
Sess#5：
SQL> alter system set "_smm_max_size"=102400;
Sess#1：
SQL> select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8;
Sess#2：
SQL> /
输入 hashvalue 的值: 3008669403
原值 12: and a.hash_value = &hashvalue
新值 12: and a.hash_value = 3008669403
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8
SORT AUTO 87.265625
137.232422 3.87109375 1 PASS 127926272
可以看到，结果正如我们所预料的。由此，得出我们重要的结论，就是在非并行方式下，“期望尺寸”为min(5%*pga_aggregate_target,50%*_pga_max_size,_smm_max_size)，而不是很多资料上所说的不是很严密的min(5%*pga_aggregate_target,50%*_pga_max_size)。oracle当然是不推荐我们修改这两个隐藏参数的。

3.3多个并发session对PGA使用情况的监控
现在我们可以来测试多个session并发时PGA的分配情况。测试并发的方式有很多，可以写一个小程序循环创建多个连接，然后执行上面的测试语句，也可以借助一些工具来完成。为了方便起见，我用了一个最简单的方式。就是写一个SQL文本，再写一个bat文件，该bat文件中执行SQL文本。两个文件准备好以后，将bat文件拷贝30份，然后选中这30份一摸一样的bat文件，按回车键后，windows XP将同时执行这30个bat文件，这样就可以模拟出30个session同时连接并同时执行测试语句的环境了。具体这两个文件的具体内容如下：
pga_test.sql：
set autotrace traceonly stat;
select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8;
run.bat：
@sqlplus -s cost/cost@ora92 @d:testpga_test.sql
我们先将pga_aggregate_target设置为60MB。
Sess#5：
SQL> alter system set pga_aggregate_target=60M;
然后同时运行30个bat文件从而启动30个执行相同SQL测试语句的并发session，我执行下面的语句以显示这时正在执行的30个session所消耗的PGA的总内存：
Sess#5：
SQL> select a.name, sum(b.value)/1024/1024 as "MB"
2 from v$statname a, v$sesstat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %'
5and sid in(select sid from v$sql_workarea_active)
6 group by a.name;
NAME MB
---------------------------------------------------------------- ----------
session pga memory 45.9951134
session pga memory max 95.6863365
session uga memory 19.757431
session uga memory max 72.6992035
我们可以看到，session pga memory max显示出大约96MB的PGA内存，很明显，PGA的总容量已经超出了pga_aggregate_target（60M）的限制的容量。实际上这也就说明，该参数只是说明，oracle会尽量维护整个PGA内存不超过这个值，如果实在没有办法，也还是会突破该参数限制的。
同时，我们可以去查看这个时候该测试SQL语句所分配的工作区变成了多少，同样在Sess#2中：
SQL> @d:pga_by_hashvalue.sql
输入 hashvalue 的值: 2656983355
原值 12: and a.hash_value = &hashvalue
新值 12: and a.hash_value = 2656983355
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB
---------------------------------------- -------------------- ----------
Est Opt MB Est OnePass MB LAST_EXECUTION LAST_TEMPSEG_SIZE
---------- -------------- -------------------- -----------------
select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8
SORT AUTO 1.8984375
66.1376953 2.75390625 2 PASSES 65011712
从结果中我们可以看到，该SQL的工作区已经从单个session时的3MB下降到了大约1.9M，我们可以看到，30个session总共至少需要57MB（1.9M*30）的SQL工作区。明显的，60MB的pga_aggregate_target是肯定不能满足需要的。

3.4其他监控并调整PGA的方法
我们监控PGA的视图除了上面介绍到的v$sql_workarea_active、v$sesstat、v$sql_workarea以及v$process以外，还有v$sql_workarea_histogram、v$pgastat以及v$sysstat。
v$sql_workarea_histogram记录了每个范围的SQL工作区内所执行的optimal、onepass、multipass的次数。如下所示：
SQL> select
2 low_optimal_size/1024 "Low (K)",
3 (high_optimal_size 1)/1024 "High (K)",
4 optimal_executions "Optimal",
5 onepass_executions "1-Pass",
6 multipasses_executions ">1 Pass"
7 from v$sql_workarea_histogram
8 where total_executions <> 0;
结果类似如下所示，我们可以看到整个系统所需要的PGA的内存大小主要集中在什么范围里面。
Low (K) High (K) Optimal 1-Pass >1 Pass
---------- ---------- ---------- ---------- ----------
8 16 360 0 0
。。。。。。。。。
65536 131072 0 2 0
另外，我们可以将上面的查询语句改写一下，以获得optimal、onepass、multipass执行次数的百分比，很明显，optimal所占的百分比越高越好，如果onepass和multipass占的百分比很高，就不需要增加pga_aggregate_target的值了，或者调整SQL语句以使用更少的PGA区。
SQL> select
2 optimal_count "Optimal",
3 round(optimal_count * 100 / total,2) "Optimal %",
4 onepass_count "OnePass",
5 round(onepass_count * 100 / total,2) "Onepass %",
6 multipass_count "MultiPass",
7 round(multipass_count * 100 / total,2) "Multipass %"
8 from (
9 select
10 sum(total_executions) total,
11 sum(optimal_executions) optimal_count,
12 sum (onepass_executions) onepass_count,
13 sum (multipasses_executions) multipass_count
14 from v$sql_workarea_histogram
15 where total_executions <> 0)
16 /
Optimal Optimal % OnePass Onepass % MultiPass Multipass %
---------- ---------- ---------- ---------- ---------- -----------
402 99.01 4 0.99 0 0
而v$pgastat则提供了有关PGA使用的整体的概括性的信息。
SQL> select * from v$pgastat;
NAME VALUE UNIT
---------------------------------------- ---------- ------------
aggregate PGA target parameter 62914560 bytes
aggregate PGA auto target 513607
68 bytes
global memory bound 104857600 bytes
total PGA inuse 5846016 bytes
total PGA allocated 8386560 bytes
maximum PGA allocated 66910208 bytes
total freeable PGA memory 0 bytes
PGA memory freed back to OS 0 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 51167232 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
over allocation count 0
bytes processed 142055424 bytes
extra bytes read/written 138369024 bytes
cache hit percentage 50.65 percent
从结果可以看出，第一行表示pga_aggregate_target设置为60M。PGA的一部分被用于无法动态调整的部分，比如UGA中的“session相关的信息”等。而PGA内存的剩下部分则是可以动态调整的，由“aggregate PGA auto target”说明。我们来看第二行的值，就表示可以动态调整的内存数量，该值不能与pga_aggregate_target设置的值差太多。如果该值太小，则oracle没有足够的内存空间来动态调整session的内存工作区。其中的global memory bound表示一个工作区的最大尺寸，并且oracle推荐只要该统计值低于1M时，就应该增加pga_aggregate_target的值。另外，9i还提供了两个有用的指标：over allocation count和cache hit percentage。如果在使用SQL工作区过程中，oracle认为pga_aggregate_target过小，则它自己会去多分配需要的内存。则多分配的次数就累加在over allocation count指标里。该值越小越好，最好为0。cache hit percentage则表示完全在内存里完成的操作的字节数与所有完成的操作(包括optimal、onepass、multipass)的字节数的比率。如果所有的操作都是optimal类，则该值为100%。
最后，我们可以查询v$sysstat视图，获得optimal、onepass、multipass执行的总次数：
SQL> select * from v$sysstat where name like 'workarea executions%';
STATISTIC# NAME CLASS VALUE
---------- ---------------------------------------- ---------- ----------
230 workarea executions - optimal 64 360
231 workarea executions - onepass 64 2
232 workarea executions - multipass 64 0
我们可以计算optimal次数占总次数的比率，比如上例中，360/(360 2 0)=99.45%，该比率越大越好，如果发现onepass和multipass较多，则需要增加pga_aggregate_target，或者调整SQL语句以使用更少的PGA区。
那么我们如何找到需要调整以使用更少的PGA的SQL语句呢？我们可以将v$sql_workarea中的记录按照estimated_optimal_size字段由大到小的排序，选出排在前几位的hash值，同时还可以选出last_execution值为“n PASSES”（这里的n大于或等于2）的hash值，将这些hash值与v$sql关联后找出相应的SQL语句，进行调整，以便使其使用更少的PGA。

3.5 PGA的自动建议特性
那么，如果我们需要调整pga_aggregate_target时，到底我们应该设置多大呢？oracle为了帮助我们确定这个参数的值，引入了一个新的视图：v$pga_target_advice。为了使用该视图，需要将初始化参数statistics_level设置为typical（缺省值）或all。
SQL> select
2 round(pga_target_for_estimate /(1024*1024)) "Target (M)",
3 estd_pga_cache_hit_percentage "Est. Cache Hit %",
4 round(estd_extra_bytes_rw/(1024*1024)) "Est. ReadWrite (M)",
5 estd_overalloc_count "Est. Over-Alloc"
6 from v$pga_target_advice
7 /

Target (M) Est. Cache Hit % Est. ReadWrite (M) Est. Over-Alloc
---------- ---------------- ------------------ ---------------
15 34 264 1
30 34 264 0
45 34 264 0
60 67 66 0
72 67 66 0
84 67 66 0
96 67 66 0
108 67 66 0
120 67 66 0
180 67 66 0
240 67 66 0
360 67 66 0
480 67 66 0
该输出告诉我们，按照系统目前的运转情况，我们pga设置的不同值所带来的不同效果。根据该输出，我们找到能使estd_overalloc_count为0的最小pga_aggregate_target的值。从这里可以看出，是30M。注意，随着我们增加pga的尺寸，estd_pga_cache_hit_percentage不断增加，同时estd_extra_bytes_rw(表示onepass、multipass读写的字节数)不断减小。从上面的结果，我们可以知道，将pga_aggregate_target设置为60MB是最合理的，因为即便将其设置为480MB，命中率也不会有所提高。

同时，我们知道v$tempstat里记录了读写临时表空间的数据块数量以及所花费的时间。这样，我们就可以结合v$pga_target_advice和v$tempstat这两个视图。可以得到每一种估计PGA值下的响应时间大致是多少，从而可以换一个角度来显示PGA的建议值：
SQL> SELECT 'PGA Aggregate Target' component,
2 ROUND (pga_target_for_estimate / 1048576) target_size,
3 estd_pga_cache_hit_percentage cache_hit_ratio,
4 ROUND ( ( ( estd_extra_bytes_rw / DECODE ((b.BLOCKSIZE * i.avg_blocks_per_io),0, 1,
5 (b.BLOCKSIZE * i.avg_blocks_per_io)))* i.iotime)/100 ) "response_time(sec)"
6 FROM v$pga_target_advice,
7 (SELECT /* AVG TIME TO DO AN IO TO TEMP TABLESPACE*/
8 AVG ( (readtim writetim) /
9 DECODE ((phyrds phywrts), 0, 1, (phyrds phywrts)) ) iotime,
10 AVG ( (phyblkrd phyblkwrt)/
11 DECODE ((phyrds phywrts), 0, 1, (phyrds phywrts))) avg_blocks_per_io
12 FROM v$tempstat) i,
13 (SELECT /* temp ts block size */ VALUE BLOCKSIZE
14 FROM v$parameter WHERE NAME = 'db_block_size') b;
COMPONENT TARGET_SIZE CACHE_HIT_RATIO response_time(sec)
-------------------- ----------- --------------- ------------------
PGA Aggregate Target 15 34 85
PGA Aggregate Target 30 34 85
PGA Aggregate Target 45 34 85
PGA Aggregate Target 60 68 21
PGA Aggregate Target 72 68 21
PGA Aggregate Target 84 68 21
PGA Aggregate Target 96 68 21
PGA Aggregate Target 108 68 21
PGA Aggregate Target 120 68 21
PGA Aggregate Target 180 68 21
PGA Aggregate Target 240 68 21
PGA Aggregate Target 360 68 21
PGA Aggregate Target 480 68 21
注意，每次我们调整了pga_aggregate_target参数以后，都应该在系统运行一、两天以后检查视图：v$sysstat、v$pgastat、v$pga_target_advice，以确定修改的值是否满足系统的需要。