Oninit Logo
The Down System Specialists
+1-913-674-0360
+44-2081-337529
Introduction Health Check Standby Admin Remote Admin Remote Monitoring Backup and Restore Custom Support Down Systems
Unix SQL Advanced SQL 4GL Applications Advanced 4GL IDS Admin Migrating to V14 IDS Performance Managing & Optimising SPL and Triggers IDS V14 Upgrading Very Old Versions Custom Training
Onstat Guide Error Code Guide Reference Datablades The Dumb DBA Sysmaster Fault Finder
Co-Location Web Hosting Managed Servers Web Development
BenchMark Tookkit RegExp Datablade
Partnerships Contact
Onstat
-g act -g afr -g all -g amx -g aqt -g arc -g ath -g btc -g buf -g cac -g cdr -g cluster -g cdr config -g cfg -g cmsm -g con -g cpu -g dbc -g ddr -g dic -g dis -g dll -g dmp -g dri -g dsc -g dsk -g dss
-g env -g ffr -g glo -g grp -g imc -g ioa -g iob -g iof -g iog -g ioq -g iov -g ipl -g lap -g laq -g lmm -g lmx -g lsc -g mem -g mgm -g nbm -g nif -g nsc -g nsd -g nss -g nta -g ntd -g ntm -g ntt -g ntu
-g opn -g osi -g pgp -g pos -g ppd -g ppf -g pqs -g prc -g proxy -g qst -g rah -g ras -g rbm -g rcv -g rep -g rea -g rss -g sch -g scn -g sds -g seg -g ses -g sle -g smb -g smx -g spi -g sql -g src -g ssc -g stk -g stm -g stq -g sts -g sym -g sync
-g rwm -g tgp -g tpf -g ufr -g wai -g vpcache -g wmx -g wst
-b/-B -F -g cac -g dic -g dsc -g prc -g ssc -g vpcache -h -p -X
-t/-T -g buf -g lap -g opn -g ppf -g scn
-g cat -g cdr -g cdrconfig -g ddr -g dss -g dtc -g grp -g nif -g que -g rcv -g rep -g rqm -g sync
-g cluster -g cmsm -g dri -g ipl -g laq -g proxy -g rss -g sds -g smx
-R -g afr -g ffr -g lmm -g lsc -g mem -g mgm -g nbm -g rbm -g seg -g ses -g stm -g stq -g ufr
-g imc -g nsc -g nsd -g nss -g nta -g ntd -g ntm -g ntt -g ntu
-G -u -x -g env -g his -g pqs -g ses -g sql
-g act -g ath -g cpu -g rea -g sle -g stk -g sts -g tpf -g wai -g wst
-a -b/-B -C -c -d/-D -F -f -G -h -i -j -k/-K -L -l -m -o -P -p -R -r -s -t/-T -u -X -x -z

onstat -g ssc Print SQL statement occurrences

The onstat -g ssc command displays the number of times that the database server reads the SQL statement in the cache.
Unless UNSECURE_ONSTAT=1, only the DBSA can view onstat -g ssc syssqltrace information.
The command takes two options

  • 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 IBM 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 

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      dbaudit            waudit
   INSERT INTO t9000audit ( c9000_char , c9000_short , c9000_key , c9000_float , c9000_smallfloat
    , c9000_decimal , c9000_serial ) VALUES ( ? , ? , ? , ? , ? , ? , ? )
  0  127       0    9   -F  b321438      dbaudit            waudit
   INSERT INTO t9010database ( c9010_char , c9010_key , c9010_short ) VALUES ( ? , ? , ? )
  1  134       0   15   -F  aae0c38      dbaudit            waudit
   SELECT c9000_char , c9000_short , c9000_key , c9000_float , c9000_smallfloat ,
    c9000_decimal , c9000_serial FROM t9000audit WHERE c9000_key = ?
  1  143       0    3   -F  b322c38      dbaudit            waudit
   INSERT INTO t9000audit ( c9000_char , c9000_key , c9000_short ) SELECT c9010_char , c9010_key
    + ? , c9010_short FROM t9010database
  2   93       0    7   -F  aae9838      dbaudit            waudit
   DELETE FROM t9000audit WHERE c9000_key = ?
  2  276       0    7   -F  aaefc38      dbaudit            waudit
   SELECT count ( * ) FROM t9000audit
  2  240       1    7   -F  b332838      dbaudit            waudit
   SELECT COUNT ( * ) FROM t9000audit WHERE c9000_char = ? AND c9000_key = ? AND
    c9000_short = ?
  3   31       0    7   -F  aaec038      dbaudit            admin
   SELECT count ( * ) FROM t9000audit WHERE c9000_key = ?
  3   45       0    1   -F  b31e438      dbaudit            admin
   DELETE FROM t9000audit
  3  116       0    0   -F  b362038      dbaudit            waudit
   SELECT COUNT ( * ) FROM t9000audit
    Total number of entries: 10.

Statement Cache Summary section

FieldDescription
#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

Statement Cache Entries section

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

FieldDescription
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

To discuss how Oninit ® can assist please call on +1-913-674-0360 or alternatively just send an email specifying your requirements.