Michael Mueller, IT Consulting

Dipl. Math. Michael Mueller

SqlStat, Informix SQL Performance Statistics

SqlStat is a Korn shell script and allows you to analyze the performance of Informix database servers. It provides an initial overview of the load distribution on large database servers, broken down into individual SQL Statements.

It is published under GNU General Public License. This shell script comes with absolutely no warranty.




SqlStat [ nsamples [ sleep-secs-per-sample [ nrepetitions ] ] ]

or: SqlStat -h
nsamples: number of samples
sleep-secs-per-sample: number of seconds between two samples
nrepetitions: number of repetitions creating a new output directory (default 1)

The total runtime is (nsamples * sleep-secs-per-sample * nrepetitions) seconds. The script generates a directory with the name erg.<hh:mm:ss>. (If nrepetitions is specified on the command line, one directory for each repetition is created.) The directory contains the SQL statements included in the samples collected. These statements are weighted according to the load that they generate on the server. A file is generated for each statement. The name of this file includes the number of samples for this statement: <samples>.<something>. For example:

$ SqlStat 120 1

$ cd erg.15:25:37

$ ls -l
total 20
-rw-r--r--    1 michaelm users        1231 Jul 21 21:27 00013.35489_1
-rw-r--r--    1 michaelm users        1750 Jul 21 21:27 00018.16460_1
-rw-r--r--    1 michaelm users        7726 Jul 21 21:27 00083.16476_1
-rw-r--r--    1 michaelm users          82 Jul 21 21:27 INFO

$ cat INFO
Wed Jul 21 15:25:37 CEST 2004
Wed Jul 21 15:27:51 CEST 2004
114 non empty samples

$ head -7 00083.16476_1
Current SQL statement :
   update t3 set c = 1

@ 21.07.21:25, ses 28, user michaelm, pid 5150, host m1, fe testpg1
  ready 1cpu sqlexec   
@ 21.07.21:25, ses 29, user michaelm, pid 5147, host m1, fe testpg1
  ready 1cpu sqlexec   

$ head -7 00013.35489_1
Current SQL statement :
   select tabid from syscolumns

@ 21.07.21:25, ses 21, user michaelm, pid 5121, host m1, fe testpg2
  running 3cpu sqlexec   
@ 21.07.21:26, ses 21, user michaelm, pid 5121, host m1, fe testpg2
  running 3cpu sqlexec   

This means, for example, that the statement "update t3 set c = 1" was found 83 times in a total of 114 samples and generated 83/114 = 72.8% of the load. In addition, other information such as thread state, thread name, session id and pid is also logged.