spool space error

Database

spool space error

Hello,

I run a heavily processing sql script via BTEQ composed of different enrichment steps to get  increase kpi's

I got 'Failure 2646 No more spool space in  db'  in 7th iteration

Which are the solutions to overpass it? 

We've collected statistics, enlarge the spool space of the user and last time added 'commit work;'  which did not work - Failure 3706 Syntax error: COMMIT WORK not allowed for a DBC/SQL session.

If views are used in selects, the collect statistic is to be done on View'columns or base table columns ?

Thank you,

Best regards,

Grigore

7 REPLIES
N/A

Re: spool space error

Hi Grigore,

first you need to find out which step runs out of spool, check dbc.QryLogStepsV for that query.

Compare to Explain to see if the estimated numbers match the actual from the steps.

Regarding the error for COMMIT, you're probably running a Teradata mode session (and there should be no relation to the 2646 anyway).

Stats are collected on the base tables check DIAGNOSTIC HELPSTATS ON FOR SESSION; to find missing stats.

Re: spool space error

Thank you Dieter!

We are running the statistics recomended by SqlAssistant Explain plan

Then we'll rerun the script.

If i understood correctly, Commit is not of big help for  "spool space" error so I'll skip COMMIT statement.

BR,

Grigore

Re: spool space error

Still same error in iteration 7...

The tables it works during generic iteration are  in this order: dropped /created /populated  then deleted at the end of iteration.

I don't know if statistics are useful once the content is completely changed; i can see in explain plan step 8 "low confidence to be 15,232,785 rows"  as the biggest volume in, but the efective insert result is about  31655 rows

N/A

Re: spool space error

Hi Grigore,

when your final result is only 31655 rows there must be some intermediate steps running out of spool.

If Explain looks ok (no product join with a stupid condition?) you need the data from dbc.QryLogStepsV to compare actual vs. estimated.

For the temporary tables used in each iteration you probably don't need any stats.

N/A

Re: spool space error

Use below query to check spoolusage and other factor:

SELECT
TB1.AMPCPUTime,TB1.SpoolUsage,TB1.TotalIOCount,
SUBSTR(TB2.SqlTextInfo,1,1000) AS SqlTextInfo
FROM
DBC.DBQLOGTBL TB1
INNER JOIN
DBC.DBQLSQLTBL TB2
ON
TB1.QueryID = TB2.QueryID
AND
TB1.ProcID = TB2.ProcID
AND
TB1.SessionID='Enter Session ID here';

Also you can consider following tips:

1) If you are dealing with huge number of records, try adding Compression on the table. This will reduce spool space used if the table is getting redistributed. 

2) If the Volatile Tables are used only for intermediary calculations and are not used later then drop them explicitly. Dont wait for session to complete. This will free some much required spool space.

3) If you are facing issues due to non-availability of SPOOL Space, then try optimizing your SQL queries , remove any PRODUCT JOIN and collect stats on columns participating in Joins conditions. ( i think you have already covered this)

4) Which TD version are you using ? In new version, you can even do collect stats on volatile tables . See if this can help you in tuning queries.

Source: http://usefulfreetips.com/Teradata-SQL-Tutorial/how-to-free-some-space-in-teradata-database/

Thanks

Nitin

Re: spool space error

Hi,

Product join exists but is required; I reduced the dataset in small batches using key MOD 32 = 0,   /  =1 /.. /=31 (processing 32 time  the iterations) and worked out. But is a under pressure choice... analysing further...

Thank you for prompt help!

Re: spool space error

Check for the step/join condition the query spool out and analyze the column/data further:

a. Rows per value (use HELP STAT). Less number of RPV will lead selective AMP's are used during data redistributed

b. Use derive table, to sub-set data or aggregate  before joining with next table

c. If PI is different for both the tables, look for applying further filter condition to limit rows processed

d. Look for any default value (like NULL, #, etc.,) and apply filter condition to eliminate those records

Also check for any left over spool "phantom spool" not released from previous sessions. Some times this act as hidden cop.

SELECT TRIM(DATABASENAME)
FROM DBC.DISKSPACE
WHERE DATABASENAME NOT IN (SEL USERNAME FROM DBC.SESSIONINFO)
AND DATABASENAME <> 'DBC'
GROUP BY 1
HAVING SUM(CURRENTSPOOL) > 0 OR SUM(CURRENTTEMP) > 0
ORDER BY 1
;

Thanks!!