| Lesson 7 | The dictionary lock views |
| Objective | List the dictionary lock views |
Oracle 13c RDBMS: Comprehensive Guide to Dictionary Lock Views
In Oracle 13c, various Data Dictionary views provide indispensable insights into the lock mechanisms that govern transactional integrity and data concurrency. As an Oracle Database Administrator (DBA), leveraging these dictionary lock views is non-negotiable for proficient database management and troubleshooting. Below is an authoritative list of essential Data Dictionary views pertinent to locking within Oracle 13c RDBMS:
DBA Views
- DBA_BLOCKERS: Provides information about sessions that are holding locks that are blocking other sessions.
- DBA_DDL_LOCKS: Displays all DDL locks held in the database, along with the sessions that hold the locks.
- DBA_DML_LOCKS: Details all DML locks active in the database, revealing information on object names and lock types.
- DBA_LOCK: Offers a comprehensive view of all locks held or requested, incorporating the session ID, lock type, mode, and other vital lock attributes.
- DBA_LOCK_INTERNAL: Extends upon DBA_LOCK to include internal locks and resources.
- DBA_WAITERS: Highlights sessions that are waiting for a lock to be released, detailing the lock type and the blocking session.
V$ Views
- V$LOCK: Shows all locks currently held or requested, including the lock type, identification, and status.
- V$LOCKED_OBJECT: Lists all locked objects in the database and the sessions that hold the locks.
- V$LOCK_ACTIVITY: Furnishes statistics about lock activities, useful for gauging database performance with respect to locking mechanisms.
- V$LOCKED_USER: Reveals user-level information on sessions that currently hold locks.
- V$SESSION_WAIT: Depicts session-level information about resources (including locks) that sessions are waiting for.
- V$SESSION_BLOCKERS: Provides data on which sessions are blocking other sessions, instrumental in diagnosing and resolving lock contention issues.
GV$ Views (For RAC Environments)
- GV$LOCK: A Global View that aggregates V$LOCK information across all RAC instances.
- GV$LOCKED_OBJECT: A Global View displaying all locked objects across all instances in a RAC environment.
- GV$SESSION_WAIT: A Global View that offers a complete picture of session waits, including locks, across all RAC instances.
Accurate identification, analysis, and resolution of lock-related issues necessitate a thorough understanding of Oracle’s Data Dictionary Lock Views. These views furnish real-time insights into lock mechanisms, session behaviors, and system performance, making them crucial tools in an Oracle DBA's toolkit. By leveraging these views, a DBA can not only diagnose but also proactively manage and optimize the locking behavior in Oracle 13c, thereby ensuring transactional integrity and high database performance.
Dictionary Lock Views (Oracle V$VIEWS)
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.
- V$LOCK_ACTIVITY and
- V$SYSSTAT view
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 common instances.
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?
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:
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.
