fbpx

Teradata IPE

Traditionally, Teradata creates execution plans based on the information available at query optimization time. The information mainly used are statistics and cost factors, such as the type of used storage devices, CPU models, available main memory, etc.

The optimizer creates several execution plans and chooses the one with the least costs.

Incremental Planning and Execution (IPE), introduced with Teradata 14.10,  goes a step further, not only relying on the information available at query optimization time but uses additional information showing up during the execution of a query that will be used.

IPE – Splitting the Execution Plan into Fragments

To get more statistical insight, the optimizer is not creating the whole execution plan for a request at once, but breaks requests into so-called “request fragments”.

For each request fragment, a “fragment execution plan” is created. The result of a plan fragment is feed into the subsequent plan fragment.

For example, one plan fragment could result in a spool table. The number of rows, or other statistical information,  can be fed into the next plan fragment during query execution.

Currently, IPE is only available for:

  • Uncorrelated scalar subqueries
  • Single‑row accesses for UPI or USI

Here is an example of an uncorrelated scalar subquery (i.e., a subquery that returns only one row and is not joined to another table):

SELECT * FROM
TheTable t01 INNER JOIN TheTable2 t02 ON t01.Key = t02.Key
WHERE
t01.Col1 > (SELECT MIN(t03.Col1) FROM TheSubTable t03)
;

The optimizer can use IPE for the above statement by splitting the request into two request fragments:

Fragment 1
(SELECT MIN(t03.Col1) FROM TheSubTable t03);

Fragment 2

SELECT * FROM
TheTable t01 INNER JOIN TheTable2 t02 ON t01.Key = t02.Key
WHERE
t01.Col1 > [Result of Fragment 1];

The feedback of a previous request fragment often animates advanced optimization techniques, such as the usage of partition elimination.

The technique used for uncorrelated, scalar subqueries is similar to “hard code” a predicate value in our query, allowing the optimizer to use static partition elimination.

Execution plans created with this technique are called “dynamic execution plans”.

The optimizer uses IPE for complex queries, which have an estimated run time of at least one minute. The parsing time is less than 10% of the estimated run time. IPE is not replacing the traditional static execution plan. Even with IPE available, a static plan is created at first. Information gathered from the static plan is then used to decide if IPE will be used.

IPE is not replacing the traditional static execution plan. Even with IPE available, a static plan is created at first. Information gathered from the static plan is then used to decide if IPE will be used.

Several newly introduced query bands allow us to force the optimizer to use IPE for non-complex queries.

IPE Query Bands

These query bands can be used to avoid or activate IPE usage if the request is eligible for IPE, but the estimated execution time is below one minute:

– The system should determine eligibility (the default behavior):

SET QUERY_BAND = ‘DynamicPlan=SYSTEM;’ FOR SESSION;

– Bypasses the cost thresholds (one minute execution time). If IPE is available, it will be used:

SET QUERY_BAND = ‘DynamicPlan=SYSTEMX;’ FOR SESSION;

– Turns off IPE for the session:

SET QUERY_BAND = ‘DynamicPlan=OFF;’ FOR SESSION;

How can we know if IPE is available?

Teradata introduced a new option for the EXPLAIN modifier: DYNAMIC EXPLAIN. We have to use this option if we want to see if the optimizer uses the static or dynamic (IPE) execution plan.

When we use this new option, Teradata will show the dynamic plan, if the query is eligible for IPE, otherwise the static plan.

Important: You should not start to use always DYNAMIC EXPLAIN to check the execution plan, as Teradata has to execute each plan fragment to show the dynamic plan!

Since Teradata 14.10, the EXPLAIN modifier gives insight about IPE usage:

  • Eligible for IPE:
    This request is eligible for incremental planning and execution (IPE). The following is the static plan for the request.
  • Eligible for IPE but does not meet cost thresholds (run time < 1 minute):
    This request is eligible for incremental planning and execution (IPE) but does not meet cost thresholds. The following is the static plan for the request.
    If the request is not eligible for IPE, the output of the EXPLAIN modifier will not mention it (i.e., the explained plan looks like in pre-Teradata 14.10 releases)

IPE and Workload Management

Currently, IPE is not fully supported by TASM. TASM applied workload rules always on the static plan. Only workload exceptions are based on the dynamic plan.

With Teradata 15.00, TASM can identify requests using IPE, and a high-level classification can be done based on this fact (IPE / non-IPE request).

Questions?
If you have any questions about all this, please ask in the comments! I’ll be paying close attention and answering as many as I can. Thank you for reading. Whatever this blog has become, I owe it all to you.
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>