Optimizer Timeout
If the query optimizer believes that the best plan it has found so far takes less time to execute than the time it has taken evaluating QEPs, then it times out. It stops searching for further QEPs and returns the best plan it has found up to that point.
To tell if the query optimizer timed out, you can check either of the following:
• The header of the QEP for set qep diagrams. In QEP diagrams generated using set qep, the keywords “timed out” and “no timeout” are indicated in the QEP diagram header.
• The QEP pane of the SQL Scratchpad window in VDBA. In the QEP pane, the Timeout check box is enabled if the optimizer timed out (and disabled if the optimizer did not time out).
Note: The fact that the query optimizer has timed out on a query does not guarantee that a better QEP is found; it indicates that not all QEPs have been checked, and so potentially, a better QEP can be found.
Because it is elapsed CPU time that is being measured, QEPs that time out can change, depending on machine load.
Control Optimizer Timeout
By default, the optimizer times out, but you can turn the timeout feature off to enable the query optimizer to evaluate all plans.
To control optimizer timeout
To turn the timeout feature off, issue the following SQL statement (for example, from a query tab in Director, from the VDBA SQL Scratchpad window, from a terminal monitor, or from within an embedded SQL application):
set joinop notimeout
To turn the timeout feature back on, issue the corresponding statement:
set joinop timeout
To control this feature using an operating system environment variable:
C shell:
setenv ING_SET "set joinop notimeout"
Bourne shell:
ING_SET = "set joinop notimeout"
export ING_SET
Greedy Optimization
The query optimizer performs an exhaustive search of all possible plans for executing a query. It computes a cost estimate for each possible plan and chooses the cheapest plan according to the cost algorithms.
Such a process is very fast for relatively simple queries. For queries involving large numbers of tables, however, the number of possible plans can be very large, and the time spent enumerating the plans and associating costs with them can be significant.
While the Optimizer Timeout feature is useful in shortening processing time, the optimizer can take an unacceptable length of time to identify an efficient query plan in the case of very large queries, especially for queries with large numbers of potentially useful secondary indexes and queries whose tables have large numbers of rows (leading to very expensive plans that do not time out quickly).
The query optimizer includes an alternative mechanism for generating query plans, called greedy optimization, that can greatly reduce the length of compile time. Rather than enumerate all possible query plans (including all permutations of table join order, all combinations of tables and useful secondary indexes, and all “shapes” of query plans), the “greedy” enumeration heuristic starts with small plan fragments, using them as building blocks to construct the eventual query plan. The chosen fragments are always the lowest cost at that stage of plan construction, so even though large numbers of potential plans are not considered, those that are chosen are also based on cost estimation techniques.
Note: The Optimizer Timeout feature, opf_joinop_timeout, does not work with greedy optimization. Unlike exhaustive enumeration, which constructs and costs whole query plans as it proceeds, greedy enumeration performs most of its work before it has any valid plan. Because of this, the optimizer timeout feature is ineffective and does not work with greedy enumeration. However, the speed of optimization using greedy enumeration is so fast, there is no need for a timeout.
Control Greedy Optimization
By default, greedy optimization is used if the query meets the following two criteria:
1. The number of base tables is at least 5.
2. The combination of base table and potentially useful secondary indexes is at least 10.
For example:
The greedy heuristic typically chooses very good query plans, especially when considering the vastly reduced compile time. However, for the rare cases in which greedy optimization produces a much slower plan, it can be turned off.
To control whether greedy optimization is used
To turn greedy optimization off for the session, issue the following SQL statement (for example, from a query tab in Director, from a terminal monitor, from the SQL Scratchpad window in VDBA, or from within an embedded SQL application):
[exec sql] set joinop nogreedy
To turn greedy optimization back on, issue the corresponding statement:
[exec sql] set joinop greedy
To turn off greedy optimization for the installation, set the opf_new_enum configuration parameter (in CBF or VCBF) to OFF.