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

Part Number A76970-01

Library

Product

Contents

Index

Go to previous page Go to next page

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

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

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. 

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.  


Note:

GC_ROLLBACK_LOCKS uses the same syntax. Do not use spaces within the quotation marks of the GC_FILES_TO_LOCKS parameter.  


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:

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

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:

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

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

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

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

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. 


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

All Rights Reserved.

Library

Product

Contents

Index