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.

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

  1. 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.

  2. 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
  3. 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

Copyright© 2019 HCL Technologies Limited