Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 12 Feb 2004 @ 21:17:18 GMT


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


Subj:   Different explains from queryman versus Session Information in Teradata Manager
 
From:   McBride, Michael

Can anyone tell me why I get different explains from the explain command in Queryman versus what actually is executing according to the SQL capture / explain in the Sessions Information monitoring tool within Teradata Manager...the difference is that the executing query is using an ALL ROWS scan in Step 5 whereas the preliminary explain (from QueryMan) indicated the utilization of an index #8 ( the slstrns_dt column has a secondary index with stats)....

This is the query as shown by the Session Information Utility while executing:

UPDATE aeo_prodstagingdb.sales_trans_sumry
set  slstrns_active_in =  aeo_prodworkdb.TT_ACTFLGUPD_ss.active_flg
where
    slstrns_stor_nu    =  aeo_prodworkdb.TT_ACTFLGUPD_ss.store_nu
and slstrns_dt         =  aeo_prodworkdb.TT_ACTFLGUPD_ss.transaction_dt
and slstrns_rgstr_nu   =  aeo_prodworkdb.TT_ACTFLGUPD_ss.register_nu
and slstrns_nu         =  aeo_prodworkdb.TT_ACTFLGUPD_ss.transaction_nu
and slstrns_seq_nu     =  aeo_prodworkdb.TT_ACTFLGUPD_ss.seq_nu;

The highlighted step below is shows the difference (step #5)

Explanation
--------------------------------------------------
 
  1)First, lock [DBId=0x0441]."pseudo table" for read on a row hash.  
  2)Next, we lock [DBId=0x0413]."pseudo table" for write on a row hash.  
  3)We lock [DBId=0x0441].[TBId=0x0002 0xED0D] for read and we lock [DBId=0x0413].[TBId=0x0003 0x0605] for write.  
  4)We do an All-AMPs RETRIEVE step from [DBId=0x0441].[TBId=0x0002 0xED0D] by way of an all-rows scan into Spool 14810, which is duplicated on all AMPs.  
  5)We do an All-AMPs JOIN step from Spool 14810 (Last Use) by way of an all-rows scan, which is joined to table [TBId=0x0003 0x0605]. Spool 14810 and table [TBId=0x0003 0x0605] are joined using a nested join . The result goes into Spool 14811, which is built locally on the AMPs.  
  6)We do an All-AMPs JOIN step from Spool 14811 (Last Use) by way of an all-rows scan, which is joined to table [TBId=0x0003 0x0605]. Spool 14811 and table [TBId=0x0003 0x0605] are joined using a row id join . The result goes into Spool 14809, which is built locally on the AMPs.  
  7)We read table ids out of table [TBId=0x0003 0x0605] and delete the corresponding table headers and data.  
  8)We do a MERGE into table [TBId=0x0003 0x0605] from Spool 14812.  
  9)We send out an END TRANSACTION step to all AMPs involved in processing the request.  


This is the explain request in QueryMan:

explain
UPDATE aeo_prodstagingdb.sales_trans_sumry
set  slstrns_active_in =  aeo_prodworkdb.TT_ACTFLGUPD_ss.active_flg
where
    slstrns_stor_nu    =  aeo_prodworkdb.TT_ACTFLGUPD_ss.store_nu
and slstrns_dt         =  aeo_prodworkdb.TT_ACTFLGUPD_ss.transaction_dt
and slstrns_rgstr_nu   =  aeo_prodworkdb.TT_ACTFLGUPD_ss.register_nu
and slstrns_nu         =  aeo_prodworkdb.TT_ACTFLGUPD_ss.transaction_nu
and slstrns_seq_nu     =  aeo_prodworkdb.TT_ACTFLGUPD_ss.seq_nu;

And this is the resulting explain text:

Note that step 5 uses an index. Stats on the indexes are up-to-date:

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct aeo_prodworkdb."pseudo table" for read on a RowHash to prevent global deadlock for aeo_prodworkdb.TT_ACTFLGUPD_ss.  
  2)Next, we lock a distinct aeo_proddb."pseudo table" for write on a RowHash to prevent global deadlock for aeo_proddb.SALES_TRANS_SUMRY.  
  3)We lock aeo_prodworkdb.TT_ACTFLGUPD_ss for read, and we lock aeo_proddb.SALES_TRANS_SUMRY for write.  
  4)We do an all-AMPs RETRIEVE step from aeo_prodworkdb.TT_ACTFLGUPD_ss by way of an all-rows scan with no residual conditions into Spool 2, which is duplicated on all AMPs. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 is estimated with low confidence to be 4,000 rows. The estimated time for this step is 0.04 seconds.  
  5)We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to aeo_proddb.SALES_TRANS_SUMRY by way of a traversal of index # 8 without accessing the base table extracting row ids only. Spool 2 and aeo_proddb.SALES_TRANS_SUMRY are joined using a nested join, with a join condition of ("aeo_proddb.SALES_TRANS_SUMRY.slstrns_dt = transaction_dt"). The input table aeo_proddb.SALES_TRANS_SUMRY will not be cached in memory. The result goes into Spool 3, which is built locally on the AMPs. Then we do a SORT to order Spool 3 by field Id 1. The size of Spool 3 is estimated with low confidence to be 25,880 rows. The estimated time for this step is 17 minutes and 53 seconds.  
  6)We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an all-rows scan, which is joined to aeo_proddb.SALES_TRANS_SUMRY. Spool 3 and aeo_proddb.SALES_TRANS_SUMRY are joined using a row id join, with a join condition of ("(aeo_proddb.SALES_TRANS_SUMRY.slstrns_seq_nu = seq_nu) AND ((aeo_proddb.SALES_TRANS_SUMRY.slstrns_nu = transaction_nu) AND ((aeo_proddb.SALES_TRANS_SUMRY.slstrns_rgstr_nu = register_nu) AND (aeo_proddb.SALES_TRANS_SUMRY.slstrns_stor_nu = store_nu )))"). The input table aeo_proddb.SALES_TRANS_SUMRY will not be cached in memory. The result goes into Spool 1, which is built locally on the AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1. The size of Spool 1 is estimated with low confidence to be 25,880 rows. The estimated time for this step is 17 minutes and 54 seconds.  
  7)We do a MERGE DELETE to aeo_proddb.SALES_TRANS_SUMRY from Spool 1 (Last Use) via the row id. New updated rows are built and the result goes into Spool 4, which is built locally on the AMPs. Then we do a SORT to order Spool 4 by row hash.  
  8)We do a MERGE into aeo_proddb.SALES_TRANS_SUMRY from Spool 4 (Last Use).  
  9)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.  


Any insight into what is happening here would be helpful...

Michael E. McBride
Teradata Certified Master
Teradata Database Administrator
Data Architect and Data Warehouse Practitioner
American Eagle Outfitters



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016