re:spool space

Database
Enthusiast

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

Tags (3)
9 REPLIES
Enthusiast

Re: re:spool space

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.

Enthusiast

Re: re:spool space

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

Enthusiast

Re: re:spool space

Thanks a lot Venky and Aftab

Enthusiast

Re: re:spool space

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

Enthusiast

Re: re:spool space

How about stats collection? It can be due to more volume of data, demography .... 

Nothing change ....Release document!!!!

Enthusiast

Re: re:spool space

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.

Senior Apprentice

Re: re:spool space

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

Fan

Re: re:spool space

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

Senior Apprentice

Re: re:spool space

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'
);