onstat -g con Conditions with Waiters

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

Conditions with waiters:                                      
cid      addr     name               waiter   waittime        
920      14aa0588 sm_read            213      187             
2370     14480848 netnorm            349      3682            
2464     14719698 netnorm            357      2991            
2504     1476ff00 netnorm            358      2991            
2508     1477b880 netnorm            359      2994            

Output Description

Heading	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.

Monitoring and Tuning

The following table lists the valid condition name fields and their associated 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.

SELECT sid FROM rstcb WHERE tid = ?

This will return the session ID for the thread waiting on the condition. It is now possible to analyze the thread at the process level by using the process ID (pid) for the session.