Workload Management Classification with IPE Queries

Blog
The best minds from Teradata, our partners, and customers blog about relevant topics and features.
Teradata Employee

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:

  • A complex request, once identified by the optimizer, is broken into smaller pieces referred to as request fragments.
  • The request fragments undergo optimization one at a time, with the first fragment feeding its results as input into the second fragment. Both optimizing and executing fragments takes place incrementally. This is in contrast to traditional, non-IPE, queries, which are optimized as a single unit, and which produce a static plan.
  • The plan generated by IPE is referred to as a dynamic plan. Results that are returned from earlier request fragments can provide more reliable information (such as hard values for input variables) for the planning of subsequent request fragments.

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:

  • Filters are applied using all the objects in the first fragment. Because the first fragment includes locking statements for the entire request, filters are able to correctly identify all objects in the request. No filtering is done in subsequent fragments.
  • Workload classification is only applied against the first fragment. Whatever workload is selected for use on the first fragment will be used for the subsequent fragments. All fragments are executed at the same priority unless there is an intervening action.
  • Throttling is only applied to the first fragment. Once the first fragment is allowed to run no further throttling is done It is desirable to avoid delays in the middle of the request, when locks have already been placed.

 

Step-Level Criteria

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:

  • Min Step estimated Row count.
  • Max Step estimated Row count.
  • Min Step estimated time.
  • Max Step estimated time.
  • Full Table Scan.
  • Join Type.
  • IPE Request Criteria.

 

Other Considerations

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.