Dictionary Lock Views (Oracle V$VIEWS)

In Oracle 19c, the dictionary lock views provide insight into locking mechanisms that Oracle uses to manage access to data dictionary objects (such as tables, indexes, views, etc.). These views help in monitoring locks that are held on data dictionary objects, which can be crucial for troubleshooting performance or concurrency issues. The primary dictionary lock views in Oracle 19c include:

1. V$LOCK: This view lists all the locks currently held by the Oracle sessions. It includes locks on dictionary objects, user tables, and other internal Oracle structures.
2. V$LOCKED_OBJECT: Provides information about the objects that are locked in the database. This includes object-level locks, such as table or index locks.
3. V$LOCKS_WITH_COLLISIONS: Contains information about the locks that are causing contention between different sessions, including dictionary locks.
4. V$ENQUEUE_LOCK: Displays the enqueue locks, which can include dictionary locks. It provides details on sessions holding or waiting for enqueues.
5. V$RESOURCE: This view provides details about resources involved in locking, including those that relate to the data dictionary.
6. V$SESSION_WAIT: Shows session-level waits, including waits on dictionary locks. This can be useful to diagnose sessions waiting for a lock on a dictionary object.
7. V$SESSION: While this is a more general view, it includes details about sessions and their locking status. It can help identify which sessions are holding or waiting for dictionary locks.
8. DBA_DDL_LOCKS: Displays information about DDL locks in the system, including locks on dictionary objects. DDL locks are used when data dictionary objects are being modified.
9. DBA_DML_LOCKS: Shows DML locks that include locks on dictionary objects due to ongoing DML operations (such as insert, update, or delete statements).
10. DBA_LOCK_INTERNAL: Provides information about all the internal locks in Oracle, including dictionary locks.

These views are useful for monitoring and troubleshooting lock contention related to dictionary objects in Oracle 19c.

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

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?

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:

select
(a.value - b.value)/(a.value) lock_ratio
from
v$sysstat a, 
v$sysstat b
where
a.name = ‘consistent gets’
and
b.name = ‘global lock converts (async)’;

Here is what the output looks like:
LOCK_RATIO
----------
.95

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.