Oracle自动化建议PGA最优值如何定位？

Oracle自动优化建议PGA分配多大最优化?使用V$PGA_TARGET_ADVICE视图定位:

SELECT   pga_target_for_estimate / 1024 / 1024 "PGA(MB)",
pga_target_factor,
estd_pga_cache_hit_percentage,
estd_overalloc_count
FROM   v$pga_target_advice;
   PGA(MB) PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
---------- ----------------- ----------------------------- --------------------
        10                .5                            34                   13
        15               .75                            34                   13
        18                 1                           100                   13
        24               1.2                           100                   13
        28               1.4                           100                   13
        32               1.6                           100                    3
        38               1.8                           100                    0
        40                 2                           100                    0
        60                 3                           100                    0
        80                 4                           100                    0
       120                 6                           100                    0

1.第一列表示PGA设置值

2.第二列PGA_TARGET_FACTOR为1表示pga_aggregate_target当前设置

通过pga_aggregate_target参数可以确认一下

show parameter pga_aggregate_target;
NAME                    TYPE                 VALUE
----------------------- -------------------- -----------
pga_aggregate_target    big integer          18M

3.第三列表示PGA的估算Cache命中率,如果PGA为18M就可以到100%命中率

4.第四列如果为0表示可以消除PGA过载,可以看到当PGA为38M时,可以消除PGA过载

5.综合以上结论，PGA的考虑设置为38M。

alter system set pga_aggregate_target=38m scope=both;
System altered.

6.查看结果:

SELECT   pga_target_for_estimate / 1024 / 1024 "PGA(MB)",
pga_target_factor,
estd_pga_cache_hit_percentage,
estd_overalloc_count
FROM   v$pga_target_advice;
   PGA(MB) PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
---------- ----------------- ----------------------------- --------------------
        18                .5                            94                    2
        27               .75                            94                    2
        38                 1                           100                    0
43.1992188               1.2                           100                    0
50.3994141               1.4                           100                    0
57.5996094               1.6                           100                    0
64.7998047               1.8                           100                    0
        72                 2                           100                    0
       108                 3                           100                    0
       144                 4                           100                    0
       216                 6                           100                    0
       288                 8                           100                    0

可以看到，PGA设置为38M时命中为100%,PGA过载消除。