There are several dynamic performance views in Oracle that contain server statistics such as events, wait times, and so forth. While these views are not used exclusively for OPS, they do contain many OPS-related statistics. Table 10.4 summarizes these views.
Table 10-4. Views with OPS-Related Statistics
Dynamic View |
Description |
---|---|
V$SYSSTAT GV$SYSSTAT |
Provide several statistics on OPS related to database resource contention and global lock operations |
V$SYSTEM_EVENT GV$SYSTEM_EVENT |
Provide wait statistics for various system events, including events related to global lock operations |
V$WAITSTAT GV$WAITSTAT |
Provide statistics for the number of waits for each class of blocks |
V$FILESTAT GV$FILESTAT |
Provide statistics on file reads and writes |
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 *
2FROM v$sysstat
3WHERE class IN (32,40)
4ORDER 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 *
2FROM v$sysstat
3WHERE 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.
Table 10-5. V$SYSTAT Statistics
Statistic Name |
Description |
---|---|
DBWR cross-instance writes |
The number of blocks written to disk by one instance so that other OPS instances can access those blocks in a consistent manner |
physical writes |
The number of blocks written to the disk by Oracle processes |
global cache gets |
A count of new PCM locks acquired |
global cache get time |
Elapsed time for acquiring new PCM locks |
global cache converts |
A count of PCM locks that are converted |
global cache convert time |
The elapsed time required to convert PCM locks |
global lock get time |
The elapsed time for acquiring new instance locks (PCM and non-PCM locks) |
global lock sync gets |
A count of synchronous instance lock acquisitions |
global lock async gets |
A count of asynchronous instance lock acquisitions |
global lock convert time |
The elapsed time required to convert instance locks |
global lock sync converts |
A count of synchronous instance lock conversions |
global lock async converts |
A count of asynchronous instance lock conversions |
remote instance undo writes |
The number of times undo blocks are written by the instance at the request of a remote instance |
remote instance undo requests |
The number of times the instance requested undo blocks from a remote instance’s rollback segment in order to create consistent read data |
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.
Table 10-6. Derivable V$SYSTAT Statistics
Statistic Name |
Derivation Formula |
Recommendations |
---|---|---|
Percentage of disk writes due to OPS ping activity |
(DBWR cross-instance writes / physical writes) * 100 |
If greater than 10, continue tuning in order to reduce pings. |
Average time taken to allocate a new PCM lock |
global cache get time / global cache gets |
Should be between 20-30 milliseconds. |
Average time taken for PCM lock mode conversion |
global cache convert time / global cache converts |
Should be between 10-20 milliseconds. |
Average time required to acquire a global lock |
global lock get time / (global lock sync gets + global lock async gets) |
Should be between 20-30 milliseconds. |
Average time required to convert a global lock |
global lock convert time / (global lock sync converts + global lock async converts) |
Should be between 10-20 milliseconds. |
Percentage of remote instance undo writes |
(remote instance undo writes / DBWR undo block writes) * 100 |
A high value indicates both rollback segment contention and rollback segment pinging activity. |
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
2FROM 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.
Table 10-7. Important V$WAITSTAT Statistics
Statistic Name |
Description |
---|---|
data block |
Number of waits for data blocks. |
segment header |
Number of waits for blocks containing segment headers. |
system undo header |
Number of waits for blocks containing system rollback segment headers. |
system undo block |
Number of waits for blocks containing system rollback segment blocks. This does not include rollback segment header blocks. |
undo header |
Number of waits for blocks containing rollback segment headers for rollback segments other than the system rollback segment. |
undo block |
Number of waits for blocks containing rollback segment blocks. This does not include system rollback segment blocks. |
free list |
Number of waits for free list blocks in the free list. |
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
18.118.12.50