21 May 2007

Tuning the PGA: The Follow-Up

I know that all 3 of you have been shivering with anticiPATION at how my PGA tuning experiment went. Today is your lucky day.

I did indeed raise the value of pga_aggregate_target to 512MB. The cache hit ratio rose to near 80%, but the estimated over-allocations was quickly into the tens of thousands again. Looking at V$PGA_TARGET_ADVICE, giving the PGA 1024MB (or 1GB) seemed the next logical choice. That change was made last weekend, and after one week, I'm getting nothing but love:

SQL> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM V$PGA_TARGET_ADVICE;

TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
128 60 13700
256 64 12475
512 82 5614
768 91 143
1024 95 0
1229 96 0
1434 96 0
1638 96 0
1843 96 0
2048 96 0
3072 96 0
4096 96 0
6144 96 0
8192 96 0
I've put my current level in blue above. I'm satisfied with these numbers at 95% and 0.

1 comment:

  1. Yeah I wondered that too. You would think that increasing my PGA by 800% would have *some* effect, but I guess not. Of course those numbers are just an estimate, and they change as the setting changes.

    But, as I said, I'm satisfied with 95-96% and no over-allocations.

    ReplyDelete