PGA: Not just for Caddyshack 2 fans
Ahh … Jackie Mason. The poor man’s Rodney Dangerfield. The pride of Sheboygan, Wisconsin (other than meat products).
But, no, today I’m not talking about golf (nor will I ever, if I can help it). I’m talking about Oracle’s PGA, the Program Global Area. The PGA is a private chunk of memory dedicated to each server process (i.e. dedicated user processes). It is used by the server processes when doing sorting or hash-joins, among others, and it is definitely something you want to pay attention to and properly tune for your database needs.
After doing this reading, I calcuated that my PGA should probably be at least 256MB for sure, probably closer to 512MB. Note that I hadn’t touched or directly set it prior to this. As soon as this output appeared on the screen, I doubled over in self-disgust.
SQL> show parameter pga_aggregate_target NAME TYPE VALUE------------------------------------ ----------- ------------------------------pga_aggregate_target big integer 24M
I then went to see what the advisor table had to say. Bear in mind that you want CACHE_HIT_PERCENTAGE as near to 100 as possible, and you definitely want ESTD_OVERALLOC_COUNT to be 0.
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---------- -------------- -------------------- 12 52 30444 18 52 30444 24 75 30444 29 75 30444 34 75 30444 38 75 30444 43 75 30443 48 75 30423 72 75 30377 96 75 30309 144 75 29990 192 76 29335
Yeah. Neither goal is even on the charts. So my plan now is to bump it up pga_aggregate_target to 512MB and see how those numbers settle out in a week. I might even want to go higher in the end. Google seems to imply that 24M is indeed the default pga_aggregate_target value, at least it was when I set this up as a new 9iR2 instance and imp’d the data over.
To be continued …
