ORACLE記憶體管理 之三 PGA v$sql_workarea_histogram v$pga_target_advice

另外還有一個很重要的試圖來觀察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的資料會重新開始.