Ensuring DLM Capacity for All Resources & Locks
To reduce contention for shared resources and gain maximum performance from the parallel server, you must ensure that adequate space is available in the DLM for all the locks and resources your system requires. This chapter covers the following topics:
See Also: "Non-PCM Instance Locks" ![[*]](jump.gif)
for a conceptual overview.
Overview
Planning PCM locks alone is not sufficient to manage locks on your system. Besides explicitly allocating parallel cache management locks, you must actively ensure that your DLM has adequate space for all the required PCM and non-PCM locks and resources.
Attention: With some DLMs, you must configure the number of locks and resources on a per node basis; with others, you must configure the number of locks and resources system-wide.
Many different types of non-PCM lock exist, and each is handled differently. Although you cannot directly adjust their number, you can estimate the overall number of non-PCM resources and locks required, and adjust either the DLM or the initialization parameters (or both) to guarantee adequate space. You also have the option of minimizing table locks to optimize performance.
Planning DLM Capacity
The number of resources and locks managed by the DLM is configurable but fixed. The DLM will manage this number of items and then stop: it will not obtain more capacity dynamically.
For this reason, you must carefully plan DLM capacity for the total number of PCM and non-PCM resources and locks you will need.
To Plan DLM Capacity for All Resources & Locks
1. Determine whether your DLM needs to be configured on a per-node or system-wide basis. (To do this, check the DLM vendor's documentation.)
In case of failover, you need to have enough resources configured on the remaining instances so that the system can continue to operate. Thus if resources are divided over 10 instances and 5 instances were to fail, you would still want the system to be able to run on the remaining 5 instances. This means that you should allow some leeway in the system by accounting for overhead and setting large enough values for the Oracle initialization parameters determining DLM locks and resources for each instance.
3. Consider the sizing of each instance on each node: number of users, volume of transactions, and so on. Determine the values you will assign to each instance's initialization parameters.
4. Calculate the number of non-PCM resources and locks required, by filling in the worksheets provided in this chapter.
5. Calculate the number of PCM resources and locks required, by using the script described in "What Is the Total Number of PCM Locks & Resources Needed?" .
6. Configure your DLM to accommodate the required number of:
See Also: Your system-specific DLM documentation for details on how to configure the DLM.
Calculating the Number of Non-PCM Resources
Use the following worksheet to analyze your system resources.
To Calculate the Number of Non-PCM Resources
1. In the following worksheet, enter values for the PROCESSES, DML_LOCKS, TRANSACTIONS, and ENQUEUE_RESOURCES initialization parameters for each instance.
2. For at least one instance, enter the value of the DB_FILES parameter, which is the same for all instances. (The instance(s) on which you configure this parameter depends upon your system's DLM.)
3. Enter values for Enqueue Locks for each instance. For each instance, you can calculate this value as follows:
Enqueue Locks = 20 + (10 * SESSIONS) + DB_FILES + GC_LCK_PROCS +
(2 * PROCESSES) + (DB_BLOCK_BUFFERS/64)
4. For each instance, enter values for Parallel Query Overhead to cover inter-instance communication. For individual instances, you can calculate this value as follows:
PQ Overhead = 7 + (MAXINSTANCES * PARALLEL_MAX_SERVERS) + PARALLEL_MAX_SERVERS + MAXINSTANCES
5. Add the entries horizontally to obtain the Subtotals: # of Non-PCM Resources per Instance.
6. Add the per-instance subtotals to obtain the Total Number of Non-PCM Resources System-Wide.
| Inst.
No.
| PRO-
CESSES
| DML_
LOCKS
| TRANS-
ACTIONS
| ENQUEUE_
RESOURCES
| DB_FILES
(on one or more instances)
| Enqueue Locks
| PQ
Over-
head
| Over-
head
| Subtotals:
# of Non-PCM Locks per Instance
|
| 1
|
|
|
|
|
|
|
| 200
|
|
| 2
|
|
|
|
|
|
|
| 200
|
|
| 3
|
|
|
|
|
|
|
| 200
|
|
| 4
|
|
|
|
|
|
|
| 200
|
|
| Total Number of Non-PCM Locks System-Wide
|
|
|
|
|
|
|
|
|
|
Table 17 - 1. Worksheet: Calculating Non-PCM Resources
7. Finally, use the figures derived from this worksheet to ensure that your DLM configuration accommodates all non-PCM resources, as described in step 6 .
Note: The worksheet incorporates a standard overhead value of 200 for each instance.
Table 17 - 2 shows sample values for a system with four instances, and with PARALLEL_MAX_SERVERS set to 8 for instances 1 and 3, and set to 4 for instances 2 and 4. The buffer cache size is assumed to be 10K.
| Inst.
No.
| PRO-
CESSES
| DML_
LOCKS
| TRANS-
ACTIONS
| ENQUEUE_
RESOURCES
| DB_FILES
(on one or more instances)
| Enqueue Locks
| PQ
Over-
head
| Over-
head
| Subtotals:
# of Non-PCM Locks per Instance
|
| 1
| 200
| 500
| 50
| 800
| 30
| 2808
| 51
| 200
| 4639
|
| 2
| 350
| 600
| 100
| 1000
| --
| 4128
| 31
| 200
| 6409
|
| 3
| 175
| 400
| 75
| 800
| --
| 2453
| 51
| 200
| 4154
|
| 4
| 225
| 350
| 125
| 1200
| --
| 3103
| 31
| 200
| 5234
|
| Total Number of Non-PCM Locks System-Wide
|
|
|
|
|
|
|
|
| 20436
|
Table 17 - 2. Calculating Non-PCM Resources (Example)
Calculating the Number of Non-PCM Locks
Use the following worksheet to analyze your system's lock needs.
To Calculate the Number of Non-PCM Locks
1. In the following worksheet, enter values for the PROCESSES, DML_LOCKS, TRANSACTIONS, and ENQUEUE_RESOURCES parameters for each instance.
2. For each instance, enter the value of the DB_FILES parameter, which is the same for all instances.
3. Enter values for Enqueue Locks for each instance. For each instance, you can calculate this value as follows:
Enqueue Locks = 20 + (10 * SESSIONS) + DB_FILES + GC_LCK_PROCS +
(2 * PROCESSES) + (DB_BLOCK_BUFFERS/64)
4. For each instance, enter values for Parallel Query Overhead to cover inter-instance communication. For individual instances, you can calculate this value as follows:
PQ Overhead = 7 + (MAXINSTANCES * PARALLEL_MAX_SERVERS) + PARALLEL_MAX_SERVERS + MAXINSTANCES
5. Add the entries horizontally to obtain the Subtotals: # of Non-PCM Locks per Instance.
6. Add the per-instance Subtotals to obtain the Total Number of Non-PCM Locks System-Wide.
| Inst.
No.
| PRO-
CESSES
| DML_
LOCKS
| TRANS-
ACTIONS
| ENQUEUE_
RESOURCES
| DB_FILES
(for all instances)
| Enqueue Locks
| PQ
Over-
head
| Over-
head
| Subtotals:
# of Non-PCM Locks per Instance
|
| 1
|
|
|
|
|
|
|
| 200
|
|
| 2
|
|
|
|
|
|
|
| 200
|
|
| 3
|
|
|
|
|
|
|
| 200
|
|
| 4
|
|
|
|
|
|
|
| 200
|
|
| Total Number of Non-PCM Locks System-Wide
|
|
|
|
|
|
|
|
|
|
Table 17 - 3. Worksheet: Calculating Non-PCM Locks
7. Finally, use the figures derived from this worksheet to ensure that your DLM configuration accommodates all non-PCM locks, as described in step 6 .
Note: The worksheet incorporates a standard overhead value of 200 for each instance.
Table 17 - 4 shows sample values for a system with four instances, again assuming that PARALLEL_MAX_SERVERS is set to 8 for instances 1 and 3, and set to 4 for instances 2 and 4. The buffer cache size is assumed to be 10K.
| Inst.
No.
| PRO-
CESSES
| DML_
LOCKS
| TRANS-
ACTIONS
| ENQUEUE_
RESOURCES
| DB_FILES
(for all instances)
| Enqueue Locks
| PQ -
Over-
head
| Over-
head
| Subtotals:
# of Non-PCM Locks per Instance
|
| 1
| 200
| 500
| 50
| 800
| 30
| 2808
| 51
| 200
| 4639
|
| 2
| 350
| 600
| 100
| 1000
| 30
| 4128
| 31
| 200
| 6439
|
| 3
| 175
| 400
| 75
| 800
| 30
| 2453
| 51
| 200
| 4184
|
| 4
| 225
| 350
| 125
| 1200
| 30
| 3103
| 31
| 200
| 5264
|
| Total Number of Non-PCM Locks System-Wide
|
|
|
|
|
|
|
|
| 20526
|
Table 17 - 4. Calculating Non-PCM Locks (Example)
Adjusting Oracle Initialization Parameters
Another way to ensure that your system has enough space for the required non-PCM locks and resources is to adjust the values of the following Oracle initialization parameters:
DB_BLOCK_BUFFERS
DB_FILES
DML_LOCKS
GC_LOCK_PROCS
PARALLEL_MAX_SERVERS
PROCESSES
SESSIONS
TRANSACTIONS
Begin by experimenting with these values in the worksheets supplied in this chapter. You could artificially inflate parameter values in the worksheets, in order to see the DLM ramifications of providing extra room for failover.
Do not, however, specify actual parameter values considerably greater than needed for each instance. Setting these parameters unnecessarily high entails overhead in a parallel server environment.
Minimizing Table Locks to Optimize Performance
This section describes two strategies for improving performance by minimizing table locks:
Obtaining table locks (DML locks) for inserts, deletes, and updates can hurt performance in a parallel server environment. Locking a table in a parallel server is very undesirable because all instances holding locks on the table must release those locks. Consider disabling these locks entirely.
Note: If you use either of these strategies you cannot perform DDL commands against either the instance or the table.
Setting DML_LOCKS to Zero
Table locks are set with the initialization parameter DML_LOCKS. If the DROP TABLE, CREATE INDEX, and LOCK TABLE commands are not needed, set DML_LOCKS to zero in order to minimize lock conversions and gain maximum performance.
Note: If DML_LOCKS is set to zero on one instance, it must be set to zero on all instances. With other values, this parameter need not be identical on all instances.
Disabling Table Locks
To prevent any user from acquiring a table lock, you can use the following command:
ALTER TABLE table_name DISABLE TABLE LOCK
Any user attempting to lock a table when its table lock is disabled will receive an error.
To re-enable table locking, the following command is used:
ALTER TABLE table_name ENABLE TABLE LOCK
The above command waits until all currently executing transactions commit before enabling the table lock. Note that the command does not need to wait for new transactions which start after the enable command was issued.
To determine whether a table has its table lock enabled or disabled, you can query the column TABLE_LOCK in the data dictionary table USER_TABLES. If you have select privilege on DBA_TABLES or ALL_TABLES, you can query the table lock state of other users tables.
