Release 8.0
A54639_01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Parallel Server
Concepts & Administration Release 8.0 A54639_01 |
|
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.
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 Server Reference Manual, including:
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:
See Also: Oracle8 Server Reference Manual for more information on dynamic views and monitoring your database.
This section covers the following topics:
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.
The ALLOW_PARTIAL_SN_RESULTS parameter permits partial results to be returned on queries to global dynamic performance views (GV$ fixed views), even if a parallel server process could 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-21.
Oracle8 Server Reference Manual for restrictions on GV$ views, and complete descriptions of all the parameters and V$ dynamic performance views.
The following dynamic views are available to monitor a parallel server:
The V$ views are accessible to the user with SYSDBA privileges or a DBA user connected as INTERNAL. 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 Server Administrator's Guide.
Note: CONNECT INTERNAL syntax may not be supported in future releases.
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.
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.
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 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.
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:
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:
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.
Use the following procedure to control distributed lock activity.
SELECT * FROM V$LOCK_ACTIVITY;
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.
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).
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);
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.
"Pinging" is a catchall term for contention. It includes
"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.
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
...
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
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
....;
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 Server Reference Manual for more information on dynamic views.
Use this view to display block contention statistics for resources such as rollback segments and free lists.
This section covers the following topics:
Use the following procedure to monitor contention for blocks in free lists.
SQL> SELECT CLASS, COUNT FROM V$WAITSTAT 2 WHERE CLASS = 'free list'; CLASS COUNT
------------------ -------
free list 12
SQL> SELECT SUM(VALUE) FROM V$SYSSTAT
2 WHERE name IN
3 ('db block gets', 'consistent gets'); SUM (VALUE)
------------
12050211
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.
Use the following procedure to monitor contention for rollback segments.
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
SQL> SELECT SUM(VALUE) FROM V$SYSSTAT
2 WHERE name IN
3 ('db block gets', 'consistent gets'); SUM (VALUE)
------------
12050211
See Also: "Data Dictionary Reference" chapter in Oracle8 Server Reference Manual.
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.
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
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.
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.
See Also: Oracle8 Server Reference Manual 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.
