Some of the columns in the dynamic performance views are found in many of the views. This section explains the meaning of the common columns and their significance, along with values of the column where appropriate.

The V$LOCK_ACTIVITY view provides statistics on the number of overall lock mode conversions in an instance. These statistics are cumulative from the time that the instance was started. The query in the following example reports on the lock conversions in instance 1:

SQL> SELECT * 
  2  FROM gv$lock_activity
  3  WHERE inst_id = 1;


FROM_VAL TO_VAL  ACTION_V                                COUNTER
-------- ------  -------------------------------------- --------
NULL      S      Lock buffers for read                     45595
NULL      X      Lock buffers for write                   139115
S         NULL   Make buffers CR (no write)                50625
S         X      Upgrade read lock to write                87555
X         NULL   Make buffers CR (write dirty buffers)    108366
X         S      Downgrade write lock to read              94261
                 (write dirty buffers)        
X         SSX    Write transaction table/undo blocks       11142
SSX       X      Rearm transaction table write mechanism   11142

8 rows selected.

X   to  NULL    108366
X   to  S       94261
X   to  SSX     11142

SQL> SELECT * FROM v$sysstat 
                     WHERE statistic# = 'DBWR cross instance writes';

STATISTIC# NAME                                CLASS      VALUE
---------- ------------------------------ ---------- ----------
        77 DBWR cross instance writes             40    417,143

Ping rate = DBWR cross instance writes
              /Lock operations resulting in disk writes
          = 417,143/213,769
          = 1.95

The V$FILE_PING view tells you the amount of lock activity for each file in your database. This view returns one row for each datafile and is very useful in identifying files that incur a high number of pings. Once you identify files with high ping rates, you can further investigate to find out which specific objects and blocks in those files are being pinged the most. You also can investigate to find out which instances are contending for those resources. Here’s an example of a query against the GV$FILE_PING view:

SQL> SELECT file_number, x_2_null, X_2_S, S_2_NULL
  2* FROM v$file_ping                                                                   

FILE_NUMBER   X_2_NULL      X_2_S   S_2_NULL
----------- ---------- ---------- ----------
          1     134198     187545     165542
          2       3094       2700       4838
          3        512      17858       4202
          4      40569         65        175

This report indicates that datafile 1 has the greatest amount of lock activity. Because these statistics are cumulative since instance startup, you should run this query repeatedly in order to determine the rate of lock activity for each file. If a table has high lock activity, particularly X_2_NULL and X_2_S lock mode conversions, this fact also implies high ping activity. Further monitor V$FALSE_PING and other views to find out if the pings are false pings or true pings.

The V$BH view returns buffer header information. For every buffer in the database buffer cache, it returns the buffer status and the number of X-to-NULL lock conversions, forced reads, and forced writes. It also provides the block number and the file number for the block that is in the buffer. V$BH does not contain any information on the database object associated with the block that is in the buffer. However, you can join this view with the OBJ$ view in order to identify the objects corresponding to V$BH’s blocks. The V$BH view is defined internally in the server, and other views such as V$CACHE and V$PING are based on the V$BH view.

The V$CACHE view returns one row for each block in the buffer cache, except for those blocks that do not have a lock element associated with them. Temporary blocks, for example, do not have locks. V$CACHE contains many of the same columns as the V$BH view, including XNC, FORCED_READS, and FORCED_WRITES. In addition, this view maps blocks to their respective database objects and also indicates the type and owner of those objects.

The V$PING view contains a subset of rows from the V$CACHE view. It shows only those blocks in the buffer cache that have experienced at least one forced read or one forced write. Query V$PING to find out which blocks and objects have a high number of forced reads and forced writes. You may limit the query to a particular datafile that you have identified as a result of querying V$FILE_PING. Run the same query on other instances to identify all the instances involved in pinging the objects in question, for example:

SQL> SELECT name, block#, kind, forced_writes, 
  2          forced_reads, lock_element_addr
  3  FROM v$ping
  4  WHERE forced_writes > 10 AND forced_reads > 10
  5  AND file# = 4
  6* ORDER BY name

NAME            BLOCK# KIND     FORCED_WRITES FORCED_READS LOCK_ELE
----------- ---------- -------- ------------- ------------ --------
CUST_INFO        13631 TABLE             3170         2180 34E054CC
CUST_INFO        13643 TABLE              150          268 34E0578C
CUST_INFO        13633 TABLE              170          180 34E0550C
.                .     .                  .            .   .       
.                .     .                  .            .   .       
.                .     .                  .            .   .       
PK_CUST_INFO     53691 INDEX             1176          200 64E0638C
PK_CUST_INFO     53759 INDEX              756          636 64E0748C
PK_CUST_INFO     53758 INDEX              756          636 64E0744C

This sample report shows the tables and indexes that have encountered high forced read and forced write activity. In this example, CUST_INFO is a “hot” object. Note the block number for each hot object. Then query the GV$BH view to find out if the same block numbers also show up in other OPS instances. Query GV$BH instead of V$BH, because you want to see this information for all instances. With respect to the example shown here, we need to query GV$BH for block number #13631, because the number of forced writes and forced reads is high for that block. If the same block number does not show up in other instances, there is contention for locks, implying false pings. If you see the same block number in other instances, then there is contention for the block, and true pings are occurring.

If the lock activity is due to true pings, check to see whether access to this object can be localized to only one instance. Investigate the various application partitioning strategies described in Chapter 11. If the object is a small object, consider using a high PCTFREE value for the object so that the data is spread out among more blocks. This reduces the likelihood of block contention.

If identifying the specific blocks of an object that are experiencing high amounts of ping activity isn’t enough to help you reduce that activity, you can further drill down to find out which rows are in those blocks. To identify the rows contained in a hot block, start by converting the block number to hexadecimal. Once you’ve done that, you can take advantage of the fact that the first part of the database rowid is the block number. In Oracle7, you can issue a query such as the following to identify the rows contained in a hot block:

SELECT * 
FROM trans_log
WHERE ROWID LIKE '%372A%'

This query will return all rows in which the ROWID contains the block number of interest. Note that Oracle8 and Oracle8i use a new ROWID format. You still can use this technique under Oracle8 and Oracle8i, but you need to use a conversion function in your query in order to convert the new ROWIDs back into the old format, for example:

SELECT * 
FROM trans_log
WHERE DBMS_ROWID.ROWID_TO_RESTRICTED(rowid,1) like '%372A%'

Drilling down to the row level and identifying the rows in a “hot” block may provide some insight as to the data for which multiple instances may be contending.

There is always the possibility that false pings will occur whenever a single PCM lock covers more than one block. Even when two instances are modifying different blocks, pinging still can occur because of lock conflicts.

The V$LOCKS_WITH_COLLISIONS view identifies locks that protect more than one block in cases in which the number of forced reads and forced writes for each block exceeds 10. Since each such lock is protecting more than one block with a high degree of ping activity, it is likely that false pings are occurring. The locks are identified by their address, which is returned by the LOCK_ELEMENT_ADDR column. LOCK_ELEMENT_ADDR is the only column returned by the view.

The V$FALSE_PING view shows the blocks that are protected by the locks identified by the V$LOCKS_WITH_COLLISIONS view. These are the blocks most likely to be experiencing false pings. The following query retrieves a list of these blocks that includes their respective object names and file numbers:

SELECT name, kind, file#, block, forced_reads, forced_writes 
FROM v$false_ping 
ORDER BY name, file#;

If a query of the V$FALSE_PING view returns a large number of blocks, then note the file IDs for those blocks and consider allocating additional hashed PCM locks to those datafiles. Doing so will reduce the granularity (the number of blocks covered by a lock) for all objects in the datafile, consequently reducing the number of false pings. If the file is very large and contains many other objects, it may not be possible to allocate enough locks to significantly reduce the granularity for the object you are interested in. In that case, consider placing the object in a separate tablespace and hence in its own datafile. The new datafile will contain fewer blocks than the original datafile, so the same incremental allocation of locks to the new datafile will greatly reduce granularity, which will more effectively reduce false pings. Alternatively, you also might consider using releasable fine-grained locks for those datafiles.

The V$CLASS_PING view summarizes ping activity by block class for the blocks in the database buffer cache. Here’s an example of the output that you’ll get when querying this view:

SQL> SELECT class, X_2_NULL, X_2_S, X_2_SSX, SSX_2_X,  S_2_NULL, S_2_X
  2  FROM v$class_ping;

CLASS              X_2_NULL   X_2_S X_2_SSX SSX_2_X S_2_NULL  S_2_X
------------------ -------- ------- ------- ------- -------- ------
                          0       0       0       0        0      0
data block           570139 1017442       0       0   238738 356407
sort block                0       0       0       0        0      0
save undo block           0       0       0       0        0      0
segment header        27842     393       0       0      258    439
save undo header          0       0       0       0        0      0
free list                 0       0       0       0        0      0
extent map                0       0       0       0        0      0
bitmap block              0       0       0       0        0      0
bitmap index block        0       0       0       0        0      0
unused                    0       0       0       0        0      0
undo header               0    2183   24705   24705     2531   2197
undo block                0       0    5335    5335        0      0

13 rows selected.

The data in this report shows that most of the lock conversion takes place for data blocks and segment headers. Segment headers contain information on free data blocks. Segment header contention occurs when more than one instance is inserting data into an object at the same time. Chapter 9, describes some techniques that you can use to reduce segment header contention.

Also notice the values for the X_2_SSX and SSX_2_X columns in this example. These represent X-to-SSX and SSX-to-X conversions, respectively. These conversions occur only for undo headers and undo blocks. The counts for X-to-SSX and SSX-to-X conversions will always be the same because each X-to-SSX conversion is followed by a corresponding SSX-to-X conversion.

A lock element is a data structure in an Oracle instance that stores information on a PCM lock. There is one lock element for each PCM lock allocated. The V$LOCK_ELEMENT view contains one row for each lock element and has information such as the lock element name, the lock element address, the number of blocks protected by the lock, and so forth. Using this view, you can get an idea of how many blocks are protected by locks and the average number of blocks protected by one lock. The following two queries provide this information:

SELECT block_count, COUNT(*) 
FROM v$lock_element
GROUP BY block_count;

SELECT AVG(block_count)
FROM v$lock_element;

Using this view, you also can confirm that the number of PCM locks allocated matches what you think you asked for using the GC_ initialization parameters. For example, with the following GC_FILES_TO_LOCKS and GC_RELEASABLE_LOCKS parameter settings, the total number of PCM locks in the instance will be 11,000:

GC_FILES_TO_LOCKS = "1=2000:2-5=1000EACH"
GC_RELEASABLE LOCKS = 5000

You can query V$LOCK_ELEMENT to find out the overall number of PCM locks that have been allocated. You also can query for the number of fixed and releasable PCM locks that have been allocated. The following query, for example, returns the total number of PCM locks allocated to each instance. This total includes both fixed and releasable PCM locks:

SQL> SELECT COUNT(*) FROM v$lock_element;

Count(*)
---------
11,0000

To count up the number of fixed PCM locks that have been allocated, use the FLAGS column in the WHERE clause as follows:

SQL> SELECT COUNT(*) FROM v$lock_element
                  WHERE BITAND(flags,4) != 0;

Count(*) 
--------
6,000

You can use a similar query to count up the number of releasable PCM locks. You only need to change the WHERE clause so that the query counts lock elements where BITAND(flags,4) = 0. Once you have the values returned by these queries, you then can verify that they correspond to your GC_ parameter settings.