By Troy L. Hewitt (troy@informix.com).

(Editors Note: This article is based on extracts from a book Troy was writing on the onstat command. I wish we could include the whole article in our newsletter but we don't have the space. I made the selection of which commands to include. The whole article will be posted on our web site when I get a chance to convert it to html)

The following reference guide is an alphabetical list of the onstat commands with the first portion consisting of the standard onstat commands (non-mt), and the second portion containing the MT (Multi-Threaded) commands.

Each command, along with its definition, is listed at the top of the page, followed by a Synopsis of the command’s purpose. This is joined by an example that illustrates the command’s output and one or more Column Definition tables describing each of the sections (if there is more than one) in the example. The table has four columns: a Column Heading, Column Description, Format and See Also as shown below.
 

Column Heading	Column Description	Format	See Also
address	In-memory address of the buffer header.	Hex	onstat –g dmp

Those columns having siginificance or requiring special attention are italicized in the table. The format column identifies the columns output as being in Hexadecimal (Hex), Decimal (Dec), Float (Flt), Pages, Bytes, or String (Str) format. The See Also column provides references to other areas in the book that relate to the current column heading.

The NOTES section contains information that might be of interest regarding the command and the nature of its output.

The TUNING & MONITORING section gives hints and ideas on how the command can be used to tune the system. Periodically an additional section, CASE STUDY, is present as well.

Please direct questions, suggestions and corrections to Troy L. Hewitt (troy@informix.com).

onstat -g act Active Threads

SYNOPSIS

The onstat -g act command displays a list of the currently active (running) threads. The output is sorted by thread ID.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
tid	The thread id incrementally assigned by OnLine.	Dec	onstat -g ath
tcb	The in-memory address of the thread control block.	Hex
rstcb	The in-memory address of the RSAM thread control block.	Hex
prty	The internal priority of the thread.	Dec
status	The status of the thread.	Str
vp-class	The class of VP on which the thread is running.	Str	onstat -g sch
name	The name of the thread.	Str

NOTES

• There are never more threads running than there are virtual processors (VP).
• A thread priority can range from 1 (lowest) to 4 (highest) with a default priority of 2. Priorities are assigned internally by the server and are not tunable.

The onstat -g act command is a quick place to identify if certain threads are actually running such as the KAIO thread, the poll and listener threads, and other threads as set in the configuration file.

onstat -g ath All Threads

SYNOPSIS

The onstat -g ath command displays a list of all threads in the system. The output is sorted by thread ID.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
tid	The thread id incrementally assigned by OnLine.	Dec	onstat –g tpf
tcb	The in-memory address of the thread control block.	Hex
rstcb	The in-memory address of the RSAM thread control block.	Hex
prty	The internal priority of the thread.	Dec
status	The current status of the thread.	Str	onstat -g con
vp-class	The VP class and number on which the thread is running. The VP classes include the following: CPU CPU VP AIO Asynchronous I/O LIO Logical Log I/O PIO Physical Log I/O MSC Miscellaneous ADM Administrative ADT Auditing OPT Optical SOC Socket TLI TLI SHM Shared Memory USR User Define Routine VP	Str	onstat -g glo
name	The name of the thread.	Str

NOTES

• A thread priority can range from 1 (lowest) to 4 (highest) with a default of 2.
• If more than one instance of a VP exists within a class, its name will be prefaced with its instance number.

The onstat -g ath command provides a quick check for what threads are currently active in the system. A glance through the output will quickly reveal whether KAIO threads are running, if there are any sessions running via sqlexec threads, or if there are a large number of scan and exchange threads running to handle parallel operations.

onstat -g con Conditions with Waiters

SYNOPSIS

The onstat -g con command displays all threads currently waiting on a condition. The output is sorted by condition ID.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
cid	Condition identifier.	Dec
addr	In-memory address of the condition control block.	Hex
name	The name of the condition upon which this thread is waiting.	Str
waiter	The thread ID of the thread waiting on the condition.	Dec	onstat -g ath
waittime	The amount of wait time the thread has waited on this condition in seconds.	Dec

NOTES

• Names for conditions are assigned individually when the condition is set.

The following table lists the values that can appear in the condition name field and a short description of their meaning.
 

Condition	Description
adt_cond	Thread is waiting on for MLS auditing to complete.
arrived	Waiting for mail (network request) to arrive
await_MC%d	Producer thread(s) are waiting to coordinate their status with their Master Consumer thread.
backend:%d	A single sort thread is waiting for backend to tell this thread of an event.
bar_cond	"Barrier Condition". Used to coordinate processing between producer and consumer threads.
block	The thread is blocked due to one of the following reasons: BLOCK_ARCHIVE BLOCK_DBS_DROP BLOCK_DDR BLOCK_DR_SWITCH BLOCK_HANG_SYSTEM BLOCK_LBU_PRESERVE BLOCK_LONGTX BLOCK_MEDIA_FAILURE
bufcond	Sort thread is waiting for output buffers to become available.
closed_down	Master consumer thread is waiting for a signal that it is OK to close.
closing	A network communications thread (tli, soc, str) is waiting to close.
cp	Thread is waiting on a checkpoint to complete.
drbt_rcvq	Thread on Data Replication secondary is waiting for buffer to be dequeued from the receive queue.
drbt_sndq	Thread on Data Replication primary is waiting for buffer to be dequeued from the send queue.
drcb_bqe	Thread waiting for buffer to show up on empty Data Replication buffer queue.
drcb_bqf	Thread waiting for buffer to show up on full Data Replication buffer queue.
free_cond	Producer thread is waiting for a free packet buffer.
gfile	A thread is waiting for an I/O operation (open, close, read, write) to finish.
incore	Sort thread(s) are waiting to determine if sort will be performed in memory.
loa_done	Waiting for an onload job to be completed
loa_request	Waiting for an onload request to be completed
log	Thread is waiting while operation is being performed against Logical Log buffers (e.g., switching to another buffer).
logbf%d	Thread is waiting for a Logical Log buffer (of three) to become available.
netexp
netnorm	A thread is waiting for a request to be placed in the network queue.
notify_MC%d	Provides coordination for synchronously opening, closing and freeing exchange threads.
nsf.cleanup
ok_to_close	Producer thread is waiting for a signal that it is OK to close.
ok_to_open	Producer thread is waiting for a signal that it is OK to open.
opened_up	Master consumer thread is waiting for a signal that it is OK to open.
opresv	Thread is waiting on optical subsystem to reserve an optical drive for a specified volume.
packet_cond	Exchange thread is waiting for additional data packets to arrive.
pddr_cbwait	Waiting for deallocation of Parallel Distributed Data Replication (PDDR) control block.
plogothr	Thread waiting on other physical log buffer (of two) to become available.
plogpl	Thread waiting on current physical log buffer (of two) to become available.
pt_%x
QbuffE	Thread waiting for buffer to show up on empty Streams buffer queue.
QbuffF	Thread waiting for buffer to show up on full Streams buffer queue.
sm_poll_cond
sm_read	A thread is waiting for a request to be placed in the shared memory queue.
sortproc:%d
unl_done	Waiting for an onunload job to be completed.
unl_request	Waiting for an onunload request to be completed.
workrdy	Waiting for pload worker threads to become ready.

To discover the session associated with the thread ID that is waiting on the condition, execute the following SQL statement against the sysmaster database, replacing the question mark (?) with the thread ID obtained from the onstat –g con output.

onstat -g glo MT Global Information

SYNOPSIS

The onstat -g glo command provides information on the status of the virtual processors in the system.

COLUMN DEFINITIONS

NOTES

• A thread performs a yield 0 when it wishes to be told when a request has been completed.
• A thread performs a yield n when it wishes to wait for a specified period before being moved from the sleep queue to the ready queue. The Btree cleaner thread is an example of a thread that yields for a specific number of seconds.

SYNOPSIS

The onstat -g iof command displays the statistics for disk I/O by chunk/file.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
gfd	The global file descriptor number for this chunk.	Dec
pathname	The pathname to the chunk.	Str
totalops	The total number of read & write operations that have occurred against the chunk.	Dec	onstat -D
dskread	The number of disk reads that have occurred against the chunk.	Dec
dskwrite	The number of disk writes that have occurred against the chunk.	Dec
io/s	The number of I/Os per second.	Flt

NOTES

• The global file descriptor (gfd) values begin at 3. 0, 1, and 2 are used internally by Informix Dynamic Server for stdin, stdout and stderr, respectively.

Examination of the values in each of the operations columns (totalops, dskread, dskwrite) can identify heavy I/Os against a particular device or chunk. This information can be used to determine where hotspots exist across the system.

onstat -g ioq Disk IO Statistics by Queue

SYNOPSIS

The onstat -g ioq command provides statistics on the disk I/O by queue.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
q name	The AIO queue name.	Str
q id	The global file descriptor associated with this queue (files only).	Dec
len	The current number of active requests in this queue.	Dec
maxlen	The maximum number of requests this queue has held.	Dec
totalops	The total number of operations performed for this queue.	Dec
dskread	The total number of read operations performed for this queue.	Dec
dskwrite	The total number of write operations performed for this queue.	Dec
dskcopy	The total number of copy operations performed for mirroring against this queue.	Dec

NOTES

• One gfd queue name exists for each chunk in the system.

The information from the onstat -g ioq command can assist in identifying bottlenecks with the processing of I/O through the queues. If the maxlen column for the AIO queue tends to exceed 25, additional AIO VPs should be started.

The onstat -g ioq output for the global file descriptors (gfd) or chunks may also identify a potential I/O problem with specific disks in the system if certain gfd queues begin to back up. It may also point out a chunk that contains a table that needs better partitioning (fragmentation).

onstat -g lap Light appends

SYNOPSIS

The onstat -g lap command provides information on the status of light appends occurring in the system.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
Session id	The session ID performing the light append operation	Dec	onstat –g ses
address	The address of the light append buffer	Hex
cur_ppage	Current physical page address	Hex
la_npused	The number of pages allocated	Dec
la_ndata	The number of data pages appended	Dec
la_nrows	The number of rows appended.	Dec
bufcnt	The number of light append buffers	Dec

onstat -g lmx Locked Mutexes

SYNOPSIS

The onstat -g lmx command displays currently locked mutexes.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
mid	The internal mutex identifier	Dec
addr	Address of the locked mutex	Hex
name	Name of the mutex	Str
holder	The session ID of the thread holding the mutex.	Dec
lkcnt	The number of waiters for this same mutex	Dec
waiter	A list of the addresses waiting for this mutex.	Dec
waittime	The amount of time in seconds this thread has been waiting.	Dec

onstat -g lsc Light scans

SYNOPSIS

The onstat -g lsc command displays the status of any currently active light scans.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
descriptor	Slot entry in the light scan table	Dec
address	Address of the light scan buffer	Hex
next_lpage	The next logical page address	Hex
next_ppage	The next physical page address	Hex
ppage_left	Pages left in current extent	Dec
bufcnt	The number of light scan buffers	Dec
look_aside	Whether look aside is needed for this light scan	Str

NOTES

• Look asides occur when a thread needs to examine the buffer pool for existing pages to obtain the latest image of a page.

onstat -g nss Net Shared Memory Status

SYNOPSIS

The onstat -g nss command displays the status of shared memory network connections. The command takes an optional session ID as a parameter. If no session ID is provided, a one-line summary for each shared memory connection is listed.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
clientid	A server assigned value for lookups.	Dec
clientPID	The client process ID.	Dec
state	The current state of the connection. It takes its from the following list: Connected Con1 Waiting Reject Badvers Closed Not connected Unknown	Str
#serverbufs	The number of database server buffers currently allocated.	Dec
#clientbufs	The number of client buffers currently allocated.	Dec
#rdwrts	The total number of buffers in use.	Dec

NOTES

• The clientid is a key that the server uses to look up information in the network shared memory status table.

SYNOPSIS

The onstat -g ppf command displays the profile for all active partitions (tables/indexes). The output is sorted by partition number.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
partnum	The partnum in decimal format of the partition.	Dec	onstat -P
lkrqs	The number of lock requests.	Dec
lkwts	The number of lock waits.	Dec
dlks	The number of deadlocks.	Dec
touts	The number of deadlock timeouts.	Dec
isrd	The number of reads against this partition.	Dec
iswrt	The number of writes against this partition.	Dec
isrwt	The number of rewrites (updates) against this partition.	Dec
isdel	The number of deletes against this partition.	Dec
bfrd	The number of buffer reads for this partition.	Dec
bfwrt	The number of buffer writes for this partition.	Dec
seqsc	The number of sequential scans against this partition.	Dec

NOTES

• The onstat –g ppf command provides a summary line …

The onstat –g ppf output is invaluable for identifying heavily hit partitions.

CASE STUDY

A simple method for capturing the partition number and the number of sequential scans when they exceed (in this case) 200, you can execute the following command:

onstat –g ppf | awk ‘{ if ($NR > 200) print $1, $NR}’ - | sort –n –r –k +2.0

onstat -g prc Stored procedure cache

SYNOPSIS

The onstat -g prc command displays the stored procedures currently being maintained within the system cache.

COLUMN DEFINITIONS

 
Global Statistics
 

Column Heading	Column Description	Format	See Also
Number of lists	The number of hash chains built for this cache.	Dec
PC_POOLSIZE	The maximum number of entries that can be maintained within the system procedure cache at one time.	Dec
Number of entries	The number of entries currently in the cache.	Dec
Number of inuse entries	The number of entries currently being used by a thread.	Dec

Stored Procedure Cache Entries


 

Column Heading	Column Description	Format	See Also
list#	The stored procedure cache hash chain ID	Dec
id	The number of entries in this hash	Dec
ref_cnt	The number of SQL statements currently referencing one of the entries in this cache.	Dec
dropped?	Whether this entry has been dropped relative since being loaded to the cache	Str
heap_ptr	The in-memory address for the heap used to store this entry.	Hex
procedure_name	The name of the procedure.	Str


 

SYNOPSIS

The onstat -g rea command displays the threads that are on the ready queue and are awaiting a VP on which to run.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
tid	The thread id incrementally assigned by OnLine.	Dec	onstat -g ath
tcb	The in-memory address of the thread control block.	Hex	onstat -g tpf
rstcb	The in-memory address of the RSAM thread control block.	Hex
prty	The internal priority of the thread.	Dec
status	The status of the thread.	Str
vp-class	The class of VP on which the thread is running.	Str	onstat -g glo
name	The name of the thread.	Str

NOTES

• A thread priority can range from 1 (lowest) to 4 (highest) with a default priority of 2.
• Threads are pulled off the ready queue based on their priority. Within a priority, threads are taken on a first-in-first-out basis (FIFO).
• Ready threads are displayed from highest priority to lowest priority within each class of virtual processor (VP).

A consistent number of entries on the ready queue may be an indication that additional CPU VPs are required. The number of CPU VPs should never exceed the number of physical CPUs. If there is already a CPU VP for every CPU and there are threads backing up on the ready queue, there may not be enough physical processors or fast enough processors on the box based on the workload required.

onstat -g sch VP Scheduler Statistics

SYNOPSIS

The onstat -g sch commands displays statistics for each of the virtual processors (VPs) currently active in the system. The output is sorted by virtual processor number.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
vp	The virtual processor ID.	Dec
pid	The system process ID for the process running this virtual processor.	Dec
class	The class of virtual processor.	Str
semops	The number of times this virtual procesor went idle and performed a semaphore operation to wait for work.	Dec
busy waits	The number of times the virtual processor found no work to do and spun to avoid going to sleep on a semaphore.	Dec
spins/wait	The average number of spins that were performed for each busy wait.	Dec

NOTES

• The number of spins performed during a busy wait is platform dependent.
• Busy waits allow a virtual processor to avoid a context switch while waiting for additional work to show up in the ready queues.
• Spins/wait is calculated as (spins * 10,000) / busy waits.
• Busy waits only occur when the configuration parameter MULTIPROCESSOR is set to a value other than 0.

SYNOPSIS

The onstat -g seg command displays a list of the shared memory segments that have been acquired for this OnLine instance.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
id	The shared memory identifier returned from the call to shmget() for acquiring shared memory segments.	Dec
key	The key passed to shmget(), based on the value of SERVERNUM.	Dec
addr	The in-memory address of the shared memory segment.	Hex
size	The size of the shared memory segment in bytes.	Dec
ovhd	The number of bytes of overhead within this segment required to track information.	Dec
class	The segment type for this block of shared memory. R Resident V Virtual M Message	Str
blkused	The number of 8K blocks in the segment that have been used.	Dec
blkfree	The number of 8K blocks remaining in the segment.	Dec

NOTES

• Some systems will display multiple R classes. This represents one portion for the buffer pool and one portion for the remainder of the resident segment.

If a large number of virtual segments (class ‘V’) are created under normal load, the SHMVIRTSIZE configuration parameter should be tuned to a higher value.

onstat -g ses Session information

SYNOPSIS

The onstat -g ses command displays a list of the sessions currently running in the system. The command takes an optional session ID as a parameter. The output is sorted in reverse order by session ID.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
session id	The session ID.	Dec	onstat -u
user	The username who started this session.	Str
tty	The tty associated with the front-end for this session.	Str
pid	The process ID associated with the front-end for this session.	Dec
hostname	The hostname from which this session has connected.	Str
#RSAM threads	The number of RSAM threads allocated for this session.	Dec
total memory	The amount of memory allocated for this session.	Dec
used memory	The amount of memory actually used by this session.	Dec

COLUMN DEFINITIONS

NOTES

• There will always be an ‘extra’ session with a PID of zero at the top of the list. This is a placeholder for the next request for a new session.

onstat -g sle Sleeping Threads

SYNOPSIS

The onstat -g sle command displays threads that are currently on the sleep queue along with the number of seconds remaining before the thread awakens (is placed on the ready queue by the ADM virtual processor).

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
tid	The thread ID.	Dec
v_proc	The ID of the virtual processor where this thread last ran.	Dec
rstcb	The in-memory address of the thread’s rstcb.	Hex
name	The name of the sleeping thread.	Str
time	The amount of time left in seconds for this thread to sleep before being moved to the ready queue.	Dec

NOTES

• A thread is put to sleep for a specific period of time or forever.

Threads will be placed onto the sleep queue when they need to sleep for a specific period of time as with the BTree cleaner thread which wakes up every 60 seconds to perform its work. sqlexec threads will end up on the sleep queue if they execute a SET LOCK MODE TO WAIT statement either with or without a specified number of seconds and the resource they are after is locked. For example, if an sqlexec thread set its lock mode to wait 30 seconds and it encounters a lock, it will be placed on the sleep queue until either 30 seconds has elapsed or the lock has been released.

Some threads are placed on the sleep queue with the number of seconds set to forever. These threads are usually threads waiting for an event to occur, such as being signalled that data they were waiting for is now ready for processing.
 

onstat -g spi Spin Locks with Long Spins

SYNOPSIS

The onstat -g spi command displays a list of the resources in the system where a wait was required to obtain a latch on a resource. During the wait, the thread will spin (or loop) trying to acquire the resource. The number of times a wait was required for the resource and the number of total loops performed are given.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
Num Waits	The number of times the resource had to be waited on due to another thread holding a latch on that resource.	Dec
Num Loops	The number of times a loop was performed to test for the release of the latch on the resource.	Dec	onstat -g spi
Avg Loop/Wait	The average number of loops that were performed for each wait.	Flt
Name	The name of the resource upon which a spin locking condition has existed.	Str	onstat -g wmx

NOTES

• A long spin is one where the number of loops for a single wait has exceeded 10,000.
• Only resources that have had at least one wait are listed.

Spin locks occur as part of the mechanism for acquiring control of a resource. When a thread needs access to a resource in shared memory, it must first acquire a latch on the resource. If the latch is currently held by another thread, the current thread will spin, waiting for the latch to be released.

When the number of loops is zero, it indicates that the lock was acquired immediately. This can occur when the latch is released between the time when the request is first made by the thread and the request is remade in the spin lock routine.

onstat -g sql SQL statements

SYNOPSIS

The onstat -g sql command displays a list of the currently active user sessions and the SQL statements associated with them. The command takes a session ID as an optional argument.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
Sess ID	The session ID.	Dec	onstat -g ses
SQL Stmt type	The type of SQL statement this session is currently running.	Str
Current Database	The name of the database to which this session is connected.	Str
Iso Lvl	The current isolation level of the session as defined by the following coded values: DR Dirty Read CR Committed Read CS Cursor Stability RR Repeatable Read	Str
Lock Mode	The current setting for the SET LOCK MODE statement for this session.	Str	onstat -u
SQL ERR	The SQL error, if any, for the last statement executed.	Dec	Informix Error Messages
ISAM ERR	The ISAM error, if any, for the last statement executed.	Dec	Informix Error Messages
F.E. Vers	The version of the front end application, if Informix, currently connected.	Str

NOTES

• The statement type will be ‘-‘ if no statement is currently being executed.
• The database name will be ‘-‘ if the session is not currently connected to any database.
• The UPDATE STATISTICS statement will periodically show a negative value in the SQL ERR and ISAM ERR columns. This is a normal function of the statement design.

The onstat -g sql command is a rapid method for determining the type of SQL statements currently running against the system, their associated databases and any potential application SQL errors that might have occurred.

On smaller systems, it is quite common to first analyze the output from the onstat -g sql command to determine the variety of statements being executed. From there, other onstat commands can be run to determine the specifics of a particular session or SQL statement being executed.

The onstat -g sql command can also be run with an optional session ID. The output contains the most recently and the current SQL statements being run by the session.

onstat -g wai Waiting Threads

SYNOPSIS

The onstat -g wai command displays a list of the threads, in the system, that are currently on the wait queue. The output is sorted by thread ID.

COLUMN DEFINITIONS
 

Column Heading	Column Description	Format	See Also
Tid	The thread ID.	Dec	onstat -g ath
Tcb	The in-memory address of the thread control block.	Hex
Rstcb	The in-memory address of the RSAM thread control block.	Hex
Prty	The priority of the thread in the system.	Dec
Status	The current status of the thread (i.e. the reason it is on the wait queue).	Str
vp-class	The virtual processor class upon which the thread runs.	Str
Name	The name of the thread.	Str

NOTES

• Threads on the wait queue are either waiting on condition such as a mutex, condition or join, or they are sleeping, either for a specific number of seconds or forever.

Threads will wait for a variety of reasons. For sqlexec threads, the most common reasons are SM_READ and NETNORM, which indicate the server is waiting for a request from the front end program.