Monitoring PCM Locking
Several dynamic views are available to monitor PCM locking activity. Some of these views provide ping statistics at the instance level, while other views provide information about ping activity at the block level. Table 10.1 lists all the V$ views that provide PCM locking statistics and tells you the level at which each view is useful. Statistics for some of the views (e.g., V$PING, V$FALSE_PING, and V$CACHE) are derived from the V$BH view. They contain the same columns as V$BH but provide additional database object information.
Table 10-1. Views with PCM Lock Contention Statistics
Dynamic View |
Description |
---|---|
V$LOCK_ACTIVITY GV$LOCK_ACTIVITY |
Instance-level lock activity Return the cumulative number of lock conversions performed by the instance. One row is returned for each type of conversion such as X to NULL, X to S, NULL to X, and so forth. |
V$FILE_PING GV$FILE_PING |
Lock activity by datafile Return the number of lock conversions for each datafile. One row is returned for each datafile. This view can be used to identify datafiles with a high amount of ping activity. |
V$BH GV$BH |
Block-level statistics Buffer header views. Return one row for each block in the buffer cache and show buffer status, forced reads, forced writes, and so forth. |
V$CACHE GV$CACHE |
Block- and object-level lock activity Like V$BH, return one row for each block in the buffer cache. The view provides the name and owner of the database object that contains the block. |
V$CACHE_LOCK ... |
Get Oracle Parallel Processing now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.