onstat -p command: Print profile counts

Use the onstat -p command to display information about profile counts either since you started the database server or since you ran the onstat -z command.


Syntax:

>>-onstat-- -p-------------------------------------------------><

Example output

Figure 1. onstat -p command output

Profile
dskreads  pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
16934     47321      203600361  99.99   103113     158697     950932     89.16  

isamtot   open     start    read     write   rewrite  delete  commit  rollbk
139214865 9195777  12257208 94191268 362691  55696    38134   128294  24

gp_read   gp_write   gp_rewrt   gp_del     gp_alloc   gp_free   gp_curs
39        2          27         51         0          0         16     

ovlock    ovuserthread   ovbuff   usercpu  syscpu   numckpts   flushes   
0         0              0        1551.59  144.82   1822       1822      

bufwaits  lokwaits  lockreqs   deadlks  dltouts  ckpwaits  compress  seqscans
176       1         195872383  0        0        1         39331     1259170

ixda-RA   idx-RA     da-RA    logrec-RA  RA-pgsused lchwaits  
0         7594       2124     0          2002       18848

Output description

The first portion of the output describes reads and writes.

Reads and writes are tabulated in three categories: from disk, from buffers, and number of pages (read or written).

The first %cached field is a measure of the number of reads from buffers compared to reads from disk. The second %cached field is a measure of the number of writes to buffers compared to writes to disk.

The database server buffers the information and writes the information to the disk in pages. For this reason, the number of disk writes displayed as dskwrits is usually less than the number of writes that an individual user runs:

dskreads: The number of actual reads from disk
pagreads: The number of pages read
bufreads: Is the number of reads from shared memory
%cached: The percent of reads cached in the buffer pool.
If bufreads exceeds the maximum integer (or long) value, its internal representation becomes a negative number, but the value appears as 0.0.
dskwrits: The actual number of physical writes to disk
This number includes the writes for the physical and logical logs reported in onstat -l .
pagwrits: The number of pages written
bufwrits: The number of writes to shared memory
%cached: The percent of writes cached in the buffer pool.

The next portion of the -p display tabulates the number of times different ISAM calls were executed. The calls occur at the lowest level of operation and do not necessarily correspond one-to-one with SQL statement execution. A single query might generate multiple ISAM calls. These statistics are gathered across the database server and cannot be used to monitor activity on a single database unless only one database is active or only one database exists:

isamtot: The total number of calls
open: Increments when a tblspace is opened
start: Increments the pointer within an index
read: Increments when the read function is called
write: Increments with each write call
rewrite: Increments when an update occurs
delete: Increments when a row is deleted
commit: Increments each time that an iscommit() call is made
No one-to-one correspondence exists between this value and the number of explicit COMMIT WORK statements that are executed.
rollbk: Increments when a transaction is rolled back

The next portion of the onstat -p command output displays information about generic pages. The Generic Page Manager provides an API for Informix® to manage nonstandard pages in the database server buffer pool. The following table describes the Generic Page Manager fields in the onstat -p command output.

gp_read: The number of generic page reads
gp_write: The number of generic page writes
gp_rewrt: The number of generic page updates
gp_del: The number of generic page deletes
gp_alloc: The number of generic page allocations
gp_free: The number of generic pages freed and returned to tblspaces
gp_curs: The number of cursors used against generic pages

The next portion of the onstat -p command output displays the number of times that a resource was requested when none was available:

ovlock: Number of times that sessions attempted to exceed the maximum number of locks
For more information, see “LOCKS” on page 1-56.
ovuserthread: The number of times that a user attempted to exceed the maximum number of user threads
ovbuff: The number of times that the database server did not find a free shared-memory buffer
When no buffers are free, the database server writes a dirty buffer to disk and then tries to find a free buffer.
usercpu: Is the total user CPU time that all user threads use, expressed in seconds
This entry is updated every 15 seconds.
syscpu: The total system CPU time that all user threads use, expressed in seconds
This entry is updated every 15 seconds.
numckpts: The number of checkpoints since the boot time
flushes: The number of times that the buffer pool wasflushed to the disk

The next portion of the onstat -p command output contains miscellaneous information, as follows:

bufwaits: Increments each time that a user thread must wait for a buffer
lokwaits: Increments each time that a user thread must wait for a lock
lockreqs: Increments each time that a lock is requested
deadlks: Increments each time that a potential deadlock is detected and prevented
dltouts: Increments each time that the distributed deadlock time-out value is exceeded while a user thread is waiting for a lock
ckpwaits: Is the number of checkpoint waits
compress: Increments each time that a data page is compressed
seqscans: Increments for each sequential scan

The last portion of the onstat -p command output contains the following information:

ixda-RA: The count of read-aheads that go from index leaves to data pages
idx-RA: The count of read-aheads that traverse index leaves
da-RA: The count of data-path-only scans
logrec-RA: The log records that the database server read ahead
RA-pgsused: The number of pages used that the database server read ahead
lchwaits: Stores the number of times that a thread was required to wait for a shared-memory latch
Many latch waits typically results from a high volume of processing activity in which the database server is logging most of the transactions.