9
Setting Instance Locks

This chapter explains how to set instance locks. It contains the following topics:

• Setting GC_FILES_TO_LOCKS: PCM Locks for Each Data File

• Tips for Setting GC_FILES_TO_LOCKS

• Setting Other GC_* Parameters

• Tuning PCM Locks

• Lock Names and Lock Name Formats

Setting GC_FILES_TO_LOCKS: PCM Locks for Each Data File

Set the GC_FILES_TO_LOCKS initialization parameter to specify the number of Parallel Cache Management (PCM) locks covering data blocks in a data file or set of data files. This section covers:

• GC_FILES_TO_LOCKS Syntax

• Fixed Lock Examples

• Releasable Lock Examples

• Guidelines for Setting GC_FILES_TO_LOCKS

  Note: Whenever you add or resize a data file, create a tablespace, or drop a tablespace and its data files, adjust the value of GC_FILES_TO_LOCKS before restarting Oracle with Parallel Server enabled.

  See Also: Oracle8i Parallel Server Concepts to understand how Oracle determines the number of data blocks that are covered by a single PCM lock.

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 releasable locks will be used instead of fixed 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 locks are releasable: they may be released by the instance when no longer needed. Releasable PCM locks are taken from the pool GC_RELEASABLE_LOCKS.

In addition to controlling the mapping of PCM locks to data files, GC_FILES_TO_LOCKS controls the number of locks in the default bucket. Oracle uses the default bucket for all files not explicitly mentioned in GC_FILES_TO_LOCKS. You can use a value of zero in setting this parameter, 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, also use fixed locks.

You can specify releasable PCM locks by using the R option with the GC_FILES_TO_LOCKS parameter. Oracle takes 1:N releasable PCM locks from the pool of GC_RELEASABLE_LOCKS.

REACH is a keyword that combines "R" with the word "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 data file contains fewer data blocks than the number of PCM locks, some PCM locks will not cover a data block in that data file.

The default value for !blocks is 1. When you specify 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 data files. You can use blocks to allocate a set of PCM locks to cover a single data file where PCM lock contention on that data file is minimal, thus reducing PCM lock management.

Always set the !blocks value to avoid interfering with the 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"

Releasable Lock Examples

To specify releasable locks with low granularity 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 locks with a group factor of 4 for file 1.

The following entry indicates that 1000 releasable locks protect file 1 in groups of 25 blocks:

   GC_FILES_TO_LOCKS = "1=1000!25R"

Guidelines for Setting GC_FILES_TO_LOCKS

Use the following guidelines to set the GC_FILES_TO_LOCKS parameter:

• Always specify all data files in GC_FILES_TO_LOCKS.

• Assign the same value to GC_FILES_TO_LOCKS for each instance accessing the same database.

• The number of PCM locks you specify for a data file should never exceed the number of blocks in the data file. This ensures that if a data file increases in size, the new blocks can be covered by the extra PCM locks.

  If a data file is defined with the AUTOEXTEND clause or if you issue the ALTER DATABASE... DATAFILE... RESIZE statement, then you should regularly monitor the data file for an increase in size. If the data file's size is increasing, then update the parameter GC_FILES_TO_LOCKS as soon as possible. Then shut down and restart Oracle Parallel Server.

  Note: Restarting Oracle Parallel Server is not required, but if you do not shut down and restart it, the locks will cover more blocks.

  If the number of PCM locks specified for file_list is less than the actual number of data blocks in the data files, the DLM uses some PCM locks to cover more datablocks than specified. This can diminish performance, so always ensure that sufficient PCM locks are available:

• When you add new data files, always specify their locks in GC_FILES_TO_LOCKS to avoid automatic allocation of the "spare" PCM locks.

  At some point, you may need to add a data file using the ALTER TABLESPACE... ADD DATAFILE statement, with Oracle Parallel Server running. If you do this, update the setting for GC_FILES_TO_LOCKS as soon as possible, then shut down and restart Oracle Parallel Server.

• To reduce resource contention by creating disjoint data to be accessed by different instances, place the data files on different disks. Use GC_FILES_TO_LOCKS to allocate PCM locks to cover the data blocks in the separate data files.

• Specify relatively fewer PCM locks for blocks containing infrequently modified index data. Place indexes in their own tablespace or in their own data files within a tablespace so a separate set of PCM locks can be assigned to them. Use only one lock for read-only indexes.

• Files not mentioned in GC_FILES_TO_LOCKS use releasable locks.

Tips for Setting GC_FILES_TO_LOCKS

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

• Providing Room for Growth

• Checking for Valid Number of Locks

• Checking for Valid Lock Assignments

• Setting Tablespaces to Read-Only

• Checking File Validity

• Adding Data Files Without Changing Parameter Values

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 data file, create a tablespace, or drop a tablespace and its data files, adjust the value of GC_FILES_TO_LOCKS before restarting Oracle with Parallel Server 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 the V$DATAFILE view 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:

• Do not assign locks to files that only hold rollback segments.

• Do not assign locks to files that only hold temporary data for internal temporary tables.

• Group read-only objects together and assign only one lock to that file. This only works if there is absolutely no writing done to the file or even if changes are made to the blocks, such as those done during block clean out.

Setting Tablespaces to Read-Only

If a tablespace is 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 on the tablespace. The exception to this is a single lock you can assign to ensure the tablespace does not have to contend for spare locks.

Checking File Validity

Determine the number of objects in each file using the following syntax:

  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 Data Files Without Changing Parameter Values

Consider the consequences for PCM lock distribution if you add a data file 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, Oracle gives the data file locks from the pool of remaining locks, and the file must contend with all files you omit from your setting for the GC_FILES_TO_LOCKS parameter.

Setting Other GC_* Parameters

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

• Setting GC_RELEASABLE_ LOCKS

• Setting GC_ROLLBACK_ LOCKS

Setting GC_RELEASABLE_ LOCKS

For GC_RELEASABLE_LOCKS, Oracle recommends that you use 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. Too low a value for GC_RELEASABLE_LOCKS could adversely affect performance.

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

Setting GC_ROLLBACK_ LOCKS

If you are using fixed locks, 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(R.BLOCKS) 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. The default setting for GC_ROLLBACK_LOCKS is:

   GC_ROLLBACK_LOCKS = "0-128=32!8REACH"

This protects rollback segments 0 through 129 with locks. The first 129 rollback segments have 32 releasable locks, with a grouping of 8. In other words, each lock covers 8 contiguous blocks.

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 of the following examples is invalid and 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

• Determining How Much Time PCM Lock Conversions Require

• Identifying Sessions That Are Waiting for PCM Lock Conversions to Complete

• This section covers the following topics:

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 assess 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 forced writes'; 

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

Table 9-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 occur. False pinging may be occurring.
> 1	False pings are definitely occurring.

Use this formula to calculate the percentage of false pings:

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 forced 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 forced writes';

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.

Determining How Much Time PCM Lock Conversions Require

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

   SELECT *  
   FROM V$SYSTEM_EVENT 
   WHERE EVENT LIKE 'global cache%' 

Oracle responds with output similar to:

EVENT                    TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
----------------------------------------------------------------------------
global cache lock open s         743              0         494    .66487214
global cache lock open x        5760              0        5945   1.03211806
global cache lock null to s      263              0         697   2.65019011
global cache lock null to x     2149              0        7804   3.63145649
global cache lock s to x        1427              0        1394   .976874562
global cache cr request        25248              5        4729   .187301965
global cache lock busy            21              0          46   2.19047619
global cache bg acks               2              0           0            0

Identifying Sessions That Are Waiting for PCM Lock Conversions to Complete

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

   SELECT * 
   FROM V$SESSION_WAIT 
   WHERE EVENT LIKE 'global cache%' AND 'wait_time = 0' 

PCM and Non-PCM Lock Names and Formats

This section covers the following topics:

• Lock Names and Lock Name Formats

• PCM Lock Names

• Non-PCM Lock Names

Lock Names and Lock Name Formats

Oracle names all enqueues and instance locks using one of the following formats:

• type ID1 ID2

• type, ID1, ID2

• type (ID1, ID2)

where:

type	A two-character type name for the lock type, as described in the V$LOCK table.
ID1	The first lock identifier, used by the DLM. The convention for this identifier differs from one lock type to another.
ID2	The second lock identifier, used by the DLM. The convention for this identifier differs from one lock type to another.

For example, a space management lock might be named ST00. A PCM lock might be named BL 1 900.

The V$LOCK table lists local and global Oracle enqueues currently held or requested by the local instance. The "lock name" is actually the name of the resource; locks are taken out against the resource.

PCM Lock Names

All PCM locks are Buffer Cache Management locks. Buffer Cache Management Locks are of type "BL". The syntax of PCM lock names is type ID1 ID2, where:

type	Is always BL because PCM locks are buffer locks.
ID1	For fixed locks, ID2 is the lock element (LE) index number obtained by hashing the block address (see the V$LOCK_ELEMENT fixed view). For releasable locks, ID2 is the database address of the block.
ID2	The block class.

Some example PCM lock names are:

BL (100, 1)	This is a data block with lock element 100.
BL (1000, 4)	This is a segment header block with lock element 1000.
BL (27, 1)	This is an undo segment header with rollback segment #10. The formula for the rollback segment is 7 + (10 * 2).

Non-PCM Lock Names

Non-PCM locks have many different names. Table 9-2 contains a list of the names:

Table 9-2 Non-PCM Lock Types and Names

Type	Lock Name
CF	Controlfile Transaction
CI	Cross-Instance Call Invocation
DF	data file
DL	Direct Loader Index Creation
DM	Database Mount
DX	Distributed Recovery
FS	File Set
KK	Redo Log "Kick"
IN	Instance Number
IR	Instance Recovery
IS	Instance State
MM	Mount Definition
MR	Media Recovery
IV	Library Cache Invalidation
L[A-P]	Library Cache Lock
N[A-Z]	Library Cache Pin
Q[A-Z]	Row Cache
PF	Password File
PR	Process Startup
PS	Parallel Slave Synchronization
RT	Redo Thread
SC	System Commit Number
SM	SMON
SN	Sequence Number
SQ	Sequence Number Enqueue
SV	Sequence Number Value
ST	Space Management Transaction
TA	Transaction Recovery
TM	DML Enqueue
TS	Temporary Segment (also Table-Space)
TT	Temporary Table
TX	Transaction
UL	User-Defined Locks
UN	User Name
WL	Begin written Redo Log
XA	Instance Registration Attribute Lock
XI	Instance Registration Lock

See Also: Oracle8i Reference for descriptions of non-PCM locks.