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.

Read syntax diagramSkip visual syntax diagram
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.


Copyright© 2019 HCL Technologies Limited