PGA自动管理原理深入分析及性能调整（三）

最新推荐文章于 2024-04-25 23:47:01 发布

转载最新推荐文章于 2024-04-25 23:47:01 发布 · 339 阅读

本文通过实操演示如何监控和调优Oracle数据库的Program Global Area (PGA)，包括使用SQL工作区、session和进程的PGA监控脚本，以及如何调整pga_aggregate_target参数以优化SQL语句执行。

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

----------

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。