另外还有一个很重要的试图来观察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 表示workarea在32m-64m 之间的在内存里运行的有51次,8次需要用到一次disk,0次多次用到disk.
128mb <= PGA < 256mb 0 6 52,为什么不用MEM而用DISK呢,因为每一个PGA PIECE最多只能是PGA_AGGREGATE的5%(估计),所以大于这部分的就只能用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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/633084/viewspace-888422/