All Forums Database
21 Nov 2012
Spool Release

If a query is utilizing many spools are they released only when the query ends? for example if my query is using till spool20
then the sum of all the 20 spools is utilized till the query execution finishes even though the result set may not be so big ?
How is spool usage when explain plan reads - "execute the following steps in parallel."
please let me know at the earliest

Qaisar Kiani 337 posts Joined 11/05
21 Nov 2012

Spool falls into three categories:
VOLATILE SPOOL - content is retained until
* Transaction completes (unless the table was created with  ON COMMIT PRESERVE ROW)
* Table is dropped manually during the session
* Session ends
* Teradata RDBMS resets

INTERMEDIATE SPOOL - whose results are retained until no longer needed. You can determine when intermediate spool is flushed by examining the output of an EXPLAIN.
Note: The first step performed after intermediate spool has been flushed is designated “Last Use.”

OUTPUT SPOOL - Output spool results are the final information returned for a query or the rows updated within, inserted into, or deleted from a base table. The length of time output spool is retained depends on the subsystem and various system conditions

21 Nov 2012

Thanks for the insight -
so if i were to estimate the total spool a query might require at each step
then will it be appropriate to add up all the spools reported in the explain plan till that particular step and
subtract the ones which have “Last Use”  till that step ?
looking for your valuable inputs also can you suggest a source where i can read more about these

Qaisar Kiani 337 posts Joined 11/05
22 Nov 2012

Yes, I think that should give you a fair idea of how much spool the query will require.

Qaisar Kiani 337 posts Joined 11/05
22 Nov 2012

The capacity planning section should get you started...

22 Nov 2012

Thanks Aftab

You must sign in to leave a comment.