如何诊断PGA设定是否合适

最新推荐文章于 2024-04-11 10:10:25 发布

sopost

最新推荐文章于 2024-04-11 10:10:25 发布

阅读量2.6k

点赞数

分类专栏： Oracle 文章标签： allocation sql cache statistics 磁盘 oracle

Oracle 专栏收录该内容

197 篇文章 0 订阅

订阅专栏

一、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

48OPTIMAL
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
49workarea executions - onepass

103407workarea 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

2751workarea executions - onepass
277232workarea 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左右。
那么我们修改就很简单了：
Alter system set PGA_AGGREGATE_TARGET=1500m;