mysql pga_PGA的监控与调整

对于PGA的监控与调整，可以通过下列视图作为参考

v$pgastat

v$pga_target_advice

v$pga_target_advice_histogram

1、查看v$pgastat视图获得PGA的相关信息

SQL> select * from v$pgastat;

NAME                                          VALUE UNIT

-------------------------------------- ------------ ------------

aggregate PGA target parameter            199229440 bytes   --当前PGA_AGGREGATE_TARGET设定的值

aggregate PGA auto target                 127153152 bytes   --剩余的能被工作区(workarea)使用的内存(AUTO模式)

global memory bound                        39845888 bytes   --单条SQL串行操作的最高PGA内存使用量(AUTO模式)

total PGA inuse                            62769152 bytes   --正被耗用的pga(包括workarea pl/sql等所有占用的pga)

total PGA allocated                       132114432 bytes   --当前实例已分配的PGA内存总量

maximum PGA allocated                     299982848 bytes   --PGA曾经扩张到的最大值

total freeable PGA memory                  10223616 bytes   --PGA的可释放的大小

process count                                    19         --当前process

max processes count                              42         --最大时候的process

PGA memory freed back to OS             46708359168 bytes   --PGA返回给操作系统的累计值

total PGA used for auto workareas           4829184 bytes   --PGA分配给auto workareas的总大小

maximum PGA used for auto workareas        39851008 bytes   --PGA分配给auto workareas峰值

total PGA used for manual workareas               0 bytes

maximum PGA used for manual workareas        531456 bytes

over allocation count                             1         --使用量超过pga大小的次数

bytes processed                         96178293760 bytes   --pga使用的字节

extra bytes read/written                 1855111168 bytes   --向临时段写的字节

cache hit percentage                             98 percent --命中率

recompute count (total)                     1473108

-->从上面的统计信息可以看出maximum PGA allocated的值远大于aggregate PGA target parameter的值，且over allocation count，

-->此时应考虑增加PGA target的值

2、v$pga_target_advice_histogram视图

该视图可以显示各种不同排序工作区的使用情况，

OPTIMAL_EXECUTIONS是不需要通过临时表空间交换的操作的次数。

ONEPASS_EXECUTIONS是需要进行一次临时表空间交换的操作次数。

MULTIPASSES_EXECUTIONS是需要多次临时表空间交换的操作次数。

原则上，如果100％的操作都是内存操作，是最佳的选择。如果内存不足，某些大型操作是ONEPASS的，也是可以接受的。

出现MULTIPASSES的操作会引起性能的急剧下降，此时应考虑调整PGA target

/**************************************************/

/* Author: Robinson Cheng                         */

/* Blog:   http://blog.csdn.net/robinson_0612     */

/* MSN:    robinson_0612@hotmail.com              */

/* QQ:     645746311                              */

/**************************************************/

SQL> SELECT LOW_OPTIMAL_SIZE/1024 low_kb,(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,

2         optimal_executions opt_exec, onepass_executions onepass_exec, multipasses_executions multipass_exec

3  FROM   v$sql_workarea_histogram

4  WHERE  total_executions != 0;

LOW_KB    HIGH_KB   OPT_EXEC ONEPASS_EXEC MULTIPASS_EXEC

---------- ---------- ---------- ------------ --------------

2          4    1013515            0              0

64        128       7041            0              0

128        256       1499            0              0

256        512       1222            0              0

512       1024      67917            0              0

1024       2048      19057            0              0

2048       4096       3450            0              0

4096       8192        770            4              0

8192      16384         37            0              0

32768      65536          2            7              0

131072     262144          0           24              0

LOW_KB    HIGH_KB   OPT_EXEC ONEPASS_EXEC MULTIPASS_EXEC

---------- ---------- ---------- ------------ --------------

262144     524288          0            4              0

3、从v$pga_target_advice获取pga设置的建议值

SQL> select pga_target_for_estimate / 1024 / 1024 pgamb,

2        pga_target_factor p_tr_fct,

3        estd_pga_cache_hit_percentage e_p_c_hit_prct,

4        estd_overalloc_count e_or_cnt

5  from   v$pga_target_advice;

PGAMB   P_TR_FCT E_P_C_HIT_PRCT   E_OR_CNT

---------- ---------- -------------- ----------

23.75       .125             82       1179

47.5        .25             82       1179

95         .5             95        233

142.5        .75             99          6

190          1             99          1

228        1.2             99          0

266        1.4             99          0

304        1.6             99          0

342        1.8             99          0

380          2             99          0

570          3             99          0

760          4             99          0

1140          6             99          0

1520          8             99          0

--从上面的查询中可以看出当设置PGA的大小为228MB时，可以消除PGA过载的情形。

"font-family:SimSun;">4、通过V$SYSSTAT查看排序区的情况

-->下面查看当前系统中optimal,onepass,multipass几种不同排序下所占的比率

-->关于排序应当尽可能的为optimal,避免过多的multipass

SQL> col profile format a40

SQL> SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total,4)) percentage

2  FROM (SELECT name, value cnt, (sum(value) over ()) total FROM V$SYSSTAT WHERE name like 'workarea exec%');

PROFILE                                         CNT PERCENTAGE

---------------------------------------- ---------- ----------

workarea executions - optimal               1113054     99.999

workarea executions - onepass                    11       .001

workarea executions - multipass                   0          0

-->下面查看内存排序占总排序的比率,此值越高越好.如此值较小,应结合v$pga_target_advice考虑调整pga

SQL> SELECT round(m.value / (m.value + d.value), 4) * 100 || '%' memory_disk_sort_ratio

2  FROM   (SELECT value FROM v$sysstat WHERE NAME = 'sorts (memory)') m,

3        (SELECT value FROM v$sysstat WHERE NAME = 'sorts (disk)') d;

MEMORY_DISK_SORT_RATIO

-----------------------------------------

100%