All Forums General
Purushotham 87 posts Joined 04/14
25 Jun 2014
How can we rectify spool space issues.

Hi Everyone,

Could anyone please tell me how can we rectify spool space issues.

How to identify spool space issues?

Regards,
Purushotham

Purushotham.M
Raja_KT 1246 posts Joined 07/09
25 Jun 2014

Hi Purushotham,
This is a good read with the note: 
Note: If the user whose query unintentionally caused the spool space problem is logged on at the time you run this query, the query will not detect the user because the query is looking for spool usage for a user that is holding spool space but is not logged on.
http://www.info.teradata.com/HTMLPubs/DB_TTU_14_10/index.html#page/Database_Management/B035_1093_112A/1093Chap20.042.05.html
Specific to queries, you can read the explains to rectify.
 

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.

M.Saeed Khurram 544 posts Joined 09/12
25 Jun 2014

If you are going out of spool spce, you need tune your query and check for the product joins in the explain plan.
Generate the explain plan and then carefully analyze if there is any product join, or redistributions of data across amps.
check for character comparisons in the join conditions.
if there is any product join, try to find the reason and modify the query.
 

Khurram

Purushotham 87 posts Joined 04/14
25 Jun 2014

Hi Raja and Khurram,
I have a file  of 10 GB data if i  insert into table i am getting error message as In suffient memory, i have a free space in database.
But i am getting error message, what might be the reason.could you please share me..
 
Thanks in advance.
Regards,
Purushotham.

Purushotham.M

Raja_KT 1246 posts Joined 07/09
26 Jun 2014

Hi,
You have not shared the details of utility name. Can you please share the script too if possible? Please paste the error too .
Cheers,

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.

goldminer 118 posts Joined 05/09
27 Jun 2014

Sounds like your primary index might be skewed.  Remember, if you run out of space on one AMP it appears as though you have used up all available currentPerm in the database.  Plug your index into the following and check for skew...
sel (hasamp(hasbucket(hashrow(list of primary index column names)))) as amp_no, count(*) as row_count
from databasename.tablename
group by 1
order by 1;
 
Joe

krishaneesh 140 posts Joined 04/13
30 Jun 2014

Have you tried verifying if there is any left over spool(spool that is not released) for the user being used. i see that you are using a file and is a straight dump into the table. As Raja asked,what type of loader are you using? What is the PI of the table being loaded and are the values in the PI column of the file skewed?

You must sign in to leave a comment.