Session memory

The database server uses the virtual portion of shared memory mainly for user sessions. Most of the memory that each user session allocates is for SQL statements. You can determine which session and which statements are using large amounts of memory. If necessary, you can set the SESSION_LIMIT_MEMORY configuration parameter to limit the amount of memory available to a session.

Use the following utility options to determine which session and prepared SQL statements are using large amounts of memory:

onstat -g mem
onstat -g stm

The onstat -g mem option displays memory usage of all sessions. You can find the session that is using the most memory by looking at the totalsize and freesize output columns. The following figure shows sample output for onstat -g mem. This sample output shows the memory use for three user sessions with the values 14, 16, 17 in the names output column.

Figure 1. onstat -g mem output

onstat -g mem

Pool Summary:
name         class addr     totalsize freesize #allocfrag #freefrag
...
14           V     a974020  45056     11960    99         10
16           V     a9ea020  90112     10608    159        5
17           V     a973020  45056     11304    97         13
...
Blkpool Summary:
name         class addr     size      #blks
mt           V     a235688  798720    19
global       V     a232800  0         0

To display the memory that is allocated by each prepared statement, use the onstat -g stm option. The following figure shows sample output for onstat -g stm.

Figure 2. onstat -g stm output

onstat -g stm


session   25 --------------------------------------------------
 sdblock  heapsz  statement (‘*' = Open cursor)
 d36b018    9216  select sum(i) from t where i between -1 and ?
 d378018    6240  *select tabname from systables where tabid=7
 d36b114    8400  <SPL statement>

The heapsz column in the output in Figure 2 shows the amount of memory that is used by the statement. An asterisk (*) precedes the statement text if a cursor is open on the statement. The output does not show the individual SQL statements in an SPL routine.

To display the memory for only one session, specify the session ID in the onstat -g stm option. For an example, see Monitor session memory with onstat -g mem and onstat -g stm output.

Set the SESSION_LIMIT_MEMORY configuration parameter to limit how much memory a session can allocate, and can prevent individual sessions from monopolizing system resources. This limit does not apply to a user who holds administrative privileges, such as user informix or a DBSA user.

For example, to limit each session to 10 MB of memory, set SESSION_LIMIT_MEMORY 102400 in the ONCONFIG file.