Oracle - PGA自动管理原理深入分析及性能调整(3)

当前系统中有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