onstat -u command: Print user activity profile

Use the onstat -u command to display a profile of user activity.


Syntax:

>>-onstat-- -u-------------------------------------------------><

Example output

Figure 1. onstat -u command output

Userthreads
address  flags   sessid   user     tty      wait     tout locks nreads   nwrites
a4d8018  ---P--D 1        informix -        0        0    0     58       4595
a4d8628  ---P--F 0        informix -        0        0    0     0        2734
a4d8c38  ---P--- 5        informix -        0        0    0     0        1
a4d9248  ---P--B 6        informix -        0        0    0     40       0
a4d9858  ---P--D 7        informix -        0        0    0     0        0
a4d9e68  Y--P--- 21       niraj    -        a65e5a8  0    1     0        0
 6 active, 128 total, 7 maximum concurrent

Output description

The -u option displays the following output for each user thread.

address

The shared-memory address of the user thread in the user table.

Compare this address with the addresses displayed in the output from the -s option (latches); the output from the -b, -B, and -X options (buffers); and the output from the -k option (locks) to learn what resources this thread is holding or waiting for.

flags

Provides the status of the session.

The flag codes for position 1:

B: Waiting for a buffer
C: Waiting for a checkpoint
G: Waiting for a write of the logical-log buffer
L: Waiting for a lock
S: Waiting for mutex
T: Waiting for a transaction
Y: Waiting for condition
X: Waiting for a transaction cleanup (rollback)
DEFUNCT: The thread has incurred a serious assertion failure, and has been suspended to allow other threads to continue their work.

The flag code for position 2:

*: Transaction active during an I/O failure

The flag code for position 3:

A: A dbspace backup thread

For other values that appear here, see the third position of flag codes for the -x option.

The flag code for position 4:

P: Primary thread for a session

The flag codes for position 5:

R: Reading
X: Thread in critical section

The flag codes for position 6:

R: Thread used in recovery (for example, physical or logical recovery)
-: Thread not used in recovery

The flag codes for position 7:

B: A B-tree cleaner thread
C: Terminated user thread waiting for cleanup
D: A daemon thread
F: A page-cleaner thread

sessid

The session identification number.

During operations such as parallel sorting and parallel index building, a session might have many user threads associated with it. For this reason, the session ID identifies each unique session.

user

The user login name, which is derived from the operating system

tty

The name of the standard error (stderr) file that the user is using, which is derived from the operating system.

This field is blank on Windows.

wait

If the user thread is waiting for a specific latch, lock, mutex, or condition, this field displays the address of the resource. Use this address to map to information provided in the output from the -s (latch) option or the -k (lock) option. If the wait is for a persistent condition, run a grep for the address in the output from the onstat -a command.

tout

The number of seconds left in the current wait

If the value is 0, the user thread is not waiting for a latch or lock. If the value is -1, the user thread is in an indefinite wait.

locks

The number of locks that the user thread is holding

The -k output should include a listing for each lock held.)

nreads

The number of disk reads that the user thread has executed

nwrites

The number of write calls that the user thread has executed

All write calls are writes to the shared-memory buffer cache.

The last line of the onstat -u command output displays the maximum number of concurrent user threads that were allocated since you initialized the database server. For example, the last line of a sample onstat -u command output is as follows:

4 active, 128 total, 17 maximum concurrent

The last part of the line, 17 maximum concurrent, indicates that the maximum number of user threads that were running concurrently since you initialized the database server is 17.

The output also indicates the number of active users and the maximum number of users allowed.