onstat -g lmx Locked Mutexes

The onstat -g lmx command displays currently locked mutexes.

Locked mutexes:                                                               
mid      addr     name               holder   lkcnt  waiter   waittime        
4976     14256468 uselock0           213      0                               

Output Description

Heading Description Format
mid The internal mutex identifier Dec
addr Address of the locked mutex Hex
name Name of the mutex Str
holder The session ID of the thread holding the mutex. Dec
lkcnt The number of waiters for this same mutex Dec
waiter A list of the addresses waiting for this mutex. Dec
waittime The amount of time in seconds this thread has been waiting. Dec

Monitoring and Tuning

  • Typically these operations are VERY fast and the mutex locking is transient. However, if you have several sessions actively modifying a table (inserting, deleting, creating indexes, compressing, reorging, etc.) there can be some contention for this single resource. There are some things you can do. Fragmenting the table ROUND ROBIN so that the number of concurrent accesses to each of the tables partitions' header page are reduced by a factor of the number of partitions/fragments is one. Changing large insert jobs to use INSERT/PUT cursors with a maximum sized communications buffer (see FET_BUF_SIZE) so that batches of many rows are inserted in a single operation is another.
  • Locked mutexes aren't by default bad, it could possibly indicate a performance problem if they are a large list of waiters on a particular mutex, or large wait times.
  • The uselock mutex is involved in shared memory communication and so it's normal to see that mutex locked while the sqlexec threads have no work.