topic Re: Recommendations for tunable shmmax in preparation for Oracle10g in Operating System - HP-UX

Recommendations for tunable shmmax in preparation for Oracle10g

Ralph Grothe — Fri, 16 Mar 2007 10:48:32 GMT

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

Re: Recommendations for tunable shmmax in preparation for Oracle10g

Tim Nelson — Fri, 16 Mar 2007 11:00:17 GMT

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.

Re: Recommendations for tunable shmmax in preparation for Oracle10g

Court Campbell — Fri, 16 Mar 2007 11:02:43 GMT

Here:

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

Re: Recommendations for tunable shmmax in preparation for Oracle10g

Court Campbell — Fri, 16 Mar 2007 11:04:58 GMT

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.

Re: Recommendations for tunable shmmax in preparation for Oracle10g

Ralph Grothe — Fri, 16 Mar 2007 11:06:42 GMT

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.

Re: Recommendations for tunable shmmax in preparation for Oracle10g

Court Campbell — Fri, 16 Mar 2007 11:12:29 GMT

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.

Re: Recommendations for tunable shmmax in preparation for Oracle10g

Court Campbell — Fri, 16 Mar 2007 11:14:54 GMT

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.

Re: Recommendations for tunable shmmax in preparation for Oracle10g

Tim Nelson — Fri, 16 Mar 2007 11:14:57 GMT

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.

Re: Recommendations for tunable shmmax in preparation for Oracle10g

A. Clay Stephenson — Fri, 16 Mar 2007 11:15:32 GMT

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.

Re: Recommendations for tunable shmmax in preparation for Oracle10g

Court Campbell — Fri, 16 Mar 2007 11:23:38 GMT

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.

Re: Recommendations for tunable shmmax in preparation for Oracle10g

Ralph Grothe — Fri, 16 Mar 2007 11:36:07 GMT

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?

Re: Recommendations for tunable shmmax in preparation for Oracle10g

Ralph Grothe — Fri, 16 Mar 2007 11:43:03 GMT

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

Re: Recommendations for tunable shmmax in preparation for Oracle10g

Court Campbell — Fri, 16 Mar 2007 11:57:13 GMT

Re: Recommendations for tunable shmmax in preparation for Oracle10g

Court Campbell — Fri, 16 Mar 2007 11:59:29 GMT

I truly recommend that you read this:

http://orafaq.com/node/8

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

Re: Recommendations for tunable shmmax in preparation for Oracle10g

TwoProc — Fri, 16 Mar 2007 12:32:31 GMT

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.

Re: Recommendations for tunable shmmax in preparation for Oracle10g

Tim Nelson — Fri, 16 Mar 2007 13:01:06 GMT

Duely noted Court,

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

Re: Recommendations for tunable shmmax in preparation for Oracle10g

Ralph Grothe — Wed, 21 Mar 2007 07:41:41 GMT

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