ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【PGA】通过10g新特性得到PGA的调整建议

【PGA】通过10g新特性得到PGA的调整建议

原创 Linux操作系统 作者:secooler 时间:2009-09-12 10:40:34 0 删除 编辑
10g Oracle给出了一系列的自动优化的建议,PGA分配多大能给系统带来最大的性能?V$PGA_TARGET_ADVICE视图给出了很好的“预测”!

看一下这个视图能给我们带来什么样的信息(视图中每个列都很有帮助):
sys@ora10g> SELECT   pga_target_for_estimate / 1024 / 1024 "PGA(MB)",
  2           pga_target_factor,
  3           estd_pga_cache_hit_percentage,
  4           estd_overalloc_count
  5    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
        20                 1                           100                   13
        24               1.2                           100                   13
        28               1.4                           100                   13
        32               1.6                           100                    3
        36               1.8                           100                    0
        40                 2                           100                    0
        60                 3                           100                    0
        80                 4                           100                    0
       120                 6                           100                    0
       160                 8                           100                    0

12 rows selected.

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

2.第二列PGA_TARGET_FACTOR为“1”表示当前的pga_aggregate_target设置大小(其他数值都是以这个数据为基础的倍数),我这里是20M,通过pga_aggregate_target参数可以确认一下
sys@ora10g> show parameter pga_aggregate_target;

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

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

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

5.综合以上的结论,我们最终可以将PGA的大小设置为36M。
sys@ora10g> alter system set pga_aggregate_target=36m;

System altered.

6.调整后,再次查询一下v$pga_target_advice视图得到如下的建议信息,可以看到基本上已经满足现在的系统需求。
sec@ora10g> SELECT pga_target_for_estimate / 1024 / 1024 "PGA(MB)",
  2         pga_target_factor,
  3         estd_pga_cache_hit_percentage,
  4         estd_overalloc_count
  5  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
        36                 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视图了。

这是一个动态的过程,可以定期的查看这个视图得到更加有效的PGA大小的设置建议。

【OEM方法】同样是上面的建议信息,我们可以通过Oracle的OEM得到更加直观的信息。
1.使用自己的服务器的IP地址登陆到OEM界面
http://144.194.192.183:1158/em/console/logon/logon

2.数据用户名(sys)和密码(sys的密码),“Connect As”选择“SYSDB”,最后点击“Login”

3.最上面有三个大的可选菜单“Home”、“Performance”、“Administration”和“Maintenance”,需要选择“Administration”,在“Database Configuration”大类中的第一个就是“Memory Parameters”,点击进入,此时您会看到“SGA”和“PGA”,点击第二个“PGA”,OK,这里就可以看到“Advice”和“PGA Memory Usage Details”两个按钮,第一个“Advice”按钮得到的信息就是我们上面通过v$pga_target_advice视图得到的信息,这里会更加直观的,以曲线图的形式进行展示。
另外一个按钮“PGA Memory Usage Details”是通过视图v$pga_target_advice_histogram得到的一个统计柱状图。

附图如下:








【附视图官方解释】10g官方文档中关于v$pga_target_advice和v$pga_target_advice_histogram两个视图的说明

V$PGA_TARGET_ADVICE

V$PGA_TARGET_ADVICE predicts how the cache hit percentage and over allocation count statistics displayed by the V$PGASTAT performance view would be impacted if the value of the PGA_AGGREGATE_TARGET parameter is changed. The prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.

The content of the view is empty if PGA_AGGREGATE_TARGET is not set. In addition, the content of this view is not updated if the STATISTICS_LEVEL parameter is set to BASIC. Base statistics for this view are reset at instance startup and when the value of the PGA_AGGREGATE_TARGET initialization parameter is dynamically modified.

Column Datatype Description
PGA_TARGET_FOR_ESTIMATE NUMBER Value of PGA_AGGREGATE_TARGET for this prediction (in bytes)
PGA_TARGET_FACTOR NUMBER PGA_TARGET_FOR_ESTIMATE / the current value of the PGA_AGGREGATE_TARGET parameter
ADVICE_STATUS VARCHAR2(3) Indicates whether the advice is enabled (ON) or disabled (OFF) depending on the value of the STATISTICS_LEVEL parameter
BYTES_PROCESSED NUMBER Total bytes processed by all the work areas considered by this advice (in bytes)
ESTD_EXTRA_BYTES_RW NUMBER Estimated number of extra bytes which would be read or written if PGA_AGGREGATE_TARGET was set to the value of the PGA_TARGET_FOR_ESTIMATE column. This number is derived from the estimated number and size of work areas which would run in one-pass (or multi-pass) for that value of PGA_AGGREGATE_TARGET.
ESTD_PGA_CACHE_HIT_PERCENTAGE NUMBER Estimated value of the cache hit percentage statistic when PGA_AGGREGATE_TARGET equals PGA_TARGET_FOR_ESTIMATE. This column is derived from the above two columns and is equal to BYTES_PROCESSED / (BYTES_PROCESSED + ESTD_EXTRA_BYTES_RW)
ESTD_OVERALLOC_COUNT NUMBER Estimated number of PGA memory over-allocations if the value of PGA_AGGREGATE_TARGET is set to PGA_TARGET_FOR_ESTIMATE. A nonzero value means that PGA_TARGET_FOR_ESTIMATE is not large enough to run the work area workload. Hence, the DBA should not set PGA_AGGREGATE_TARGET to PGA_TARGET_FOR_ESTIMATE since Oracle will not be able to honor that target.

V$PGA_TARGET_ADVICE_HISTOGRAM

V$PGA_TARGET_ADVICE_HISTOGRAM predicts how statistics displayed by the V$SQL_WORKAREA_HISTOGRAM dynamic view would be impacted if the value of the PGA_AGGREGATE_TARGET parameter is changed. This prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.

The content of the view is empty if PGA_AGGREGATE_TARGET is not set. In addition, the content of this view is not updated when the STATISTICS_LEVEL initialization parameter is set to BASIC. Base statistics for this view are reset at instance startup or when the value of the PGA_AGGREGATE_TARGET initialization parameter is dynamically modified.

Column Datatype Description
PGA_TARGET_FOR_ESTIMATE NUMBER Value of PGA_AGGREGATE_TARGET for this prediction (in bytes)
PGA_TARGET_FACTOR NUMBER PGA_TARGET_FOR_ESTIMATE / the current value of the PGA_AGGREGATE_TARGET parameter
ADVICE_STATUS VARCHAR2(3) Indicates whether the advice is enabled (ON) or disabled (OFF) depending on the value of the STATISTICS_LEVEL parameter
LOW_OPTIMAL_SIZE NUMBER Lower bound for the optimal memory requirement of work areas included in this row (in bytes)
HIGH_OPTIMAL_SIZE NUMBER Upper bound for the optimal memory requirement of work areas included in this row (in bytes)
ESTD_OPTIMAL_EXECUTIONS NUMBER Number of work areas with an optimal memory requirement comprised between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE which are predicted to run optimal given a value of PGA_AGGREGATE_TARGET equal to PGA_TARGET_FOR_ESTIMATE
ESTD_ONEPASS_EXECUTIONS NUMBER Number of work areas with an optimal memory requirement comprised between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE which are predicted to run one-pass given a value of PGA_AGGREGATE_TARGET equal to PGA_TARGET_FOR_ESTIMATE
ESTD_MULTIPASSES_EXECUTIONS NUMBER Number of work areas with an optimal memory requirement comprised between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE which are predicted to run multi-pass given a value of PGA_AGGREGATE_TARGET equal to PGA_TARGET_FOR_ESTIMATE
ESTD_TOTAL_EXECUTIONS NUMBER Sum of ESTD_OPTIMAL_EXECUTIONS, ESTD_ONEPASS_EXECUTIONS, and ESTD_MULTIPASSES_EXECUTIONS
IGNORED_WORKAREAS_COUNT NUMBER Number of work areas with optimal memory requirement between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE ignored in the advice generation due to memory and CPU constraints


-- The End --



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-614500/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    7994310