INSERT Failed. 2646: No more spool space in sinpuv

Teradata Applications

INSERT Failed. 2646: No more spool space in sinpuv

Hi,

Im running with the spool space error when im trying to insert into a table based on the selection i do on the join of a fact and few other dimension tables.

Following are the details:

1.      Problem Description

a.      Query Being Executed

 

INSERT INTO CARWORK.DM_SALES_LINE_FACT_JAN11_APR11
( SALES_LOCATION_RK ,
LOYALTYCARD_RK ,
ITEM_RK ,
PROMOTION_RK ,
CALENDAR_DT ,
TRANSACTION_CD ,
LINE_NUMBER,
TIMEPERIOD ,
ITEM_QTY ,
ITEM_WEIGHT ,
SELLING_RETAIL_AMT)
SELECT
SALES_LOCATION_RK,
LOYALTYCARD_RK,
ITEM_RK,
PROMOTION_RK,
CAST(DATETIME AS DATE) AS CALENDAR_DT,
TRANSACTION_CD,
LINE_NUMBER,
CAST(0 AS TIME(0)) AS TIMEPERIOD,
ITEM_QTY,
ITEM_WEIGHT,
SELLING_RETAIL_AMT
FROM
CARWORK.STG_SALES_FACT_JAN11_APR11 A,
CARWORK.DM_LOYALTY_CARD B,
CARWORK.DM_PROMOTION C,
CARWORK.DM_ITEM D,
CARWORK.DM_SALES_LOCATION E
WHERE
A.LOYALTYCARD_CD=B.LOYALTYCARD_CD
AND A.PROMOTION_CD=C.PROMOTION_CD
AND A.ITEM_CD=D.ITEM_CD
AND A.SALES_LOCATION_CD=E.SALES_LOCATION_CD

               

 

b.      Explain Plan for the Query

 

Explain Plan Steps

Spool Space Needed in GB

  1) First, we lock a distinct CARWORK."pseudo table" for write on a RowHash to prevent global deadlock for      CARWORK.DM_SALES_LINE_FACT_JAN11_APR11.

  2) Next, we lock a distinct CARWORK."pseudo table" for read on a RowHash to prevent global deadlock for CARWORK.A.

  3) We lock a distinct CARWORK."pseudo table" for read on a RowHash to prevent global deadlock for CARWORK.E.

4) We lock a distinct CARWORK."pseudo table" for read on a RowHash to prevent global deadlock for CARWORK.C.

  5) We lock a distinct CARWORK."pseudo table" for read on a RowHash to prevent global deadlock for CARWORK.B.

  6) We lock a distinct CARWORK."pseudo table" for read on a RowHash to prevent global deadlock for CARWORK.D.

  7) We lock CARWORK.DM_SALES_LINE_FACT_JAN11_APR11 for write, we lock CARWORK.A for read, we lock CARWORK.E for read, we lock CARWORK.C for read, we lock CARWORK.B for read, and we lock CARWORK.D for read.

  8) We execute the following steps in parallel

       1) We do an all-AMPs RETRIEVE step from CARWORK.E by way of an all-rows scan with a condition of ("NOT           (CARWORK.E.SALES_LOCATION_CD IS NULL)") into Spool 2           (all_amps), which is duplicated on all AMPs.  The size of           Spool 2 is estimated with low confidence to be 331,776 rows (        16,257,024 bytes).  The estimated time for this step is 0.10           seconds.

0.015141 GB

       2) We do an all-AMPs RETRIEVE step from CARWORK.A by way of an all-rows scan with a condition of ("(NOT  (CARWORK.A.SALES_LOCATION_CD IS NULL )) AND ((NOT           (CARWORK.A.ITEM_CD IS NULL )) AND ((NOT (CARWORK.A.PROMOTION_CD IS NULL )) AND (NOT          (CARWORK.A.LOYALTYCARD_CD IS NULL ))))") into Spool 3           (all_amps), which is redistributed by the hash code of (           CARWORK.A.LOYALTYCARD_CD) to all AMPs.  The size of  Spool 3 is estimated with no confidence to be 79,229,965 rows (          16,717,522,615 bytes).  The estimated time for this step is           12.07 seconds.

15.56941 GB

  9) We do an all-AMPs JOIN step from CARWORK.B by way of an all-rows scan, which is joined to Spool 3 (Last Use) by way of an all-rows scan.  CARWORK.B and Spool 3 are joined using a single partition hash join, with a join condition of ("(LOYALTYCARD_CD )=      CARWORK.B.LOYALTYCARD_CD").  The result goes into Spool 4 (all_amps), which is redistributed by the hash code of (     CARWORK.A.PROMOTION_CD) to all AMPs.  The size of Spool 4 is estimated with no confidence to be 105,639,954 rows (19,332,111,582 bytes).  The estimated time for this step is 11.09 seconds.

18.00443 GB

10) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to CARWORK.D by way of an all-rows scan with a condition of ("NOT (CARWORK.D.ITEM_CD IS NULL)").  Spool 2 and CARWORK.D are joined using a product join, with a join      condition of ("(1=1)").  The result goes into Spool 5 (all_amps), which is built locally on the AMPs into 13 hash join partitions.  The size of Spool 5 is estimated with low confidence to be 43,130,880 rows (3,666,124,800 bytes).  The estimated time for      this step is 0.88 seconds.

3.414345 GB

11) We do an all-AMPs JOIN step from CARWORK.C by way of an all-rows scan, which is joined to Spool 4 (Last Use) by way of an all-rows scan.  CARWORK.C and Spool 4 are joined using a single partition hash join, with a join condition of  ("(PROMOTION_CD )= CARWORK.C.PROMOTION_CD").  The result goes into Spool 6 (all_amps), which is redistributed by the hash code of (CARWORK.A.ITEM_CD) to all AMPs into 13 hash join partitions.  The size of Spool 6 is estimated with no  confidence to be 140,853,272 rows (21,832,257,160 bytes).  The estimated time for this step is 12.42 seconds.

20.33287 GB

12) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an all-rows scan, which is joined to Spool 6 (Last Use) by way of an all-rows scanSpool 5 and Spool 6 are joined using a hash join of 13 partitions, with a join condition of  ("((SALES_LOCATION_CD)= SALES_LOCATION_CD) AND ((ITEM_CD )= ITEM_CD)").  The result goes into Spool 1 (all_amps), which is redistributed by the hash code of (CARWORK.E.SALES_LOCATION_RK, CARWORK.B.LOYALTYCARD_RK, CARWORK.D.ITEM_RK, CARWORK.C.PROMOTION_RK,     CAST((CARWORK.A.DATETIME) AS DATE)) to all AMPs. Then we do a SORT to order Spool 1 by row hash.  The size of Spool 1 is estimated with no confidence to be 301,208,447 rows (     29,217,219,359 bytes).  The estimated time for this step is 29.58      seconds.

27.21066 GB

13) We do an all-AMPs MERGE into     CARWORK.DM_SALES_LINE_FACT_JAN11_APR11 from Spool 1 (Last Use). The size is estimated with no confidence to be 301,208,447 rows. The estimated time for this step is 2 hours and 39 minutes.

27.21066 GB

14) We spoil the parser's dictionary cache for the table.

15) 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.

TOTAL

111.7575 GB

 

 

 

c.       Details of the space available for Users

 

DATABASE NAME

DETAILS

USEDSPACE_IN_GB

MAXSPACE_IN_GB

PERCENTAGE USED

REMAININGSPACE_IN_GB

sinpuv                       

User executing the query

0

20

0%

20

carwork                      

Database containing the data

285.02

500

57%

214.98

 

d.      Details of the Tables involved in the query

 

DATABASE NAME

TABLE NAME

SKEW FACTOR

ROW COUNT

PRIMARY INDEX COLUMNS

carwork                      

DM_ITEM                      

14.2

69,289

ITEM_CD

carwork                       

DM_LOYALTY_CARD              

4.72

18,45,722

LOYALTYCARD_CD

carwork                      

DM_PROMOTION                 

5.91

5,25,916

PROMOTION_CD

carwork                      

DM_SALES_LOCATION            

64.93

91

SALES_LOCATION_CD

carwork                      

STG_SALES_FACT_JAN11_APR11   

0.92

12,04,92,321

DATETIME, ITEM_CD, LOYALTYCARD_CD, PROMOTION_CD, SALES_LOCATION_CD

 

 

2.      Observations

 

·         Whatever I have read in the Teradata forum is that the Spool space error usually comes if the query is not properly optimized. However in the above mentioned case, the only thing that is being done is Joining of the tables and selection of columns. 

·         Also, taking a look at the explain plan, it is observed that around 100 GB of spool space is required for the query to be executed.

·         The Maximum space available to ‘sinpuv’, the user executing the query, is only 20 GB and all of it is available.

Any help on this would be helpful.

1 REPLY
Senior Apprentice

Re: INSERT Failed. 2646: No more spool space in sinpuv

As you already noticed, 20GB is not enough to run that query, but what do you expect when you join a 120 million row fact table to several dimensions without any WHERE-condition?

The query needs 112GB spool, but not at the same point in time, whenever there's a "(Last Use)" this specific spool is released after the step. Of course all those GBs are just a guess by the optimizer based on available statistics.  And explain only has low or even no confidence, so there might be some missing stats leading to 300 million rows estimated vs. 120 million actual rows.

Dieter