Oracle SGA Spans Multiple Segments

[spex]

The HP-UX Performance Cookbook says that performance is negatively impacted when Oracle's SGA spans multiple segments. Could someone provide a specific example of this happening and the performance impact?

[A. Clay Stephenson · Accepted solution]

They are referring to cases where the tunable shmmax is set too small (e.g. 1GB) but the size of the SGA is larger (e.g 5GB). In that case, Oracle shmget()'s multiple segments (at least 5 in this case) to build the SGA but this fragmented access is less efficient than 1 contiguous memory area. (I assume that you are referring to 64-bit HP-UX and Oracle.)

If you wish to measure this effect then reduce shmmax and leave your SGA large. Do an ipcs -ma and you will see multiple shared memory segents associated with the same Oracle instance. Do some benchmarks comparing the performance of 1 large vs multiple small segments.

[TwoProc]

This is referring to an old data point that the Oracle database could keep only so many data segments accessible at once. If my memory is correct (and this could be wrong, but the point would be the same) it was 5 (maybe 7?). The system could simultaneously read/write/update 5(7?) memory segments at once, if you had more than this than those other segments would have to be turned on live, and then some other one would go cold . This would make your requests kind of queue up behind this switching mechanism.

Regardless, I would say that it would certainly be less overhead to let Oracle manage its shared memory segments in as few as possible. You should always try to do them in as few as possible, by making sure that shmmax is bigger than the SGA you're trying to stand up, when and where possible.

[spex]

A. Clay & John,

Thank you for the insight. Yes, 64-bit HP-UX 11.00 and Oracle 8i on this box.

# kmtune |grep shmmax
shmmax 1073741824

# ipcs -ma |awk '/oracle/ {print $10}'
1073741824
564658176
579985408

The first two lines are the buffer cache, and the third is the remainder of the pools.

I won't be able to reboot for some time, so I was hoping someone could provide specific before and after figures from a similar adjustment (SGA in three segments with shmmax set to 1GB to one segment with shmmax at, say, 3GB). I'm basically wondering if it's worth recompiling just for this.

[TwoProc]

I'd do it, but it's not urgent. Do it at your next scheduled reboot/patch/etc.