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.)
See also ‘Oracle Parallel Server Tuning - Reduce dynamic resource allocation’.
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).
See also ‘Oracle Parallel Server Tuning - Reduce dynamic resource allocation’.
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.