Tune Queries for Balanced Processing

However, each query should also balance resource and processing evenly across coservers. Even if the table data is evenly distributed across table fragments and coservers, a query might require significantly different numbers of rows from table fragments on some coservers. This dynamic data skew produces unbalanced processing because the coservers that process fewer rows must wait for coservers that process more rows. Monitor balance of processing for individual queries to find out if dynamic data skew causes poor performance.

To detect skew during query processing

Examine repeated onstat -g rgm output to see information about pending queues and the basic memory allocation for the query.
The onstat -g rgm output also displays the session and query plan IDs, which you use in analyzing output from other onstat options to track specific queries.
Check the onstat -g ses and onstat -g sql output to see basic session information, such as the SQL statement. Because you execute these onstat options on the connection coserver, you see information about all coserver processes.
Examine the output of onstat -g xqp and onstat -g xqs, which you execute on the connection coserver. These two onstat options display query segment information for the entire database server.
Examine the output of xctl onstat -g xmp, which displays query information by SQL operator on each coserver.
In addition, you might obtain CPU and disk I/O information from operating-system utilities, and you can run the following database server utility programs to examine processing across the high-speed interconnect between coservers:
- Run xctl onstat -g xps and examine any signs of operators that use all of their allocated memory and write output to temporary space.
  If the query uses a lot of temporary space, run xctl onstat -d and examine the output to see how the space is being used.
- Examine the output of xctl onstat -g dfm and xctl onstat -g xmf to see work and overall cycle information as well as sender and receiver information.

If you find that a few queries result in data skew that cannot be corrected by changing the table fragmentation or rewriting the query, or if changing table fragmentation would create data skew in many more queries, then issue the SET ENVIRONMENT HANDLE_SKEW ON statement for the sessions that run the skewed queries. For information about the SET ENVIRONMENT options, refer to the IBM Informix: Guide to SQL Syntax.

For general information about these and other onstat options, refer to the utilities chapter in the IBM Informix: Extended Parallel Server Administrator's Reference.