onstat -g ssc command: Print SQL statement occurrences

Use the onstat -g ssc command to monitor the number of times that the database server reads the SQL statement in the cache.

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

Figure 1. Syntax:

The all option reports the key-only cache entries as well as the fully cached statements. If the value in the hits column is less than the STMT_CACHE_HITS value, that entry is a key-only cache entry. For more information, see memory utilization in the Informix® Performance Guide.

The pool option reports usage of all memory pools for the SQL statement cache. The output displays information on the name, class, address, and total size of the memory pools. For more information, see improving query performance in the Informix® Performance Guide.

Example output

Figure 2. onstat -g ssc command output


Statement Cache Summary:
#lrus   currsize  maxsize   Poolsize  #hits   nolimit
4       117640    524288    139264    0       1       
Statement Cache Entries: 
lru hash ref_cnt hits flag heap_ptr     database           user
----------------------------------------------------------------
  0  262       0    7   -F  aad8038      sscsi007           admin
   INSERT INTO ssc1 ( t1_char , t1_short , t1_key , t1_float , t1_smallfloat
    , t1_decimal , t1_serial ) VALUES ( ? , ? , ? , ? , ? , ? , ? )
  0  127       0    9   -F  b321438      sscsi007           admin
   INSERT INTO ssc2 ( t2_char , t2_key , t2_short ) VALUES ( ? , ? , ? )
  1  134       0   15   -F  aae0c38      sscsi007           admin
   SELECT t1_char , t1_short , t1_key , t1_float , t1_smallfloat ,
    t1_decimal , t1_serial FROM ssc1 WHERE t1_key = ?
  1  143       0    3   -F  b322c38      sscsi007           admin
   INSERT INTO ssc1 ( t1_char , t1_key , t1_short ) SELECT t2_char , t2_key
    + ? , t2_short FROM ssc2
  2   93       0    7   -F  aae9838      sscsi007           admin
   DELETE FROM ssc1 WHERE t1_key = ?
  2  276       0    7   -F  aaefc38      sscsi007           admin
   SELECT count ( * ) FROM ssc1
  2  240       1    7   -F  b332838      sscsi007           admin
   SELECT COUNT ( * ) FROM ssc1 WHERE t1_char = ? AND t1_key = ? AND
    t1_short = ?
  3   31       0    7   -F  aaec038      sscsi007           admin
   SELECT count ( * ) FROM ssc1 WHERE t1_key = ?
  3   45       0    1   -F  b31e438      sscsi007           admin
   DELETE FROM ssc1
  3  116       0    0   -F  b362038      sscsi007           admin
   SELECT COUNT ( * ) FROM ssc1
    Total number of entries: 10.

Output description - Statement Cache Summary section

#lrus: Number of least recently used queues (LRUS)
currsize: Current® cache size
maxsize: Limit on total cache memory
Poolsize: Total pool size
#hits: The number of hits before insertion. This number equals the value of the STMT_CACHE_HITS configuration parameter
nolimit: The value of the STMT_CACHE_NOLIMIT configuration parameter

Output description - Statement Cache Entries section

The Statement Cache Entries section shows the entries that are fully inserted into the cache.

lru: The index of lru queue to which the cache entry belongs
hash: Hash values of cached entry
ref_count: Number of threads referencing the statement
hits: Number of times a statement matches a statement in the cache. The match can be for a key-only or fully cached entry.
flag: Cache entry flag -D indicates that the statement is dropped, -F indicates that the statement is fully cached, and -I indicates that the statement is in the process of being moved to a fully cached state
heap_ptr: Address of memory heap for cache entry