onstat -g pqs command: Print operators for all SQL queries

Use the onstat –g pqs command to display information about the operators used in all of the SQL queries that are currently running.

You can use this command to troubleshoot an application, to find which operators are running for the query and for how long, and how many rows each operator returns. While the EXPLAIN file contains information that will give you a general sense of the query plan, the onstat –g pqs command displays the runtime operator information for the query and the query plan.

>>-onstat-- -g--pqs--+-session_id-+-----------------------------><
                     +-ready------+
                     +-running----+
                     +-busy-------+
                     +-bufwait----+
                     +-critsec----+
                     +-iowait-----+
                     +-logwait----+
                     +-mutexwait--+
                     +-lockwait---+
                     '-active-----'

You can specify one of the following invocations:

onstat -g pqs: Displays a one-line summary for each session
onstat -g pqs session_id: Displays information for a specific session
ready: Displays information for all sessions containing a thread that is on the ready queue
running: Displays information for all sessions containing a thread that is running
busy: Displays information for all sessions containing a thread that is either ready or running
bufwait: Displays information for all sessions containing a thread that is waiting on a buffer
critsec: Displays information for all sessions containing a thread that is in a critical section
iowait: Displays information for all sessions containing a thread that is waiting on I/O
logwait: Displays information for all sessions containing a thread that is waiting on a log buffer
mutexwait: Displays information for all sessions containing a thread that is waiting on a mutex
lockwait: Displays information for all sessions containing a thread that is waiting on a lock
active: Displays information for all sessions containing a thread that is in any of the states described above

Example output

The following example shows the results when three separate SQL statements are run in different sessions. The statements are:

select * from syscolumns;
select * from systables a, systables b;
update t1 set rowsize = rowsize +100;

Figure 1. onstat –g pqs command output

Query Operators:
addr     ses-id   opname    phase   rows   time           in1         in2    stmt-type
ae50b3a  23       scan      open    0      00:00.00         0           0    SELECT
af269d0   5       nljoin    next    224717 00:01.82   af26a90     aeb4478    SELECT
af26a90   5       scan      next    472    00:00.20         0           0    SELECT
aeb4478   5       scan      next    50     00:01.63         0           0    SELECT
ad3c530  26       scan      open    0      00:00.00         0           0    UPDATE (all)

Output description

addr: The address of the operator in memory. You can use this address to track which SCAN operator belongs to each JOIN operator.
ses-id: The session ID in which the SQL statement was run.
ready: The sessions containing a thread that is ready
running: The session containing a thread that is running
busy: The session containing a thread that is running or busy
bufwait: The session containing a thread that is waiting on a buffer
critsec: The session containing a thread in a critical section
iowait: The session containing a thread waiting on I/O
logwait: The session containing a thread waiting on a log buffer
mutexwait: The session containing a thread waiting on a mutex
lockwait: The session containing a thread waiting on a lock
active: The session containing a thread that is active
opname: The name of the operator.
phase: The phase in which the operator was used. For example OPEN, NEXT, CLOSE.
rows: The number of rows that are processed by the operator.
time: The amount of time to process the operator. The time is displayed to the millisecond. A time of 01:20.10 is 1 minute, 20 seconds, and 10 milliseconds.
in1: The first (outer) operator in the join.
in2: The second (inner) operator in the join.
stmt-type: The type of SQL statement, such as SELECT, UPDATE, DELETE.