The Puzzle

A UNIX administrator contacted me (all the way from San Diego) with a problem regarding semaphore configuration on a new HP-UX 11i v3 server running an Oracle database:

The default semaphore settings were:

SEMMSL=2048   (semaphores per set)
SEMMNI=2048   (number of sets)

He had read Oracle’s installation documentation which said that the product of these two parameters must be at least the number of PROCESSES as defined in the Oracle database’s init.ora.  By default SEMMSL x SEMMNI = 4,194,304; much higher than the database’s PROCESSES parameter of 10,000.

Following Tune’n’Tools guidance, the UNIX administrator changed the parameters to:

SEMMSL=128
SEMMNI=8192

The new settings resulted in product of 1,048,576, which should support 10,000 database processes right?  So why did the database fail to start and report the following error?

SQL> startup nomount pfile="/${ORACLE_SID}/oracle/admin/pfile/init${ORACLE_SID}.ora";
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semids_per_proc failed with status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: sskgpwcr2
ORA-27303: additional information: semids = 136, maxprocs = 10000

The Solution

Oracle will try to get PROCESSES+4 semaphores per set/id, and then divide the result by two until it is smaller than SEMMSL.  In this case, we start with 10,004 and end up with 78 (74 for database and 4 overhead per set).  This requires 136 sets/ids.

There is an Oracle compilation parameter SS_SEM_MAX which limits the maximum number of semaphore sets oracle will allocate, and this normally defaults to 10.  OS tuning tools may not take this into consideration when making recommendations.

So, there is a hidden limit: Least(SEMMNI,10) x (SEMMSL-4) >= PROCESSES

For the case above: 10 x 124 < 10,000 [error], but 10 x 2044 > 10,000 [no error].

(See Metalink Doc 15566.1 for more about SS_SEM_MAX).

Metalink Doc 15654.1 ( TECH: Calculating Oracle’s SEMAPHORE Requirements) gives this guidance:

SEMMSL is an arbitrary figure which is best set to a round figure no smaller that the smallest ‘processes’ figure for any database on the system.

Which makes sense given the SS_SEM_MAX limitation, and given that the init parameter “processes” for a DB may be increased after the initial installation.  I’d suggest SEMMSL be at least 4 + maximum expected PROCESSES value for all instances, or else Oracle’s “halve and try again” method may introduce inefficiencies.

The default values of SEMMSL=2048 and SS_SEM_MAX=10 would support up to 16,387 processes for a single instance, or ten instances with 2,000 processes each.  Not many people would have run into the SS_SEM_MAX limitation with these default values.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s