Display information about the memory that SQL statements use in a session
Use the onstat -g stm session-id to display information about the memory each SQL statement uses in a session.
The following figure displays the output of onstat -g stm session-id for the same session (14) as in onstat -g ses session-id in Figure 1 and onstat -g sql session-id in Figure 1.
When the SQL statement
cache (SSC) is on, the database server creates the heaps in the SSC
pool. Therefore, the heapsz output field in Figure 1 shows that this SQL statement uses
10056 bytes, which is contained within the 11600 bytes in the SSC
pool that the onstat -g sql 14 shows.
Figure 1. onstat -g stm session-id
output
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