Session Memory

The database server uses the virtual portion of shared memory mainly for user sessions. The majority of the memory that each user session allocates is for SQL statements. The amount of used memory can vary from one statement to another.

Use the following utility options to determine which session and prepared SQL statements have high memory utilization:

• 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. Figure 16 shows sample output for onstat -g mem. This sample output shows the memory utilization of three user sessions with the values 14, 16, 17 in the names output column.

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 allocated by each prepared statement, use the onstat -g stm option. Figure 16 shows sample output for onstat -g stm.

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 16 shows the amount of memory 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 onstat -g mem and onstat -g stm.