Release 8.1.5
A67778-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Parallel Server Concepts and Administration Release 8.1.5 A67778-01 |
|
To reduce contention for shared resources and gain maximum Oracle Parallel Server (OPS) performance, ensure that the Integrated Distributed Lock Manager (IDLM) is adequately configured for all locks and resources your system requires. This chapter covers the following topics:
Planning PCM locks alone is not sufficient to manage locks on your system. Besides explicitly allocating parallel cache management locks, you must actively ensure IDLM is adequately configured, on each node, for all required PCM and non-PCM locks and resources. Consider also that larger databases and higher degrees of parallelism require increased demands for many resources.
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 the LM_* or GC_* initialization parameters (or both) to guarantee adequate space. You also have the option of minimizing table locks to optimize performance.
Carefully plan and configure the number of resources and locks to be managed by the IDLM. Allocate these locks and resources using the initialization parameters LM_LOCKS and LM_RESS. Although additional locks and resources can be allocated dynamically, you should avoid this.
If the number of locks or resources required becomes greater than the amount you have allocated, additional locks or resources will be allocated from the SGA shared pool. This feature prevents the instance from stopping.
Dynamic allocation causes Oracle to write a message to the alert file indicating that you should recompute and adjust the initialization parameters for the next time the database is started. Since performance and memory usage may be adversely affected by dynamic allocation, it is highly recommended that you correctly compute your lock and resource needs.
The recommended default value for SHARED_POOL_SIZE is 16MB for 64-bit applications, and 8MB for 32-bit applications.
Use the following approach to carefully plan IDLM capacity, on a per node basis, for the total number of PCM and non-PCM resources and locks needed.
In case of failover, you need enough resources configured on the remaining instances so the system can continue operating. Thus, if resources are shared by 10 instances and 5 instances fail, the system must be able to run on the remaining 5 instances. To do this, you must somewhat over estimate system resources by accounting for overhead. In other words, set large enough values for the Oracle initialization parameters determining IDLM locks and resources for each instance.
The V$RESOURCE_LIMIT view provides information about global resource use for some system resources. Using this view to monitor the current and maximum resource use. It is important to notice when the values approach the limits. With this information you can make better decisions when choosing values for resource limit-controlling parameters.
|
See Also:
"Determining the Amount of Locks Needed and Setting LM_* Parameters" . Also refer to the Oracle8i Reference regarding V$RESOURCE_LIMIT, and to Oracle8i Tuning for a complete discussion of resource limits. |
Use the following worksheet to analyze your system resources.
PQ Overhead = 7 + (MAXINSTANCES * PARALLEL_MAX_SERVERS) + PARALLEL_MAX_SERVERS + MAXINSTANCES
Table 16-1 Worksheet: Calculating Non-PCM Resources
Table 16-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.
Another way to ensure your system has enough space for the required non-PCM locks and resources is to adjust the values of the following Oracle initialization parameters:
Begin by experimenting with these values in the worksheets supplied in this chapter. You could artificially inflate parameter values in the worksheets to see the IDLM 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.
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 OPS. Locking a table in OPS is very undesirable because all instances holding locks on the table must release those locks. Consider disabling these locks entirely.
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 to minimize lock conversions and gain maximum performance.
To prevent users from acquiring table locks, use the following command:
ALTER TABLE table_name DISABLE TABLE LOCK
Users attempting to lock a table when its table lock is disabled will receive an error.
To re-enable table locking, use the following command:
ALTER TABLE table_name ENABLE TABLE LOCK
The above command waits until all currently executing transactions commit before enabling the table lock. The command does not need to wait for new transactions starting after issuing the ENABLE command.
To determine whether a table has its table lock enabled or disabled, 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.
