onstat -u User Threads

The onstat -u command displays a list of the userthreads running on the system.

Userthreads                                                                     
address  flags   sessid   user     tty      wait     tout locks nreads   nwrites
140d3018 ---P--D 1        informix -        0        0    0     36       194    
140d35d8 ---P--F 0        informix -        0        0    0     0        1655   
140d3b98 ---P--F 0        informix -        0        0    0     0        292    
140d4158 ---P--F 0        informix -        0        0    0     0        13     
140d4718 ---P--F 0        informix -        0        0    0     0        20     
140d4cd8 ---P--F 0        informix -        0        0    0     0        16     
140d5298 ---P--F 0        informix -        0        0    0     0        0      
....
14a585d8 ---P--B 13       informix -        0        0    0     0        0      
14a58b98 Y--P--- 54       recommen -        14aa0588 0    1     1376     0      
14a59158 ---P--D 17       informix -        0        0    0     0        0      
14a59718 Y--P--- 23       informix -        14480848 0    1     325      0      
14a59cd8 Y--P--- 83       contact  -        14719698 0    1     361      0      
14a5a298 Y--P--- 85       contact  -        1476ff00 0    1     1        0      
14a5a858 Y--P--- 86       contact  -        1477b880 0    1     2        0      
 136 active, 256 total, 136 maximum concurrent                                  

Detail Definitions

Heading Description Format See Also
address The shared memory address of the session thread Hex  
flags Describes the status of the userthread using the following coded values: Position 1:
 B Waiting on a buffer
 C Waiting on a checkpoint
 G Waiting on write of the Logical Log buffer
 L Waiting on a lock
 S Waiting on a mutex
 T Waiting on a transaction
 X Waiting on a transaction cleanup
 Y Waiting on a condition

 DEFUNCT The thread has incurred a serious assertion 
 	failure, and has been suspended to allow 
	other threads to continue their work
Position 3:

Position 3:
 A DBSpace backup thread
 B Begin work
 P Preparing/prepared
 X XA prepare
 C Commiting/committed
 R Aborting/aborted
 H Heuristic aborting/aborted
Position 4:
 P Primary thread for a session
Position 5:
 R Reading (RSAM Call)
 X Critical Write
Position 7:
 B BTree clean thread
 C Terminated user thread waiting for    cleanup
 D Daemon thread
 F Page cleaner thread (flusher)
 M On-Monitor thread
Str onstat -b
onstat -g con
onstat -k
onstat -s
onstat -x
sessid The session ID assigned to the user. Dec  
user The user login name. Str  
tty The tty the user is using. Str  
wait The address of the resource for which this session thread is waiting. Hex onstat -k
onstat -s
tout The number of seconds remaining from a call to SET LOCK MODE TO WAIT n. If the value is 0, the thread is not waiting on a lock, if -1, the thread is waiting indefinitely. Dec onstat -k
locks The number of locks currently held by this user thread. Dec onstat -k
nreads The number of disk reads executed by the user thread. Dec onstat -p
onstat -d
nwrites The number of disk writes executed by the user thread. Dec onstat -p
onstat -d

Summary Information

Heading Description
active The number of currently active userthreads.
total The current number of userthreads that the engine has been dynamically allocated to handle.
maximum concurrent The maximum number of userthreads that have been running at the same time.

Notes

The engine will dynamically allocate additional memory for new userthreads if the number of requests exceeds the current total.

Informix Dynamic Server prevents a userthread from entering into a critical section of code while a checkpoint is occurring. The reverse is also true, while a userthread is in the middle of a critical section a checkpoint must wait.

There is typically one userthread per session. If a session is running with PDQPRIORITY turned on, there will be multiple userthreads for the session, all with the same session ID.

Monitoring and Tuning

An examination of the first flag in the flags column will quickly indicate the status of the userthreads. In a highly active system it is normal to see userthreads waiting on latches, locks and conditions. These waits will normally change very rapidly as resources are grabbed and released. If a particular session is having a problem, rapid problem resolution can be performed by first examining the flag values; then perform the corresponding onstat command to identify the resource on which the session might be waiting. For example, if the first flag is L, the session is waiting on a lock. The corresponding value in the wait column is the address of the lock being held. Performing an onstat -k will list locks being held. An examination of the list will show what other userthread is holding the lock. It is then a matter of determining why the application is holding the lock.

The locks column can assist in identifying sessions which are holding an excessive number of locks. In these instances, the application should be checked to determine if higher level locks, such as a table lock, might be warranted.

The isolation level for a session can be identified by either performing an onstat -x and looking up the corresponding userthread address, or by performing an onstat -g sql and examining the Iso Lvl column.

The NETTYPE parameter in the configuration file can be used to limit the total number of user connections to the system.