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.
dba_blockers – show all the sessions that have someone waiting on a lock they hold, but that are not themselves waiting on a lock.
dba_lock – has a row for each lock that is being held, and one row for each outstanding request for a lock or latch.
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
http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch53.htm#1005731
Oracle 10g
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/scripts002.htm#sthref4200
Oracle 11g see The Hang Manager utility
http://download.oracle.com/docs/cd/B28359_01/server.111/b28281/hafeatures.htm#sthref81
