ITPub博客

首页 > Linux操作系统 > Linux操作系统 > (ZT)如何诊断PGA设定是否合适

(ZT)如何诊断PGA设定是否合适

原创 Linux操作系统 作者:zergduan 时间:2009-06-22 16:19:44 0 删除 编辑
如何诊断PGA设定是否合适

如何诊断PGA设定是否合适

一、V$PGASTAT
这个视图收集了很多和PGA相关的信息,但是只适用于自动管理的PGA。
如下几个指标是需要特别关注的:
指标
说明
目标值
aggregate PGA target parameter
PGA的target值

aggregate PGA auto target
当前可被自动方式的work area使用的内存总量,也就是剩余了多少PGA使用。它是一个可变的量,随着PGA_AGGREGATE_TARGET和系统负载变化而变化。
正常情况下,这个值应该和PGA_AGGREGATE_TARGET设定差不多,如果差得很多,说明有很多内存正在被PGA的其他部分使用,如(PL/SQL、JAVA)。
这个值不宜过小。

足够大,最好能接近PGA_AGGREGATE_TARGET
total PGA inuse
当前正在被work area使用的PGA总量。正常情况下
(total PGA inuse) + (aggregate PGA auto target) = PGA_AGGREGATE_TARGET

total PGA allocated
当前实例已分配的PGA内存总量。一般来说,这个值应该小于PGA_AGGREGATE_TARGET,但是如果进程需求的PGA快速增长,它可以在超过PGA_AGGREGATE_TARGET的限定值。
如果这个值大于PGA_AGGREGATE_TARGET设定值,表明PGA_AGGREGATE_TARGET设置的过小。

over allocation count
当PGA使用量超出PGA_AGGREGATE_TARGET,这个值就加1. 这个统计信息记录从实例启动以来所有PGA使用量超过PGA_AGGREGATE_TARGET的次数。
0
extra bytes read/written
额外读写的字节数。这个数值一般发生在排序或者hash 连接中PGA对应区域不足,发生磁盘排序/Hash等,它们对应direct temp read/write
0
cache hit percentage
PGA命中率。它是一个累计值,当发生process操作不能完全发生在PGA中时,这个值就会降低。
100

我们看一个例子:
SQL> select name,value from v$pgastat where name in ('aggregate PGA target parameter','aggregate PGA auto target','total PGA inuse','total PGA allocated','over allocation count','extra bytes read/written','cache hit percentage')

2
;


NAME                                          VALUE
---------------------------------------- ----------
aggregate PGA target parameter            536870912
aggregate PGA auto target                  33554432
total PGA inuse                           999627776
total PGA allocated                      1473125376
over allocation count                       3055913
extra bytes read/written                 5.8616E+12
cache hit percentage                          73.49

aggregate PGA auto target   33554432:只有30多M可用PGA。
total PGA inuse            999627776:正在使用的PGA远大于536870912。
total PGA allocated         1473125376:已分配的PGA远大于536870912。
over allocation count       3055913:PGA溢出次数太大
extra bytes read/written    5.8616E+12:磁盘IO很多
cache hit percentage       73.49:CACHE太低

从上面的值可以明显看出,当前PGA的设定值是远远不足的。


二、v$sql_workarea和v$sql_workarea_*
1、v$sql_workarea
v$sql_workarea保存了当前共享池中的SQL的work area信息,包含各种排序、hash等。

--optimal:SQL语句能够完全在所分配的SQL工作区内完成所有的操作。这时的性能最佳。
--onepass:SQL语句需要与磁盘上的临时表空间交互一次才能够在所分配的SQL工作区中完成所有的操作。
--multipass:由于SQL工作区过小,从而导致SQL语句需要与磁盘上的临时表空间交互多次才能完成所有的操作。这个时候的性能将急剧下降。


查看共享池中的SQL最近一次work area 信息:
--只查询PGA自动管理的情况
SQL> select last_execution,count(1) from v$sql_workarea where POLICY='AUTO' group by last_execution;

LAST_EXECUTION
COUNT(1)
------------------------------ ----------
1 PASS

48
OPTIMAL
3937

这个查询结果显示共享池的SQL最近一次执行中有48次发生了1 PASS。

SQL> select sum(OPTIMAL_EXECUTIONS) OPTIMAL,sum(ONEPASS_EXECUTIONS) ONEPASS ,sum(MULTIPASSES_EXECUTIONS) MULTIPASSES from v$sql_workarea where POLICY='AUTO';

   OPTIMAL    ONEPASS MULTIPASSES
---------- ---------- -----------
   2146939       3310           0

这个查询结果显示共享池中的一个发生了3291次ONEPASS。
最理想的情况就是全都是OPTIMAL,不要发生1 PASS,更不要发生MUTIL PASSES。
上面的结果显示PGA还不足以消除ONEPASS,所以需要加大。

2、v$sql_workarea_histogram
按照不同的optimal size统计自实例启动以来的workarea的信息:
SQL> SELECT LOW_OPTIMAL_SIZE/1024 low_kb,(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
       optimal_executions optimal, onepass_executions onepass, multipasses_executions multipasses
FROM   v$sql_workarea_histogram
WHERE  total_executions != 0;  

    LOW_KB    HIGH_KB    OPTIMAL    ONEPASS MULTIPASSES
---------- ---------- ---------- ---------- -----------
         2          4  127629065          0           0
        16         32          0        680           0
        32         64          0        234           0
        64        128     775105         52           0
       128        256     971360        216           4
       256        512    1077843        805           0
       512       1024    2153928       1295           0
      1024       2048     307950       1241          20
      2048       4096      95651      10106          90
      4096       8192     217982      12657         476
      8192      16384     202518      62863         251
     16384      32768     175048     181970        1437
     32768      65536        431       2826         206
     65536     131072        146       1757         147
    131072     262144         84        329          36
    262144     524288          2        155          62
    524288    1048576          0         23          12
   1048576    2097152          0          6           8
   2097152    4194304          0          2           2
以上信息可以看出,系统启动以来发生过很多ONEPASS和MUTILPASSES的操作,PGA严重不足。


3、v$sql_workarea_active
         这个视图记录正在活动的SQL的workarea信息。

三、v$sesstat和v$sysstat
v$sessta记录当前会话的一些统计信息;
SQL> select n.name,sum(s.value) value
from v$sesstat s,v$statname n where s.statistic#=n.statistic#
and n.name like 'workarea executions%'
group by n.name;
2
3
4

NAME

VALUE
---------------------------------------------------------------- ----------
workarea executions - multipass
49
workarea executions - onepass

103407
workarea executions - optimal
3981196

V$sysstat记录实例启动以来的统计信息:


SQL> select n.name,sum(s.value) value
from v$sysstat s,v$statname n where s.statistic#=n.statistic#
and n.name like 'workarea executions%'
group by n.name;
2
3
4

NAME
VALUE
---------------------------------------------------------------- ----------
workarea executions - multipass

2751
workarea executions - onepass
277232
workarea executions - optimal
133615647

和上面一样,我们不希望出现onepass和multipass,但这个查询结果明显说明PGA不足。

四、修改PGA
以上都是在讨论如何判断PGA设置是否足够过小,那么如果PGA过小,怎么去设置一个合理的大小呢?

Oracle提高了一个视图V$PGA_TARGET_ADVICE给我们建议。
         使用这个视图的前提条件是:
1、
PGA自动管理
2、
timed_statistics=true

从这个视图中,我们主要管理两个目标:
ESTD_PGA_CACHE_HIT_PERCENTAGE:PGA命中率,理想目标是100或者接近100.
ESTD_OVERALLOC_COUNT:PGA溢出次数,理想目标是0.

SQL> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
FROM   v$pga_target_advice;  2    3    4

TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
        64             31               712373
       128             31               712352
       256             33               661895
       384             34               605838
       512             77               511817
       614             80               396989
       717             84               298813
       819             88               183100
       922             93                70906
      1024             97                20068
      1536             99                    0
      2048             99                    0
      3072             99                    0
      4096             99                    0

从这个查询可以看出,oracle建议我们把PGA_AGGREGATE_TARGET设置为1500M左右。
那么我们修改就很简单了:

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/386081/viewspace-607159/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-03-12

  • 博文量
    77
  • 访问量
    751412