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 ...