Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 16 Mar 2007 @ 20:16:40 GMT

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

Subj:   Re: Statistics and lock contention
From:   Ballinger, Carrie

Just to emphasize the point that Todd made in an earlier email, if you look an explain of a COLLECT STATISTICS statement, the row hash write lock on the dictionary tables is done (in the case below) in Step 4. This is after Step 3 where the work of scanning the table and producing the histogram has been accomplished. This minimizes the time and impact of that write lock. Also notice the table level access lock on the base table.

Thanks, -Carrie

     collect statistics on parttbl column (p_brand);
  1)First, we lock TPCD50G.parttbl for access.  
  2)Next, we do a COLLECT STATISTICS step from TPCD50G.parttbl by way of an all-rows scan into Spool 3 (all_amps), which is built locally on the AMPs.  
  3)Then we save the UPDATED STATISTICS from Spool 3 (Last Use) into Spool 4, which is built locally on the AMP derived from DBC.TVFields by way of the primary index.  
  4)We lock DBC.TVFields for write on a RowHash.  
  5)We do a single-AMP MERGE DELETE to DBC.TVFields from Spool 4 (Last Use) by way of a RowHash match scan. New updated rows are built and the result goes into Spool 5 (one-amp), which is built locally on the AMPs.  
  6)We do a single-AMP MERGE into DBC.TVFields from Spool 5 (Last Use).  
  7)We spoil the parser's dictionary cache for the table.  
  8)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  

  <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: 28 Jun 2020