Analyze explain plan

Analytics
Enthusiast

Analyze explain plan

Hi,
We have the below explain plan for inserting into a global temp table. i have few questions on the same. Kindly help me understanding the same.

1) what does "pseudo table" mean ? is this a temp table create for global temp table ?
2) what does DBC.TVM,DBC.DBase mean ?
3) i do not see any insert into the global temprary table. can someone explain how data is inerted into global temporary table

1) First, we lock a distinct AB28880."pseudo table" for exclusive use
on a RowHash to prevent global deadlock for
AB28880.sample_MACRO.
2) Next, we lock AB28880.sample_MACRO for exclusive use.
3) We lock DBC.TVM for write on a RowHash.
4) We execute the following steps in parallel.
1) We do a single-AMP ABORT test from DBC.DBase by way of the
unique primary index.
2) We do a single-AMP ABORT test from DBC.TVM by way of the
unique primary index.
3) We do a single-AMP DELETE from DBC.TVFields by way of the
primary index with no residual conditions.
4) We do a single-AMP RETRIEVE step from DBC.TVM by way of the
unique primary index with no residual conditions into Spool 1
(group_amps), which is redistributed by hash code to all AMPs.
5) We do a single-AMP DELETE from DBC.TVM by way of the unique
primary index with no residual conditions.
5) We do a group-AMP MERGE into DBC.TVM from Spool 1 (Last Use).
6) We spoil the parser's dictionary cache for the table.
7) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> No rows are returned to the user as the result of statement 1.

thanks,
teradata developer
4 REPLIES
Enthusiast

Re: Analyze explain plan

Psuedo tables are dummy tables that sit on all AMPs.Pseudo tables store the Table ID Hash codes of database objects which are involved in ALL-Amp operations.Whenever a ALL-AMP operation is performed on a database object, a Row-Hash lock is placed on psuedo table to prevent the deadlock situation(if we have another user submitted another request on same object).

Looking at the explain,looks like you are performing a DDL change through a macro.DBC.TVM and DBC.Dbase are dictionary tables that store the meta data about the database objects Tables/views/macros. Whenever a database object is created/deleted/modified these dictionary tables are updated.
Enthusiast

Re: Analyze explain plan

Thanks Leo Issac for the clear explanation!!! appreciate ur interest in clarifing my doubt.

also can u throw some lights on "group-amp MERGE into DBC>TVM" operation

thanks,
teradata developer
Senior Apprentice

Re: Analyze explain plan

Hi newtera,
this isn't an explain of an insert into a temp table, but a "replace macro".
But maybe that insert is part of that macro, so you better "explain exec AB28880.sample_MACRO;"

The first insert into a global temp table will materialize a session local version.
Any following DML will show only one difference in explain: "temporary table" instead of just "table"

Dieter
Enthusiast

Re: Analyze explain plan

Learning to understand the EXPLAIN output is like learning a foriegn language (example: Japanese...assuming you aren't Japanese!).

And the task becomes relatively easy if you start EXPLAINing small SQL's before progressing to the big ones. The SQL you tried to EXPLAIN must have left you cross eyed...

If you are trying to learn the art of interpreting this language, I recommend you read the standard reference manuals as to how SQLs are processed internally. After you do this you could experiment and observe the EXPLAIN output for SQL's similar to the ones below.

/**********************************************************************/

EXPLAIN SELECT * FROM Employee;

EXPLAIN SELECT * FROM Employee WHERE idno = 5;

EXPLAIN SELECT * FROM Employee WHERE idno > 5;

EXPLAIN SELECT * FROM Employee WHERE idno <> 5;

EXPLAIN SELECT * FROM Employee ORDER BY deptno;

EXPLAIN INSERT INTO Employe....;

EXPLAIN DELETE FROM Employee ;

EXPLAIN INSERT SELECT .... INTO Employee;

EXPLAIN SELECT * FROM EMPLOYEE E, DEPARTMENT D WHERE E.deptno = D.deptno;

EXPLAIN CREATE TABLE ....;

EXPLAIN DROP TABLE...;

/**********************************************************************/

In the EXPLAIN output you should pay attention to

(a). Lock Information
(b). Number of AMPS involved in the operation (Single AMP, ALL AMP)
(c). Confidence levels and Row estimates

--Foxbat