Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 16 Mar 2007 @ 19:43:25 GMT


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


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

If you run an explain on table level statistics collection, you can see each separate pass of the table. Each column/index is a separate sequential collection. There is some savings at the end because they share a common spool file and a single update is made to the dictionary tables.


Thanks, -Carrie


     EXPLAIN
     COLLECT STATISTICS customer;
Explanation
--------------------------------------------------
 
  1)First, we lock CAB.customer for access.  
  2)Next, we do a COLLECT STATISTICS step from CAB.customer by way of an all-rows scan into Spool 1 (all_amps), which is built locally on the AMPs.  
  3)Then we save the UPDATED STATISTICS from Spool 1 (Last Use) into Spool 3, which is built locally on the AMP derived from DBC.TVFields by way of the primary index.  
  4)We do a COLLECT STATISTICS step from CAB.customer by way of a traversal of index # 8 without accessing the base table into Spool 4 (all_amps), which is built locally on the AMPs.  
  5)Then we save the UPDATED STATISTICS from Spool 4 (Last Use) into Spool 3, which is built locally on the AMP derived from DBC.TVFields by way of the primary index.  
  6)We do a COLLECT STATISTICS step from CAB.customer by way of a traversal of index # 4 without accessing the base table into Spool 8 (all_amps), which is built locally on the AMPs.  
  7)Then we save the UPDATED STATISTICS from Spool 8 (Last Use) into Spool 3, which is built locally on the AMP derived from DBC.TVFields by way of the primary index.  
  8)We lock DBC.TVFields for write on a RowHash.  
  9)We do a single-AMP MERGE DELETE to DBC.TVFields from Spool 3 (Last Use) by way of a RowHash match scan. New updated rows are built and the result goes into Spool 9 (one-amp), which is built locally on the AMPs.  
  10)We do a single-AMP MERGE into DBC.TVFields from Spool 9 (Last Use).  
  11)We spoil the parser's dictionary cache for the table.  
  12)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: 15 Jun 2023