Detecting root node contention

You can analyze the output of the onstat -g spi command to identify the performance bottlenecks that a forest of trees index can alleviate.

About this task

To detect root node contention and determine whether you need a forest of trees index:

Procedure

Run the onstat -g spi | sort -nr command to display information about spin locks with long spins.
The output of the onstat -g spi command shows spin locks with waits, which occur when threads are reading from or writing to an index concurrently and a particular thread did not succeed in acquiring the lock on the first try.
Analyze the onstat -g spioutput. Look for loop and wait information in these columns:
- Num Waits: The Total number of times a thread waited for the spin lock.
- Num Loops: The total number of attempts before a thread successfully acquired the spin lock.
- Avg Loop/Wait: The average number of attempts to acquire the spin lock, computed as Num Loops / Num Waits.
For example, the following output snippet shows spin locks with large numbers of waits and loops:
```
Spin locks with waits:
Num Waits Num Loops Avg Loop/Wait Name
332480    1568908   4.72  fast mutex, 3:bf[1234] 0x2d00008  0x1028a0d8000
39722     498769    12.56 mutex lock, name = log
20761     101831    4.90  fast mutex, 7:bf[62] 0x1300003  0x109da128000
14818     77680     5.24  mutex lock, name = MGM mutex
6523      34350     5.27  fast mutex, 3:bf[362] 0x20008e   0x10289a08000
```

Query sysmaster:systabnames with the hexadecimal representation of the part number shown in the onstat -g spi output. If the tabname represents an index name, the index is a forest of trees candidate.

For example, run this query:

echo "select tabname, hex(partnum) from systabnames
 where hex(partnum) =  '0x02d00008'" | dbaccess sysmaster -

tabname       daily_market_idx
(expression)  0x02d00008

$ echo 'select tabname, hex(partnum) from systabnames'
 where hex(partnum) = 0x01300003 | dbaccess sysmaster -

tabname       trade_history_idx
(expression)  0x01300003

$ echo 'select tabname, hex(partnum) from systabnames'
 where hex(partnum) = 0x0020008E | dbaccess sysmaster -

tabname       trade_request_idx2
(expression)  0x0020008E