O'Reilly logo

Oracle Parallel Processing by Sanjay Mishra, Tushar Mahapatra

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required