onstat -p Profile statistics

The onstat -p command provides statistical information that has accumulated since either boot time or the last onstat -z command.

Output Description

dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
5767486485 13351774132 794077832853 99.27   2966809974 31758349077 12100380523 76.06

isamtot    open       start      read       write      rewrite    delete     commit     rollbk
354237566035 7013984563 13082182077 294705874019 2960909735 1505722497 695139443  587215777  709276

gp_read    gp_write   gp_rewrt   gp_del     gp_alloc   gp_free    gp_curs
73058341   13212332   169280097  14446106   0          0          24

ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes
0          0            0          2252336.00 153187.69 160776     265772

bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans
287904902  154998     1433942663655 24514      0          141104     78935790   46200522

ixda-RA    idx-RA     da-RA      RA-pgsused lchwaits
17763868   253562     5421349289 5439235742 3567810106

Heading Description Format See Also
dskreads The number of actual physical block reads from disk in terms of I/O. Dec onstat -g iof
pagreads The number of pages read as a result of the disk reads. Dec  
bufreads The number of times a read occurred against the buffer pool in shared memory. Dec  
%cached The percentage of reads from shared memory relative to disk reads. The formula is: 100 * (bufreads- dskreads) / bufreads Flt  
dskwrits The number of actual physical block writes to disk in terms of I/O. This number includes Physical and Logical Log I/O as well. Dec  
pagwrits The number of pages actually written to disk. Dec  
bufwrits The number of writes made against a page in the buffer pool. Dec  
%cached The percentage of writes to shared memory relative to disk writes. The formula is: 100 * (bufwrits- dskwrits) / bufwrits Flt  
isamtot The total number of ISAM calls that have been made. Dec  
open The number of open table calls. Dec  
start The number of index read calls. Dec  
read The number of data read calls (usually SELECT). Dec  
write The number of write calls (usually INSERT). Dec  
rewrite The number of update calls (UPDATE). Dec  
delete The number of delete calls (DELETE). Dec  
commit The number of times OnLine performed a commit. Dec  
rollbk The number of times a transaction was rolled back. Dec  
gp_read The number of generic page reads that have occurred. Dec onstat -g pgp
gp_write The number of generic page writes that have occurred. Dec  
gp_rewrt The number of generic page updates that have occurred. Dec  
gp_del The number of generic page delete that have occurred. Dec  
gp_alloc The number of generic page allocations that have occurred. Dec  
gp_free The number of generic pages freed and returned to partitions. Dec  
gp_curs The number of cursors used against generic pages. Dec  
ovlock Increments when the value of LOCKS is exceeded. Dec  
ovuserthread Increments when the maximum number of userthreads is exceeded relative to the setting of NETTYPE. Dec  
ovbuff The number of times a request was made for a buffer in the buffer pool but none was available. Dec  
usercpu The total user cpu time used by all userthreads. Flt onstat -g glo
syscpu The total system cpu time used by all userthreads. Flt onstat -g glo
numckpts The number of checkpoints that have occurred since OnLine was booted. Dec onstat -m
flushes The number of times the buffer pool has been flushed to disk. Dec  
bufwaits Increments when a userthread must wait to acquire a buffer. Dec  
lokwaits Increments when a userthread must wait to acquire a lock. Dec  
lockreqs The total number of lock requests that have occurred. Dec  
deadlks The number of potential deadlocks situations that have been encountered. Dec  
dltouts Increments when a distributed deadlock condition is encountered. Dec  
ckpwaits The number of times a thread has had to wait for a checkpoint to complete before continuing. Dec  
compress Increments when a data page in memory is compressed. Dec  
seqscans The number of sequential scans that have occurred. Dec  
ixda-RA The number of read-aheads that involved index leaves to data pages. Dec  
idx-RA The number of read-aheads that involved index only traversal. Dec  
da-RA The number of read-aheads that involved data page only traversal. Dec  
RA-pgsused The actual number of read ahead pages used of those read. Dec  
lchwaits Increments when a userthread must wait to acquire a shared memory resource. Dec  


    The values for ovtbls and ovuserthread are leftovers from previous versions of OnLine. Starting with 7.x, the values for these fields are dynamically allocated and therefore the overflows should always be zero.

    The number of flushes is not driven entirely by the number of checkpoints.

    The value for commits and rollbacks is not necessarily reflective of actual transaction requests by sessions. Internally, Informix performs its own commits and rollbacks as well.

    Actual deadlocks (two or more threads waiting on one anotherís locks) canít actually happen within a single Informix Dynamic Server instance. The thread that would create the deadlock fails and receives an error.

    Page compression occurs when rows have been deleted from a page. The compression is actually the repositioning of the remaining rows on the page such that they are contiguous.

Monitoring and Tuning

    Cached reads and cached writes will usually be above 90%. If they are under 85% in an OLTP system, this may be an indication of a shortage of buffers. Although the %cached writes should be above 85%, the actual value can vary greatly. Low %cached writes may simply indicate that not many actual writes have occurred. This can be determined by examining the number of writes, rewrites, and deletes relative to the number of reads.

    Bufwaits shows the number of times a thread had to wait to acquire a latch on a buffer. If the readahead values (RA_PAGES and RA_THRESHOLD) are too high, bufwaits may grow excessively. High bufwaits may also be caused by too small a buffer pool relative to the amount of work being performed (see %cached values).

    Lock waits should normally be less than 1% of lock requests unless you have applications that hold locks for an inordinant amount of time. If locks waits are excessive, examine the isolation levels used by SQL statements plus the overall design of transaction management.

    Checkpoint waits can and do occur in a high volume system. Checkpoints are prevented from occurring if a userthread is in the middle of a critical section (known as a critical write). If a checkpoint must wait, it is usually only for a brief moment.

    The sequential scans field should monitored in an OLTP system since most OLTP system operate most efficiently using index reads.

    Read aheads allow the system to perform more efficient I/O when a need arises to scan multiple sequential pages from disk. This capability is highly desired in DSS systems where large table and index scans are normal and in certain types of OLTP processing. In an ideal situation, ixda-RA + idx-RA + da-RA should equal RA-pgsused. However, if the values of RA_PAGES and RA_THRESHOLD are too high, the system may end up reading in more pages for a read ahead than it should. This will result in high bufwaits since the pages being read in will force existing buffer pool pages to be overwritten or, in the case of dirty pages, flushed out to disk.