Determining the amount of inventory lock contention

Determining level of lock contention in Oracle

You can determine the level of inventory lock contention with the following techniques. In Oracle:

• Use AWR to calculate the amount of lock contention.
• In Oracle, query the v$session table to understand the extent of the lock contention.

AWR reports provide a measure of the total amount of time (in seconds) all transactions waited for record locks. This metric is found in the "Wait Events for DB" section (page 2) of a AWR report. In the following example, transactions waited for enqueues for a total of 741 seconds in that 30-minute measurement interval:

Wait Events for DB: YRAC05  Instance: YRAC051  Snaps: 15202 -15203
                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read           903,826          0      6,246      7      3.0
db file scattered read            879,659          0      4,281      5      2.9
enqueue                             3,542          6        741    209      0.0
library cache pin                     375        231        719   1918      0.0
buffer busy waits                 116,687          0        449      4      0.4
log file sync                     129,571          0        134      1      0.4

Dividing that number of enqueue wait times (741 seconds) by the measurement interval (30 minutes) shows that the enqueue contention was on average 0.41 blocked seconds per second. From a statistical point of view, one transaction was blocked 41% of the time every second. If you have ten concurrently running transactions, at one extreme, this statistic could be interpreted as all transaction was blocked 4.1%. At the other extreme, one transaction could have been completely blocked for 719 seconds.

In the example above, the lock contention is minimal. As a guideline, high lock contention situations are characterized as:

• Enqueue wait seconds per second is greater than 5 second per second or
• Enqueue wait is the top wait

If enqueue wait times are significant, run the following query to identify the sessions that are blocked, the amount of time that they were blocked for, and the objects they are blocked on:

   select sid,last_call_et, sql_text
   from v$session vs, v$sqlarea sa
   where last_call_et > 0 and
        vs.sql_hash_value = sa.hash_value and 
        vs.lockwait > ' '
   order by last_call_et desc;
   
   SID      LAST_CALL_ET       SQL_TEXT                                
   13               1       SELECT  /*YANTRA*/   YFS_ORDER_HEADER.* 
                         FROM YFS_ORDER_HEADER YFS_ORDER_HEADER     
                         WHERE ENTERPRISE_KEY =:"SYS_B_0" AND  
                               ORDER_NO = :"SYS_B_1"  FOR UPDATE 
   

In the example above, session (SID=13) blocked for 1 second while trying to lock a YFS_ORDER_HEADER record.

We suggest you look at the following:

• Determine the objects that transactions are blocked on (e.g., are transactions blocked on YFS_INVENTORY_ITEM or some other table).
• Determine the amount of time these transactions block for - If the blocks are for a few seconds (e.g., 1-2 seconds) and the number of order lines per order are small, the level of contention may be acceptable.

This query, along with the contention level derived from AWR, lets you determine the extent of the lock contention.

Determining the level of lock contention in Db2

For Db2®, check the following monitor elements:

• lock_wait_time to determine the amount of lock contention. If you divide this number by the measurement interval, you get the average lock wait (in milliseconds) per second.
• Check the table_name monitor element in the snapshot_lockwait monitor to see where most of the lock contention are coming from.
• For each blocked agent, check the stmt_text and uow_lock_wait_time monitor elements in the snapshot_statement monitor.

We suggest you look at the following:

• Determine the objects that transactions are blocked on (e.g., are transactions blocked on YFS_INVENTORY_ITEM or some other table).
• Determine the amount of time these transactions block for - if the blocks are for a few seconds (e.g., 1-2 seconds) and the number of order lines per order are small, the level of contention may be acceptable.