上图中，当开始处理一条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的大小的工作区。
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:/test/pga_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 %' 5 and 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 51360768 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，命中率也不会有所提高。
