Use the following options of the onstat utility to monitor sessions and threads:
The onstat -u option displays information on active threads that require a database server task-control block. Active threads include threads that belong to user sessions, as well as some that correspond to database server daemons (for example, page cleaners). Figure 80 shows an example of output from this utility.
Use onstat -u to determine if a user is waiting for a resource or holding too many locks, or to get an idea of how much I/O the user has performed.
The utility output displays the following information:
For information on these flags, see the IBM Informix: Dynamic Server Administrator's Reference.
A session ID of 0 indicates a daemon thread.
If you execute onstat -u while the database server is performing fast recovery, several database server threads might appear in the display.
Userthreads address flags sessid user tty wait tout locks nreads nwrites 80eb8c ---P--D 0 informix - 0 0 0 33 19 80ef18 ---P--F 0 informix - 0 0 0 0 0 80f2a4 ---P--B 3 informix - 0 0 0 0 0 80f630 ---P--D 0 informix - 0 0 0 0 0 80fd48 ---P--- 45 chrisw ttyp3 0 0 1 573 237 810460 ------- 10 chrisw ttyp2 0 0 1 1 0 810b78 ---PR-- 42 chrisw ttyp3 0 0 1 595 243 810f04 Y------ 10 chrisw ttyp2 beacf8 0 1 1 0 811290 ---P--- 47 chrisw ttyp3 0 0 2 585 235 81161c ---PR-- 46 chrisw ttyp3 0 0 1 571 239 8119a8 Y------ 10 chrisw ttyp2 a8a944 0 1 1 0 81244c ---P--- 43 chrisw ttyp3 0 0 2 588 230 8127d8 ----R-- 10 chrisw ttyp2 0 0 1 1 0 812b64 ---P--- 10 chrisw ttyp2 0 0 1 20 0 812ef0 ---PR-- 44 chrisw ttyp3 0 0 1 587 227 15 active, 20 total, 17 maximum concurrent
Use the onstat -g ath option to obtain a listing of all threads. Unlike the onstat -u option, this listing includes internal daemon threads that do not have a database server task-control block. On the other hand, the onstat -g ath display does not include the session ID (because not all threads belong to sessions).
Threads that a primary decision-support thread started have a name that indicates their role in the decision-support query. For example, Figure 81 shows four scan threads that belong to a decision-support thread.
Threads: tid tcb rstcb prty status vp-class name ... 11 994060 0 4 sleeping(Forever) 1cpu kaio 12 994394 80f2a4 2 sleeping(secs: 51) 1cpu btclean 26 99b11c 80f630 4 ready 1cpu onmode_mon 32 a9a294 812b64 2 ready 1cpu sqlexec 113 b72a7c 810b78 2 ready 1cpu sqlexec 114 b86c8c 81244c 2 cond wait(netnorm) 1cpu sqlexec 115 b98a7c 812ef0 2 cond wait(netnorm) 1cpu sqlexec 116 bb4a24 80fd48 2 cond wait(netnorm) 1cpu sqlexec 117 bc6a24 81161c 2 cond wait(netnorm) 1cpu sqlexec 118 bd8a24 811290 2 ready 1cpu sqlexec 119 beae88 810f04 2 cond wait(await_MC1) 1cpu scan_1.0 120 a8ab48 8127d8 2 ready 1cpu scan_2.0 121 a96850 810460 2 ready 1cpu scan_2.1 122 ab6f30 8119a8 2 running 1cpu scan_2.2
Use the onstat -g act option to obtain a list of active threads. The onstat -g act output shows a subset of threads that is also listed in onstat -g ath output.
Use the onstat -a ses option to monitor the resources allocated for and used by a session; in particular, a session that is running a decision-support query. For example, in Figure 82, session number 49 is running five threads for a decision-support query.
session #RSAM total used id user tty pid hostname threads memory memory 57 informix - 0 - 0 8192 5908 56 user_3 ttyp3 2318 host_10 1 65536 62404 55 user_3 ttyp3 2316 host_10 1 65536 62416 54 user_3 ttyp3 2320 host_10 1 65536 62416 53 user_3 ttyp3 2317 host_10 1 65536 62416 52 user_3 ttyp3 2319 host_10 1 65536 62416 51 user_3 ttyp3 2321 host_10 1 65536 62416 49 user_1 ttyp2 2308 host_10 5 188416 178936 2 informix - 0 - 0 8192 6780 1 informix - 0 - 0 8192 4796
Use the onstat -g mem and onstat -g stm options to obtain information on the memory used for each session. You can determine which session to focus on by the used memory column of the onstat -g ses output.
Figure 83 shows sample onstat -g ses output and an excerpt of the onstat -g mem and onstat -g stm outputs for Session 16.
The totalsize column of the onstat -g mem 16 output shows the total amount of memory allocated to the session.
The heapsz column of the onstat -g stm 16 output in Figure 83 shows the amount of memory allocated for the current prepared SQL statement.
onstat -g ses
session #RSAM total used
id user tty pid hostname threads memory memory
18 informix - 0 - 0 12288 8928
17 informix 12 28826 lyceum 1 45056 33752
16 virginia 6 28743 lyceum 1 90112 79504
14 virginia 7 28734 lyceum 1 45056 33096
3 informix - 0 - 0 12288 10168
2 informix - 0 - 0 12288 8928
onstat -g mem 16
Pool Summary:
name class addr totalsize freesize #allocfrag #freefrag
16 V a9ea020 90112 10608 159 5
...
onstat -g stm 16
session 16 ---------------------------------------------------------------
sdblock heapsz statement ('*' = Open cursor)
aa0d018 10056 *SELECT C.customer_num, O.order_num
FROM customer C, orders O, items I
WHERE C.customer_num = O.customer_num
AND O.order_num = I.order_num