All Forums Database
TD_DEV245 20 posts Joined 08/12
09 Oct 2012
re:spool space

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.
 
Thanks,
Vijayshankar

''Human race acts as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about''-Albert Einstein
Qaisar Kiani 337 posts Joined 11/05
09 Oct 2012

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.

venkylingutla 19 posts Joined 06/12
10 Oct 2012

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.
Venkat

TD_DEV245 20 posts Joined 08/12
10 Oct 2012

Thanks a lot Venky and Aftab

''Human race acts as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about''-Albert Einstein

21 May 2014

Hi All,
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
 

Raja_KT 1246 posts Joined 07/09
21 May 2014

How about stats collection? It can be due to more volume of data, demography .... 
Nothing change ....Release document!!!!

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

spidermonk 9 posts Joined 11/12
03 Sep 2014

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.
 

dnoeth 4628 posts Joined 11/04
03 Sep 2014

If you can locate old vs. new Explains in DBQL you will see what SOMETHING changed :-)

Dieter

Salokh 4 posts Joined 08/14
04 Sep 2014

Hi Dieter,
Can u pls tune the below as getting spool space error ?
select count(distinct str.cust_id) from cust_Track ctr
inner join
subscr_track str
on ctr.cust_id=str.cust_id
and str.cust_id<>-1
and str.eff_to_dt=date'9999-12-31'
where ctr.eff_to_dt>date'2012-11-01';
 
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 ,

 NO RANGE, UNKNOWN);

 

 

Please help me to modify this with all possible way.

 

Thanks,

Shiva

dnoeth 4628 posts Joined 11/04
05 Sep 2014

Hi Shiva,
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:

SELECT COUNT(*) 
FROM cust_Track ctr
WHERE ctr.eff_to_dt>DATE'2012-11-01'
AND EXISTS
 (
   SELECT * FROM subscr_track str
   WHERE ctr.cust_id=str.cust_id
   AND str.cust_id<>-1
   AND str.eff_to_dt=DATE'9999-12-31'
 );

 
 
 

Dieter

You must sign in to leave a comment.