您的当前位置：首页正文

基于Oracle10g的PGA原理深入剖析及性能优化

2020-10-31 来源：个人技术集锦

栢图教育数据库实验室 www.btlinux.cn PGA

基于Oracle10g的PGA原理深入剖析

及性能优化

1 自动PGA内存管理原理

PGA是一个私有内存区域，服务器进程分配各种操作的内存比如sort、hash join以及bitmap merge。因此，PGA内存区域是有别于SGA的，甚至还有第三个内存区域UGA，它持有会话以及游标的状态信息。专用服务器进程在PGA中分配UGA，共享服务器进程将UGA放于SGA中，因为它必须得被所有共享服务器访问到。如果SGA把一个大池，共享服务器进程就会把UGA置于大池中。在Oracle10g中，共享池、大池、java池、流池、默认的缓冲池会被ASMM动态分配。

在9i之前的版本中，必须使用几个*_area_size参数去适应各种PGA内存区域的大小。在UNIX系统上，ORACLE DBMS是作为一个多进程架构来实现的，进行了内存密集型操作之后，PGA内存不能总是返回到操作系统。它徘徊在服务器进程的虚拟地址空间中，并可能造成分页。因此像这样已经分配了的内存也不能提供给其他服务器进程。同样PGA内存区域也没有实例范围的限制。因为每个服务器进程都可以为每个操作分配内存，甚至可以达到*_AREA_SIZE参数设置的范围，因此当存在数百个服务器进程时，实例范围内存消耗会变得非常大。还要注意的是*_area_size参数限制每个操作的范围，而不是每一个会话的范围。因为一个查询可能同时打开多个游标，并且每一个游标可能执行包含ORDER BY或者hash join等代价很高的SELECT语句，这些在手动PGA内存管理中是没有总的内存消耗限制的。为了解决这些缺点，从9i开始引入了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工作区了。

2 对PAT参数的必要说明

这个参数是一个目标值而不是一个绝对的限值。这意味着在高负荷下实际消耗的内存量是连续的或者至少间歇性的比目标值高。但是自动PGA管理会使得进程在所有可能的情况下释放内存，使得总的内存消耗快速的降到目标值之下。尤其是在PL/SQL中，会分配大量的内存，例如执行集合，索引表，则会永久的超出目标值。而sort的内存需求可以通过使用临时数据段降下来，但是PL/SQL对内存的需求则不行。

栢图教育数据库实验室 www.btlinux.cn PGA

3 研究PGA_AGGREGATE_TARGET

案例在10gR2上完成。

3.1 使用管道表函数创建一个大表

首先需要一个大表，大到足以导致磁盘在排序操作过程中溢出。创建一个管道表函数，这个函数可以被用来与包DBMS_RANDOM结合，以创建任意大小的随机数据表。由于管道函数返回一个集合类型，我们首先创建一个对象类型来接收行编号：

SQL> CREATE OR REPLACE TYPE row_nr_type AS OBJECT (row_nr number); 2 / Type created

管道表函数将会返回一个由row_nr_types组成的集合类型。嵌套表的元素存放对象。 SQL> CREATE OR REPLACE TYPE row_nr_type_tab AS TABLE OF row_nr_type; 2 / Type created

函数row_factory返回任意数据量的行数据，包含两个参数first_nr和last_nr，通过这两个参数控制返回多少行数据：

CREATE OR REPLACE FUNCTION row_factory(first_nr number, last_nr number) RETURN row_nr_type_tab PIPELINED AS / 当last_nr比first_nr大，row_factory将返回last_nr-first_nr+1行数据。其返回的结果与select rownum from table的结果非常相似，除了参数值以及不能控制从一个表中返回多少行数据之外。下面是一个例子：

SQL> select * from table(pga_research.row_factory(1,2)); ROW_NR ---------- 1 2

创建一个大表：100万行数据。这种方式使用管道表函数，在数据段上不会造成任何一致性读和物理读。也可以通过普通的方式创建，然后再清除缓存的方式实现，二者均可。

SQL> create table random_strings as

2 select dbms_random.string('a',128) as random_string 3 from table(pga_research.row_factory(1,1000000)) 4 nologging; Table created

row_nr row_nr_type:=NEW row_nr_type(0); FOR i IN first_nr .. last_nr LOOP

row_nr.row_nr:=i; PIPE ROW(row_nr); BEGIN

END LOOP; return;

END;

栢图教育数据库实验室 www.btlinux.cn PGA

3.2 v$sql_workarea_active

在会话级别监测PGA内存管理的一个好方法就是查询动态性能视图v$sql_workarea_active，包含如下的列： SQL> desc v$sql_workarea_active

Name Type Nullable Default Comments ---------------- ------------ -------- ------- -------- SQL_HASH_VALUE NUMBER Y SQL_ID VARCHAR2(13) Y WORKAREA_ADDRESS RAW(4) Y OPERATION_TYPE VARCHAR2(20) Y OPERATION_ID NUMBER Y POLICY VARCHAR2(6) Y SID NUMBER Y QCINST_ID NUMBER Y QCSID NUMBER Y ACTIVE_TIME NUMBER Y WORK_AREA_SIZE NUMBER Y EXPECTED_SIZE NUMBER Y ACTUAL_MEM_USED NUMBER Y MAX_MEM_USED NUMBER Y NUMBER_PASSES NUMBER Y TEMPSEG_SIZE NUMBER Y TABLESPACE VARCHAR2(30) Y SEGRFNO# NUMBER Y SEGBLK# NUMBER Y 写一个SHELL程序监测PGA工作区域的使用情况。该Perl程序在v$sql_workarea_active上每秒执行一个select并将结果打印在屏幕上。除了会话标识符（v$session.sid）、当前和最大的工作区域的大小、临时数据段的大小之外，该查询同样也检索时间戳。所有大小都是以MB为单位的。

##################################################################### ## sql_workarea_active.sh ## ##################################################################### #!/bin/ksh name=admin/admin@ORCL sqlplus -s $name <spool /u01/oracle/sql_workarea_active.txt

select rpad('sid',5,' '),rpad('time',20,' '),rpad('work_area_size_mb',18,' '),rpad('max_mem_used_mb',18,' '),rpad('number_passes',13,' '),rpad('tempseg_size_mb',20,' ') from dual;

select rpad('-',5,'-'),rpad('-',20,'-'),rpad('-',18,'-'),rpad('-',18,'-'),rpad('-',13,'-'),rpad('-',20,'-') from dual; spool off exit

栢图教育数据库实验室 www.btlinux.cn PGA

栢图教育数据库实验室

while true do

sqlplus -s $name <col work_area_size_mb format 99999.9 col max_mem_used_mb format 99999.9 col number_passes format 99999.9 col tempseg_size_mb format 99999.9

www.btlinux.cn PGA

spool /u01/oracle/sql_workarea_active.txt append SELECT sid,

to_char(sysdate, 'mi:ss') time,

round(work_area_size / 1048576, 1) work_area_size_mb, round(max_mem_used / 1048576, 1) max_mem_used_mb, number_passes,

nvl(tempseg_size / 1048576, 0) tempseg_size_mb FROM v\\$sql_workarea_active ORDER BY sid; spool off exit ! sleep 1 done

现在有了一个大表和监测工具，可以运行一些实际的测试了。因为我是唯一的使用该实例的测试人员，可以假设不管是否设置了PGA_AGGREGATE_TARGET，全部内存多我是可见的。表的段大约为150M：

SQL> select sum(bytes)/1024/1024||'MB' as TBMB from user_extents where segment_name=upper('random_strings'); TBMB ------------------------------------------ 152MB

设置PGA_AGGREGATE_TARGET为256M对于一个在内存中的排序是绰绰有余的。因此： SQL> alter system set pga_aggregate_target=256M; System altered 启动监测。

[oracle@localhost oracle]$ sh sql_workarea_active.sh

sid time work_area_size_mb max_mem_used_mb number_passes tempseg_size_mb ----- -------------------- ------------------ ------------------ ------------- --------------------

这个shell脚本直到分配了一个或者多个工作区之后才会显示数据。使用如下的脚本来作为模拟测试程序： C:\\Users\\Administrator>sqlplus admin/admin@ORCL

栢图教育数据库实验室 www.btlinux.cn PGA

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SELECT sid, server FROM v$session WHERE audsid=userenv('sessionid'); SID SERVER ---------- --------- 144 DEDICATED SQL> set timing on

SQL> set autotrace traceonly statistics

SQL> SELECT * FROM random_strings ORDER BY 1; 1000000 rows selected. Elapsed: 00:00:50.31 Statistics

---------------------------------------------------------- 361 recursive calls 7 db block gets 18905 consistent gets 35829 physical reads 0 redo size

135867024 bytes sent via SQL*Net to client 733703 bytes received via SQL*Net from client 66668 SQL*Net roundtrips to/from client 5 sorts (memory) 1 sorts (disk) 1000000 rows processed 以下是shell脚本输出的结果：出现了可用内存不足并且排序溢出到磁盘的情况，该会话执行了一次one-pass排序，因为它只获得了51.2M工作区大小，该语句在50s内完成，临时数据段增长到133M，比该表的数据段大小稍微小一点。显然不管是否设置了PAT，整个内存对会话是不可见的。

[oracle@localhost oracle]$ sh sql_workarea_active.sh

sid time work_area_size_mb max_mem_used_mb number_passes tempseg_size_mb ----- -------------------- ------------------ ------------------ ------------- -------------------- 144 39:34 37.6 31.4 .0 .0 144 39:35 51.2 51.2 .0 6.0 144 39:36 51.2 51.2 .0 33.0 144 39:38 51.2 51.2 .0 58.0

144 39:39 17.9 51.2 1.0 79.0 --开始溢出 144 39:40 21.4 51.2 1.0 96.0 144 39:41 25.8 51.2 1.0 117.0 144 39:42 2.9 51.2 1.0 133.0 144 39:43 2.9 51.2 1.0 133.0 144 39:45 2.9 51.2 1.0 133.0

栢图教育数据库实验室 www.btlinux.cn PGA

栢图教育数据库实验室

„„

www.btlinux.cn PGA

144 40:23 2.9 51.2 1.0 133.0 --溢出到最大实际上影响自动PGA内存管理的是一些隐藏参数。分别是：_PGA_MAX_SIZE、_SMM_MAX_SIZE和_SMM_PX_MAX_SIZE。其中，_PGA_MAX_SIZE是以字节为单位的，其余两个是以千字节为单位的，这些参数的值通入以下查询可以获得：

SQL> conn sys/orcl@ORCL as sysdba Connected.

SQL> col name format a20 SQL> col value format 99999999 heading \"Value (KB)\" SQL> col description format a45 word_wrapped SQL> set verify off SQL> set lines 83 SQL> SELECT x.ksppinm name, 2 CASE WHEN x.ksppinm like '%pga%' THEN to_number(y.ksppstvl)/1024 3 ELSE to_number(y.ksppstvl) 4 END AS value, 5 x.ksppdesc description 6 FROM x$ksppi x, x$ksppcv y 7 WHERE x.inst_id = userenv('Instance') 8 AND y.inst_id = userenv('Instance') 9 AND x.indx = y.indx 10 AND x.ksppinm IN ('pga_aggregate_target', '_pga_max_size', '_smm_max_size', '_smm_px_max_size'); NAME Value (KB) DESCRIPTION -------------------- ---------- ------------------- ----------------------------------------------------------------------------------- pga_aggregate_target 262144 Target size for the aggregate PGA memory consumed by the instance _pga_max_size 204800 Maximum size of the PGA memory for one process _smm_max_size 52428 maximum work area size in auto mode (serial) _smm_px_max_size 131072 maximum work area size in auto mode (global) 分别给PAT设置不同的参数进行测试，研究PAT的变化对于隐藏参数的影响，结果如下，过程省略： PAT 10M 32M 64M _PGA_MAX_SIZE 200M（2000%） 200M（625%） 200M（320%） _SMM_MAX_SIZE 2M（1%） 6.4M（3.2%） 12.8M（6.4%） 25M（12%） 51M（25%） 100M（50%） 102M（50%） 205M（50%） 208M（50%） 240M（50%） 240M（50%） _SMM_MAX_SIZE 20% 20% 20% 20% 20% 19.5% 10% 10% 6.8% 5.8% 2.9% _SMM_PX_MAX_SIZE （占PAT的百分比） 5M（50%） 16M（50%） 32M（50%） 64M（50%） 128M（50%） 256M（50%） 512M（50%） 1G（50%） 1536M（50%） 2G（50%） 4G（50%）（占PAT百分比）（占_PGA_MAX_SIZE的百分比）（占PAT的百分比） 128M 200M（156%） 256M 200M（78%） 512M 200M（39%） 1G 2G 3G 4G 8G 204（20%） 410（20%） 416（13.5%） 480（11.7%） 480（5.8%）栢图教育数据库实验室 www.btlinux.cn PGA

栢图教育数据库实验室 www.btlinux.cn PGA

3.3 _PGA_MAX_SIZE

参数_PGA_MAX_SIZE限制了单个进程所占所有工作区的最大大小。  当PAT值低于1GB时，该参数为200M  当PAT在1G~2G之间时，该参数为PAT的20%

 当PAT超过2G时，随着PAT的增加，该参数会不断的增长，但是以一个较低的比例增长，这样这个参数

的值就会低于PAT的值的20%

 限制_PGA_MAX_SIZE的值在480M时会对4G的PAT产生影响；

 当PAT超过4G时，增加PAT的值将不会使得_PGA_MAX_SIZE的值超过480M  在Oracle9i中，_PGA_MAX_SIZE限制在200M内

和PAT一样，_PGA_MAX_SIZE也是一个动态参数，可以通过alter system命令修改。通过改变_PGA_MAX_SIZE增加_SMM_MAX_SIZE，和修改PAT的方式类似。然而，当PAT大于等于512M时，_SMM_MAX_SIZE为_PGA_MAX_SIZE的50%这一规则对于手动修改_PGA_MAX_SIZE是不成立的。

下面的例子说明了手动修改_PGA_MAX_SIZE使得_PGA_MAX_SIZE增大到超过480M的限制： SQL> alter system set \"_PGA_MAX_SIZE\"=500M; System altered.

SQL> SELECT x.ksppinm name,

2 CASE WHEN x.ksppinm like '%pga%' THEN to_number(y.ksppstvl)/1024 3 ELSE to_number(y.ksppstvl) 4 END AS value, 5 x.ksppdesc description 6 FROM x$ksppi x, x$ksppcv y 7 WHERE x.inst_id = userenv('Instance') 8 AND y.inst_id = userenv('Instance') 9 AND x.indx = y.indx 10 AND x.ksppinm IN ('pga_aggregate_target', '_pga_max_size', '_smm_max_size', '_smm_px_max_size');

NAME Value (KB) DESCRIPTION -------------------- ---------- --------------------------------------------- pga_aggregate_target 262144 Target size for the aggregate PGA memory consumed by the instance _pga_max_size 512000 Maximum size of the PGA memory for one process _smm_max_size 52428 maximum work area size in auto mode (serial) _smm_px_max_size 131072 maximum work area size in auto mode (global) 通过增加_PGA_MAX_SIZE，工作区有效大小也可以增加，而不用扩展整个实例的可用内存。内存有限可能会避免一些分页活动。只要很少的会话同时请求大工作区，即对PGA的内存的争用较低，这可能会涉及大数据集排序操作的相应时间更短。通过修改_PGA_MAX_SIZE，_SMM_MAX_SIZE的值可动态设置的比正常限制范围240M大一些。

3.4 _SMM_MAX_SIZE

参数_SMM_MAX_SIZE限制了单个进程一个单独工作区的最大大小。  当PAT的值低于512M的时候，_SMM_MAX_SIZE为PAT的20%

 当PAT的值大于等于512M时，_SMM_MAX_SIZE总是_PGA_MAX_SIZE值的50%  在Oracle9i中，_SMM_MAX_SIZE限制在100M内。

以下为当给定参数有效时，一个会话同时有两个活跃工作区的例子：

栢图教育数据库实验室 www.btlinux.cn PGA

栢图教育数据库实验室

www.btlinux.cn SQL> alter system set pga_aggregate_target=1500M; System altered.

SQL> alter system set \"_PGA_MAX_SIZE\"=300M; System altered.

SQL> SELECT x.ksppinm name,

10 AND x.ksppinm IN ('pga_aggregate_target', '_pga_max_size', '_smm_max_size', '_smm_px_max_size');

NAME Value (KB) DESCRIPTION -------------------- ---------- --------------------------------------------- pga_aggregate_target 1536000 Target size for the aggregate PGA memory consumed by the instance _pga_max_size 307200 Maximum size of the PGA memory for one process _smm_max_size 153600 maximum work area size in auto mode (serial) _smm_px_max_size 768000 maximum work area size in auto mode (global)

SQL> set autotrace traceonly statistics SQL> SELECT /*+ use_hash(a,b) */ a.*,b.* FROM random_strings a,random_strings b where a.random_string=b.random_string ORDER BY 1; 1000000 rows selected. Statistics

---------------------------------------------------------- 394 recursive calls 1107 db block gets 37758 consistent gets 48670 physical reads 0 redo size 264867083 bytes sent via SQL*Net to client 733703 bytes received via SQL*Net from client 66668 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 1000000 rows processed

SQL> SELECT sid,

2 to_char(sysdate, 'mi:ss') time, 3 sql_hash_value, 4 operation_type,

栢图教育数据库实验室 www.btlinux.cn PGA

PGA

栢图教育数据库实验室 www.btlinux.cn PGA

5 round(work_area_size / 1048576, 1) work_area_size_mb, 6 round(max_mem_used / 1048576, 1) max_mem_used_mb, 7 number_passes,

8 nvl(tempseg_size / 1048576, 0) tempseg_size_mb 9 FROM v$sql_workarea_active 10 ORDER BY sid;

SID TIME HASH_VALUE TYPE WORK_AREA_SIZE_MB MAX_MEM_USED_MB NUMBER_PASSES TEMPSEG_SIZE_MB ---------- ----- -------------- --------------- ------------------------------- -------------------------------- ---------------------- --------------- 144 30:19 3715844002 HASH-JOIN 157.2 157.7 0 138 144 30:19 3715844002 SORT (v2) 99.3 99.3 0 94 ......

144 30:28 3715844002 HASH-JOIN 157.2 157.7 0 138 144 30:28 3715844002 SORT (v2) 99.3 99.3 1 166

两个工作区相加157.2+99.3=256.5超过了_SMM_MAX_SIZE但是没有超过_PGA_MAX_SIZE。

3.5 _SMM_PX_MAX_SIZE

_SMM_PX_MAX_SIZE始终设置为PAT的50%，对于_SMM_PX_MAX_SIZE没有任何限制。在9i中，_SMM_PX_MAX_SIZE为PAT的30%

这个隐藏参数适用于并行执行，一个重要的概念是：一个n度并行全表全表扫描是在n个并行执行进程中切分工作，这样每个进程处理的数据量大约相当于全部数据量的n分之一。数字n称作DOP（并行度）。

每个并行执行进程分配各自的工作区，由于每个进程处理数据的一小部分，在并行模式下单独进程所需的工作区比在串行模式下一个单独的工作区要小。

_SMM_PX_MAX_SIZE对最大工作区大小做出了额外的限制，它被用于并行执行进程中。每个PX进程都不可能使用超出_SMM_PX_MAX_SIZE/dop的内存。_SMM_MAX_SIZE对每个进程的限制对PX是有效的，这样可用内存就是_SMM_MAX_SIZE和_SMM_PX_MAX_SIZE/dop中较小的一个，完全在内存中进行排序，必须满足以下两个条件：

 每个PX进程的数据量必须低于_SMM_MAX_SIZE。  每个PX进程的数据量必须低于_SMM_PX_MAX_SIZE/dop 做一个测试，研究并行执行情况下PGA的使用情况：

从之前的测试结果可以看出，select语句大概有133MB的数据量，因此，当DOP为4时，每个PX进程需要的工作区大约为133M，对于一个最优排序来说，除以4大约为34M，进位大概为40M，这使得其在PX进程间允许一定的数据量波动，我们设置_SMM_MAX_SIZE=40960（单位为KB），为了避免PAT或者_SMM_PX_MAX_SIZE的限制，设置这两个参数为_SMM_MAX_SIZE的DOP倍或者160M。

SQL> alter system set pga_aggregate_target=160M; System altered.

SQL> alter system set \"_smm_px_max_size\"=163840; --并行执行全部工作区的大小 System altered.

SQL> alter system set \"_smm_max_size\"=40960; --单个进程一个单独工作区的最大大小 System altered.

SQL> alter system set \"_pga_max_size\"=200M; --单个进程所占所有工作区的最大大小 System altered.

SQL> col name format a20

SQL> col value format 99999999 heading \"Value (KB)\"

栢图教育数据库实验室 www.btlinux.cn PGA

栢图教育数据库实验室

www.btlinux.cn SQL> col description format a45 word_wrapped

SQL> set verify off SQL> set lines 83

SQL> SELECT x.ksppinm name,

10 AND x.ksppinm IN ('pga_aggregate_target', '_pga_max_size', '_smm_max_size', '_smm_px_max_size');

NAME Value (KB) DESCRIPTION -------------------- ---------- --------------------------------------------- pga_aggregate_target 163840 Target size for the aggregate PGA memoryconsumed by the instance _pga_max_size 204800 Maximum size of the PGA memory for one process _smm_max_size 40960 maximum work area size in auto mode (serial) _smm_px_max_size 163840 maximum work area size in auto mode (global) 进行并行测试，第一步启动监测：

[oracle@localhost oracle]$ sh sql_workarea_active.sh

sid time work_area_size_mb max_mem_used_mb number_passes tempseg_size_mb ----- -------------------- ------------------ ------------------ ------------- -------------------- 执行测试语句：dop=4 SQL> set timing on SQL> set autotrace traceonly statistics SQL> SELECT /*+ full(r) parallel(r,4) */ * FROM random_strings r ORDER BY 1;

1000000 rows selected.

Elapsed: 00:00:51.11 Statistics

---------------------------------------------------------- 376 recursive calls 4 db block gets 19066 consistent gets 18881 physical reads 680 redo size

135867024 bytes sent via SQL*Net to client 733703 bytes received via SQL*Net from client 66668 SQL*Net roundtrips to/from client

栢图教育数据库实验室 www.btlinux.cn PGA

PGA

栢图教育数据库实验室

11 sorts (memory) 0 sorts (disk) 1000000 rows processed

www.btlinux.cn PGA

[oracle@localhost oracle]$ sh sql_workarea_active.sh

sid time work_area_size_mb max_mem_used_mb number_passes tempseg_size_mb ----- -------------------- ------------------ ------------------ ------------- -------------------- 137 22:00 8.2 7.2 .0 .0 140 22:00 6.7 6.2 .0 .0 146 22:00 6.7 6.1 .0 .0 151 22:00 8.2 7.1 .0 .0

137 22:02 21.2 18.9 .0 .0 140 22:02 17.5 16.2 .0 .0 146 22:02 17.5 16.0 .0 .0 151 22:02 21.2 18.3 .0 .0

137 22:03 37.6 31.4 .0 .0 140 22:03 31.1 26.7 .0 .0 146 22:03 31.1 26.5 .0 .0 151 22:03 31.1 30.3 .0 .0

137 22:04 36.6 36.6 .0 .0 140 22:04 31.2 31.2 .0 .0 146 22:04 30.9 30.9 .0 .0 151 22:04 35.3 35.3 .0 .0 ......

137 22:17 36.6 36.6 .0 .0 140 22:17 31.2 31.2 .0 .0 146 22:17 30.9 30.9 .0 .0 ...... 137 22:30 36.6 36.6 .0 .0 140 22:30 31.2 31.2 .0 .0 ......

137 22:40 36.6 36.6 .0 .0 ......始终无溢出

可以看出，执行了一个最优排序，相应时间为51S。DOP减半后只有两个进程共享工作负载，以下是结果： SQL> SELECT /*+ full(r) parallel(r,2) */ * FROM random_strings r ORDER BY 1; 1000000 rows selected. Elapsed: 00:00:55.04 Statistics

---------------------------------------------------------- 459 recursive calls

栢图教育数据库实验室 www.btlinux.cn PGA

栢图教育数据库实验室

13 db block gets 18986 consistent gets 35833 physical reads 632 redo size

www.btlinux.cn PGA

135867024 bytes sent via SQL*Net to client 733703 bytes received via SQL*Net from client 66668 SQL*Net roundtrips to/from client 7 sorts (memory) 2 sorts (disk) 1000000 rows processed

[oracle@localhost oracle]$ sh sql_workarea_active.sh

141 51:16 25.7 23.9 .0 .0 147 51:16 31.1 26.3 .0 .0

141 51:17 37.6 36.3 .0 .0 147 51:17 40.0 40.0 .0 16.0

141 51:18 40.0 40.0 .0 12.0 147 51:18 40.0 40.0 .0 40.0

141 51:19 40.0 40.0 .0 40.0 147 51:19 40.0 40.0 .0 40.0

141 51:20 40.0 40.0 .0 43.0

147 51:20 40.0 40.0 1.0 54.0 -开始溢出超过40M的时候才会造成临时数据段损耗，所以，一定是临时数据段使用超过40M才算做溢出 141 51:21 40.0 40.0 1.0 54.0 147 51:21 14.9 40.0 1.0 66.0

141 51:22 1.2 40.0 1.0 63.0

147 51:22 1.2 40.0 1.0 70.0 --最大溢出 ......

147 52:08 1.2 40.0 1.0 70.0

以上测试看出相应时间增加到55S，因为DOP=2时，每个进程大约需要70M的工作区大小，但是只有40M可用，导致one-pass排序并且溢出到磁盘。

如果在DOP=4时，_SMM_PX_MAX_SIZE小于133M/4的话，同样会导致溢出。以下是测试的结果： SQL> alter system set pga_aggregate_target=160M;

栢图教育数据库实验室 www.btlinux.cn PGA

栢图教育数据库实验室

System altered.

www.btlinux.cn PGA

SQL> alter system set \"_smm_px_max_size\"=122880; System altered.

SQL> alter system set \"_smm_max_size\"=40960; System altered.

SQL> col name format a20

SQL> col value format 99999999 heading \"Value (KB)\" SQL> col description format a45 word_wrapped SQL> set verify off SQL> set lines 83

SQL> SELECT x.ksppinm name,

10 AND x.ksppinm IN ('pga_aggregate_target', '_pga_max_size', '_smm_max_size', '_smm_px_max_size');

NAME Value (KB) DESCRIPTION -------------------- ---------- --------------------------------------------- pga_aggregate_target 163840 Target size for the aggregate PGA memory consumed by the instance _pga_max_size 204800 Maximum size of the PGA memory for one process _smm_max_size 40960 maximum work area size in auto mode (serial) _smm_px_max_size 122880 maximum work area size in auto mode (global) 这次，_SMM_PX_MAX_SIZE是限制因素。 [oracle@localhost oracle]$ sh sql_workarea_active.sh

sid time work_area_size_mb max_mem_used_mb number_passes tempseg_size_mb ----- -------------------- ------------------ ------------------ ------------- -------------------- 136 11:19 2.6 2.3 .0 .0 137 11:19 2.6 2.6 .0 .0 140 11:19 3.1 2.6 .0 .0 147 11:19 2.6 2.3 .0 .0

136 11:20 14.5 13.9 .0 .0 137 11:20 17.5 16.0 .0 .0 140 11:20 17.5 16.5 .0 .0 147 11:20 14.5 14.1 .0 .0

136 11:21 25.7 25.3 .0 .0 137 11:21 30.0 29.0 .0 .0

栢图教育数据库实验室 www.btlinux.cn PGA

140 11:21 30.0 30.0 .0 1.0 147 11:21 25.7 25.6 .0 .0

136 11:23 30.0 26.2 .0 .0 137 11:23 30.0 30.0 .0 10.0 140 11:23 30.0 30.0 .0 30.0 147 11:23 30.0 26.5 .0 .0

136 11:24 30.0 29.8 .0 .0 137 11:24 30.0 30.0 .0 30.0 140 11:24 30.0 30.0 .0 30.0 147 11:24 30.0 30.0 .0 16.0

136 11:25 30.0 30.0 .0 30.0 137 11:25 30.0 30.0 .0 30.0 140 11:25 30.0 30.0 .0 30.0 147 11:25 30.0 30.0 .0 30.0

136 11:26 1.2 30.0 1.0 31.0 --开始溢出超过120M/4=30M的时候才会造成临时数据段损耗，所以，一定是临时数据段使用超过30M才算做溢出，一直溢出到40M为止。因为有_SMM_MAX_SIZE=40M的限制 137 11:26 1.2 30.0 1.0 35.0 140 11:26 1.2 30.0 1.0 37.0 147 11:26 1.2 30.0 1.0 31.0 ......

136 11:50 1.2 30.0 1.0 31.0 140 11:50 1.2 30.0 1.0 37.0 147 11:50 1.2 30.0 1.0 31.0 ...... 140 12:02 1.2 30.0 1.0 37.0 147 12:02 1.2 30.0 1.0 31.0 ...... 140 12:14 1.2 30.0 1.0 37.0 此时对于并行度的设定值已经有了答案：

 如果PAT=_SMM_PX_MAX_SIZE——串行执行时发生one-pass时的临时数据段耗用量 / DOP =

_smm_max_size=每个并行进程的工作区量；

 如果PAT>_SMM_PX_MAX_SIZE——_SMM_PX_MAX_SIZE / DOP = 单并发进程工作区发生溢出的临界

值，但是溢出不会超过_smm_max_size

4 通过动态性能视图验证PGA原理 4.1 常见的关于PGA的动态性能视图测试

编写如下的程序，来测试：

create table pga_test_tab as select a.* from pga_test a,pga_test b where 1=2; --创建测试用表

栢图教育数据库实验室 www.btlinux.cn PGA

栢图教育数据库实验室

www.btlinux.cn PGA

create or replace package PGA_RESEARCH authid current_user is -- Author : liujing

-- Purpose : 研究PGA的原理机制

FUNCTION row_factory(first_nr number, last_nr number) RETURN row_nr_type_tab PIPELINED;

procedure pga_value(v_sid in number, v_pat in varchar2 default '0'); procedure pga_sql(v_sql in varchar2);

procedure pga_test(v_pat varchar2,v_sql varchar2); end PGA_RESEARCH;

create or replace package body PGA_RESEARCH is -- Author : liujing

-- Purpose : 研究PGA的原理机制

FUNCTION row_factory(first_nr number, last_nr number) RETURN row_nr_type_tab PIPELINED AS

row_nr row_nr_type := NEW row_nr_type(0); BEGIN

FOR i IN first_nr .. last_nr LOOP row_nr.row_nr := i; PIPE ROW(row_nr); END LOOP; return; END;

procedure pga_value(v_sid in number, v_pat in varchar2 default '0') is --监控SQL语句所使用的SQL工作区的脚本 v_name varchar2(100); v_value number(10); cursor csr_pga(v_sid number) is select a.name, b.value from v$statname a, v$sesstat b where a.statistic# = b.statistic# and b.sid = v_sid and a.name like '%ga %' order by a.name; begin

dbms_output.put_line(rpad('NAME', 30, ' ') || ' ' || lpad('VALUE', 20, ' ')); dbms_output.put_line(rpad('-', 30, '-') || ' ' || lpad('-', 20, '-')); open csr_pga(v_sid); loop

fetch csr_pga

栢图教育数据库实验室 www.btlinux.cn PGA

栢图教育数据库实验室

www.btlinux.cn into v_name, v_value; exit when(csr_pga%notfound);

dbms_output.put_line(rpad(v_name, 30, ' ') || ' ' || lpad(to_char(v_value), 20, ' ')); end loop; close csr_pga; if v_pat <> '0' then

execute immediate 'alter system set pga_aggregate_target=' || v_pat; end if; end;

procedure pga_sql(v_sql in varchar2) is --监控session所使用的PGA和UGA的大小 v_hv number; v_opt_typ varchar2(20); v_lst_exec varchar2(10); v_used_mb number; v_est_opt_mb number; v_est_onepass_mb number; v_lst_tmp_size number; cursor csr_sql(v_hv number) is SELECT a.operation_type,

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 INTO v_opt_typ, v_used_mb, v_est_opt_mb, v_est_onepass_mb, v_lst_exec, v_lst_tmp_size FROM v$sql_workarea a, v$sql b WHERE a.hash_value = b.hash_value and a.hash_value = v_hv and rownum=1 order by a.operation_id desc; begin

select hash_value into v_hv from v$sql where sql_text = v_sql and rownum=1 order by first_load_time desc; dbms_output.put_line(rpad('opt_typ', 20, ' ') || ' ' || lpad('used_mb', 20, ' ') || ' ' || lpad('est_opt_mb', 20, ' ') || ' ' || lpad('est_onepass_mb', 20, ' ') || ' ' || rpad('v_lst_exec', 20, ' ') || ' ' || lpad('v_lst_tmp_size', 20, ' ')); dbms_output.put_line(rpad('-', 20, '-') || ' ' || lpad('-', 20, '-') || ' ' ||

栢图教育数据库实验室 www.btlinux.cn PGA

PGA

栢图教育数据库实验室 www.btlinux.cn lpad('-', 20, '-') || ' ' || lpad('-', 20, '-') || ' ' || rpad('-', 20, '-') || ' ' || lpad('-', 20, '-')); open csr_sql(v_hv); loop

fetch csr_sql

into v_opt_typ, v_used_mb, v_est_opt_mb, v_est_onepass_mb, v_lst_exec, v_lst_tmp_size; exit when(csr_sql%notfound);

dbms_output.put_line(rpad(v_opt_typ, 20, ' ') || ' ' || lpad(v_used_mb, 20, ' ') || ' ' || lpad(v_est_opt_mb, 20, ' ') || ' ' || lpad(v_est_onepass_mb, 20, ' ') || ' ' || rpad(v_lst_exec, 20, ' ') || ' ' || lpad(v_lst_tmp_size, 20, ' ')); end loop; close csr_sql; end;

procedure pga_process(v_sid number) is --监控进程所使用的PGA的大小 v_pga_used_mem number; v_pga_alloc_mem number; v_pga_max_mem number; begin

SELECT a.pga_used_mem \"PGA Used\ a.pga_alloc_mem \"PGA Alloc\ a.pga_max_mem \"PGA Max\" into v_pga_used_mem, v_pga_alloc_mem, v_pga_max_mem FROM v$process a, v$session b where a.addr = b.paddr and b.sid = v_sid; dbms_output.put_line(lpad('PGA Used', 10, ' ') || ' ' || lpad('PGA Alloc', 10, ' ') || ' ' || lpad('PGA Max', 10, ' ')); dbms_output.put_line(lpad('-', 10, '-') || ' ' || lpad('-', 10, '-') || ' ' || lpad('-', 10, '-')); dbms_output.put_line(lpad(v_pga_used_mem, 10, ' ') || ' ' || lpad(v_pga_alloc_mem, 10, ' ') || ' ' || lpad(v_pga_max_mem, 10, ' ')); end;

procedure pga_test(v_pat varchar2, v_sql varchar2) is --测试用的主程序 v_sid number; begin

select sid into v_sid from v$mystat where rownum = 1; dbms_output.put_line('SID');

栢图教育数据库实验室 www.btlinux.cn PGA

PGA

栢图教育数据库实验室

dbms_output.put_line('----------------'); dbms_output.put_line(to_char(v_sid));

www.btlinux.cn PGA

dbms_output.put_line('=======================programme segment1==========================='); pga_value(v_sid, v_pat);

dbms_output.put_line('=======================programme segment2==========================='); execute immediate 'truncate table pga_test_tab'; execute immediate v_sql; commit; pga_sql(v_sql);

dbms_output.put_line('=======================programme segment3==========================='); pga_value(v_sid);

dbms_output.put_line('======================programme segment4============================'); pga_process(v_sid);

dbms_output.put_line('=====================programme segment5============================='); end;

end PGA_RESEARCH;

首先将PAT设置为60M进行如下测试：

SQL> exec PGA_RESEARCH.pga_test('60M','insert into pga_test_tab select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8'); SID ---------------- 141 --会话ID

=======================programme segment1=========================== NAME VALUE ------------------------------ -------------------- session pga memory 1109588 session pga memory max 15920724 session uga memory 549412 session uga memory max 14100460 =======================programme segment2=========================== opt_typ used_mb est_opt_mb est_onepass_mb v_lst_exec v_lst_tmp_size -------------------- -------------------- ---------------------------------------- -------------------- -------------------- SORT (v2) 12.0419921875 59.9765625 2.6328125 1 PASS 56623104

Used MB——最后一次执行的内存（SQL工作区）耗用。

Est Opt MB_无论从优化器统计或最后一次实际执行，估计操作完全在内存中的所需工作区执行（最佳执行）的大小

Est OnePass MB——无论从优化器统计或最后一次实际执行，估计操作在内存中溢出一次的所需工作区执行的大小,当前的案例就是溢出了一次。

=======================programme segment3=========================== NAME VALUE ------------------------------ -------------------- session pga memory 1175124 session pga memory max 15920724 session uga memory 614876

栢图教育数据库实验室 www.btlinux.cn PGA

session uga memory max 14100460

为该session分配的PGA为15920724个字节(15M)，其中UGA为14100460个字节，大约14M。

执行完测试语句以后，oracle就把该session的PGA空间回收了（PGA从15920724下降到1175124而UGA从14100460下降到614876

该SQL语句所分配的工作区约为12MB，这个值就是20%*pga_aggregate_target（60M*0.2）

而SQL工作区占整个PGA大小大约为80%（12/15），从这个方面也可以看出，SQL工作区是PGA中最占空间、也是最重要的部分。

======================programme segment4============================ PGA Used PGA Alloc PGA Max ---------- ---------- ---------- 1144741 4400361 66164897

=====================programme segment5============================= PL/SQL procedure successfully completed.

继续按照当前的测试，这个SQL最少要60M大小的工作区才能不溢出，按照前面的表格，PAT设置到310M就可以达到目的：

SQL> exec PGA_RESEARCH.pga_test('310M','insert into pga_test_tab select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8'); SID ---------------- 148

=======================programme segment1=========================== NAME VALUE ------------------------------ -------------------- session pga memory 1240660 session pga memory max 66973268 session uga memory 745804 session uga memory max 65227844 =======================programme segment2=========================== opt_typ used_mb est_opt_mb est_onepass_mb v_lst_exec v_lst_tmp_size -------------------- -------------------- ---------------------------------------- -------------------- -------------------- SORT (v2) 60.70703125 68.2958984375 2.794921875 OPTIMAL

达到了最佳执行的目的。没有溢出。验证了上面的表格正确性。

=======================programme segment3=========================== NAME VALUE ------------------------------ -------------------- session pga memory 1896020 session pga memory max 67169876 session uga memory 745804 session uga memory max 65947948

为该session分配的PGA为67169876个字节(64M)，其中UGA为65947948个字节，大约61M。

执行完测试语句以后，oracle就把该session的PGA空间回收了（PGA从67169876下降到1896020而UGA从65947948下降到745804

该SQL语句所分配的工作区约为60.7MB，这个值就是20%*pga_aggregate_target（310M*0.195）

而SQL工作区占整个PGA大小大约为95%，从这个方面也可以看出，SQL工作区是PGA中最占空间、也是

栢图教育数据库实验室 www.btlinux.cn PGA

栢图教育数据库实验室

最重要的部分。

www.btlinux.cn PGA

======================programme segment4============================ PGA Used PGA Alloc PGA Max ---------- ---------- ----------

1081561 67118313 67118313 --针对整个实例的参数

=====================programme segment5============================= PL/SQL procedure successfully completed.

4.2 其他的关于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)\ --SQL工作区最优的下限（单位：K） 3 (high_optimal_size + 1)/1024 \"High (K)\ -- SQL工作区最优的上限（单位：K）

4 optimal_executions \"Optimal\ --实例启动以来，在SQL工作区的最优区间内，最优执行的SQL工作区的数量

5 onepass_executions \"1-Pass\ --实例启动以来，在SQL工作区的最优区间内，ONEPASS的SQL工作区的数量

6 multipasses_executions \">1 Pass\" --实例启动以来，在SQL工作区的最优区间内，MULTIPASSES PASS的SQL工作区的数量

7 from v$sql_workarea_histogram 8 where total_executions <> 0;

Low (K) High (K) Optimal 1-Pass >1 Pass ---------- ---------- ---------- ---------- ---------- 2 4 31237 0 0 64 128 79 0 0 128 256 9 0 0 256 512 12 0 0 512 1024 619 0 0 1024 2048 42 1 0 2048 4096 154 0 0 32768 65536 0 30 0 65536 131072 4 1 0

9 rows selected.

结果类似如上所示，我们可以看到整个系统所需要的PGA的内存大小主要集中在什么范围里面。

另外，我们可以将上面的查询语句改写一下，以获得optimal、onepass、multipass执行次数的百分比，很明显，optimal所占的百分比越高越好，如果onepass和multipass占的百分比很高，就需要增加pga_aggregate_target的值了，或者调整SQL语句以使用更少的PGA区。 SQL> select

2 optimal_count \"Optimal\

栢图教育数据库实验室 www.btlinux.cn PGA

栢图教育数据库实验室

4 onepass_count \"OnePass\

www.btlinux.cn PGA

3 round(optimal_count * 100 / total,2) \"Optimal %\ 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);

Optimal Optimal % OnePass Onepass % MultiPass Multipass % ---------- ---------- ---------- ---------- ---------- ----------- 32431 99.9 32 .1 0 0

v$pgastat则提供了有关PGA使用的整体的概括性的信息。 SQL> select * from v$pgastat;

NAME VALUE UNIT ---------------------------------------------------------------- ---------- ------------ aggregate PGA target parameter 325058560 bytes --PAT大小

aggregate PGA auto target 254103552 bytes --PAT可以动态调整的部分 global memory bound 65011712 bytes --一个SQL工作区的最大尺寸 total PGA inuse 42830848 bytes total PGA allocated 94473216 bytes --PGA共计分配了多少 maximum PGA allocated 160068608 bytes total freeable PGA memory 7667712 bytes process count 24 max processes count 31 PGA memory freed back to OS 907083776 bytes total PGA used for auto workareas 110592 bytes maximum PGA used for auto workareas 63766528 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 536576 bytes over allocation count 0 -- bytes processed 3343619072 bytes extra bytes read/written 1993229312 bytes cache hit percentage 62.65 percent recompute count (total) 55987 19 rows selected.

从结果可以看出，第一行表示pga_aggregate_target设置为310M。PGA的一部分被用于无法动态调整的部分，比如UGA中的“session相关的信息”等。而PGA内存的剩下部分则是可以动态调整的，由“aggregate PGA auto target”

栢图教育数据库实验室 www.btlinux.cn PGA

栢图教育数据库实验室

说明。

www.btlinux.cn PGA

看第二行的值，就表示可以动态调整的内存数量，该值不能与pga_aggregate_target设置的值差太多。如果该值太小，则oracle没有足够的内存空间来动态调整session的内存工作区。其中的global memory bound表示一个工作区的最大尺寸，并且oracle推荐只要该统计值低于1M时，就应该增加pga_aggregate_target的值。

另外还提供了两个有用的指标：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 STAT_ID ---------- ---------------------------------------- ---------- ---------- ---------- 333 workarea executions - optimal 64 34085 3211650785 334 workarea executions - onepass 64 32 798730793 335 workarea executions - multipass 64 0 3804491469

可以计算optimal次数占总次数的比率，比如上例中，34805/(34805+32+0)=99.91%，该比率越大越好，如果发现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。 select b.sql_text, a.operation_type,

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.last_execution >= '1 pass' and a.last_execution <> 'optimal' order by a.estimated_optimal_size desc; 如果我们需要调整pga_aggregate_target时，到底我们应该设置多大呢？oracle为了帮助我们确定这个参数的值，引入了一个新的视图：v$pga_target_advice。为了使用该视图，需要将初始化参数statistics_level设置为typical（缺省值）或all。

SQL> show parameter statistics_level

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------ statistics_level string TYPICAL SQL> select

2 round(pga_target_for_estimate /(1024*1024)) \"Target (M)\ 3 estd_pga_cache_hit_percentage \"Est. Cache Hit %\

栢图教育数据库实验室 www.btlinux.cn PGA

栢图教育数据库实验室

5 estd_overalloc_count \"Est. Over-Alloc\" 6 from v$pga_target_advice;

www.btlinux.cn PGA

4 round(estd_extra_bytes_rw/(1024*1024)) \"Est. ReadWrite (M)\

Target (M) Est. Cache Hit % Est. ReadWrite (M) Est. Over-Alloc ---------- ---------------- ------------------ --------------- 39 100 0 1 78 100 0 0 155 100 0 0 233 100 0 0 310 100 0 0 372 100 0 0 434 100 0 0 496 100 0 0 558 100 0 0 620 100 0 0 930 100 0 0 1240 100 0 0 1860 100 0 0 2480 100 0 0 14 rows selected.

根据该输出，我们找到能使estd_overalloc_count为0的最小pga_aggregate_target的值。从这里可以看出，是78M。注意，随着我们增加pga的尺寸，estd_pga_cache_hit_percentage不断增加，同时estd_extra_bytes_rw(表示onepass、multipass读写的字节数)不断减小。从上面的结果，我们可以知道，将pga_aggregate_target设置为78MB是最合理的，因为即便将其设置为2480MB，命中率也不会有所提高。

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 / 5 DECODE((b.BLOCKSIZE * i.avg_blocks_per_io), 6 0, 7 1,

8 (b.BLOCKSIZE * i.avg_blocks_per_io))) * i.iotime) / 100) \"response_time(sec)\" 9 FROM v$pga_target_advice,

10 (SELECT /*+AVG TIME TO DO AN IO TO TEMP TABLESPACE*/ 11 AVG((readtim + writetim) /

12 DECODE((phyrds + phywrts), 0, 1, (phyrds + phywrts))) iotime, 13 AVG((phyblkrd + phyblkwrt) /

14 DECODE((phyrds + phywrts), 0, 1, (phyrds + phywrts))) avg_blocks_per_io 15 FROM v$tempstat) i, 16 (SELECT /* temp ts block size */

栢图教育数据库实验室 www.btlinux.cn PGA

栢图教育数据库实验室

17 VALUE BLOCKSIZE 18 FROM v$parameter

www.btlinux.cn PGA

19 WHERE NAME = 'db_block_size') b;

COMPONENT TARGET_SIZE CACHE_HIT_RATIO response_time(sec) -------------------- ----------- --------------- ------------------ PGA Aggregate Target 39 100 0 PGA Aggregate Target 78 100 0 PGA Aggregate Target 155 100 0 PGA Aggregate Target 233 100 0 PGA Aggregate Target 310 100 0 PGA Aggregate Target 372 100 0 PGA Aggregate Target 434 100 0 PGA Aggregate Target 496 100 0 PGA Aggregate Target 558 100 0 PGA Aggregate Target 620 100 0 PGA Aggregate Target 930 100 0 PGA Aggregate Target 1240 100 0 PGA Aggregate Target 1860 100 0 PGA Aggregate Target 2480 100 0 14 rows selected.

5 小结

当使用自动PGA内存管理时，三个隐藏参数_pga_max_size、_smm_max_size、_smm_px_max_size就在后台自动工作以加强对内存消耗的限制。

 参数_pga_max_size限制单个进程使用的所有工作区的大小。

 参数_smm_max_size限制无论是串行还是并行执行情况下一个单独工作区的大小。

 当使用并行执行时，就会对进程涉及使用的所有工作区总的大小设置一个额外限制，该限制使用参数_smm_px_max_size进行控制。

在这些限制范围内，由于修改了PAT，所以这三个参数都在运行时重新计算。这三个参数可以手动设置以覆盖该计算结果。

对于一个新搭建的ORACLE环境： (一) 如果数据库为OLTP（联机事务处理）应用的，则其应用一般都是小的短的进程，所需要的PGA也相应较

少，所以该值该值通常为总共分配给oracle实例内存的20%，另外的80%则给了SGA

(二) 如果数据库为OLAP（DSS）（数据仓库或决策分析）应用的，则其应用一般都是很大的，运行时间很长的

进程，因此需要的PGA就多。所以通常为PGA分配50%的内存。

(三) 如果数据库为混合类型的，则情况比较复杂，一般会先分配40%的初始值，而后随着数据库的应用，而不

断对PGA进行监控，并进行相应的调整。

这些值并不一定正确，可能设置过大，也可能设置过小。必须随着系统的不断运行，DBA需要不断监控，从而对其进行调整。

比如，对于16GB物理内存的数据库服务器来说，分配给oracle实例的内存为物理内存的80%。  对于OLTP应用来说：pga_aggregate_target~2622MB = 16GB× 80%×20%。  对于OLAP来说，pga_aggregate_target~6554MB =16384MB×80%×50%。

每次修改了PAT，需要在系统运行几天之后，检查视图：v$sysstat、v$pgastat、v$pga_target_advice，以确定修

栢图教育数据库实验室 www.btlinux.cn PGA

栢图教育数据库实验室

www.btlinux.cn 改的值是否满足系统的需要。

栢图教育数据库实验室 www.btlinux.cn PGA

PGA

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文