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.
PCM locks are tunable by setting the INIT.ORA parameters shown in the following table:
|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.|
Set LM_PROCS equal to:
PROCESSES x number-of-nodes
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.)
Oracle provides two separate means of calculating
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.
This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.