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

     explain
     cexplain
     collect statistics on parttbl column (p_brand);

`Explanation --------------------------------------------------`
	`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>>

Attachments

Library

Quick Reference