There are several V$VIEWS that can be used to display the internal locks within Oracle. These are the V$LOCK_ACTIVITY and the V$SYSSTAT views.
The V$LOCK_ACTIVITY view
The V$LOCK_ACTIVITY view is a very good way to determine if you have reached the maximum lock convert rate for your DLM. Because the maximum lock convert rate is unique to each vendor's DLM, you need to compare the results from V$LOCK_ACTIVITY with the maximum values in your OS vendor's documentation for their DLM. Regardless, if the maximum lock convert rate has been reached, you will need to repartition the application to balance alike transactions into
The V$SYSSTAT view
The V$SYSSTAT view can be used to determine whether lock converts are being performed too often.
Excessive lock convert rates usually mean there is contention for a common resource within the database. This resource may be a commonly updated table. For example, inventory management systems often utilize sequences (rows in the DBA_SEQUENCES View). A sequence row may be used to keep the order number of the last order, and all application tasks must increment this row when a new order is placed. This type of architecture forces each parallel instance to single-thread all requests for this resource.
But how do you identify these types of database resources?
Lock hit ratio
Just as the buffer hit ratio measures contention for data blocks, the lock hit ratio can be used to identify excessive lock conversion by the DLM. The lock hit ratio should generally be above 90 percent, and if it falls below 90 percent, you should look for sources of data contention. Here is the SQL to determine the lock hit ratio for Oracle parallel server:
(a.value - b.value)/(a.value) lock_ratio
a.name = ‘consistent gets’
b.name = ‘global lock converts (async)’;
Here is what the output looks like:
If you suspect that there may be data contention, here are two remedies you can use:
Now let us look at some Oracle utility script that will enhance the information in the basic V$LOCK views.