Lock Conflicts (LockConflicts)

The LockConflicts parameter provides all lock details for the following types of row and table locks:

• TX (Transaction lock) – An exclusive data lock is acquired for an individual row on behalf of a transaction when the row is modified by one of the following statements: insert, update, delete, and select with the 'for update' clause.
• TM (DML lock) – A transaction acquires a table lock when a table is modified in the following DML statements: insert, update, delete, select with the 'for update' clause, and lock table.
• UL (PL/SQL user lock)

This parameter also detects the deadlock situation.
 If lock contention has been occuring for more than 2 minutes, the LockConflicts parameter goes into alarm. The value is the number of lock conflicts observed. When the parameter goes into alarm, the annotation is the Lock Conflict report. The Lock Conflict report lists the following information for each lock conflict for all sessions, with their SID and serial number:

The lock contention time is customizable, to customize lock contention time in second use following command at system output window:

%PSL pconfig("REPLACE", "/OracleConfig.LockTime",N);

N should be greater than 0, else default value will be considered. You can set the same using PCM or wpconfig.

Also, you can set lock time at ORACLE SID level by adding following pconfig variable
%PSL pconfig("REPLACE", "/OracleConfig.<ORACLE_SID>.LockTime",m);

If both the variables are set then variable at ORACLE SID level will get preference.

In case of performance issue due to incorect hint being used for the LockConflicts query, add the following pconfig varible with the appropriate Oracle hint:
%PSL pconfig("REPLACE", "/OracleConfig.".sid.".LockConflictsHint","<oracle_hint>");

BMC PATROL properties

BMC ProactiveNet Performance Management properties