Recommendations for tunable shmmax in preparation for Oracle10g

[Ralph Grothe]

Hi,

I was given a list of prerequisite kernel tunables for an Oracle10g installation/upgrade that I am supposed to deploy tomorrow night.
I've been putting up a Perl script to do this because this needs to be done on a bunch of hosts where SAM wouldn't be a sensible tool,
when I tripped over shmmax.
In the list I was given, it laconically reads, "AvailMem".
As I haven't got a clue about Oracle system tuning I wonder what "Available Memory" compares to in my case?
The boxes where I am supposed to set this are for instance furnished with abt. 32 GB of RAM.

Regards
Ralph

[Tim Nelson]

Set to physical memory. shmmax is only a top end limit. If you wish, set to 2GB or so less to reserver some for system processes to stop oracle admins from taking all the memory.

[Court Campbell]

Here:

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=1109448

[Court Campbell]

P.S. Don't set it equal to available physical memory. It's a waste. Not unless you are planning on having a 32Gb SGA.

[Ralph Grothe]

Hi Tim,

I've just had a talk to one of my colleagues who is an Informix admin.
His advice was exactly the same as yours,
viz. to set it to the whole of physical RAM minus an allowance of abt. 2GB for the system itself.
He also showed me a terse explenation of this tunable from his docs, where it says:


- The value of the kernel parameter "shmmax" should be set to the
maximum allowable on the system. Setting "shmmax" to a high value
will prevent the OS from creating multiple shared memory segments
which can degrade performance.

[Court Campbell]

Look if your SGA is 4GB then make shmmax equal to 5Gb in size. This will allow for some growth of the SGA. shmmax can always be changed to be larger later if necessary. Then when oracle requests a shared memory segment for the SGA it will only choose one. Why is this so hard to understand. Oracle recommendation to set shmmax to the size of available physical memory is just hogwash in my opinion.

[Court Campbell]

Also if you are planning on having multiple instances on the same box, set shmmax to something a little larger than the largest SGA. This will allow any instance to pull only one shared memory segment for each instances SGA.

[Tim Nelson]

FYI,

There is no preallocation with shmmax, it is only a limit, not like static tables.

Yes, if oracle attempts to create an SGA that is larger than shmmax it will fragment and create multiples, in turn creating a performance problem. Other applications will error out.

[A. Clay Stephenson · Accepted solution]

While it is true that setting shmmax to a very large value does not consume resources, it also sets the stage for abuse. Ask your DBA's what their largest anticipated SGA size is and set it to that (if reasonable). One thing to keep in mind iis that this is a per-segment limit and a process can have many shared memory segments. Even with a small value of shmmax, it is possible to allocate enormous amounts of shared memory but setting shmmax to a large value makes this much easier to abuse. Also note that you aren't limited by physical memory but rather virtual memory. The downside to having a smaller shmmax (e.g 1GiB) is that oracle will have to make multiple segments so that the SGA is less efficient. It's better if everything in the SGA will fit in one segment. So again, the correct approach, is too ask what their largest anticapted needs are and set it accordingly. In any event, with 32GiB of physical memory, I would think that an SGA of 16GiB would be more than adequate and I would set shmmax very near to that value.

[Court Campbell]

Tim do you really believe that he is going to be running other apps/services on his oracle database server that are going to require a shared memory segment larger than what the size of his largest SGA is going to be. Come on, be realistic. Let's say his SGA is going to be 5Gb and he sets shmmax to be 6Gb, I highly doubt that if he runs ipcs -mb that he will see another process that is going to need a shared mem segment larger than 6Gb.