Monitor blocking threads with the onstat -g bth and onstat -g BTH commands

Running threads take ownership of various objects and resources; for example, buffers, locks, mutexes, decision support memory, and others. Contention for these resources among hundreds or thousands of threads can result in chains of dependencies. Use the onstat -g bth command to display the dependencies between blocking and waiting threads. Use the onstat -g BTH command to display session and stack information for the blocking threads.

For example, a thread that is blocked waiting to enter a critical section might own a row lock for which another thread is waiting. The second thread might be blocking a third thread that is waiting in the MGM query queue. Usually, the duration of such contention is short. However, if a thread is blocked long enough to be noticed, you might need to identify the source of the contention. The onstat -g bth command discovers the chains of dependency and displays blocker threads followed by waiting threads, in order. You can use the resulting picture of contentions to diagnose and correct the issues.

The following example of the onstat -g bth command output has multiple threads that are waiting on resources.

This command attempts to identify any blocking threads.

Highest level blocker(s)
 tid      name                 session
 48       sqlexec              26

Threads waiting on resources
 tid      name                 blocking resource              blocker
 49       sqlexec              MGM                            48
 13       readahead_0          Condition (ReadAhead)           -
 50       sqlexec              Lock (0x4411e578)              49
 51       sqlexec              Lock (0x4411e578)              49
 52       sqlexec              Lock (0x4411e578)              49
 53       sqlexec              Lock (0x4411e578)              49
 57       bf_priosweep()       Condition (bp_cond)             -
 58       scan_1.0             Condition (await_MC1)           -
 59       scan_1.0             Condition (await_MC1)           -

Run 'onstat -g BTH' for more info on blockers.

In this example, four threads are waiting for a lock that is owned by thread 49. Thread 49 is waiting for MGM resources that are owned by thread 48. If you run the onstat -g BTH command, the output shows the session and stack information for the blocking thread, which in this case is thread 48.