onstat -g spi command: Print spin locks with long spins

Use the onstat -g spi command to display information about spin locks with long spins.

Figure 1: Syntax:

Many resources in the server are accessed by two or more threads. In some of these accesses (such as updating a shared value), the server must guarantee that only one thread is accessing the resource at a time. A spin lock is the mechanism used to provide this mutually exclusive access for some resources. With this type of lock, a thread that did not succeed in acquiring the lock on the first try (because another thread was holding it) repeatedly attempts to acquire the lock until it succeeds.

The overhead cost of a spin lock is small, and spin locks are normally used for resources that require mutual exclusion for short periods of time. However, if a spin lock becomes highly contended, the loop-and-retry mechanism can become expensive.

The onstat -g spi command is helpful for identifying performance bottlenecks that are caused by highly contended spin locks. This option lists spin locks with waits, those spin locks for which a thread was not successful in acquiring the lock on its first attempt and thus had to loop and re-attempt.

Example output

Figure 2: onstat -g spi command output


Spin locks with waits:

Num Waits   Num Loops   Avg Loop/Wait    Name
114         117675      1032.24          lockfr3
 87         256461      2947.83          fast mutex, lockhash[832]
  1             11        11.00          fast mutex, 1:bhash[16668]
  4          51831     12957.75          fast mutex, 1:lru-4
  1            490       490.00          fast mutex, 1:bf[994850] 0xe00002 0x14eb32000

Output description

Num Waits (decimal)

Total number of times a thread waited for this spin lock.

Num Loops (decimal)

Total number of attempts before a thread successfully acquired the spin lock.

Avg Loop/Wait (floating point)

Average number of attempts needed to acquire the spin lock. Computed as Num Loops / Num Waits.

Name (string)

Uses the following codes to name the spin lock

lockfr: The lock free list. The number after lockfr is the index into the lock free list array.

lockhash[]: The lock hash bucket. The field inside the brackets is the index into the lock hash bucket array.

:bhash []: The buffer hash bucket. The field before the colon is the buffer pool index; the field inside the brackets after bhash is the index into the buffer hash bucket array.

:lru-: The LRU latch. The field before the colon is the buffer pool index; the field after lru- identifies the buffer chain pairs that are being used.

:bf[]: The buffer latch. The field before the colon is the buffer pool index; the field inside the brackets after bf is the position of buffer in the buffer array. The next two fields are the partition number and the page header address in memory for the buffer in hex form.