Two new onstat commands introduced in 12.10.xC2 to view the dependencies between blocking and waiting threads. A running threads take ownership of various objects and resources; for example, buffers, locks, mutexes, decision support memory etc. Contention for these resources among hundreds or thousands of threads can result in chains of  dependencies.

Now, you can use the 'onstat -g bth' command to display the dependencies between blocking and waiting threads. Next, use the 'onstat -g BTH' command to display session and stack information for the blocking threads.

Here is a scenario where these onstat commands can be helpful. It possible 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 and user never notice any problem. However, if a thread is blocked long enough, 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.

The following is a sample output of the 'onstat -g bth' command where multiple threads are waiting on resources.

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)           -

In the above example, four threads are waiting for a lock that is owned by thread 49. However, that's not the actual problem. The thread 49 is waiting for MGM resources that are owned by thread 48. So, originally problem started with the thread 48. Next, run the 'onstat -g BTH' command to see the session and stack information of thread 48.  Following is the example for 'onstat -g BTH' output:

Stack for thread: 48 sqlexec
 base: 0x00000000461a3000
  len:   69632
   pc: 0x00000000017b32c3
  tos: 0x00000000461b2e30
state: ready
   vp: 1

0x00000000017b32c3 (oninit) yield_processor_svp
0x00000000017bca6c (oninit) mt_wait
0x00000000019d4e5c (oninit) net_buf_get
0x00000000019585bf (oninit) recvsocket
0x00000000019d1759 (oninit) tlRecv
0x00000000019ce62d (oninit) slSQIrecv
0x00000000019c43ed (oninit) pfRecv
0x00000000019b2580 (oninit) asfRecv
0x000000000193db2a (oninit) ASF_Call
0x0000000000c855dd (oninit) asf_recv
0x0000000000c8573c (oninit) _iread
0x0000000000c835cc (oninit) _igetint
0x0000000000c72a9e (oninit) sqmain
0x000000000194bb38 (oninit) listen_verify
0x000000000194ab8a (oninit) spawn_thread
0x0000000001817de3 (oninit) th_init_initgls
0x00000000017d3135 (oninit) startup



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

session          effective                      #RSAM    total   used   dynamic
id      user     user      tty   pid   hostname threads  memory  memory explain
26      informix -         45    31041 darwin   2        212992  186568 off

Program :
/user/informix/bin/dbaccess

tid      name     rstcb            flags    curstk   status
48       sqlexec  448bc5e8         ---P---  4560     ready-
58       scan_1.0 448bb478         Y------  896      cond wait  await_MC1 -

Memory pools    count 2
name         class addr           totalsize  freesize   #allocfrag #freefrag
26           V     45fcc040       208896     25616      189        16        
26*O0        V     462ad040       4096       808        1          1         

name           free       used           name           free       used      
overhead       0          6576           mtmisc         0          72        
resident       0          72             scb            0          240       
opentable      0          7608           filetable      0          1376      
log            0          33072          temprec        0          17744     
blob           0          856            keys           0          176       
ralloc         0          55344          gentcb         0          2240      
ostcb          0          2992           sqscb          0          21280     
sql            0          11880          xchg_desc      0          1528      
xchg_port      0          1144           xchg_packet    0          440       
xchg_group     0          104            xchg_priv      0          336       
hashfiletab    0          1144           osenv          0          2520      
sqtcb          0          15872          fragman        0          1024      
shmblklist     0          416            sqlj           0          72        
rsam_seqscan   0          368            

sqscb info
scb              sqscb          optofc   pdqpriority optcompind  directives
4499c1c0         461c1028       0        100         2           1         

Sess       SQL            Current       Iso Lock       SQL  ISAM F.E.
Id         Stmt type      Database      Lvl Mode       ERR  ERR  Vers  Explain    
26         SELECT         stores        CR  Not Wait   0    0    9.24  Off        

Current statement name : unlcur

Current SQL statement (5) :
  select * from systables,syscolumns,sysfragments

Last parsed SQL statement :
  select * from systables,syscolumns,sysfragments
 

-Sanjit Chakraborty