• The usage of V$PGA_TARGET_ADVICE


    Oracle 10g 给出了一系列的自动优化的建议,告诉我们PGA分配多大能给系统带来最大的性能?V$PGA_TARGET_ADVICE视图给出了很好的“预测”!

    看一下这个视图能给我们带来什么样的信息:
    SQL> 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              .125                           100                    1
            20               .25                           100                    1
            40                .5                           100                    1
            60               .75                           100                    0
            80                 1                           100                    0
            96               1.2                           100                    0
           112               1.4                           100                    0
           128               1.6                           100                    0
           144               1.8                           100                    0
           160                 2                           100                    0
           240                 3                           100                    0

       PGA(MB) PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
    ---------- ----------------- ----------------------------- --------------------
           320                 4                           100                    0
           480                 6                           100                    0
           640                 8                           100                    0

    14 rows selected.

    通过上面的数据可以得到如下的结论:
    1.第一列表示不同的PGA的具体值

    2.第二列PGA_TARGET_FACTOR为“1”表示当前的pga_aggregate_target设置大小(其他数值都是以这个数据为基础的倍数),我这里是80M,

    通过pga_aggregate_target参数可以确认一下
    SQL> show parameter pga_aggregate_target

    NAME                                 TYPE                             VALUE
    ------------------------------------ -------------------------------- ------------------------------
    pga_aggregate_target                 big integer                      80M

    3.第三列表示PGA的估算得到的Cache命中率的百分比
    目前系统如果PGA为10M的时候,就可以达到100%的命中率

    4.第四列如果为“0”表示可以消除PGA的过载
    从上面的数据中可以得到,当PGA为60M的时候,可以消除PGA的过载。

    5.通过上面的结论,我们可以将PGA的大小设置为60M。
    SQL> alter system set pga_aggregate_target=60m;

    System altered.


    6.调整后,再次查询一下v$pga_target_advice视图得到如下的建议信息,可以看到基本上已经满足现在的系统需求。
    SQL> 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
            60                 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

    12 rows selected.

    通过上面的描述,您一定已经会了解了这个v$pga_target_advice视图了。

     

  • 相关阅读:
    Flex 开源框架及工具 枫
    String.Format格式说明 枫
    HTTP服务器状态代码定义(Status Code Definitions) 枫
    像素对应表 枫
    js各种获取屏幕高度宽度 枫
    c#正则表达式帮助 枫
    使用模板引擎Trimpath 枫
    分布式缓存HttpRuntime.cache应用到单点登陆中_优化登陆 枫
    Query this 和 $(this) 的区别 枫
    css and js style 枫
  • 原文地址:https://www.cnblogs.com/javawebsoa/p/3150399.html
Copyright © 2020-2023  润新知