This view contains several performance statistics. Statistics are grouped into various statistics classes. For example, statistics related to the operating system are stored with a class value of 16. Statistics specific to Oracle Parallel Server are stored with class values of 32 and 40. This section discusses some of the statistics available in this view that are related to OPS and explains how they can be used.

The following example shows V$SYSTAT being queried for all the OPS-related statistics:

SQL> SELECT * 
  2  FROM v$sysstat 
  3  WHERE class IN (32,40) 
  4  ORDER BY name;

STATISTIC# NAME                                          CLASS      VALUE
---------- ---------------------------------------- ---------- ----------
        77 DBWR cross instance writes                       40     839727
       169 DDL statements parallelized                      32          0
       168 DML statements parallelized                      32          0
       171 PX local messages recv'd                         32     103968
       170 PX local messages sent                           32     102989
       173 PX remote messages recv'd                        32       1821
       172 PX remote messages sent                          32         42
        91 Unnecesary process cleanup for SCN batching      32          0
        92 calls to get snapshot scn: kcmgss                32   28876985
        81 cross instance CR read                           40          0
       116 global cache convert time                        40          0
       115 global cache converts                            40    1073863
       111 global cache defers                              40       5999
       110 global cache freelist waits                      40          0
       114 global cache get time                            40          0
       113 global cache gets                                40     236035
       112 global cache queued converts                     40        960
        32 global lock async converts                       32    1618504
        29 global lock async gets                           32     303083
        33 global lock convert time                         32          0
        30 global lock get time                             32          0
        34 global lock releases                             32    7375572
        31 global lock sync converts                        32    2210896
        28 global lock sync gets                            32    7188009
       117 instance recovery database freeze count          32          0
        95 kcmccs called get current scn                    32          0
        94 kcmgss read scn without going to DLM             32          0
        93 kcmgss waited for batching                       32          0
        90 next scns gotten without going to DLM            32          0
       167 queries parallelized                             32         21
        78 remote instance undo block writes                40       4707
        79 remote instance undo header writes               40       5122
        80 remote instance undo requests                    40       5066

In addition to the statistics for classes 32 and 40, there are a few other statistics that are helpful in tuning OPS. You can obtain them using the following query:

SQL> SELECT * 
  2  FROM v$sysstat 
  3  WHERE name IN ('db block gets', 'consistent gets',
                                      'physical writes', 'DBWR undo block writes'),

STATISTIC# NAME                                          CLASS      VALUE
---------- ---------------------------------------- ---------- ----------
        38 db block gets                                     8   78576050
        39 consistent gets                                   8  920284235
        41 physical writes                                   8    4304306
        64 DBWR undo block writes                            8     739797

Table 10.5 summarizes the most useful of the statistics returned by the previous two queries.

Using the statistics described in Table 10.5, you can compute a number of other statistics useful for tuning OPS configurations. These other statistics are summarized in Table 10.6, which also shows the formulas used to derive them.

The V$SYSTEM_EVENT view returns information about events for which Oracle processes have had to wait. The query shown in the following example returns the number of waits due to different types of global lock operations in an OPS instance. The TOTAL_WAITS and TIME_TIMEOUTS columns provide the number of waits and the cumulative wait times for each of these global cache lock events. A high value of waits for global cache lock operations compared to other waits indicates a high amount of contention for PCM locks.

SQL> SELECT event,total_waits_timeouts
  2  FROM v$system_event
  3* WHERE event like '%global%';


EVENT                           TOTAL_WAITS TOTAL_TIMEOUTS
------------------------------- ----------- --------------
buffer busy due to global cache       32406            181
global cache lock open s                166              0
global cache lock open x              33378              1
global cache lock open ss              5490            304
global cache lock null to s          102362             43
global cache lock null to x          269541             52
global cache lock s to x             223473             16
global cache multiple locks            8716              1
global cache lock busy                 1690             13
global cache bg acks                      2              1

The V$WAITSTAT view provides statistics on the number of waits for each class of blocks. These statistics are cumulative from when the instance first started. The following example shows some results returned as the result of a query to V$WAITSTAT:

SQL> SELECT * FROM v$waitstat;

CLASS                   COUNT       TIME
------------------ ---------- ----------
data block               5899          0
sort block                  0          0
save undo block             9          0
segment header            168          0
save undo header            0          0
free list                  36          0
extent map                  0          0
bitmap block                0          0
bitmap index block          0          0
unused                      0          0
system undo header         65          0
system undo block         124          0
undo header              3100          0
undo block                123          0

14 rows selected.

Table 10.7 summarizes the more important V$WAITSTAT statistics.

To reduce contention for undo header blocks, add more rollback segments. To reduce contention for undo blocks, use larger rollback segments. Compare the number of waits for rollback segment blocks to the total number of blocks that were requested. If the ratio of waits to requests is greater than 0.01, then you should create additional rollback segments for the instance. To compute the total number of blocks requested, query V$SYSTAT for the “db block gets” and “consistent gets” statistics, and add those two values together.

To reduce waits on data blocks, change the PCTFREE and PCTUSED settings so that you have fewer rows per block. Use reverse key indexes too. Waits on segment header blocks indicate contention for free lists in the segment header. Use free list groups. A high number of “free list” waits implies high contention for the free list blocks. To remedy this, you can increase the value of your FREELIST GROUP storage parameters. Refer to Chapter 9 for an in-depth discussion of tuning these parameters.

This view provides I/O statistics for different datafiles. The PHYRDS and PHYWRTS columns provide information on the number of reads and the number of writes to each datafile. Run the query shown in the following example, and analyze the results to determine if a particular disk is overburdened with I/O activities. Redistribute files on different disks as necessary in order to balance the I/O load.

SQL> SELECT name, phyrds, phywrts 
                  FROM v$datafile, v$filestat 
                       WHERE v$datafile.file# = v$filestat.file#;

NAME                                         PHYRDS    PHYWRTS
---------------------------------------- ---------- ----------
/dev/rifassys1                               497215     544468
/dev/rifassystemp1                             4789       6663
/dev/rifasrbs1                                 3877     423011
/dev/rifastools1                               1435        588
/dev/rifasusers1                                384        383
/dev/rifasrbs2                                 4960     308014
/dev/rifasdata                             42928399     255236
/dev/rifasindex                             1353862    2299197
/dev/rifaslog                               1773110     134947
/dev/rifastemp1                                 384        383