Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sun, 10 Sep 2002 @ 18:14:31 GMT


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


Subj:   OLAP function Spool Usage
 
From:   Jeremy Christiansen

I am running some queries on a large table in our system and am noticing some seemingly bizarre spool usage. During query execution a large amount of data is going to just a few amps, creating a "hot amp" situation. Moreover, the total amount of spool being used ranges from a little over the size of the table being queried to three times that much (at different points during execution). I have narrowed down the problem to the OLAP functions I am using. Here is an example of a test query that produces the same spool problems as the original query.

sel A.*
    ,CSUM(1,A.COLUMN_1,A.COLUMN_2)
 from TABLE_1 A
group by COLUMN_1;

Table "A" is not a view and is evenly distributed on column 1. Column 1 is the non-unique primary index. The logical key to the table is columns 1 and 2 (there is no corresponding index, but if the table were redistributed on these columns, the data would still be perfectly even).

Okay. Consider this: when I just select all data (no OLAP function) from the table, I get about 215 megs of data per amp (48 amps in all). When I add the OLAP function, data gets shuffled around such that at some points (during execution) the busiest amp will have 1,000+ megs while the least busy will have close to 18.

I don't get why data is being shuffled around at all. I am grouping by the primary index, so it would seem that each amp has all the rows it needs before the query even starts. Again, the table is already distributed on column 1 and that is the column on which I am basing the csum function. It would seem that all each amp would have to do is sort its own data and compute the csum value. Why data is being passed all over place, and to some places more than others, is beyond me. Also, even if for some strange reason the data was being re- distributed on the sort columns (which I would argue is unnecessary in this case), there should still be no skew. Yet there is.

I would appreciate any explanatory and/or helpful thoughts. Also, as a side question, can anyone tell me what it means for an explain to state "which is assumed to be redistributed by value to all AMPs".

Here is the explain:

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct beneficiary."pseudo table" for read on a RowHash to prevent global deadlock for beneficiary.a.  
  2)Next, we lock beneficiary.a for read.  
  3)We do an all-AMPs RETRIEVE step from beneficiary.a by way of an all-rows scan with no residual conditions into Spool 2, which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The result spool file will not be cached in memory. The size of Spool 2 is estimated with high confidence to be 77,173,397 rows. The estimated time for this step is 20 minutes and 10 seconds.  
  4)We do an all-AMPs STAT FUNCTION step from Spool 2 (Last Use) by way of an all-rows scan into Spool 5 (Last Use), which is assumed to be redistributed by value to all AMPs. The result rows are put into Spool 1, which is built locally on the AMPs.  
  5)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->The contents of Spool 1 are sent back to the user as the result of statement 1.  



     
  <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