shmmax at 100%

[Mauro Livi]

Hi,

We have a new rx2800 i4 with 128 GB (HP-UX 11.31) of physical memory running Oracle 11g.

I noticed that the shmmax is currently at 100% and it is set at the default 1GB. Oracle recommendation is to set it to the value of  " the amount of available physical memory on the system". Well I tend to get skeptical in setting this to 128GB "just because Oracle says so".

So I thought I'd get a second opinion. I thought to maybe try to set it at 50GB and see how that goes then increment from there as needed. Thoghts?

Any input would  be greatly appreciated.

Thanks

Mauro

[Bill Hassell]

shmmax is not a consummable resource, it is a sysadmin limit to the maximum segment size a program can request. Oracle used to request very large segments years ago but now typically grabs multiple segments as needed. To set shmmax to "unlimited" is simply uninformed. As a sysadmin, I would set shmmax to 1 or 2 GB. Oracle may grab 10 segments if needed, but to allow an unlimited segment size is not a good idea. I believe that Oracle is discouraging small segments (like 100 MB) as segment management and overhead would be a poor choice.

[Mauro Livi]

Hi Bill,

Thank you for your input. I tend to agree with your assessment of shmmax. The puzzling thing, however, is that in referencing my trusty "HP-UX Performance Cookbook" (attached for those who would like to view it), doesn't seem to object to setting shmmax to the max (WHICH I DEFINITELY WILL NOT DO).

Granted, I don't take everything this white paper says as a "bible" on tuning, but it has been pretty informative. For my purpose I'll probably find a "happy medium" for this setting and go on with life. I typically want to get someone else's opinion, and yours was very much valued and appreciated...so thanks again.

Below is an excerpt on the shmmax from the white paper and I've attached it in its entirety.

Mauro

shmmax
We have seen 64bit Oracle break up it’s SGA shared memory allocations (ipcs –ma)
when this tunable is configured too low. This can hurt performance: if you have the
physical memory available, then let the DB allocate as much as it needs in one chunk.
Bump the segment limit up to its max (unless you fear ‘rogue’ applications causing a
problem by hogging shared memory, which typically ain’t nuthin’ to worry about). The
default is 1GB… a little too low for big servers.

[Bill Hassell]

I would be curious if there is a measureable performance hit with one segment versus 10.

You can check the current segment count with:

# ipcs -bmop

Depending on how massive the Oracle SGA (or SGAs) are, you can certainly combine the segments for the instance that has the largest total (ie, 50GB) and set shmmax = 60 GB. It doesn't mean that each Oracle instance will request 60 GB at a time, it just sets a limit to the largest segment that can be requested. Using a single segment is probably simpler inside the Oracle engine.

[Mauro Livi · Accepted solution]

60GB sounded about right, but I went just below that and set it to 48GB and things seem to be humming along quite nicely.

I probably have some room to even tune that down further, but I'll see how 48GB works out in the near term and go from there.

As always, I appreciate the input.

Thanks

Mauro