All Forums Database
princykoshy 3 posts Joined 12/13
17 Dec 2013
Spool space issue

Teradata queries are executed top down or bottom up?
We're getting spool space error in some of the quereis. Any pointers to tune the queries will be helpful.
Have checked indices, collect stats, spool allocated for the id. all are fine.

praveen_reddy 22 posts Joined 10/11
18 Dec 2013

Hi,
 
Check the number of sessions connected or queries executed by the user.
 
Regards,
Praveen

Raja_KT 1246 posts Joined 07/09
18 Dec 2013

I am not able to understand your question properly. Do you mean to ask the order , in which a query has so many joins?

 

Cheers,

Raja

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
18 Dec 2013

HI,
If you are talking about the sequence of joins execution, Teradata optmizer decides how to and in which sequence process the joins. 
In order to avoid spool space problem you can check and move any drived tables to Volatile tables. 
Also check that the appropriate spool is assigned to the user. Collect only those stats which are required.
 

Khurram

princykoshy 3 posts Joined 12/13
18 Dec 2013

which part is executed first? conditions in "where" clause or joins in "from" clause, when the queries are written in ANSI92 stnds.

dnoeth 4628 posts Joined 11/04
18 Dec 2013

According to Standard SQL FROM is processed before WHERE in theory, but all optimizers try to push the WHERE-conditions into the join, thus they're are practically done before or during the join (this might be different for some conditions in Outer joins).
 
Spool is calculated on a user level, not on session level.
Each queriy might fit into the user's spool, but could fail if the same user runs multiple queries in different sessions concurrently.

Dieter

princykoshy 3 posts Joined 12/13
19 Dec 2013

these queries are triggered from business objects reports, which uses a generic id, that has a high spool allocated. there aren't many users running these reports simultaneously.
The query has around 15 tables joined and around 10 conditions applied.

dnoeth 4628 posts Joined 11/04
19 Dec 2013

You should check the spool usage of an individual query based on QueryLog. 
Those functional users like BO normally get an insane amount of spool to be able to run multiple reports in parallel. What spool size is assigned to it and what's your system space?
If there are too many queries in parallel you might implement a throttle using TDWM.

Dieter

You must sign in to leave a comment.