Explain Plan

Teradata Applications
Enthusiast

Explain Plan

The explain plan which we see in teradata by using command like EXPLAIN < QUERY> is estimated plan or actual plan ?

if it is estimated plan then where is actual plan ?

Thanks.

5 REPLIES
Senior Apprentice

Re: Explain Plan

It's the actual plan.

The actual resource usage (CPU/IO/etc.) can be found in the dbc.QryLogSTepsV.

Enthusiast

Re: Explain Plan

Ohh...is it ?????

I was under the impression that EXPLAIN <QUERY> shows an estimated plan that can vary in actual may be because of stats and other things. and the actual plan can be seen in DBQL.

Thanks.

Senior Apprentice

Re: Explain Plan

If the estimated row counts are wrong a step might perform quite bad, but all steps are executed exactly as-is.

Teradata Employee

Re: Explain Plan

That is true through TD14.0.

In TD14.10 and later, there is IPE (Incremental Planning and Execution) feature which may result in the actual plan being different from the default (STATIC) EXPLAIN. In 14.10 and 15.0, the actual plan differs only for certain queries with single-row / non-correlated scalar subqueries, and atyou can see the "actual" plan at a point in time by requesing DYNAMIC EXPLAIN <query> (which runs the subqueries at EXPLAIN time and uses the current results in conjunction with stats / sampling to plan the rest of the query).

And realize that at best, EXPLAIN will tell you how the query would be executed if you ran it now. But if the stats, data, etc. change significantly then it may run differently now than it did yesterday or than it will tomorrow. So for diagnostic purposes, DBQL provides options to capture the explain text or an XML representation of the plan at run time.

Enthusiast

Re: Explain Plan

Thanks Dieter and Fred.

It is quite clear now.