Oracle8 Parallel Server Concepts & Administration Release 8.0 A58238-01 |
Library | Product | Contents | Index

# 20
Monitoring Views & Tuning a Parallel Server

A needless Alexandrine ends the song,
That like a wounded snake drags its slow length along.

-Alexander Pope

This chapter describes how to monitor performance of a parallel server by querying data dictionary views and dynamic performance views. It also explains how to tune a parallel server.

- Monitoring Data Dictionary Views with CATPARR.SQL
- Monitoring Dynamic Performance Views
- Querying V$LOCK_ACTIVITY to Monitor Instance Lock Activity
- Querying the V$PING View to Detect Pinging
- Querying V$CLASS_PING, V$FILE_PING, and V$BH
- Querying the V$WAITSTAT View to Monitor Contention
- Querying V$FILESTAT and V$DATAFILE to Monitor I/O Activity
- Querying and Interpreting V$SESSTAT and V$SYSSTAT Statistics

See Also: "Oracle Parallel Server Management" on page 18-2

## Monitoring Data Dictionary Views with CATPARR.SQL

The SQL script CATPARR.SQL creates parallel server data dictionary views. To run this script, you must have SYSDBA privileges and either log in with the SYS username or use the CONNECT INTERNAL command.

Note: CONNECT INTERNAL may not be supported in future releases.

CATALOG.SQL creates the standard V$ dynamic views, as described in the Oracle8 Reference, including:

- GV$CACHE
- GV$PING
- GV$CLASS_PING
- GV$FILE_PING

You can rerun CATPARR.SQL if you want the EXT_TO_OBJ table to contain the latest information after you add extents. Note that if you drop objects without rerunning CATPARR.SQL, EXT_TO_OBJ may display misleading information.

The following data dictionary views, created by CATPARR.SQL, are available to monitor a parallel server:

- FILE_LOCK
- FILE_PING

See Also: Oracle8 Reference for more information on dynamic views and monitoring your database.

## Monitoring Dynamic Performance Views

This section covers the following topics:

- Global Dynamic Performance Views
- The V$ Views

### Global Dynamic Performance Views

Tuning and performance information for the Oracle database is stored in a set of dynamic performance tables (the V$ fixed views). Each active instance has its own set of fixed views. In a parallel server environment, you can query a global dynamic performance (GV$) view to retrieve the V$ view information from all qualified instances. A global fixed view is available for all of the existing dynamic performance views except for V$ROLLNAME, V$CACHE_LOCK, V$LOCK_ACTIVITY, and V$LOCKS_WITH_COLLISIONS.

The global view contains all the columns from the local view, with an additional column, INST_ID (datatype INTEGER). This column displays the instance number from which the associated V$ information was obtained. You can use the INST_ID column as a filter to retrieve V$ information from a subset of available instances. For example, the query:

    SELECT * FROM GV$LOCK WHERE INST_ID = 2 or INST_ID = 5

retrieves the information from the V$ views on instances 2 and 5.

Each global view contains a GLOBAL hint which creates a parallel query that fetches the contents of the local view on each instance. You can use the GV$ views to return information on groups of instances defined with the OPS_ADMIN_GROUP parameter. Note that a query over G$V views will only return data from instances in instance group g1.

If you have reached the limit of PARALLEL_MAX_SERVERS on an instance and you attempt to query a GV$ view, one additional parallel server process will be spawned for this purpose. This extra process will serve any subsequent GV$ queries until expiration of the PARALLEL_SERVER_IDLE_TIME, at which point the process will terminate. The extra process is not available for any parallel operation other than GV$ queries.

Note: If PARALLEL_MAX_SERVERS is set to zero for an instance, then no additional parallel server process will be allocated to accommodate a GV$ query.

If you have reached the limit of PARALLEL_MAX_SERVERS on an instance and issue multiple GV$ queries, all but the first query will fail--unless ALLOW_PARTIAL_SN_RESULTS is set. This parameter permits partial results to be returned on excess queries to global dynamic performance views, even if a corresponding parallel server process cannot not be allocated on the instance. In most parallel queries, if a server process could not be allocated this would result in either an error or a sequential execution of the query by the query coordinator. For global views, it may be acceptable to continue running the query in parallel and return the data from the instances which could allocate servers for the query. If the desired behavior is to report an error if server allocation on an instance fails, then the value of ALLOW_PARTIAL_SN_RESULTS should be set to FALSE. If it is acceptable to retrieve results only from instances where server allocation succeeded, then the value of the parameter should be set to TRUE.

See Also: "Specifying Instance Groups" on page 18-22

Oracle8 Reference for restrictions on GV$ views, and complete descriptions of all the parameters and V$ dynamic performance views.

### The V$ Views

The following dynamic views are available to monitor a parallel server:

V$BH | GV$BH
V$CACHE | GV$CACHE
V$CACHE_LOCK |
V$CLASS_PING | GV$CLASS_PING
V$DLM_LOCKS | GV$DLM_LOCKS
V$FALSE_PING | GV$FALSE_PING
V$FILE_PING | GV$FILE_PING
V$LOCK_ACTIVITY |
V$LOCK_ELEMENT | GV$LOCK_ELEMENT
V$LOCKS_WITH_COLLISIONS |
V$PING | GV$PING

The V$ views are accessible to the user with SYSDBA privileges. You can grant PUBLIC access to V$ views by running the script MONITOR.SQL, or you can grant individual users SELECT access to new views based on the dynamic views, as described in the "Data Dictionary Reference" chapter of Oracle8 Administrator's Guide.

The V$BH, V$CACHE, and V$PING views contain statistics about the frequency of PCM lock conversion due to contention between instances. Each row in these views represents one block in the buffer cache of the current instance.

#### The COUNTER Column

In the V$LOCK_ACTIVITY view, the COUNTER column shows the number of times each type of PCM lock conversion has occurred since the instance started up.

#### The XNC Column

In the V$BH, V$CACHE, and V$PING views, the XNC column shows the number of times the PCM lock covering that block has converted from X (exclusive) to NULL at the request of another instance since the block entered the buffer cache. XNC therefore indicates the amount of contention for data. If the PCM lock covers a set of blocks, some or all of the lock conversions could be caused by requests for other blocks in that set.

Each block starts with an XNC value of zero when it first enters the buffer cache. This value is incremented whenever the instance releases the PCM lock covering that block. If a PCM lock covers multiple blocks, they can have different values of XNC because they may enter the buffer cache at different times.

Note: A single block can appear in multiple rows of the V$BH, V$CACHE, and V$PING views. Each row represents a different copy (version) of the block. Multiple versions created for read-consistent queries appear with the status CR. For tuning purposes, you only need consider the current copy (status XCUR or SCUR) that contains the greatest value of XNC.

When an instance writes a block to disk and reuses that buffer for other data, XNC is reset to zero. If the block returns to the buffer cache while other versions of that block are still in the cache, it starts with the greatest value of XNC for any version of the same block, rather than starting with zero.

#### Null Values

Null values appear in rows for distributed locks on temporary segments, such as sort blocks. Null values can also appear in some rows of the dynamic views after you create or modify database objects, or after the Oracle Server allocates new extents to database objects; in this case, you should update the views by rerunning CATPARR.SQL.

Use the following procedure to monitor and tune the distributed lock activity in a parallel server.

## Querying V$LOCK_ACTIVITY to Monitor Instance Lock Activity

The V$LOCK_ACTIVITY view lists the frequencies of various types of PCM lock conversions for all buffers in the SGA of the current instance; it does not contain information about particular blocks, files, or database objects.

This section covers the following topics:

- Analyzing V$LOCK_ACTIVITY
- Monitoring and Tuning Lock Activity

### Analyzing V$LOCK_ACTIVITY

Query the V$LOCK_ACTIVITY view for each instance of a parallel server periodically. The Server Manager command CONNECT @instance-path allows you to specify an instance before querying its dynamic performance views. Net8 must be installed to use the CONNECT command for an instance on a remote node. When analyzing the V$LOCK_ACTIVITY view, note that:

- Many PCM locks are initially converted when an instance is started.
- Rapid increases in the number of lock conversions in successive queries (for example, increments of 500+) indicate system contention problems.
- Excessive lock conversions (for example, exceeding 5,000 per minute) indicate contention problems on the system.

For example, the following query could display rows as shown:

    SELECT * FROM V$LOCK_ACTIVITY;
    FROM TO   ACTION                                             COUNTER
    ---- ---- -------------------------------------------------- -------
    NULL S    Lock buffers for read                                 5953
    NULL X    Lock buffers for write                                1118
    S    NULL Make buffers CR (no write)                            6373
    S    X    Upgrade read lock to write                            2077
    X    NULL Make buffers CR (write dirty buffers)                    1
    X    S    Downgrade write lock to read (write dirty buffers)    3164
    X    SSX  Write transaction table/undo blocks                   1007
    SSX  NULL Transaction table/undo blocks (write dirty buffers)      2
    SSX  S    Make transaction table/undo block available share        1
    SSX   X    Rearm transaction table write mechanism              1007

See Also: Your platform-specific Oracle documentation for information about connecting with Net8.

### Monitoring and Tuning Lock Activity

Use the following procedure to control distributed lock activity.

1. Repeatedly query each instance that you want to monitor with the following SQL statement:

       SELECT * FROM V$LOCK_ACTIVITY;

2. If this increases rapidly for any instance, identify the types of lock conversions that are most active in the instance with the following SQL statement:

       SELECT * FROM V$LOCK_ACTIVITY;

   Any lock activities from X to a lower mode (such as X to S, X to Null, X to SSX, or S to N) indicate that there is contention among instances for blocks in the buffer cache (blocks are being "pinged") and the instance is releasing locks at the request of other instances. Query the instance repeatedly to find out whether the number of conversions is increasing rapidly.

3. Query the V$LOCK_ACTIVITY view of each instance to identify which instances have the most NULL to S conversions or S to X conversions. These instance are making most of the requests for data that is locked by other instances ("pinging").

   If the pinging occurs mainly between two instances, you should consider letting the applications on those instances run on a single instance.

   If pinging occurs on several instances at approximately the same rate, you may need to tune your PCM lock allocations (see Step 7) or you may have a set of data that the instances access equally, in which case you need to tune your applications (see Step 8).

4. Identify which blocks are pinging by querying the V$PING view of an instance you are monitoring:

       SELECT * FROM V$PING;

   You might want to restrict this query with a qualifier to display the blocks that have undergone the most contention; for example:

       SELECT * FROM V$PING WHERE  FORCED_READS > 10 OR FORCED_WRITES > 10;

   or:

       SELECT NAME, KIND, STATUS, SUM(FORCED_READS), SUM(FORCED_WRITES)
       FROM V$PING
       GROUP BY NAME, KIND, STATUS
       ORDER BY SUM(FORCED_READS);

   Note: Querying V$BH is faster than querying V$PING or V$CACHE. You can query V$BH to find the block numbers and file numbers of interest. Since V$BH has an OBJD (object number) field, you can join with OBJ$ to find the name of the object, as follows:

       SELECT O.NAME, BH.*
       FROM V$BH BH, OBJ$ O
       WHERE O.OBJ# = BH.OBJD
       AND (BH.FORCED_READS > 10 OR BH.FORCED_WRITES > 10);

5. For blocks that show high rates of pinging, compare FILE# with the datafiles specified in GC_FILES_TO_LOCKS to find out whether their PCM locks cover multiple blocks. If so, also note whether the locks cover blocks in multiple files.
6. If the PCM locks cover multiple blocks, you should determine whether other instances require data from the same block or from different blocks in the same set. To do this, query V$CACHE (or V$BH) in other instances for the BLOCK# that corresponds to a high value of XNC in the instance you are monitoring.
7. If the block does not appear in another instance, there is unnecessary contention (false pinging) because instances that require different blocks are using the same PCM lock for those blocks. To minimize unnecessary contention within one or more datafiles, reduce the number of blocks per lock by allocating more PCM locks to the files with the GC_FILES_TO_LOCKS parameter. If the PCM locks cover multiple files, you can reduce contention by allocating separate sets of locks to individual files.
8. If the same blocks show up in multiple buffer caches, the instances are contending for the same data.

   When multiple instances frequently need to modify data in the same block, you may be able to improve performance by running the applications that require the data on the same instance.

   If the instances modify different rows within the same block, you can re-create the table using the FREELIST GROUPS storage option, then alter the table to allocate extents to particular instances and update selectively to place the data in the appropriate extents.

   For a small table, you can use the PCTFREE and PCTUSED parameters to ensure that a block only contains one row.

   If the contention is for rows that are used to generate unique numbers, you can change the applications so that they use SEQUENCE numbers instead of generating their own numbers.

Note: Contention for data blocks and other shared resources does not necessarily have a significant effect on performance. If the response time of your applications is acceptable and you do not anticipate substantial increases in system usage, you may not need to tune your parallel server.

## Querying the V$PING View to Detect Pinging

"Pinging" is a catchall term for contention. It includes

- "pings", which are lock down-conversions
- forced reads and forced writes, which constitute the I/O subset of contention. Note that some pings can cause multiple forced writes.

"False pinging" occurs when different instances request different blocks, which happen to map to the same PCM lock. This pinging is unnecessary because it can be reduced by decreasing the granularity of the PCM locks.

Use the following procedure to detect pings.

1. Query V$PING to display summary statistics about lock conversions.

       SQL> SELECT NAME, FILE#, CLASS#, MAX(XNC) FROM V$PING
            2  GROUP BY NAME, FILE#, CLASS#
            3  ORDER BY NAME, FILE#, CLASS#;

       NAME               FILE#     CLASS#       MAX(XNC)
       ------------    --------     ------      ---------
       ...
       DEPT                   8     1                 492
       DEPT                   8     4                  10
       EMP                    8     1                3197
       EMP                    8     4                  29
       ...

2. Query V$PING again to display the frequency of PCM lock conversions and information for blocks in file 8.

       SQL> SELECT * FROM V$PING WHERE FILE# = 8;
       FILE#   BLOCK#   STAT    XNC  CLASS#   NAME            KIND
       ------  ------   ----  -----  ------   --------------  -------
            8      98   XCUR    450       1   EMP             TABLE
            8      764  SCUR     59       1   DEPT            TABLE

3. Query the EMP table to display the rows contained in block 98. Convert the BLOCK# to a hexadecimal value and compare it to the ROWID. (98 equals 62 in hexadecimal.)

       SQL> SELECT ROWID, EMPNO, ENAME FROM EMP
            2  WHERE chartorowid(rowid) like '00000062%';
       ROWID                EMPNO  ENAME
       ------------------  ------  ----------
       00000062.0000.0008   12340  JONES
       00000062.0000.0008    6491  CLARK
       ...;

## Querying V$CLASS_PING, V$FILE_PING, and V$BH

Using dynamic performance views you can separate out, by file, the block classes that are causing most of the contention.

The V$CLASS_PING view helps you identify which class of blocks (such as rollback segments) are being pinged the most. It provides a detailed breakdown by lock conversion type (such as Null to Shared), with read and write physical I/O incurred due to the conversion. Its statistics are cumulative since instance startup. To distribute the contention, you can move different classes of blocks to separate files. For example, you might want to separate rollback segments and datablocks into different files.

The V$FILE_PING view helps you identify which files are being pinged the most. Its statistics are also cumulative since instance startup. To distribute the contention, you can move to other files the objects contained within a heavily pinged file. If a table is heavily pinged, you could partition the table, and place the partitions on separate files.

The V$BH view is a changing snapshot of the buffer cache at any given time. You should periodically sample it, and see how it changes over time. Its statistics are dynamic, not cumulative since startup. V$BH should be sampled periodically to get an idea of ping activity at different points in time during the workload (as stated earlier). You can use V$BH to identify objects in the buffer cache that are undergoing pings, and to determine the forced read/write I/O caused by these pings. V$BH has the object identifier, which can be joined with OBJ$ to get the object name.

Note: You can also monitor the global (GV$) dynamic performance view corresponding to each of these views.

See Also: Oracle8 Reference for more information on dynamic views.

## Querying the V$WAITSTAT View to Monitor Contention

Use this view to display block contention statistics for resources such as rollback segments and free lists.

This section covers the following topics:

- Monitoring Contention for Blocks in Free Lists
- Monitoring Contention for Rollback Segments

### Monitoring Contention for Blocks in Free Lists

Use the following procedure to monitor contention for blocks in free lists.

1. To check the number of waits for free blocks in free lists:

       SQL> SELECT CLASS, COUNT FROM V$WAITSTAT
            2 WHERE CLASS = 'free list';
       CLASS                COUNT
       ------------------ -------
       free list               12

2. Compare the COUNT obtained with total number of requests (SUM) for data over the same period.

       SQL> SELECT SUM(VALUE) FROM V$SYSSTAT
            2 WHERE name IN
            3 ('db block gets', 'consistent gets');
       SUM (VALUE)
       ------------
           12050211

3. If the number of waits for free blocks (COUNT) is greater than 1% of the total requests (SUM), consider adding more free lists to tables to reduce contention. To add more free lists to a table, recreate the table with a larger value for the FREELISTS storage parameter. Make the value of FREELISTS equal to the number of users that concurrently insert data into the table.

       SQL> CREATE TABLE new_emp
            2 STORAGE (FREELISTS 5)
            3 AS SELECT * FROM emp;
       Table created.
       SQL> DROP TABLE emp;
       Table dropped.
       SQL> RENAME new_emp TO emp;
       Table renamed.

### Monitoring Contention for Rollback Segments

Use the following procedure to monitor contention for rollback segments.

1. Determine contention for rollback segments with the V$WAITSTAT view.

       SQL> SELECT CLASS, COUNT
            2 FROM V$WAITSTAT
            3 WHERE CLASS IN ('system undo header',
            4 'system undo block','undo header','undo block');
       CLASS                COUNT
       ------------------ -------
       system undo header      12
       system undo block       11
       undo header             28
       undo block               6

2. Compare the COUNT obtained with total number of requests (SUM) for data over the same period.

       SQL> SELECT SUM(VALUE) FROM V$SYSSTAT
            2 WHERE name IN
            3 ('db block gets', 'consistent gets');
       SUM (VALUE)
       ------------
           12050211

3. If the number of waits for any class of blocks (COUNT) is greater than 1% of the total requests (SUM), use the CREATE ROLLBACK SEGMENT command to add more rollback segments.

See Also: "Data Dictionary Reference" chapter in Oracle8 Reference.

## Querying V$FILESTAT and V$DATAFILE to Monitor I/O Activity

Use the V$FILESTAT and V$DATAFILE views to monitor statistics on disk/file access and determine the greatest I/O activity in the system.

1. To determine the number of reads and writes to each database file and the name of each datafile, query the V$FILESTAT and V$DATAFILE views.

       SQL> SELECT NAME, PHYRDS, PHYWRTS
            2  FROM V$DATAFILE df, V$FILESTAT fs
            3  WHERE df.file# = fs.file#;
       NAME                             PHYRDS     PHYWRTS
       -------------------------   -----------  ----------
       /test71/ora_system.dbs             7679        2735
       /test71/ora_system1.dbs              32         546

2. To determine the number of reads and writes to each non-database file, use an operating system utility, such as the UNIX utility iostat. The total I/O for each disk is the total number of reads and writes to all files on the disk.
3. Analyze statistics from the V$FILESTAT view to determine whether disk I/O needs to be distributed to avoid overloading one or more disks. To minimize contention for disk I/O:
   - Separate datafiles and redo log files on different disks.
   - Separate (or stripe) table data on different disks.
   - Separate tables and indexes on different disks.
   - Reduce disk I/O not related to the Oracle server.
4. Analyze the statistics from the V$DATAFILE view to determine whether files need to be placed on separate disks to avoid contention for disk I/O.
   - Place frequently accessed datafiles on separate disks to allow multiple processes to access the data with less contention.
   - Place each set of redo log files on a separate disk with little activity. Information in a redo log file is written sequentially; writing can take place much faster if there is no concurrent activity on the same disk.
   - Stripe a large table to store the table data on separate disks.

Note: Consult your hardware documentation to determine disk I/O limits. Any disks operating at or near full capacity are potential sites for disk contention. For example, 40 or more I/Os per second is excessive for most disks on VMS or UNIX operating systems.

## Querying and Interpreting V$SESSTAT and V$SYSSTAT Statistics

The V$SESSTAT and V$SYSSTAT views provide parallel statistics for monitoring contention for various resources including data blocks, rollback segment blocks, and free space lists. This section describes how to query and interpret these statistics.

To display system statistics for analyzing your parallel server (class = 32 or class = 40), issue the following command:

    SQL> SELECT * FROM V$SYSSTAT
     WHERE CLASS = 32 OR CLASS = 40;

        STATISTIC# NAME                                   CLASS    VALUE
        ---------- -------------------------------------- --- --------
                28 global lock gets (non async)            32   225663
                29 global lock gets (async)                32   169023
                30 global lock get time                    32    23199
                31 global lock converts (non async)        32   773052
                32 global lock converts (async)            32    93488
                33 global lock convert time                32    65636
                34 global lock releases (non async)        32   381994
                35 global lock releases (async)            32        0
                36 global lock release time                32    13637
                59 DBWR cross instance writes              40      230
                60 remote instance undo writes             40        0
                61 remote instance undo requests           40      255
                62 cross instance CR read                  40       24
                69 next scns gotten without going to DLM   32        0
                73 calls to get snapshot scn kcmgss        32      349
                74 kcmsss waited for batching              32        0
                75 kcmgss reads scn without going to DLM   32        0
                84 hash latch wait gets                    40        1
        18 rows selected.

The following tips will help you interpret statistics obtained from these views.

global lock converts (async) | Divide this number by the V$SYSSTAT statistic "user commits" to calculate the percentage of cache hits.
DBWR cross-instance writes | Equals the number of blocks pinged. For large values, reallocate locks based on V$PING statistics.
remote instance undo writes | A large value may signify pinging activity.
remote instance undo requests | A large value indicates that data modified by this instance if often read by another instance; locate applications (and thus transactions) contending for the same data on the same instance.
cross-instance CR read | This is a slow read because every instance has to write out the block; a large value indicates that the instance is spending too much time waiting on blocks modified by other instances. Evaluate the distribution of locks in the GC_FILES_TO_LOCKS parameter and reallocate to keep the value of this statistic small.
next scns gotten without going to DLM | Divide this value by the total number of SCN gets given by the "user commits" statistic to calculate the percentage of SCN gets satisfied from the cache and thus measure the effectiveness of a parallel server's SCN cache.
hash latch wait gets | If this value is large or rapidly increasing, increase the number of hash latches.
kcmgss waited for batching | An internal call to get a snapshot might have to wait (for an on-going fetch of a SCN to complete) before contacting the distributed lock manager. This statistic value indicates system load and the number of opportunities that Oracle has to batch a single get-snapshot-SCN with other SCN fetches.
kcmgss reads scn without going to LM | If an internal call (to get a snapshot SCN) waits for an on-going SCN fetch, it may use the SCN acquired by the SCN fetch, thus avoiding overhead in using the lock manager. The ratio of "kcmgss reads scn without going to LM" and "kcmgss waited for batching" indicates the effectiveness of the parallel server's SCN batch algorithm.

See Also: Oracle8 Reference for definitions of these statistics.

Oracle Server Manager User's Guide descriptions of the MONITOR STATISTICS CACHE display for information about monitoring contention for various kinds of blocks.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index