ITPub博客

首页 > 数据库 > Oracle > ORACLE内存管理 之三 PGA v$sql_workarea_histogram v$pga_target_advice

ORACLE内存管理 之三 PGA v$sql_workarea_histogram v$pga_target_advice

原创 Oracle 作者:weifenghq 时间:2007-01-05 10:01:10 0 删除 编辑

另外还有一个很重要的试图来观察PGA的效率v$sql_workarea_histogram

pga_aggregate_target big integer 1236648591

1 SELECT

2 case when low_optimal_size < 1024*1024

3 then to_char(low_optimal_size/1024,'999999') ||

4 'kb <= PGA < ' ||

5 (HIGH_OPTIMAL_SIZE+1)/1024|| 'kb'

6 else to_char(low_optimal_size/1024/1024,'999999') ||

7 'mb <= PGA < ' ||

8 (high_optimal_size+1)/1024/1024|| 'mb'

9 end ||' '||

10 optimal_executions||' '||

11 onepass_executions||' '||

12 multipasses_executions

13 from v$sql_workarea_histogram

14 where total_executions <> 0

15* order by low_optimal_size

SQL> /

CASEWHENLOW_OPTIMAL_SIZE<1024*1024THENTO_CHAR(LOW_OPTIMAL_SIZE/1024,'999999')||'

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

16kb <= PGA < 32kb 53646550 0 0

32kb <= PGA < 64kb 26062 0 0

64kb <= PGA < 128kb 20361 0 0

128kb <= PGA < 256kb 893 0 0

256kb <= PGA < 512kb 941 0 0

512kb <= PGA < 1024kb 8331 0 0

1mb <= PGA < 2mb 1836 0 0

2mb <= PGA < 4mb 505 0 0

4mb <= PGA < 8mb 218 4 0

8mb <= PGA < 16mb 294 8 0

16mb <= PGA < 32mb 261 16 0

CASEWHENLOW_OPTIMAL_SIZE<1024*1024THENTO_CHAR(LOW_OPTIMAL_SIZE/1024,'999999')||'

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

32mb <= PGA < 64mb 51 8 0

64mb <= PGA < 128mb 5 6 2

128mb <= PGA < 256mb 0 6 52

256mb <= PGA < 512mb 0 24 59

512mb <= PGA < 1024mb 1 2 0

PGA workarea去分析. 32mb <= PGA < 64mb 51 8 0 表示workarea32m-64m 之间的在内存里运行的有51,8次需要用到一次disk,0次多次用到disk.

128mb <= PGA < 256mb 0 6 52,为什么不用MEM而用DISK,因为每一个PGA PIECE最多只能是PGA_AGGREGATE5%(估计),所以大于这部分的就只能用DISK.

v$pga_target_advice

1 select

2 trunc(pga_target_for_estimate/1024/1024)

3 pga_target_for_estimate,

4 to_char(pga_target_factor * 100,'999.9') ||'%'

5 pga_target_factor,

6 trunc(bytes_processed/1024/1024) bytes_processed,

7 trunc(estd_extra_bytes_rw/1024/1024) estd_extra_bytes_rw,

8 to_char(estd_pga_cache_hit_percentage,'999') || '%'

9 estd_pga_cache_hit_percentage,

10 estd_overalloc_count

11* from v$pga_target_advice

SQL> /

PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FAC BYTES_PROCESSED ESTD_EXTRA_BYTES_RW

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

ESTD_PGA_C ESTD_OVERALLOC_COUNT

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

147 12.5% 1074286 211218 84% 44

294 25.0% 1074286 180162 86% 0

589 50.0% 1074286 170085 86% 0

884 75.0% 1074286 168992 86% 0

1179 100.0% 1074286 167579 87% 0

1415 120.0% 1074286 51551 95% 0

1651 140.0% 1074286 51551 95% 0

1886 160.0% 1074286 51380 95% 0

2122 180.0% 1074286 51186 95% 0

2358 200.0% 1074286 51186 95% 0

3538 300.0% 1074286 51186 95% 0

4717 400.0% 1074286 51186 95% 0

7076 600.0% 1074286 51186 95% 0

9434 800.0% 1074286 51186 95% 0

可以看到统计数据表现这个库有很大的问题.现在是1179M, estd_pga_cache_hit_percentage=87%,即便将PGA_TARGET扩大到8倍命中率还是不高95%.(会在以后的文章里修改次问题的)

当你重新设置pga_aggregate_target后这个view的数据会重新开始.

[@more@]

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

请登录后发表评论 登录
全部评论

注册时间:2008-11-25

  • 博文量
    100
  • 访问量
    578968