Workload Management Classification with IPE Queries
Incremental Planning and Execution (IPE) is a framework within the database that provides a method to improve plans for complex queries. It does this by breaking a plan into “fragments” and interleaving optimization and AMP execution.
Workload management uses classification criteria that relies on knowledge of the query plan to associate a query to a given workload. This posting explains how the classification process functions when only a fragment of the plan is available before the query begins to execute.
What is IPE
The basic approach used by IPE is as follows:
This can result in a more optimal overall plan and provide out-of-the-box performance benefit when processing the more complex queries on a platform.
Static vs. Dynamic Query Plans
In Teradata Database 15.0 and above, the optimizer first builds a static plan (the non-IPE plan). The optimizer then automatically examines each static plan looking for candidate queries and applies IPE, if appropriate. For queries selected to be IPE queries the optimizer then builds a dynamic plan a fragment at a time. The second fragment cannot be built until the first fragment completes execution.
You can view the dynamic plan by issuing a DYNAMIC EXPLAIN command. However, if you do that, all the fragments but the first will be executed so that the output will contain all steps in the dynamic plan. Depending on the complexity of the request, this could take some time and will cause additional resources to be used on the platform, just as though the query were be executed. Use DYNAMIC EXPLAIN commands selectively and not at busy times.
A dynamic plan may be different from a static plan for the same query. For example, the order of joins and the type of joins used could be different, and estimated processing time is likely to be less.
Classification Process with IPE Queries
When a query uses a dynamic (IPE) plan, the TASM classification process only has information about the first fragment. That is incomplete for classification purposes. For example, the first fragment may not include all the objects that will be processed in the query, or the type of joins that will be used. Using an IPE-specific dynamic plan, TASM only has the information on the initial few steps of the query that is included in the first fragment, and until the optimizer builds the plan for the next fragment, information about the subsequent fragments of the query is unavailable.
Classification works in the following way with IPE queries:
Since TASM/TIWM does not have the complete view of the query characteristics based solely on dynamic plans, all rule criteria may not be able to be applied, particularly rules that include things like estimated step times, or types of joins that a given table might be involved in. All step-level criteria for steps not within the first fragment are unknown until the subsequent fragments are optimized.
Taking this into account, and to minimize the need to change existing rule sets, TASM, by default, simply ignores step level criteria when faced with IPE query dynamic plans. This includes the first criteria.
Specific rule criteria that are ignored by TASM when attempting to classify an IPE query to a TASM object include the following:
Workload Management allows you to use IPE as a classification criterion for workload, filter, and throttle rules. This allows you to identify IPE requests being executed and manage them differently from non-IPE requests, if you wish.
You can identify which requests are IPE queries, and which are not, by examining the DBC.DBQLogTbl table. The NumFragments field in that table is NULL for non-IPE requests and reports the number of fragments for IPE requests.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.