Additional views for lock investigations

The lock analyses is one of important step in problem detection, also lock analyses can help to improve performance.

The standard views for analyses is: v$lock and v$session_wait

But Oracle Corp. suggest use additional views for more powerful and fast lock analyzes:

dba_waiters — show all the sessions waiting for locks and the session that holds the lock.

image

dba_blockers — show all the sessions that have someone waiting on a lock they hold, but that are not themselves waiting on a lock.

image

dba_lock — has a row for each lock that is being held, and one row for each outstanding request for a lock or latch.

image

dba_kgllock — auxiliary view containing the KGL locks and pins.internal (slow) view similar as dba_lock.

dba_dml_lock — has a row for each DML lock that is being held, and one row for each outstanding request for a DML lock. It is subset of dba_lock.

dba_ddl_lock — has a row for each DDL lock that is being held, and one row for each outstanding request for a DDL lock. It is subset of dba_lock.

The catblock.sql should be used for create such views. You can run it from sqlplus under sys account: @$ORACLE_HOME/rdbms/admin/catblock.sql

Documentation:

Oracle 9.2i

Oracle 10g

Oracle 11g see The Hang Manager utility