Archives of the TeradataForum

Message Posted: Thu, 03 Feb 2005 @ 10:01:19 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi:

I generated the following plan and found some thing I have not notice before.

The step 14 is followed by step 22, there is no info about step 15 thru to 21. Not sure what is being processed.

Has anyone else seem something like this before???

             Step                Est. Time        Actual Time        Est. IOs
     Actual IOs        Step Text
             1                0:00.00        0:00.00        0        1
     First, lock PREF."pseudo table" for read on a row hash.
             2                0:00.00        0:00.01        0        1
     Next, we lock PREF."pseudo table" for read on a row hash.
             3                0:00.00        0:00.00        0        1
     We lock PREF."pseudo table" for read on a row hash.
             4                0:00.00        0:00.00        0        1
     We lock PREF."pseudo table" for read on a row hash.
             5                0:00.00        0:00.01        0        1
     We lock BRSOLAP.TM10609_BRSSPMDOM_v42 for access, we lock
     PREF.TM10614_BRSDOMCLYD for read, we lock PREF.TM10624_SLERGNKEY for
     read, we lock PREF.TM10623_SLEDIVKEY for read, we lock
     PREF.TM10622_CTYNMKEY for read, we lock PREF.TM10601_BRSCTRYKEY for
     access, we lock PREF.TM10612_BRSRLOCGRP for access, we lock
     PDATA.TMU0427_TPTRANS for access, we lock PDATA.TMU0411_TPAIRSEG for
     access, we lock PDATA.TMU0538_WNSALESLCN for access and we lock
     PDATA.TMU0401_TRAVLPLAN for access.
             6                0:00.03        0:00.16        229        8
     We do an All-AMPs RETRIEVE step from PREF.TM10601_BRSCTRYKEY by way of
     an all-rows scan into Spool 30669, which is redistributed by hash code
     to all AMPs.
             7                0:00.03        0:00.17        229        5
     We do an All-AMPs JOIN step from Spool 30669 (Last Use) by way of an
     all-rows scan, which is joined to table TM10623_SLEDIVKEY. Spool 30669
     and table TM10623_SLEDIVKEY are joined using asingle partition hash join
     . The result goes into Spool 30670, which is redistributed by hash code
     to all AMPs.
             8                0:00.02        0:00.03        228        2
     We do an All-AMPs JOIN step from Spool 30670 (Last Use) by way of an
     all-rows scan, which is joined to table TM10622_CTYNMKEY. Spool 30670
     and table TM10622_CTYNMKEY are joined using asingle partition hash join
     . The result goes into Spool 30671, which is built locally on the AMPs.
             9                0:00.02        0:00.07        832        13
     We do an All-AMPs RETRIEVE step from PREF.TM10624_SLERGNKEY by way of an
     all-rows scan into Spool 30672, which is duplicated on all AMPs. This
     step begins a parallel block of steps.
             9                0:00.20        0:03.92        31872
     17742        We do an All-AMPs RETRIEVE step from
     BRSOLAP.TM10609_BRSSPMDOM_v42 by way of an all-rows scan into Spool
     30673, which is duplicated on all AMPs. This step ends a parallel block
     of steps.
             10                0:06.35        0:43.44        11384
     63584        We do an All-AMPs JOIN step from Spool 30673 (Last Use) by
     way of an all-rows scan, which is joined to table TMU0411_TPAIRSEG.
     Spool 30673 and table TMU0411_TPAIRSEG are joined using a nested join .
     The result goes into Spool 30674, which is built locally on the AMPs.
             11                0:06.41        4:16.74        11384
     54587        We do an All-AMPs JOIN step from Spool 30674 (Last Use) by
     way of an all-rows scan, which is joined to table TMU0411_TPAIRSEG.
     Spool 30674 and table TMU0411_TPAIRSEG are joined using a row id join .
     The result goes into Spool 30675, which is built locally on the AMPs.
             12                0:00.07        0:00.13        14592        229
     We do an All-AMPs JOIN step from Spool 30671 (Last Use) by way of an
     all-rows scan, which is joined to Spool 30672. Spool 30671 and Spool
     30672 are joined using asingle partition hash join . The result goes
     into Spool 30676, which is duplicated on all AMPs.
             13                0:02.19        0:30.95        11384
     54619        We do an All-AMPs JOIN step from PDATA.TMU0538_WNSALESLCN
     by way of an all-rows scan, which is joined to Spool 30675. table
     TMU0538_WNSALESLCN and Spool 30675 are joined using a merge join . The
     result goes into Spool 30677, which is built locally on the AMPs.
     ====>        14                0:00.05        0:01.47        11335
     56531        We do an All-AMPs JOIN step from Spool 30676 (Last Use) by
     way of an all-rows scan, which is joined to Spool 30677. Spool 30676 and
     Spool 30677 are joined using asingle partition hash join . The result
     goes into Spool 30678, which is built locally on the AMPs.
     ====>        22                0:00.00                0
     We send out an END TRANSACTION step to all AMPs involved in processing
     the request.

Also step 14 thru 22 takes a alot of time and processing..... (Just wondering???)

It also generates a good plan flow in Teradata Visual Explain.

Thanks & Regards,

Pete
Senior System Analyst

	<Prev	Next>		<<First	<Prev	Next>	Last>>