Oracle Parallel Server How To...

Locking - Notes on

Types of parallel server lock are:

Parallel Cache Management (PCM) locks
Table locks (i.e. DML_LOCKS)
Transaction Locks
System change number (SCN) locks.
SCNs are used to time stamp changes made in the database.

Parallel Cache Management (PCM) Locks

PCM locks are tunable by setting the INIT.ORA parameters shown in the following table:

Parameter	Recommendation
GC_FILES_TO_LOCKS	Oracle recommends tuning. See ‘Parallel Server Concepts and Administration’ for guidance on how to set this
GC_ROLLBACK_LOCKS	Default is probably ok.
GC_LCK_PROCS	Default is probably ok.
GC_LATCHES	Default is probably ok.
GC_RELEASEABLE_LOCKS	Default is probably ok.
LM_LOCKS	See ‘Parallel Server Concepts and Administration’ or notes below for guidance on how to set this.
LM_PROCS	See ‘Parallel Server Concepts and Administration’ or notes below for guidance on how to set this.
LM_RESS	The default is probably too low. See ‘Parallel Server Concepts and Administration’ or notes below for guidance on how to set this.

LM_PROCS

Set LM_PROCS equal to:

PROCESSES x number-of-nodes

LM_RESS

Set LM_RESS equal to:

2 * (GC_FILES_TO_LOCKS + GC_ROLLBACK_LOCKS (fixed-only) + GC_RELEASABLE_LOCKS)

The ‘GC_FILES_TO_LOCKS’ value here indicates the number of non-releasable locks allocated to files. (This is 0 by default.)

LM_LOCKS

Oracle provides two separate means of calculating LM_LOCKS. The simplest is:

LM_RESS + (LM_RESS * ( <number_of_nodes> - 1) ) / <number_of_nodes>

Which, for a two node system is simply:

LM_RESS + LM_RESS / 2

A more detailed formula is available in the Parallel Server Concepts and Administration guide, and this will tend to yield a significantly lower figure (although it will always be greater than LM_RESS).

These notes have been tested with Oracle Parallel Server 8.0.5 running under NT 4.