Working with SQL/MX EXPLAIN

This article continues the topic and describes the next steps: how to apply the understanding of a query plan to performance tuning and how to influence query plans. This article describes a sequence of steps to modify query plans. As mentioned in the previous article, one way to analyze a query plan is to get an understanding of the join order and join algorithms and then to look at how the operators in the query tree are distributed over multiple processes (application, DP2, and ESP). With this understanding how SQL/MX executes a given query, a user may want to try out alternative execution strategies to improve performance.

Step One – Using Defaults

The SQL/MX Query guide describes several default values that can be used to address more global problems with the optimization of queries. For example, if a user experiences a general problem with too many random accesses into tables with nested joins or insert/selects, the defaults PREFERRED_PROBING_ORDER_ FOR_NESTED_JOIN and UPD_ORDERED can be used to produce more plans that lead to a more sequential access. The most simple change, however, is to set the OPTIMIZATION_LEVEL default to MAXIMUM. For larger joins (greater than five tables) this allows the optimizer to look at all potential plans. For all following steps it is advisable to leave the optimization level at MAXIMUM. Otherwise the optimizer may fail to produce the desired plan because that plan was not found within the optimizer’s time limit. Other joins that switch off compiler heuristics are CROSS_PRODUCT_CONTROL, ZIG_ZAG_JOINS, and DATA_FLOW_OPTIMIZATION. When tuning a query plan it is best to start by leaving the optimizer maximal freedom (once the desired plan is found one can always limit that freedom again).

control query default OPTIMIZATION_LEVEL ‘MAXIMUM’;control query default DATA_FLOW_OPTIMIZATION ‘OFF’;control query default CROSS_PRODUCT_CONTROL ‘OFF’;control query default ZIG_ZAG_JOINS ‘ON’;
Another very powerful yet simple default is JOIN_ORDER_BY_USER. With this default, a user can determine the join order of a query plan with the sequence in which the tables appear in the FROM clause.

Step Two – Start Using Control Query Shape

The SQL/MX optimizer picks up the most efficient plan it can, based on the search space it is allowed to traverse; the search space is affected by the control query defaults that are in effect at that time. Sometimes, customer might know a good plan for a given query. The customer can then influence the optimizer through the CQS statement. Writing a shape for a more complex query, however, is a complex task. Starting with a working shape, even if it is not the plan we want, will usually help quite a bit. The SHOWSHAPE command will usually provide such a shape, describing the plan chosen by the optimizer. We can then make incremental modifications to this shape. Before starting with these modifications, we can do two more things to simplify the task. First, we can make the shape easier to work with by formatting it in a readable way. Second, we can remove any parts that we do not need to force, such as extra arguments to scan operators and entire subtrees, e.g. for subqueries (replace such subtrees with the word “anything”).

Step Three – Making Incremental Modifications

Finally, we do want to incrementally change the shape to get the desired plan. The SQL/MX Query Guide contains detailed information on how to approach the use of CQS. It is a good idea to break this task up into small changes and to test the shape after each change. Unfortunately there are many reasons why a modification might not work. Here are some common ones:
Missing Enforcers
A changed plan may require a sort or a repartitioning step that has to be added to the shape as a sort or exchange node. Also, a partition_access node needs to be used above any operators that are to be executed in DP2.

Missing Root Groupby
Groupby operators can often be pushed down to DP2 or be executed in parallel, but in many cases a second “root” groupby operator is required to finalize the results from the parallel instances. Rather than moving such a groupby operator it needs to be split into two.
Missing or Extra Index Join
Accessing a base table through an index is modeled as a nested join between the index and the base table in SQL/MX and is sometimes called an “index join”. SQL/MX uses index joins only where necessary, and a shape needs to contain index joins if and only if one is needed. Index joins are necessary if the index itself does not supply the columns needed in the query.