RE: Oracle Server tuning guide for RAC nodes (2-node and 3-node) with 64-GB and 128GB memory
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <Pradeep.Chetal@xxxxxxxxxxxxxx>, <tim@xxxxxxxxx>
- Date: Wed, 5 Aug 2009 12:05:09 -0400
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@xxxxxxxxx
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>
--
//www.freelists.org/webpage/oracle-l
Other related posts: