onstat -g stm command: Print SQL statement memory usage

Use the onstat -g stm command to display the memory that each prepared SQL statement uses.

By default, only the DBSA can view onstat -g stm syssqltrace information. However, when the UNSECURE_ONSTAT configuration parameter is set to 1, all users can view this information.

Read syntax diagramSkip visual syntax diagram
Syntax:

>>-onstat-- -g--stm--------------------------------------------><

To display the memory for only one session, specify the session ID in the onstat -g stm command.

Example output

Figure 1. onstat -g stm command output
session   65 ----------------------------------------------------------
 sdblock  heapsz  statement ('*' = Open cursor)
 aad8028   16544   SELECT COUNT ( * ) FROM ssc1 WHERE t1_char = ? 
 AND t1_key = ? AND t1_short = ?

Output description

sdblock
Address of the statement descriptor block
heapsz
Size of the statement memory heap
statement
Query text

Copyright© 2019 HCL Technologies Limited