Oracle8i Parallel Server Administration, Deployment, and Performance
Release 2 (8.1.6)

Part Number A76970-01





Go to previous page Go to next page

Planning the Use of PCM and Non-PCM Instance Locks

This chapter explains the initialization parameters you set to allocate Parallel Cache Management (PCM) locks and non-PCM locks to data files in Oracle Parallel Server environments. It contains the following sections:

Planning the Use and Maintenance of PCM Locks

This section describes planning the use and maintenance of PCM locks. It covers:

Planning and Maintaining Instance Locks

The Distributed Lock Manager (DLM) allows you to allocate only a finite number of locks. For this reason you need to analyze and plan for the number of locks your application requires. You also need to know how much memory the locks and resources require. In planning locks, consider the following issues:

The Key to Allocating PCM Locks

The key to allocating PCM locks is to analyze how often data is changed using the INSERT, UPDATE, and DELETE statements. You can then determine how to group objects into files based on whether they should be read-only or read/write. Finally, assign locks based on the groupings you have made. In general, follow these guidelines:

The key distinction is not between types of objects (index or table), but between operations being performed on an object. The operation dictates the quantity of locks needed.

See Also:

Chapter 5 and Chapter 6

Examining Data Files and Data Blocks

You must allocate locks at various levels by specifying:

Begin by examining your data files and the blocks they contain.

Determining File ID, Tablespace Name, and Number of Blocks

Use the following statement to determine the file ID, file name, tablespace name, and number of blocks for all databases.


Oracle displays results as in the following example:

/v7/data/data01.dbf     1           SYSTEM               200
/v7/data/data02.dbf     2           ROLLBACK            1600
. . .

Determining the Number of Locks You Need

Use the following approach to estimate the number of locks required for particular uses.

How Oracle Assigns Locks to Blocks

This section explains how fixed locks and releasable locks are assigned to blocks. (1:1 locks, of course, have a one-to-one correspondence to blocks.)

File-to-Lock Mapping

Two data structures in the System Global Area control file-to-lock mapping. The first structure maps each file (DB_FILES) to a bucket (index) in the second structure. This structure contains information on the number of locks allocated to this bucket, base lock number, and grouping factor. To find the number of locks for a tablespace, count the number of actual fixed locks that protect the different files. If files share locks, you count the shared locks only once.

  1. To find the number of locks for a tablespace, begin by performing a select from the FILE_LOCK data dictionary table:


    For example, Oracle responds with something similar to the following if you set GC_FILES_TO_LOCKS="1=500:5=200":

    ------- --------------- -------------- ---------- ---------- ----------  
          1 \\.\OPS_SYS01   SYSTEM                100       1500          1  
          2 \\.\OPS_USR01   USER_DATA            1600       3000          1  
          3 \\.\OPS_RBS01   ROLLBACK_DATA           0        100          1  
          4 \\.\OPS_TMP01   TEMPORARY_DATA          0        100          1  
          5 \\.\OPS_USR03   TRAVEL_DEMO          4600       4000          1  
          6 \\.\PROBLEM_REP PROBLEM_REP             0        100          1  
    6 rows selected. 
  2. Count the number of locks in the tablespace by summing the number of locks (value of the NLOCKS column) only for rows with different values in the START_LCK column.

    In this example, both file 1 and file 5 have different values for START_LCK. You therefore sum their NLOCKS values for a total of 700 locks.

If, however, you had set GC_FILES_TO_LOCKS="1-2=500:5=200", your results would look like the following:

1        file1      system             1         500    1
1        file2      system             1         500    1
1        file3      system             0
1        file4      system             0
1        file5      system           501         200    1

This time, file 1 and file 2 have the same value for START_LCK indicating that they share the locks. File 5 has a different value for START_LCK. You therefore count once the 500 locks shared by files 1 and 2, and add an additional 200 locks for file 5, for a total of 700.

Number of Locks Per Block Class

You need only concern yourself with the number of blocks in the data and undo block classes. Data blocks (class 1) contain data from indexes or tables. Undo header blocks (class 10) are also known as the rollback segment headers or transaction tables. System undo blocks (class 11) are part of the rollback segment and provide storage for undo records.

User undo segment n header blocks are identified as class 10 + (n x 2), where n represents the rollback segment number. A value of n = 0 indicates the system rollback segment; a value of n > 0 indicates a non-system rollback segment. Similarly, user undo segment n header blocks are identified as class 10 + ( (n x 2) + 1).

The following query shows the number of locks allocated per class:


The following query shows the number of fixed (non-releasable) PCM locks:

     WHERE bitand(flag, 4)!=0;

The following query shows the number of releasable PCM locks:

     WHERE bitand(flag, 4)=0;

Lock Element Number

For a data class block the file number is determined from the data block address (DBA). The bucket is found through the X$KCLFI dynamic performance table. Data class blocks are fixed to lock element numbers as follows:

Other block classes are fixed to lock element numbers as follows:

(DBA) modulo (locks_in_class)

Examples of Mapping Blocks to PCM Locks


The following examples show different ways of mapping blocks to PCM locks and how the same locks are used on multiple data files.


These examples discuss very small sample files to illustrate important concepts. The actual files you manage will be significantly larger. 

Figure 7-1 Mapping PCM Locks to Data Blocks

Example 1

Figure 7-1 shows an example of mapping blocks to PCM locks for the parameter value GC_FILES_TO_LOCKS = "1=60:2-3=40:4=140:5=30".

In data file 1 shown in Figure 7-1, 60 PCM locks map to 120 blocks, which is a multiple of 60. Each PCM lock therefore covers two data blocks.

In data files 2 and 3, 40 PCM locks map to a total of 160 blocks. A PCM lock can cover either one or two data blocks in data file 2, and two or three data blocks in data file 3. Thus, one PCM lock may cover three, four, or five data blocks across both data files.

In data file 4, each PCM lock maps exactly to a single data block, since there is the same number of PCM locks as data blocks.

In data file 5, 30 PCM locks map to 170 blocks, which is not a multiple of 30. Each PCM lock therefore covers five or six data blocks.

Each of the PCM locks illustrated in Figure 7-1 can be held in either read-lock mode or read-exclusive mode.

Example 2

The following parameter setting allocates 500 PCM locks to data file 1; 400 PCM locks each to files 2, 3, 4, 10, 11, and 12; 150 PCM locks to file 5; 250 PCM locks to file 6; and 300 PCM locks collectively to files 7 through 9:

GC_FILES_TO_LOCKS = "1=500:2-4,10-12=400EACH:5=150:6=250:7-9=300" 

This example assigns a total of (500 + (6*400) + 150 + 250 + 300) = 3600 PCM locks. You may specify more than this number of PCM locks if you intend to add more data files.

Example 3

In Example 2, 300 PCM locks are allocated to data files 7, 8, and 9 collectively with the clause "7-9=300". The keyword EACH is omitted. If each of these data files contains 900 data blocks, for a total of 2700 data blocks, then each PCM lock covers 9 data blocks. Because the data files are multiples of 300, the 9 data blocks covered by the PCM lock are spread across the 3 data files; that is, one PCM lock covers 3 data blocks in each data file.

Example 4

The following parameter value allocates 200 PCM locks each to files 1 through 3; 50 PCM locks to data file 4; 100 PCM locks collectively to data files 5, 6, 7, and 9; and 20 data locks in contiguous 50-block groups to data files 8 and 10 combined:

GC_FILES_TO_LOCKS = "1-3=200EACH 4=50:5-7,9=100:8,10=20!50" 

In this example, a PCM lock assigned to the combined data files 5, 6, 7, and 9 covers one or more data blocks in each data file, unless a data file contains fewer than 100 data blocks. If data files 5 to 7 contain 500 data blocks each and data file 9 contains 100 data blocks, then each PCM lock covers 16 data blocks: one in data file 9 and five each in the other data files. Alternatively, if data file 9 contained 50 data blocks, half of the PCM locks would cover 16 data blocks (one in data file 9); the other half of the PCM locks would only cover 15 data blocks (none in data file 9).

The 20 PCM locks assigned collectively to data files 8 and 10 cover contiguous groups of 50 data blocks. If the data files contain multiples of 50 data blocks and the total number of data blocks is not greater than 20 times 50 (that is, 1000), then each PCM lock covers data blocks in either data file 8 or data file 10, but not in both. This is because each of these PCM locks covers 50 contiguous data blocks. If the size of data file 8 is not a multiple of 50 data blocks, then one PCM lock must cover data blocks in both files. If the sizes of data files 8 and 10 exceed 1000 data blocks, then some PCM locks must cover more than one group of 50 data blocks, and the groups might be in different files.

Sample Settings for Fixed Locks with GC_FILES_TO_LOCKS

Examples 5, 6, and 7 show the results of specifying various values of GC_FILES_TO_LOCKS. In the examples, files 1 and 2 each have 16 blocks of data.

Example 5


In this example four locks are specified for files 1 and 2. Therefore, the number of blocks covered by each lock is 8 ((16+16)/4). The blocks are not contiguous.

Figure 7-2 GC_FILES_TO_LOCKS Example 5

Example 6


In this example, four locks are specified for files 1 and 2. However, the locks must cover 8 contiguous blocks.

Figure 7-3 GC_FILES_TO_LOCKS Example 6

Example 7


In this example four locks are specified for file 1 and four for file 2. The locks must cover four contiguous blocks.

Figure 7-4 GC_FILES_TO_LOCKS Example 7

Sample Releasable Setting of GC_FILES_TO_LOCKS

The following example shows releasable locking mixed with fixed locking.

Example 8


File 1 has fixed PCM locking with 4 locks. On file 2, releasable locks are allocated on demand--none are initially allocated.

Figure 7-5 GC_FILES_TO_LOCKS Example 8

Using Worksheets to Analyze PCM Lock Needs

On large applications, carefully study the business processes involved. Worksheets similar to those in this section may be useful.

Determine the types of operations your system performs on a daily basis. The distinction between operations needing X locks and those needing S locks is a key issue. Every time Oracle converts a lock from one mode to the other, you need locks. Consider the interaction of different instances on a table. Also consider the number of rows in a block, the number of rows in a table, and the table's growth rate. Based on this analysis, group your objects into files, and assign free list groups.

Figure 7-6 PCM Lock Worksheet 1

Object  Operations needing X mode: Writes  Oracle Parallel Server needing S mode: Reads  TS/Data File 





Full table scan?

Single row? 

















Figure 7-7 PCM Lock Worksheet 2

Object  Instance 1  Instance 2  Instance 3 












Figure 7-8 PCM Lock Worksheet 3

Table Name  TS to put it in  Row Size  Number of Columns 













Mapping Fixed PCM Locks to Data Blocks

In many cases, you need relatively few PCM locks to cover read-only data compared to data that is updated frequently. This is because read-only data can be shared by all instances of an Oracle Parallel Server. Data that is never updated can be covered by a single PCM lock. Data that is not read-only should be covered by more than a single PCM lock.

If data is read-only, then once an instance owns the PCM locks for the read-only tablespace, the instance never disowns them. The DLM operations are not required after the initial lock acquisition.

For best results, partition your read-only tablespace so it is covered by its own set of PCM locks. Do this by placing read-only data in a tablespace that does not have writable data. Then allocate PCM locks to the data files in the tablespace using the GC_FILES_TO_LOCKS parameter.


Do not put read-only data and writable data in the same tablespace.  

Partitioning PCM Locks Among Instances

You can map PCM locks to particular data blocks to partition PCM locks among instances based on the data each instance accesses.

This technique minimizes unnecessary distributed lock management. Likewise, it minimizes the disk I/O caused by an instance having to write out data blocks because a requested data block was covered by a PCM lock owned by another instance.

For example, if Instance X primarily updates data in data files 1, 2, and 3, while Instance Y primarily updates data in data files 4 and 5, you can assign one set of PCM locks to files 1, 2, and 3 and another set to files 4 and 5. Then each instance acquires ownership of the PCM locks for the data it updates. One instance disowns the PCM locks only if the other instance needs access to the same data.

By contrast, if you assign one set of PCM locks to data files 3 and 4, I/O increases. This is because both instances regularly use the same set of PCM locks.

Non-PCM Instance Locks

This section describes some of the most common non-PCM instance locks. It covers the following information:

Overview of Non-PCM Instance Locks

This section explains how Oracle uses non-PCM locks to manage locks for transactions, tables, and other entities within an Oracle environment. Prefixes for each type of lock, such as "TX" for transaction locks and "TM" for table locks, refer to the naming scheme Oracle uses to identify them.

Figure 7-9 highlights non-PCM locks in relation to other locks used in Oracle.

Figure 7-9 Oracle Locking Mechanisms: Non-PCM Locks

Whereas PCM locks are static (you allocate them when you design your application), non-PCM locks are very dynamic. Their number and corresponding space requirements will change as your system's initialization parameter values change.

Transaction Locks (TX)

Row locks are locks that protect selected rows. A transaction acquires a global enqueue and an exclusive lock for each individual row modified by one of the following statements:

These locks are stored in the block, and each lock refers to the global transaction enqueue.

A transaction lock is acquired in exclusive mode when a transaction initiates its first change. It is held until the transaction performs a COMMIT or ROLLBACK. SMON also acquires it in exclusive mode when recovering (undoing) a transaction. Transaction locks are used as a queuing mechanism for processes awaiting the release of an object locked by a transaction in progress.

Table Locks (TM)

Table locks are DML locks that protect entire tables. A transaction acquires a table lock when a table is modified by one of the following statements: INSERT, UPDATE, DELETE, SELECT with the FOR UPDATE clause, and LOCK TABLE. A table lock can be held in any of several modes: null (N), row share (RS), row exclusive (RX), share lock (S), share row exclusive (SRX), and exclusive (X).

When an instance attempts to mount the database, a table lock is used to ensure that all participating instances either have DML_LOCKS = 0 or DML_LOCKS != 0. If they do not, Oracle displays error ORA-61 and the mount attempt fails. Table locks are acquired during the execution of a transaction when referencing a table with a DML statement so that the object is not dropped or altered during the execution of the transaction. This occurs if and only if the DML_LOCKS parameter is non-zero.

You can also selectively turn table locks on or off for a particular table, using the statement:


If DML_LOCKS is set to zero, then no DDL operations are allowed. The same is true for tables that have disabled table locks.

See Also:

"Minimizing Table Locks to Optimize Performance" for more information about minimizing instance locks and disabling table locks for improved performance. 

System Change Number (SCN)

The System Change Number (SCN) is a logical timestamp that Oracle uses to order events within a single instance, and across all instances. One of the schemes Oracle uses to generate SCNs is the lock scheme.

The lock SCN scheme keeps the global SCN in the value block of the SCN lock. Oracle increments this value in response to many database events, most notably after COMMITs. A process incrementing the global SCN obtains the SCN lock in exclusive mode, increments the SCN, writes the lock value block, and downgrades the lock. Access to the SCN lock value is batched. Oracle keeps a cache copy of the global SCN in memory. A process may obtain an SCN without any communication overhead by reading the SCN fetched by other processes.

The SCN implementation can differ from platform to platform. On most platforms, Oracle uses the lock SCN scheme when the MAX_COMMIT_PROPAGATION_DELAY initialization parameter is smaller than a platform-specific threshold (typically 7).

Oracle uses the Lamport SCN scheme when MAX_COMMIT_PROPAGATION_DELAY is larger than the threshold.You can examine the alert log after an instance is started to see which SCN generation scheme has been picked.

See Also:

Your Oracle operating system-specific documentation for information about SCN implementation.  

Library Cache Locks (L[A-Z]), (N[A-Z])

When a database object (table, view, procedure, function, package, package body, trigger, index, cluster, synonym) is referenced during parsing or during the compiling of a SQL (DML/DDL) or PL/SQL statement, the process parsing or compiling the statement acquires the library cache lock in the correct mode. In Oracle8 the lock is held only until the parse or compilation completes (for the duration of the parse call).

Dictionary Cache Locks (Q[A-Z])

The data dictionary cache contains information from the data dictionary, the meta-data store. This cache provides efficient access to the data dictionary.

Creating a new table, for example, causes the meta-data of that table to be cached in the data dictionary. If you drop a table, the meta-data needs to be removed from the data dictionary cache. To synchronize access to the data dictionary cache, latches are used in exclusive mode and in single shared mode. Instance locks are used in multiple shared (parallel) mode.

In Oracle Parallel Server, the data dictionary cache on all nodes may contain the meta-data of a table that gets dropped on one instance. The meta-data for this table needs to be flushed from the data dictionary cache of every instance. This is performed and synchronized by instance locks.

Database Mount Lock (DM)

The mount lock shows whether an instance has mounted a particular database. This lock is only used with Oracle Parallel Server. It is the only multi-instance lock used by Oracle Parallel Server in exclusive mode and prevents another instance from mounting the database in shared mode.

In Oracle Parallel Server single shared mode, a DM lock is held in shared mode, so another instance can mount the same database in shared mode. In Oracle Parallel Server exclusive mode, however, another instance cannot to obtain the lock.

Go to previous page Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.