Tutorial Flow:



Identifying the scope of the SQL statements involved


As a part of the initial diagnoses, the database administrator typically tries to determine the part of the user workload impacted by the slowdown. To get this information, the database administrator gets more detailed information of the locks and tries to get the information about the SQLs/tables/applications involved in locking. To get this information, the database administrator uses MONREPORT.LOCKWAIT report. The report contains information about each lock wait currently in progress.


Monitoring report used



Report highlights

Pay attention to 'Part 1 - Summary of current lock waits' and 'Part 2: Details for each current lock wait' sections.

Results

You can notice many instances as:
  LOCK_OBJECT_TYPE     = ROW                                                       
  TABSCHEMA            = TRIPATHY                                                  
  TABNAME              = INVENTORY                                                 
  ROWID                =                                                          
  LOCK_STATUS          = W                                                         

This shows that the table (TABNAME) involved in the locks is INVENTORY. You can also see the AGENT_ID which is holding lock in 'Lock holder current agents' section. The same row in the INVENTORY table is being requested by other applications introducing lockwait in the system.

Conclusion

Multiple applications are waiting for the same lock.