【IT168技术文档】

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当然是不推荐我们修改这两个隐藏参数的。