Oracle - PGA自动管理原理深入分析及性能调整(6)

最新推荐文章于 2021-04-12 02:57:59 发布

LeosHope

最新推荐文章于 2021-04-12 02:57:59 发布

阅读量1.6k

点赞数

分类专栏： Oracle 文章标签： oracle allocation sql cache session 工作

本文链接：https://blog.csdn.net/LeosHope/article/details/1652820

版权

Oracle 专栏收录该内容

10 篇文章 2 订阅

订阅专栏

3.4其他监控并调整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)",

3 (high_optimal_size + 1)/1024 "High (K)",

4 optimal_executions "Optimal",

5 onepass_executions "1-Pass",

6 multipasses_executions ">1 Pass"

7 from v$sql_workarea_histogram

8 where total_executions <> 0;

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

Low (K) High (K) Optimal 1-Pass >1 Pass

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

8 16 360 0 0

。。。。。。。。。

65536 131072 0 2 0

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

SQL> select

2 optimal_count "Optimal",

3 round(optimal_count * 100 / total,2) "Optimal %",

4 onepass_count "OnePass",

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)

16 /

Optimal Optimal % OnePass Onepass % MultiPass Multipass %

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

402 99.01 4 0.99 0 0

而v$pgastat则提供了有关PGA使用的整体的概括性的信息。

SQL> select * from v$pgastat;

NAME VALUE UNIT

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

aggregate PGA target parameter 62914560 bytes

aggregate PGA auto target 51360768 bytes

global memory bound 104857600 bytes

total PGA inuse 5846016 bytes

total PGA allocated 8386560 bytes

maximum PGA allocated 66910208 bytes

total freeable PGA memory 0 bytes

PGA memory freed back to OS 0 bytes

total PGA used for auto workareas 0 bytes

maximum PGA used for auto workareas 51167232 bytes

total PGA used for manual workareas 0 bytes

maximum PGA used for manual workareas 0 bytes

over allocation count 0

bytes processed 142055424 bytes

extra bytes read/written 138369024 bytes

cache hit percentage 50.65 percent

从结果可以看出，第一行表示pga_aggregate_target设置为60M。PGA的一部分被用于无法动态调整的部分，比如UGA中的“session相关的信息”等。而PGA内存的剩下部分则是可以动态调整的，由“aggregate PGA auto target”说明。我们来看第二行的值，就表示可以动态调整的内存数量，该值不能与pga_aggregate_target设置的值差太多。如果该值太小，则oracle没有足够的内存空间来动态调整session的内存工作区。其中的global memory bound表示一个工作区的最大尺寸，并且oracle推荐只要该统计值低于1M时，就应该增加pga_aggregate_target的值。另外，9i还提供了两个有用的指标：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

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

230 workarea executions - optimal 64 360

231 workarea executions - onepass 64 2

232 workarea executions - multipass 64 0

我们可以计算optimal次数占总次数的比率，比如上例中，360/(360+2+0)=99.45%，该比率越大越好，如果发现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。