15.6 Statistics affecting RAC

 < 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.

Table 15.5: Class Description

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

Global cache gets

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.

Global cache get time

This indicates the total time spent by the process waiting to get the required permission to read the block from disk.

Global cache converts

This indicates the number of times the access permission on a block has changed due to lock conversion.

Global cache convert time

This relates to the global cache converts statistic and indicates the total elapsed time to get the conversion complete.

GV$BH

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.

GV$CACHE_LOCK

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.

GV$CACHE_TRANSFER

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 > 



Oracle Real Application Clusters
Oracle Real Application Clusters
ISBN: 1555582885
EAN: 2147483647
Year: 2004
Pages: 174

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net