Oracle8i Parallel Server Concepts and Administration
Release 8.1.5

A67778-01

Library

Product

Contents

Index

Prev Next

15
Allocating PCM Instance Locks

This chapter explains the init.ora parameters you must set to allocate PCM locks to datafiles for an OPS instance.

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 IDLM 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 locks and resources require. Consider these ramifications:

Key to Allocating PCM Locks

The key to assigning locks is to analyze how often data is changed using the INSERT, UPDATE, and DELETE commands. 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:

They 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 12, "Application Analysis".  

Examining Datafiles and Data Blocks

You must allocate locks at various levels:

Begin by getting to know your datafiles and the blocks they contain.

How to Determine File ID, Tablespace Name, and Number of Blocks

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

   SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME, BLOCKS
   FROM DBA_DATA_FILES;

Results are displayed as in the following example:

FILE_NAME               FILE_ID     TABLESPACE_NAME     BLOCKS
---------------------------------------------------------------
/v7/data/data01.dbf     1           SYSTEM               200
/v7/data/data02.dbf     2           ROLLBACK            1600
. . .

How Many Locks Do You Need?

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

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 the key. Every time you have to go from one mode to the other, you need locks. Take into consideration the interaction of different instances on a table. Also take into consideration the number of rows in a block, the number of rows in a table, and the growth rate. Based on this analysis, group your objects into files, and assign free list groups.

Figure 15-1 PCM Lock Worksheet 1

Object   Operations needing X mode: Writes   OPS needing S mode: Reads   TS/Datafile  
  INSERTS   UPDATES   DELETES   SELECTS    

A  

 

80%  

 

20%

Full table scan?

Single row?  

 

B  

 

 

 

100%  

 

C  

 

 

 

 

 

D  

 

 

 

 

 

Figure 15-2 PCM Lock Worksheet 2

Object   Instance 1   Instance 2   Instance 3  

D  

INSERT

UPDATE

DELETE  

SELECT  

 

E  

 

 

 

F  

 

 

 

Figure 15-3 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 a 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 Integrated Distributed Lock Manager (IDLM) 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 datafiles in the tablespace using the GC_FILES_TO_LOCKS parameter.


Note:

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 datafiles 1, 2, and 3, while Instance Y primarily updates data in datafiles 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 datafiles 3 and 4, I/O increases. This is because both instances regularly use the same set of PCM locks.

Setting GC_FILES_TO_LOCKS: PCM Locks for Each Datafile

Set the GC_FILES_TO_LOCKS initialization parameter to specify the number of PCM locks covering data blocks in a datafile or set of datafiles. This section covers:

GC_FILES_TO_LOCKS Syntax

The syntax for setting the GC_FILES_TO_LOCKS parameter specifies the translation between the database address and class of a database block, and the lock name protecting it. You cannot specify this translation for files not mentioned in the GC_FILES_TO_LOCKS parameter.

The syntax for setting this parameter is:

GC_FILES_TO_LOCKS="{file_list=#locks[!blocks][R][EACH][:]} . . ."

Where:

file_list  

file_list specifies a single file, range of files, or list of files and ranges as follows: fileidA[-fileidC][,fileidE[-fileidG]] ...

Query the data dictionary view DBA_DATA_FILES to find the correspondence between file names and file ID numbers.  

#locks  

Sets the number of PCM locks to assign to file_list. A value of zero (0) for #locks means that fine grain locks will be used instead of hashed locks.  

!blocks  

Specifies the number of contiguous data blocks to be covered by each lock.  

EACH  

Specifies #locks as the number of locks to be allocated to each file in file_list.  

R  

Specifies that the hashed locks are releasable: they may be released by the instance when no longer needed. Releasable hashed PCM locks are taken from the pool GC_RELEASABLE_LOCKS.  


Note:

GC_ROLLBACK_LOCKS uses the same syntax.  


Spaces are not permitted within the quotation marks of the GC_FILES_TO_LOCKS parameter.

In addition to controlling the mapping of PCM locks to datafiles, GC_FILES_TO_LOCKS controls the number of locks in the default bucket. The default bucket is used for all files not explicitly mentioned in GC_FILES_TO_LOCKS. A value of zero can be used and the default is "0=0". For example, "0=100", "0=100R", "0-9=100EACH". By default, locks in this bucket are releasable; you can however, set these locks to be fixed.

You can specify releasable hashed PCM locks by using the R option with the GC_FILES_TO_LOCKS parameter. Releasable hashed PCM locks are taken from the pool of GC_RELEASABLE_LOCKS

REACH is a keyword that combines "R" and "EACH". For example, GC_FILES_TO_LOCKS="0-9=100REACH". EACHR is not a valid keyword.

Omitting EACH and "!blocks" means that #locks PCM locks are allocated collectively to file_list and individual PCM locks cover data blocks for every file in file_list. However, if any datafile contains fewer data blocks than the number of PCM locks, some PCM locks will not cover a data block in that datafile.

The default value for !blocks is 1. When specified, blocks contiguous data blocks are covered by each one of the #locks PCM locks. To specify a value for blocks, you must use the "!" separator. You would primarily specify blocks, and not specify the EACH keyword, to allocate sets of PCM locks to cover multiple datafiles. You can use blocks to allocate a set of PCM locks to cover a single datafile where PCM lock contention on that datafile is minimal, thus reducing PCM lock management.

Always set the !blocks value to avoid breaking data partitioning gained by using free list groups. Normally you do not need to pre-allocate disk space. When a row is inserted into a table and new extents need to be allocated, contiguous blocks specified with !blocks in GC_FILES_TO_LOCKS are allocated to the free list group associated with an instance.

Fixed Lock Examples

For example, you can assign 300 locks to file 1 and 100 locks to file 2 by adding the following line to the parameter file of an instance:

   GC_FILES_TO_LOCKS = "1=300:2=100"

The following entry specifies a total of 1500 locks; 500 each for files 1, 2, and 3:

   GC_FILES_TO_LOCKS = "1-3=500EACH"

By contrast, the following entry specifies a total of only 500 locks spread across the three files:

   GC_FILES_TO_LOCKS = "1-3=500"

The following entry indicates that 1000 distinct locks should be used to protect
file 1. The data in the files is protected in groups of 25 blocks.

   GC_FILES_TO_LOCKS = "1=1000!25"

The following entry indicates that the 1000 hashed locks protecting file 1 in groups of 25 blocks may be released by the instance when no longer needed.

   GC_FILES_TO_LOCKS = "1=1000!25R"

Releasable Lock Example

To specify fine grain locks for data blocks with a group factor, specify the following in the parameter file of an instance:

   GC_FILES_TO_LOCKS="1=0!4"

This specifies fine grain locks with a group factor of 4 for file 1.

Guidelines

Use the following guidelines to set the GC_FILES_TO_LOCKS parameter:

Tips for Setting GC_FILES_TO_LOCKS

Setting GC_FILES_TO_LOCKS is an important tuning task in OPS. This section covers some simple checks to help ensure your parameter settings are providing the best performance. This section covers:

Providing Room for Growth

Sites that run continuously cannot afford to shut down to permit adjustment of parameter values. Therefore, when you size these parameters, remember to provide room for growth, or room for files to extend.

Additionally, whenever you add or resize a datafile, create a tablespace, or drop a tablespace and its datafiles, adjust the value of GC_FILES_TO_LOCKS before restarting Oracle with OPS enabled.

Checking for Valid Number of Locks

Check that the number of locks allocated is not larger than the number of data blocks allocated.


Note:

Blocks currently allocated may be zero if you are about to insert into a table.  


Check the FILE_LOCK data dictionary view to see the number of locks allocated per file. Check V$DATAFILE to see the maximum size of the data file.

See Also:

Oracle8i Reference for more information about FILE_LOCK and V$DATAFILE.  

Checking for Valid Lock Assignments

To avoid lock assignment problems, check the following:

Setting Tablespaces to Read-only

If a tablespace is actually read-only, consider setting it to read-only in Oracle. This ensures that no write to the database occurs and no PCM locks are used. The exception to this is a single lock you can assign to ensure the tablespace will not have to contend for spare locks.

Checking File Validity

Count the number of objects in each file, as follows:

  SELECT E.FILE_ID      FILE_ID, 
         COUNT(DISTINCT OWNER||NAME ) OBJS
    FROM DBA_EXTENTS     E,
         EXT_TO_OBJ V        
   WHERE E.FILE_ID = FILE#
     AND E.BLOCK_ID >= LOWB
     AND E.BLOCK_ID <= HIGHB
     AND KIND != 'FREE EXTENT'  
     AND KIND != 'UNDO'     
   GROUP BY E.FILE_ID;

Examine the files storing multiple objects. Run CATPARR.SQL to use the EXT_TO_OBJ view. Make sure the objects can coexist in the same file. That is, make sure the GC_FILES_TO_LOCKS settings are compatible.

Adding Datafiles without Changing Parameter Values

Consider the consequences for PCM lock distribution if you add a datafile to the database. You cannot assign locks to this file without shutting down the instance, changing the GC_FILES_TO_LOCKS parameter, and restarting the database. This may not be possible for a production database.

In this case, the datafile will be assigned to the pool of remaining locks and the file must contend with all files not mentioned in the GC_FILES_TO_LOCKS parameter.

Setting Other GC_* Parameters

This section describes how to set two additional GC_* parameters:

Setting GC_RELEASABLE_ LOCKS

For GC_RELEASABLE_LOCKS, Oracle recommends the default setting. This is the value of DB_BLOCK_BUFFERS. This recommendation generally provides optimal performance. However, you can set GC_RELEASABLE_LOCKS to less than the default to save memory, or more than the default to get a possible reduction in locking activity. Too low a value for GC_RELEASABLE_LOCKS could adversely affect performance.

The statistic "releasable freelist waits" in the V$SYSSTAT view tracks the number of times the system runs out of releasable locks. If this condition occurs, as indicated by a non-zero value for releasable freelist waits, you must increase the value of GC_RELEASABLE_LOCKS.

Setting GC_ROLLBACK_ LOCKS

If you are using fixed locks, it is wise to check that the number of locks allocated is not larger than the number of data blocks allocated. Blocks currently allocated may be zero if you are about to insert into a table. Find the number of blocks allocated to a rollback segment by entering:

   SELECT S.SEGMENT_NAME NAME, 
         SUM(RBLOCKS) BLOCKS
    FROM DBA_SEGMENTS S, 
         DBA_EXTENTS R
   WHERE S.SEGMENT_TYPE = 'ROLLBACK'
     AND S.SEGMENT_NAME = R.SEGMENT_NAME
   GROUP BY S.SEGMENT_NAME;

This query displays the number of blocks allocated to each rollback segment. When there are many unnecessary forced reads/writes on the undo blocks, try using releasable locks. By default, all rollback segments are protected by releasable locks.

The parameter GC_ROLLBACK_LOCKS takes arguments much like the GC_FILES_TO_LOCKS parameter, for example:

GC_ROLLBACK_LOCKS="0=100:1-10=10EACH:11-20=20EACH"

In this example rollback segment 0, the system rollback segment, has 100 locks. Rollback segments 1 through 10 have 10 locks each, and rollback segments 11 through 20 have 20 locks each.


Note:

You cannot use GC_ROLLBACK_LOCKS to make undo segments share locks. The first example below is invalid, but the second is valid, since each of the undo segments has 100 locks to itself:  


Invalid: GC_ROLLBACK_LOCKS="1-10=100".

Valid: GC_ROLLBACK_LOCKS="1-10=100EACH".

Tuning PCM Locks

This section discusses several issues to consider before tuning PCM locks:

Detecting False Pinging

False pinging occurs when you down-convert a lock element protecting two or more blocks that are concurrently updated from different nodes. Assume that each node is updating a different block covered by the same lock. In this event, each node must ping both blocks, even though the node is updating only one of them. This is necessary because the same lock covers both blocks.

No statistics are available to show false pinging activity. To asses false pinging, you can only consider circumstantial evidence. This section describes activity you should look for.

The following SQL statement shows the number of lock operations causing a write, and the number of blocks actually written:

   SELECT VALUE/(A.COUNTER + B.COUNTER + C.COUNTER) "PING RATE" 
     FROM V$SYSSTAT, 
       V$LOCK_ACTIVITY A, 
       V$LOCK_ACTIVITY B, 
       V$LOCK_ACTIVITY C 
   WHERE A.FROM_VAL = 'X' 
       AND A.TO_VAL = 'NULL' 
       AND B.FROM_VAL = 'X' 
       AND B.TO_VAL = 'S' 
       AND C.FROM_VAL = 'X' 
       AND C.TO_VAL = 'SSX' 
       AND NAME = 'DBWR cross instance writes'; 

Table 15-1 shows how to interpret the ping rate.

Table 15-1 Interpreting the Ping Rate
Ping Rate   Meaning  

< 1  

False pings may be occurring, but there are more lock operations than writes for pings. DBWR is writing out blocks fast enough, causing no write for a lock activity. This is also known as a "soft ping", meaning
I/O activity is not required for the ping, only lock activity.  

= 1  

Each lock activity involving a potential write causes the write to happen. False pinging may be occurring.  

> 1  

False pings are definitely occurring.  

Use this formula to calculate the percentage of pings that are definitely false:


Then check the total number of writes and calculate the number due to false pings:

   SELECT Y.VALUE "ALL WRITES", 
       Z.VALUE "PING WRITES",  
       Z.VALUE * pingrate "FALSE PINGS", 
   FROM V$SYSSTAT Z, 
       V$SYSSTAT Y,
   WHERE Z.NAME = 'DBWR cross instance writes' 
   AND Y.NAME = 'physical writes'; 

Here, ping_rate is given by the following SQL statement:

   CREATE OR REPLACE VIEW PING_RATE AS
   SELECT ((VALUE/(A.COUNTER+B.COUNTER+C.COUNTER))-1)/ 
       (VALUE/(A.COUNTER+B.COUNTER+C.COUNTER)) RATE
   FROM V$SYSSTAT,
       V$LOCK_ACTIVITY A,
       V$LOCK_ACTIVITY B,
       V$LOCK_ACTIVITY C
   WHERE A.FROM_VAL = 'X'
       AND A.TO_VAL   = 'NULL'
       AND B.FROM_VAL = 'X'
       AND B.TO_VAL   = 'S'
       AND C.FROM_VAL = 'X'
   AND C.TO_VAL   = 'SSX'
   AND NAME = 'DBWR cross instance writes';

Needless to say, the goal is not only to reduce overall pinging, but also to reduce false pinging. To do this, look at the distribution of instance locks in GC_FILES_TO_LOCKS and check the data in the files.

How Much Time Do PCM Lock Conversions Take?

Be sure to check the amount of time needed for a PCM lock to convert. This time differs across systems. Enter the following SQL statement to find the lock conversion duration:

   SELECT *  
   FROM V$SYSTEM_EVENT 
   WHERE EVENT = 'lock element cleanup' 

This SQL statement displays a table similar to the following:

                       TOTAL_    TOTAL_      TIME_     AVERAGE_
EVENT                  WAITS     TIMEOUTS    WAITED    WAIT
--------------------   ------    -------     ------    ----------
lock element cleanup   32709     44          685660    20.9624262 

This means that a lock conversion took 20.9 hundredths of a second (0.209 seconds).

Which Sessions Are Waiting for PCM Lock Conversions to Complete?

Enter the following SQL statement to see which sessions are currently waiting, and which have just waited for a PCM lock conversion to complete:

   SELECT * 
   FROM V$SESSION_WAIT 
   WHERE EVENT = 'lock element cleanup'

What Is the Total Number of PCM Locks and Resources Needed?

This section explains how to determine the number of PCM locks and resources your system requires. This is the value you need to set for the LM_LOCKS and LM_RESS parameters.

Formula for PCM Locks and Resources

To find this value, add the number of fixed (non-releasable) locks set per instance (the sum of GC_FILES_TO_LOCKS and GC_ROLLBACK_LOCKS--fixed locks only) to the total number of releasable locks (the value of GC_RELEASABLE_LOCKS), and multiply by two.

2 *(GC_FILES_TO_LOCKS + GC_ROLLBACK_LOCKSfixed + GC_RELEASABLE_LOCKS)

This figure represents the maximum number of PCM locks and resources your system requires. This calculation is independent of the number of instances.

Also consider the following:

Calculating PCM Locks and Resources: Example

Assume your system has the following settings for each instance:

GC_FILES_TO_LOCKS="1=100:2-5=1000:6-10=1000EACH:11=100R"

GC_ROLLBACK_LOCKS="1-10=10EACH:11-20=20EACH"

GC_RELEASABLE_LOCKS=50,000

Add the GC_FILES_TO_LOCKS values as follows: File 1 has 100 fixed locks. Files 2, 3, 4, and 5 share 1000 locks. File 6 has 1000 fixed locks, file 7 has 1000 fixed locks, file 8 has 1000 fixed locks, file 9 has 1000 fixed locks, and file 10 has 1000 fixed locks. File 11 does not have fixed locks. Hence there is a total of 6,100 fixed locks set by GC_FILES_TO_LOCKS.

Add the GC_ROLLBACK_LOCKS values as follows: Files 1 through 10 have 10 fixed locks each, and Files 11 through 20 have 20 fixed locks each, for a total of 300 fixed locks.

Entering these figures into the formula, calculate the following:

2 * (6,100 + 300 + 50,000) = 112,800

You would thus set the LM_LOCKS and LM_RESS parameters to 112,800.


Note:

Use the above equation only to calculate PCM resources.  


See Also:

For the complete equation to calculate locks and resources, please refer to "Determining the Amount of Locks Needed and Setting LM_* Parameters". Also refer to "GC_FILES_TO_LOCKS Syntax" to see details about the syntax of using this parameter.  




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index