Oracle8 Parallel Server Concepts & Administration
Release 8.0

A58238-01

Library

Product

Contents

Index

Prev Next

16
Ensuring IDLM Capacity for All Resources & Locks

To reduce contention for shared resources and gain maximum performance from the parallel server, you must ensure that the Integrated Distributed Lock Manager (Integrated DLM, or IDLM) is adequately configured for all the locks and resources your system requires. This chapter covers the following topics:

See Also: Chapter 10, "Non-PCM Instance Locks", 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 the Integrated DLM is adequately configured, on each node, for all the required PCM and non-PCM locks and resources. Bear in mind that larger databases and higher degree of parallelism require increased demand 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.

Planning IDLM Capacity

Carefully plan and configure an appropriate number of resources and locks to be managed by the Integrated DLM. You allocate these locks and resources using the initialization parameters LM_LOCKS and LM_RESS. Although additional locks and resources can be allocated dynamically, this should be avoided.

Avoiding Dynamic Allocation of Resources and Locks

If the number of locks or resources required becomes greater than the amount you have statically allocated, then the additional locks or resources will be allocated from the SGA shared pool. This feature prevents the instance from stopping.

When dynamic allocation occurs, a message is written 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.

Computing Lock and Resource Needs

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.

  1. Consider failover requirements.

    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 IDLM locks and resources for each instance.

  2. 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.
  3. Calculate the number of non-PCM resources and locks required, by filling in the worksheets provided in this chapter.
  4. Calculate the number of PCM resources and locks required, by using the script in "What Is the Total Number of PCM Locks and Resources Needed?" on page 15-19.
  5. Configure the IDLM to accommodate the required number of:
    • non-PCM resources
    • non-PCM locks
    • PCM resources
    • PCM locks

Monitoring Resource Utilization

The V$RESOURCE_LIMIT view provides information about global resource utilization for some of the system resources. Using this view you can monitor the current and the maximum resource utilization, and be forewarned if the values approach the limit. With this information you can make better decisions when choosing values for resource limit-controlling parameters.

See Also: "Setting LM_* Parameters" on page 18-11
Oracle8 Reference regarding V$RESOURCE_LIMIT
Oracle8 Tuning for a complete discussion of resource limits

Calculating the Number of Non-PCM Resources

Use the following worksheet to analyze your system resources.

  1. In the following worksheet, enter values for the PROCESSES, DML_LOCKS, TRANSACTIONS, and ENQUEUE_RESOURCES initialization 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)
  • 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

  • Add the entries horizontally to obtain the Subtotals: # of Non-PCM Resources per Instance.
  • Add the per-instance subtotals to obtain the Total Number of Non-PCM Resources System-Wide.
  • Table 16-1 Worksheet: Calculating Non-PCM Resources
    Inst.
    No.
     
    PRO-
    CESSES
     
    DML_
    LOCKS
     
    TRANS-
    ACTIONS
     
    ENQUEUE_
    RESOURCES
     
    DB_FILES
    (on one or more instances)
     
    Enqueue Locks   PQ Overhead   Over-head   Subtotals:
    # NonPCM Locks per Instance
     

    1

     

     

     

     

     

     

     

     

    200

     

     

    2

     

     

     

     

     

     

     

     

    200

     

     

    3

     

     

     

     

     

     

     

     

    200

     

     

    4

     

     

     

     

     

     

     

     

    200

     

     

    Total Number of Non-PCM Locks System-Wide:

     

     

    1. Finally, use the figures derived from this worksheet to ensure that LM_RESS is set to accommodate all non-PCM resources (see step 6 on page 16 - 4).

      Note: The worksheet incorporates a standard overhead value of 200 for each instance.

    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.

    Table 16-2 Calculating Non-PCM Resources (Example)
    Inst.
    No.
     
    PRO-
    CESSES
     
    DML_
    LOCKS
     
    TRANS-
    ACTIONS
     
    ENQUEUE_
    RESOURCES
     
    DB_FILES
    (on one or more instances)
     
    Enqueue Locks   PQ Over-
    head
     
    Overhead   Subtotals:
    # 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

     

    Calculating the Number of Non-PCM Locks

    Use the following worksheet to analyze your system's lock needs.

    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.

    Table 16-3 Worksheet: Calculating Non-PCM Locks
    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:

     

     

    1. Finally, use the figures derived from this worksheet to ensure that the LM_LOCKS parameter is configured to accommodate all non-PCM locks (see step 6 on page 16 - 4).

    Note: The worksheet incorporates a standard overhead value of 200 for each instance.

    Table 16-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.

    Table 16-4 Calculating Non-PCM Locks (Example)
    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

     

    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_LCK_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 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.

    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.




    Prev

    Next
    Oracle
    Copyright © 1997 Oracle Corporation.

    All Rights Reserved.

    Library

    Product

    Contents

    Index