Spool space and Temp space allocations

Database
Enthusiast

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

7 REPLIES
Enthusiast

Re: Spool space and Temp space allocations

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.

Junior Contributor

Re: Spool space and Temp space allocations

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

Enthusiast

Re: Spool space and Temp space allocations

Can't disagree with Dieter :)

Re: Spool space and Temp space allocations

Any idea how to find available temp space in teradata?

Junior Contributor

Re: Spool space and Temp space allocations

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.
Enthusiast

Re: Spool space and Temp space allocations

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

Junior Contributor

Re: Spool space and Temp space allocations

Hi Sri,

yep, this is correct.