RE: Oracle Server tuning guide for RAC nodes (2-node and 3-node) with 64-GB and 128GB memory

From: Pradeep Chetal <>
Date: Thu, 6 Aug 2009 03:25:45 -0400
Message-ID: <>

Thanks mark.

Once we get down to setting this up and start workload, I will post my feedback.

  • Pradeep

-----Original Message-----
From: Mark W. Farnham [] Sent: Wednesday, August 05, 2009 9:35 PM To: Pradeep Chetal;
Cc: 'Oracle-L Freelists'
Subject: RE: Oracle Server tuning guide for RAC nodes (2-node and 3-node) with 64-GB and 128GB memory

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.




Cc: Oracle-L Freelists
Subject: RE: Oracle Server tuning guide for RAC nodes (2-node and 3-node)
with 64-GB and 128GB memory


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

Pradeep Chetal
Sr. Director - Infrastructure Architecture

Mformation Technologies Inc.
Switchboard: +1 732 692 6200  

Visit WWW.MFORMATION.COM and see how the MFORMATION SERVICE MANAGER can help you to transform your business

Please consider the environment before printing

Received on Thu Aug 06 2009 - 02:25:45 CDT

Original text of this message