All Forums Teradata Applications
anagpure 8 posts Joined 07/12
28 Jul 2015
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.

dnoeth 4628 posts Joined 11/04
28 Jul 2015

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

Dieter

anagpure 8 posts Joined 07/12
28 Jul 2015

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.
 

dnoeth 4628 posts Joined 11/04
28 Jul 2015

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

Dieter

Fred 1096 posts Joined 08/04
28 Jul 2015

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.

anagpure 8 posts Joined 07/12
29 Jul 2015

Thanks Dieter and Fred.
It is quite clear now.

You must sign in to leave a comment.