onstat -g xmp query_id

The onstat -g xmp query_id command prints information about currently executing iterator instances for the query specified by the query_id parameter. If the query_id parameter is omitted, the command prints information about all queries executing at the coserver at which the command is issued.

In heavily used XPS systems, this command is useful for identifying sessions where insufficient PDQ memory causes iterator functions to overflow to another disk partition. This feature, however, is implemented only for hash join iterators in Version 8.50 of Extended Parallel Server. If overflow occurs in the group iterators, a DBA can detect the overflow only by examining stacks and partition profiling.

In XPS 8.51, the overflow display field in output from the onstat -g xmp query_id command displays disk partition overflow directly for group iterator threads, as well as for hash-join iterator threads, as in the following section of the output display.

XMP Query Operators
opaddr            qry segid branch brtid opname phase    rows  in1               in2
0x700000010d43b60 13  1     0-0    222   xchg   open     1310  0x700000010d47210 0x0
0x700000010d47210 13  1     0-0    222   group  overflow 1358  0x700000010d47bb0 0x0
0x700000010d47bb0 13  1     0-0    222   xchg   done     83267 0x0               0x0

In this example, the phase column indicates an overflow in the group status record. Overflow to a disk partition can severely degrade query performance. The DBA can correct this condition by allocating more memory to the group iterator threads.