15.7 Monitoring Cache Transfers
|
| < Day Day Up > |
|
Cache transfer activity across the instances can be observed using the following set of queries. The queries below will help identify the objects that have high cache transfer activities between instances. It displays contention statistics of buffers that are currently in the buffer cache of the corresponding instance. This could be identified with objects that have a high number of exclusive to NULL conversions.
SELECT INST_ID, NAME, FILE#, CLASS#, MAX(XNC) FROM GV$CACHE_TRANSFER GROUP BY INST_ID, NAME, FILE#, CLASS# /
From the output below, it is clear that the object COMPANY is the possible source of high cache transfer activity:
INST_ID NAME FILE# CLASS# MAX(XNC) ------- --------- ----- ------ -------- 1 IDL_UB2$ 1 4 231 1 PK_USPRL 4 1 47 1 PK_COMP 4 1 39 1 COMPANY 171 1 2849
Using the query below on the GV$CACHE_TRANSFER will help identify the frequency of lock conversions and the block-related information in the COMPANY table:
SELECT FILE#, BLOCK#, CLASS#, STATUS, XNC FROM GV$CACHE_TRANSFER WHERE NAME = AND FILE# = 171 /
The output below displays the frequency of lock conversions for the object found in the previous query, namely the COMPANY table:
FILE# BLOCK# CLASS# STAT XNC ----- ------- ------ ---- ---- 171 898 1 XCUR 1321 171 1945 1 XCUR 27 171 1976 1 XCUR 19 171 2039 1 XCUR 849
To drill down further, the query below is used to display the rows in the block found in the previous query. The DBMS_ROWID package is used to extract the block number from the ROWID pseudocolumn. This helps identify values that may be partitionable to avoid future contention. If the data cannot be partitioned and the data in these identified tables is updated frequently, then another alternative to reduce contention would be to reduce the number of rows per block to spread out the I/O activity over different blocks.
SELECT COMP_ID, COMP_NAME FROM COMPANY WHERE DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) = 898 /
The output below displays the rows contained in block 898:
COMP_ID NAME ------- ------------------------ 3949 SUMMERSKY DB CONSULTANTS 3952 CATAMARAN INC. 3957 PRIYAR BROTHERS INC. 3961 DIGITAL BROADCASTING INC.
The GV$CACHE_TRANSFER view could also be used to determine the objects involved in forced reads across instances:
SELECT INST_ID, NAME, CLASS#, SUM(FORCED_READS) FROM GV$CACHE_TRANSFER WHERE CLASS# IN (4,8,32,40) GROUP BY INST_ID, NAME, CLASS# ORDER BY SUM(FORCED_READS)
The output below is a list of objects that are involved in forced reads across the instances:
NAME SUM(FORCED_READS) ------------------------ ----------------- USER_PROFILE 1948 _SYSSMU16$ 1 PK_USPRL 584 COMPANY 958 STATS$BG_EVENT_SUMMARY 184 GV$CLASS_CACHE_TRANSFER
This view provides inter-instance cache transfer information on the number of blocks pinged based on a block class:
COL IID FORMAT 999 COL X2N FORMAT 99999999999 COL X2NFW FORMAT 99999999999 COL X2NFS FORMAT 99999999999 COL X2S FORMAT 99999999999 COL X2SF FORMAT 99999999999 COL S2N FORMAT 99999999999 COL S2NFS FORMAT 99999999999 COL N2S FORMAT 99999999999 COL S2X FORMAT 99999999999 COL N2X FORMAT 99999999999 SELECT VCCT.CLASS, VCCT.INST_ID IID, VCCT.X_2_NULL X2N, VCCT.X_2_NULL_FORCED_WRITE X2NFW, VCCT.X_2_NULL_FORCED_STALE X2NFS, VCCT.X_2_S X2S, VCCT.X_2_S_FORCED_WRITE X2SF, VCCT.S_2_NULL S2N, VCCT.S_2_NULL_FORCED_STALE S2NFS, VCCT.NULL_2_S N2S, VCCT.S_2_X S2X, VCCT.NULL_2_X N2X FROM GV$CLASS_CACHE_TRANSFER VCCT WHERE VCCT.CLASS IS NOT NULL /
The query against the class cache transfer provides class-level statistics of the cache transfer information. The output below indicates that the cache transfer is more of the data block level rather than at the segment header level. There are some N to S lock conversions at the segment level.
CLASS IID X2N X2NFW X2NFS X2S ------------------------------------------------------------------------------- X2SF S2N S2NFS N2S S2X N2X ------------------------------------------------------------------------------- data block 1 758251 0 758250 173391 0 100550 100550 66682981 132974 1244082 sort block 1 0 0 0 0 0 0 0 0 0 0 save undo block 1 0 0 0 0 0 0 0 0 0 0 segment header 1 0 0 0 1 0 2 2 2765 5 8
GV$FILE_CACHE_TRANSFER
This view identifies and displays the number of cache transfers per object file. The information collected through this view would be helpful to determine the file access patterns.
COL NAME FORMAT A45 COL IID FORMAT 999 COL RBR FORMAT 9999 COL X2N FORMAT 99999999 COL X2NFW FORMAT 99999999 COL X2NFS FORMAT 99999999 COL X2S FORMAT 99999999 COL S2N FORMAT 99999999 COL N2S FORMAT 99999999 COL RFW FORMAT 99999999 COL RFS FORMAT 99999999 COL S2X FORMAT 99999999 COL N2X FORMAT 99999999 COL CRT FORMAT 99999999 COL CURT FORMAT 99999999 SELECT VDF.NAME, VTS.NAME TABLESPACE_NAME, VFCT.INST_ID IID, VFCT.X_2_NULL X2N, VFCT.X_2_NULL_FORCED_WRITE X2NFW, VFCT.X_2_NULL_FORCED_STALE X2NFS, VFCT.X_2_S X2S, VFCT.S_2_NULL S2N, VFCT.RBR, VFCT.RBR_FORCED_WRITE RFW, VFCT.RBR_FORCED_STALE RFS, VFCT.NULL_2_S N2S, VFCT.S_2_X S2X, VFCT.NULL_2_X N2X, VFCT.CR_TRANSFERS CRT, VFCT.CUR_TRANSFERS CURT FROM GV$FILE_CACHE_TRANSFER VFCT, V$DATAFILE VDF, V$TABLESPACE VTS WHERE VFCT.FILE_NUMBER = VDF.FILE# AND VDF.TS# = VTS.TS# AND VFCT.CUR_TRANSFERS > 0 /
The output from the query above provides a view of the cache transfer activity at the file level. It provides the details on the kind of locks that are being placed at the data file level, and are related to the cache transfer requests from other instances. The instance ID column helps correlate and map the lock conversion requests on the specific file from the different instances.
NAME TABLESPACE_NAME -------------------------------------------------------------------- IID X2N X2NFW X2NFS X2S S2N RBR RFW RFS --------------------------------------------------------------------- N2S S2X N2X CRT CURT --------------------------------------------------------------------- /dev/vx/rdsk/oraracdg/partition1G_3 SYSTEM 1 33 2 33 96 36 2 0 0 1938 137 35 196 721 /dev/vx/rdsk/oraracdg/partition1G_3 SYSTEM 2 13 0 13 51 86 2 4 4 1709 43 38 428 867
| Lock Conversion | Description |
|---|---|
| NULL to S (lock buffers for read) | A NULL to S conversion occurs when a block is selected by a query. The block may or may not be present in the SGA. If a current copy of the block is in the cache, then only a lock conversion takes place, and the lock is not read from disk again. After the conversion, the status of the block is shared current (SCUR) |
| NULL to X (lock buffers for write) | A NULL to X conversion occurs when reading the block into the SGA for a DML operation (INSERT, UPDATE, DELETE). If the lock is not already held in exclusive mode, the status of the block after the conversion is exclusive current (XCUR) |
| S to NULL (release the shared lock from a buffer) | An S to NULL conversion occurs when an instance has acquired a block for read-only mode (SCUR mode) and another instance wants to modify the same block. The status of the first instance changes from SCUR to consistent read (CR) |
| S to X (upgrade read lock to write lock) | An S to X conversion occurs when a block is read into SGA by a SELECT statement and then a DML statement is issued against the same block. The status of the block changes from SCUR to XCUR |
| X to NULL (make buffers consistent read) | An X to NULL conversion occurs when an instance is modifying a block and another instance wants to modify the same block. The lock on the first instance is converted from X to NULL, a write to disk takes place, and the status of the block in the first instance's XCUR to CR. |
| X to S (downgrade write lock to read) | An X to S conversion takes place when an instance has modified a block and another instance wants to read the current version of the same block. This causes an X to S conversion on the first instance. This type of lock conversion occurs only if the block has been written out to disk prior to the request for the downgrade. If the block is still in memory, the block server process builds a read-consistent image of the required block and transmits it to the requesting instance without requiring a lock conversion |
GV$LOCK_ACTIVITY
This view displays the DLM lock operation activity of the current instance. Each row corresponds to a type of lock operation (described in Table 15.6).
SELECT * FROM GV$LOCK_ACTIVITY / INST_ID FROM TO_V ACTION_VAL COUNTER ------- ---- ---- ------------------------------------ -------- 1 NULL S Lock buffers for read 70198319 1 NULL X Lock buffers for write 10533 1 S NULL Make buffers CR (no write) 70033838 1 S X Upgrade read lock to write 1744 1 X NULL Make buffers CR (write dirty buffers) 4175 1 X S Downgrade write lock to read 311 (write dirty buffers) 2 NULL S Lock buffers for read 1616 2 NULL X Lock buffers for write 42 2 S NULL Make buffers CR (no write) 310 2 S X Upgrade read lock to write 27 2 X NULL Make buffers CR (write dirty buffers) 11 2 X S Downgrade write lock to read 28 (write dirty buffers)
12 rows selected.
From the data provided by the GV$LOCK_ACTIVITY view, the NULL to X and S to X conversions should be monitored. The instances with the highest number of conversions indicate requests for data locked by other instances.
The GV$FILE_CACHE_TRANSFER view would be helpful to determine which files are participating in this processes.
|
| < Day Day Up > |
|
EAN: 2147483647
Pages: 174