【IT168技术文档】

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。