All Forums Database
spidermonk 9 posts Joined 11/12
18 Nov 2012
Spool space and Temp space allocations

I'm having issues with spool space so I decided to break down my query.  Even in it's simplist form I'm getting spool space errors.
My question is does allocated temp space figure into spool space?
 
Allocated Spool - 299,999,999,952
Allocated Temp -   48,318,382,080
 
 

Qaisar Kiani 337 posts Joined 11/05
18 Nov 2012

Spool space is taken from the disk cylinders that are not being used for permanent or temporary data...
However the query spooling out needs to be revisited. Explain plan could probably help you out to deteminte the results that are consuming the big chunk of spool.

dnoeth 4628 posts Joined 11/04
19 Nov 2012

This is a query to find "phantom spool", i.e. a user who is not logged on to the system, but reported as having some spool in use. It's a problem in Teradata's internal housekeeping.
It will be fixed during the next database restart, but if this spool size causes problems (e.g. it reaches a double digit percentage of the user's assigned spool and the user runs out of spool) it can be fixed by the dba using the UPDATE SPACE utility.
In your case this shouldn't be a problem as you got 300GB spool and you probably didn't get a "out of spool on disk" error.
You should post your failing query + explain.
Following query will check for spool usage/skew:
select
  min(peakspool),
  avg(peakspool),
  max(peakspool),
  100*max(currentspool)/max(coalesce(maxprofilespool, maxspool)) as PercentInUse
from dbc.DiskSpace
where DatabaseName = user;
Dieter

Dieter

Qaisar Kiani 337 posts Joined 11/05
19 Nov 2012

Can't disagree with Dieter :)

koushiksaha83 3 posts Joined 12/13
30 Jan 2014

Any idea how to find available temp space in teradata?

dnoeth 4628 posts Joined 11/04
30 Jan 2014

On a User level?

SELECT TempSpace FROM dbc.UsersV WHERE UserName = USER;

Or on a system level?

Temp space is similar to spool space, (almost) all currently not used perm space is available as temp.
 

Dieter

sgarlapa 88 posts Joined 03/13
30 Jan 2014

Hi Dieter,
do  Spool and tempspaces are unused permspace ?
So only the upper limits we specify for spool and temspace for the users.
Both are shared by all users if it is available under their limits. Is my understanding correct ?
 
-Sri

dnoeth 4628 posts Joined 11/04
30 Jan 2014

Hi Sri,
yep, this is correct.

Dieter

You must sign in to leave a comment.