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.

[Ralph Grothe]

Hi Court,

at the moment the value is set to abt. 22 GB (of 32 GB RAM), so I would think I could well leave it at that.

# kmtune -q shmmax|perl -alne 'print hex($F[1])/2**30 if $F[0] eq "shmmax"'
22


How can I find out how much memory was assigned to the old 9i DB?
In the pfile init.ora I couldn't find some sga param.
There must exist a view in the data dictionary that one can query by a simple SQL statement?

[Ralph Grothe]

Wait, I think this should suffice as a clue

# ipcs -mob|grep oracle
m 368649 0xe0377310 --rw-rw---- oracle dba 13 98275328
m 3082 0xe71d289c --rw-r----- oracle dba 797 13826273280

[Court Campbell]

Login to sqlplus and;

>show sga

That will show the sga size.

[Court Campbell]

I truly recommend that you read this:

http://orafaq.com/node/8

I believe that it will answer a lot of your questions.

[TwoProc]

Small point - but , set it just a bit bigger than db_cache_size - because this most likely is your largest piece of the SGA. The other parts of the SGA will be perfectly happy living in their own segment(s) after the buffer cache gets its own big space.

[Tim Nelson]

Duely noted Court,

I think all responses are valid and helpfull enough to help Ralph make his decision.

[Ralph Grothe]

show sga:

SQL>
Total System Global Area 1.3809E+10 bytes
Fixed Size 756808 bytes
Variable Size 1795162112 bytes
Database Buffers 1.2012E+10 bytes
Redo Buffers 1060864 bytes


which indeed is the shm seg @ ID 522 as I already have assumed:

# ipcs -mob|grep ora
m 167945 0xe0377310 --rw-rw---- oracle dba 12 98275328
m 522 0xe71d289c --rw-rw---- oracle dba 1165 13826273280