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

Profile
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

Notes

The Generic Page Manager provides an API for Dynamic Server to manage nonstandard pages in the database server buffer pool

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) cannot 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.