| < Day Day Up > |
|
GV$SESSTAT provides information on statistics that affect the perfor mance of the clustered instances. Of the various statistics collected at the session and system level in the GV$SESSTAT and GV$SYSSTAT respectively, the statistics that belong to classes 4, 8, 32, and 40 should be of primary concern in a RAC implementation.
The listing below is a comprehensive view of the various statistics that are directly related to the performance of the RAC instances.
COL NAME FORMAT A50 SELECT STATISTIC#, NAME, CLASS FROM V$STATNAME WHERE CLASS IN (4,8,32,40) STATISTIC# NAME CLASS ---------- ----------------------------------- ----- 22 enqueue timeouts 4 23 enqueue waits 4 24 enqueue deadlocks 4 25 enqueue requests 4 26 enqueue conversions 4 27 enqueue releases 4 28 global lock sync gets 32 29 global lock async gets 32 30 global lock get time 32 31 global lock sync converts 32 32 global lock async converts 32 33 global lock convert time 32 34 global lock releases 32 35 total file opens 8 36 opens requiring cache replacement 8 37 opens of replaced files 8 38 gcs messages sent 32 39 ges messages sent 32 40 db block gets 8 41 consistent gets 8 42 physical reads 8 43 db block changes 8 44 consistent changes 8 45 recovery blocks read 8 46 physical writes 8 47 physical writes non checkpoint 8 48 summed dirty queue length 8 49 DBWR checkpoint buffers written 8 50 DBWR transaction table writes 8 51 DBWR undo block writes 8 52 DBWR revisited being-written buffer 8 53 DBWR make free requests 8 54 DBWR free buffers found 8 55 DBWR lru scans 8 STATISTIC# NAME CLASS ---------- ----------------------------------- ----- 56 DBWR summed scan depth 8 57 DBWR buffers scanned 8 58 DBWR checkpoints 8 59 DBWR cross instance writes 40 60 DBWR fusion writes 40 61 remote instance undo block writes 40 62 remote instance undo header writes 40 63 prefetch clients - keep 8 64 prefetch clients - recycle 8 65 prefetch clients - default 8 66 prefetch clients - 2k 8 67 prefetch clients - 4k 8 68 prefetch clients - 8k 8 69 prefetch clients - 16k 8 70 prefetch clients - 32k 8 71 change write time 8 72 redo synch writes 8 73 redo synch time 8 74 exchange deadlocks 8 75 free buffer requested 8 76 dirty buffers inspected 8 77 pinned buffers inspected 8 78 hot buffers moved to head of LRU 8 79 free buffer inspected 8 80 commit cleanout failures: write disabled 8 81 commit cleanout failures: block lost 8 82 commit cleanout failures: cannot pin 8 83 commit cleanout failures: hot backup in progress 8 84 commit cleanout failures: buffer being written 8 85 commit cleanout failures: callback failure 8 86 commit cleanouts 8 87 commit cleanouts successfully completed 8 88 recovery array reads 8 89 recovery array read time 8 90 CR blocks created 8 91 current blocks converted for CR 8 92 switch current to new buffer 8 93 write clones created in foreground 8 94 write clones created in background 8 95 prefetched blocks 8 96 prefetched blocks aged out before use 8 97 physical reads direct 8 98 physical writes direct 8 99 physical reads direct (lob) 8 100 physical writes direct (lob) 8 STATISTIC# NAME CLASS ---------- ------------------------------------- ----- 101 cold recycle reads 8 102 consistent gets - examination 8 103 shared hash latch upgrades - no wait 8 104 shared hash latch upgrades - wait 8 108 next scns gotten without going to GES 32 109 Unnecessary process cleanup for SCN 32 batching 110 calls to get snapshot scn: kcmgss 32 111 kcmgss waited for batching 32 112 kcmgss read scn without going to GES 32 113 kcmccs called get current scn 32 126 global cache gets 40 127 global cache get time 40 128 global cache converts 40 129 global cache convert time 40 130 global cache cr blocks received 40 131 global cache cr block receive time 40 132 global cache current blocks received 40 133 global cache current block receive time 40 134 global cache cr blocks served 40 135 global cache cr block build time 40 136 global cache cr block flush time 40 137 global cache cr block send time 40 138 global cache current blocks served 40 139 global cache current block pin time 40 140 global cache current block flush time 40 141 global cache current block send time 40 142 global cache freelist waits 40 143 global cache defers 40 144 global cache convert timeouts 40 145 global cache blocks lost 40 146 global cache claim blocks lost 40 147 global cache blocks corrupt 40 148 global cache prepare failures 40 149 global cache skip prepare failures 40 150 total number of slots 8 151 instance recovery database freeze count 32 152 background checkpoints started 8 153 background checkpoints completed 8 154 number of map operations 8 155 number of map misses 8 208 queries parallelized 32 209 DML statements parallelized 32 210 DDL statements parallelized 32 211 DFO trees parallelized 32 212 Parallel operations not downgraded 32 213 Parallel operations downgraded to serial 32 214 Parallel operations downgraded 75 to 99 pct 32 215 Parallel operations downgraded 50 to 75 pct STATISTIC# NAME CLASS ---------- ------------------------------------- ----- 216 Parallel operations downgraded 25 to 50 pct 32 217 Parallel operations downgraded 1 to 25 pct 32 218 PX local messages sent 32 219 PX local messages recv’d 32 220 PX remote messages sent 32 221 PX remote messages recv’d 32 225 table lookup prefetch client count 8 134 rows selected.
The statistics collected by V$SESSTAT and V$SYSSTAT by themselves do not reflect any direct relation to a problem; they are just clues. To drill down further, the class that the statistics belong to provides an indication including the type of block that could potentially be causing the problem.
Table 15.5 provides a list of classes and the types of blocks that the class is related to. This is helpful during performance analysis when the statistics collected from the V$SESSTAT and V$SYSSTAT views need to be mapped to the actual area of the database or table. The following are descriptions for some of the statistics listed in the output above.
Class | Type of Block |
---|---|
1 | Data or index block |
2 | Sort block |
3 | Save undo block |
4 | Segment header |
5 | Save undo segment header |
6 | Free lists block |
7 | System undo segment header |
8 | System undo segment block |
7 + 2n | Header of undo segment number n |
8 + 2n | Block in undo segment number n |
32 | Parallel operation |
40 | Global cache segment |
This gets incremented every time a block is not found in any of the remote caches and results in a disk read. The number indicates the approximate number of disk I/Os performed.
This indicates the total time spent by the process waiting to get the required permission to read the block from disk.
This indicates the number of times the access permission on a block has changed due to lock conversion.
This relates to the global cache converts statistic and indicates the total elapsed time to get the conversion complete.
This view displays pinging statistics against the various SGA buffers. Because this view only provides statistics for the local instance, viewing information at a global level would not provide significant benefits.
A single block can appear in multiple rows of these tables. Each row represents a different copy of the block. Multiple versions created for read- consistent queries appear with the status CR. For tuning purposes, the current copy with a status of XCUR or SCUR that contains the greatest value of XNC should be considered.
This view displays information from the block header of each block in the SGA of the current instance as related to the particular database objects.
This view displays types and classes of blocks that Oracle has transferred over the cluster interconnect at least once. It contains information from the block header of each block in the SGA of the current instance as related to particular database objects, i.e., it represents a block in the buffer cache of the current instance. This can be used to help identify which blocks are being pinged between instances, using the XNC column, which shows the number of lock conversions from exclusive to NULL status. These conversions represent potential pings. This view only shows buffers with a nonzero XNC count.
If the NAME column is blank, it indicates that the buffer is associated with a temporary segment. The FORCED_WRITES and FORCED_READS columns determine which type of objects RAC instances share. Values in the FORCED_WRITES column provide counts of how often a certain block type experiences a transfer out of a local buffer cache because the current version was requested by another instance.
The data in the XNC column is probably the most important in this view as it maintains the count of the block-level locks. Each block starts with an XNC value of zero when it first enters the buffer cache. This value is incremented each time the instance releases the 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.
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 the 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.
| < Day Day Up > |
|