The onstat -g ssc Option

Monitors the number of times that the database server reads the SQL statement in the cache.

Example Output

Figure 94. onstat -g ssc Output

IBM Informix Dynamic Server Version 10.00.UC1 -- On-Line -- Up 00:08:26 -- 29696 Kbytes

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

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
-F indicates the statement is fully cached
-D indicates the statement is dropped
heap_ptr: Address of memory heap for cache entry