RE: Oracle Server tuning guide for RAC nodes (2-node and 3-node) with 64-GB and 128GB memory
Date: Wed, 5 Aug 2009 12:05:09 -0400
Message-ID: <A9359A64C74F4D9996B945C9DBDB621B_at_rsiz.com>
an sga target of 42GB would likely be within the "reasonable starting point" plateau for both these systems. Then you just do what Greg and Tim wrote.
If a memory target setting drives frequent actual swapping or page ins, that artifact might tend to make the addm/awr advice less than optimal, likewise if you set the targets ludicrously small. The 42 is slightly a joke number from the hitchhiker's guide, but you'll probably be in the broad plateau that won't distort the recommendations anywhere from about 25% to 75% of memory for these large memory systems, so it will probably work well enough to get a recommendation. If you're using ancient releases that produce no recommendations, start high (lower as needed to avoid swap) and reduce in a binary search pattern until you start waiting for things that you wouldn't be waiting for if the Oracle instance had more memory. (Or just leave it high if you have no better use for the memory.) But we really don't need to do that any more because Oracle computes an answer for us.
Variability in your actual workload versus what you throw at addm/awr for you first feedback advice might generate a change in recommendation, so keep an eye on it in a routine non-panicked way over the long haul. If you're using spfiles then each instance should pick up at a good place on instance restarts anyway. Generate a pfile and look at the double __ parameters if you are curious how Oracle is allocating things. If you have distinct workshift differences in workloads and objects you need to cache it is possible you might benefit from keeping a "tuned" set of startup files and bouncing some of the instances particularly to handle those specfic loads, but it is somewhat rare that it is worth the extra complexity to actually do so, and left automatic Oracle will adjust. I only mention this in case you've read something about this elsewhere and might be confusing precise workload tuning with a reasonable starting point to get target recommendations from Oracle.
As long as you are a healthy margin away from actual swapping and excessive page-ins, I tend to run a little more memory rich (for Oracle) than the suggested target. If you have multiple sample shift workloads to test, and unless it would drive you into swapping or paging, use the highest pga target recommendation of the two. Often it is possible to completely avoid disk based temp actions, and if you have memory to spare, that is a good thing. If it drives you into swap or heavy page-ins, it is a bad thing.
So to recap: Do what Greg wrote and do what Tim wrote. (Come to think of it that's probably a general rule unless one of them is having a really bad day or misunderstood your actual problem from the question posted.) I hope my little notes about a starting point and the other stuff help you get started and dissuade you from trying manual tuning.
Regards,
mwf
<snip>
To: tim_at_evdbt.com
Cc: Oracle-L Freelists
Subject: RE: Oracle Server tuning guide for RAC nodes (2-node and 3-node)
with 64-GB and 128GB memory
Tim,
Thanks. However, I wanted to see if folks on this list have some tunable parameters for memory of that size, and then we can start from there with the work loads.
- Pradeep
<snip>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 05 2009 - 11:05:09 CDT