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.

Syntax:

onstat -gpqs [ sessionid ]

You can specify one of the following invocations:

Table 1. Descriptions of each onstat -g pqs command invocation
Invocation	Explanation
onstat -g pqs	Displays a one-line summary for each session.
onstat -g pqs sessionid	Displays information for the session that you specify.

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 2: 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.
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.