You can use arguments of the onstat -g option to obtain memory information for each session.
The onstat -g ses displays memory usage by session id. When the session shares the memory structures in the SSC, the value in the used memory column should be lower than when the cache is turned off. For example, Figure 74 shows sample onstat -g ses output when the SQL statement cache is not enabled, and Figure 75 shows output after it is enabled and the queries in Session 4 are run again. Figure 74 shows that Session 4 has 45656 bytes of used memory. Figure 75 shows that Session 4 has less used bytes (36920) when the SSC is enabled.
session #RSAM total used id user tty pid hostname threads memory memory 12 informix - 0 - 0 12288 7632 4 informix 11 5158 smoke 1 53248 45656 3 informix - 0 - 0 12288 8872 2 informix - 0 - 0 12288 7632
session #RSAM total used id user tty pid hostname threads memory memory 17 informix - 0 - 0 12288 7632 16 informix 12 5258 smoke 1 40960 38784 4 informix 11 5158 smoke 1 53248 36920 3 informix - 0 - 0 12288 8872 2 informix - 0 - 0 12288 7632
Figure 75 also shows the memory allocated and used for Session 16, which executes the same SQL statements as Session 4. Session 16 allocates less total memory (40960) and uses less memory (38784) than Session 4 (Figure 74 shows 53248 and 45656, respectively) because it uses the existing memory structures in the SQL statement cache.
The onstat -g ses session-id option displays detailed information for a session. The following onstat -g ses session-id output columns display memory usage:
Figure 76 shows that Session 16 has currently allocated 69632 bytes, of which 11600 bytes are allocated from the sscpool.
onstat -g ses 14 session #RSAM total used id user tty pid hostname threads memory memory 14 virginia 7 28734 lyceum 1 69632 67384 tid name rstcb flags curstk status 38 sqlexec a3974d8 Y--P--- 1656 cond wait(netnorm) Memory pools count 1 name class addr totalsize freesize #allocfrag #freefrag 14 V a974020 69632 2248 156 2 ... Sess SQL Current Iso Lock SQL ISAM F.E. Id Stmt type Database Lvl Mode ERR ERR Vers 14 SELECT vjp_stores CR Not Wait 0 0 9.03 Current statement name : slctcur Current SQL statement : 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 Last parsed SQL statement : 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 11600 byte(s) of memory is allocated from the sscpool
The onstat -g sql session-id option displays information about the SQL statements executed by the session. Figure 77 shows that onstat -g sql session-id displays the same information as the bottom portion of the onstat -g ses session-id in Figure 76, which includes the number of bytes allocated from the sscpool.
onstat -g sql 14
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers
14 SELECT vjp_stores CR Not Wait 0 0 9.03
Current statement name : slctcur
Current SQL statement :
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
Last parsed SQL statement :
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
11600 byte(s) of memory is allocated from the sscpool The onstat -g stm session-id option displays information about the memory each SQL statement uses in a session. Figure 78 displays the output of onstat -g stm session-id for the same session (14) as in onstat -g ses session-id in Figure 76 and onstat -g sql session-id in Figure 77. When the SQL statement cache is on, the database server creates the heaps in the sscpool. Therefore, the heapsz output field in Figure 78 shows that this SQL statement uses 10056 bytes, which is contained within the 11600 bytes in the sscpool that the onstat -g sql 14 shows.
onstat -g stm 14 session 14 --------------------------------------------------------------- sdblock heapsz statement ('*' = Open cursor) aa11018 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