When i get an error stating 'NO MORE SPOOL SPACE IN USER' what does it mean?
My understanding is that spool space is allocated equally across all AMPs and upper limit is defined for an USER.
For example if FREE SPACE available is 500 GB and there are 10 AMPS then each amp gets 50 GB of spool space.
IF user A has spool space limit defined as 10GB and this results in 1GB of spool space for each AMP under this user.
Under this scenario if the user executes a query which takes up more than 1GB of space on a particular AMP then I get this error 'NO MORE SPOOL SPACE IN USER'..
Pls correct me if I am wrong.
Your understanding is correct! If the required spool space for a USER's query on one AMP is less than what is currently available then TD can't hold the intermediate results in spool and thus end up with no more spool space error.
You can fix this error by revisiting the index selection for the tables involved in the query, because if too much data is pulled in the spool then this is not a good sign. May be refreshing STATS can help because may be the TD optimizer is choosing a wrong execution plan because of the available stale stats.
all your words are correct but spool space problem depends on processing data and index selection on columns. If ur processing data goes into one emp which is holding only 1gb spool then it will fail with the spool out problem.
Go with the explain plan and collect the stats on specific columns it may resolve ur problem.
ONe ETL job is failing with No More spool space. But the reason is that earlier it was running fine on TD13.10 but system has upgraded to 14.10. The same job is failing ith spool space nothing has change. we have increased the spool 3 times then the job got success. Any idea . is this any changes in Teradata 14.1 version. It would be more help ful
I have to agree with the prior post. We didn't have the same spool space issues with 14.1 as we did in 13.1. Even simplistic queries are running into spool space issues.
Data size isn't large, stats were collected and we still have issues. SOMETHING has changed.
Can u pls tune the below as getting spool space error ?
select count(distinct str.cust_id) from cust_Track ctr
note: For cust_Track table PI is cust_id and for subscr_track table PI is subscr_id and subscr_track table has the below partition:
PARTITION BY RANGE_N(Eff_To_Dt BETWEEN DATE '2004-01-01' AND DATE '2016-12-31' EACH INTERVAL '1' DAY ,
DATE '9999-12-31' AND DATE '9999-12-31' EACH INTERVAL '1' DAY ,
it's hard to tell without knowing the logical PK/FK and Explain.
Assuming ctr.cust_is is the PK you could rewrite it using EXISTS, imho this should be logically equivalent:
FROM cust_Track ctr
SELECT * FROM subscr_track str