onstat -u User Threads
The onstat -u command displays a list of the userthreads running on the system.
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
||The shared memory address of the session thread
||Describes the status of the userthread using the following coded values:
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
A DBSpace backup thread
B Begin work
X XA prepare
H Heuristic aborting/aborted
P Primary thread for a session
R Reading (RSAM Call)
X Critical Write
B BTree clean thread
C Terminated user thread waiting for cleanup
D Daemon thread
F Page cleaner thread (flusher)
M On-Monitor thread
onstat -g con
||The session ID assigned to the user.
||The user login name.
||The tty the user is using.
||The address of the resource for which this session thread is waiting.
||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.
||The number of locks currently held by this user thread.
||The number of disk reads executed by the user thread.
||The number of disk writes executed by the user thread.
||The number of currently active userthreads.
||The current number of userthreads that the engine has been dynamically allocated to handle.
||The maximum number of userthreads that have been running at the same time.
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.