EXPLAIN PLAN IN TERADATA
In this article, we will see the explanation of plan in Teradata
Explain plan is the step-by-step English translation of a query plan generated by the parsing engine. Explain plan can give you a clear picture the way optimizer will execute a query.
It is a good practice to analyse the explain plan for a newly developed query before running it in the Teradata system.
You can run an Explain function on the SQL statements in the Query window and display the results in the Answerset window. Teradata SQL Assistant displays the steps that would be executed if you were to actually run the query.
Do one of the following:
Select Tools > Explain.
The explanation appears in the Answerset window.
The explanation below is returned for the following statement:
Query : sel * from dbc.sessioninfo
select * from dbc.sessioninfo;
*** Help information returned. 12 rows.
*** Total elapsed time was 1 second.
1.First, we lock DBC.SessionTbl in view sessioninfo in
TD_DATADICTIONARYMAP for access.
2. Next, we do an all-AMPs RETRIEVE step in TD_DATADICTIONARYMAP from DBC.SessionTbl in view session info by way of an all-rows scan with no residual conditions into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 6 rows (97,890 bytes). The estimated time for this step is 0.01 seconds.
3. Finally, we send out an END TRANSACTION step to all AMPs involvedin processing the request.
-> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.01 seconds.
Following is the list of terms commonly seen in EXPLAIN plan.
A spool file is no longer needed and will be released when this step completes.
****with no residual conditions****
All applicable conditions have been applied to the rows.
Transaction locks are released, and changes are committed.
****eliminating duplicate rows****
Duplicate rows only exist in spool files, not set tables. Doing a DISTINCT operation.
****by way of a traversal of index #n extracting row ids only****
A spool file is built containing the Row IDs found in a secondary index (index #n)
****we do a SMS (set manipulation step)****
Combining rows using a UNION, MINUS, or INTERSECT operator.
****which is redistributed by hash code to all AMPs****
Redistributing data in preparation for a join.
****which is duplicated on all AMPs****
Duplicating data from the smaller table (in terms of SPOOL) in preparation for a join.
****(one_AMP) or (group_AMPs)****
Indicates one AMP or subset of AMPs will be used instead of all AMPs.
Explain plan provides information about locking, which is placed by the Teradata lock manager on the database objects during query processing. This lock can be like Pseudo table which is placed to avoid global deadlock conditions, read, write, access and exclusive lock.
Row retrieval Strategy
From explain plan you will get the information about how Teradata will retrieve the rows from the disks. As we know there may be several access paths to fetch data. Based on the availability of the index, Teradata may fetch rows by full table scan, using primary index, using secondary index or any other access path.
As all the AMP work independently, they can’t access other AMP data directly. So for join processing rows should be in the same AMP. Teradata decides to redistribute, duplicate to bring the rows to be joined on the same AMP.
In case of join operation, explain plan will show you what kind of join operation is chosen by the optimizer base on the situation.
You will see something like product join, single partition hash join, merge join etc.
AMPs involvement information
During any kind of operation like retrieving rows, joining tables, aggregation you will get information about how many amps take part in that operation. Depends on the work, it can be single amp, group amp or all amps.
In the Explain plan you will see terminology like All-AMPs retrieve step, group_amps, Single-AMP retrieve step, All-AMP join step, Single-AMP join step etc. The operation group amp means more than 1 but fewer than all AMPs will participate.
Time & Size estimation information
Explain plan also provides the information about estimated row counts and estimated time to complete a particular step and query.