Problem:
Query failed with Error code: 2646 No more spool space in %DBID
Cause:
The following reason the 2646 error can occur:
1. The user spool reaches the maximum limit allocated for the user. The following reason the spool might reach the maximum limit
1.1. The user may have insufficient spool space to run a query
1.2. Spool space allocated to user so if multiple people sign in on using same user ID and running sql that takes spool then the last request may run out of spool space because of excessive request of another session using the same User ID.
1.3. Phantom Spool space (When the DBC.DatabaseSpace table is not properly updated to reflect current and actual space usage that cause the phantom spool)
2. Spool has been exhausted on a single AMP caused by an extremely skewed query. Following reason, the query can be extremely skewed:
2.1. If a query become skewed recently and taking lots of spool space but was not skewed earlier then it can be for not to have updated statistics. Check the statistics are up to date
2.2. The query can be skewed usually for following reason:
2.2.1. Skewed table e.g Bad choice of PI data is skewed
2.2.2. Bad execution plan
2.2.3. Bad data model (normalization, data types, PI etc)
2.2.4. Too many joins
2.2.5. Hash Collision
Solution:
The following way you can start investigation of the root cause of problem:
Reset the pick spool of user:
lock table dbc.dbase for access
update dbc.databasespace
set peakspoolspace = 0
where tableid = '000000000000'xb
and databaseid = (
sel databaseid
from dbc.dbase
where databasenamei = '<User Name>');
Check if the user reaches the maximum spool limit from the Peak Spool and maximum spool information.
SELECT DataBaseName (title 'UserName', format 'x(20)')
,CAST(SUM(MaxSpool)/1024.00/1024.00/1024.00 as DECIMAL(6,2)) as MAX_SPOOL_GB
,CAST(SUM(PeakSpool)/1024.00/1024.00/1024.00 as DECIMAL(6,2)) as Peak_Spool_GB
,CAST(AVG(PeakSpool)/1024.00/1024.00/1024.00 as DECIMAL(6,2)) as AMP_AAVG_GB
,CAST(MIN(PeakSpool)/1024.00/1024.00/1024.00 as DECIMAL(6,2)) as AMP_MIN_GB
,CAST(MAX(PeakSpool)/1024.00/1024.00/1024.00 as DECIMAL(6,2)) as AMP_MAX_GB
,(100 - (AVG(PeakSpool)/nullifzero(MAX(PeakSpool)*100)))(format 'zzz,zzz,999', title 'SkewFactor')
FROM Dbc.DiskSpace
GROUP BY 1 ORDER BY 1
WHERE DataBaseName = '<User name>';
Example Output:
UserName MAX_SPOOL_GB Peak_Spool_GB AMP_AAVG_GB AMP_MIN_GB AMP_MAX_GB SkewFactor
-----------------------------------------------------------------------------------------------------------------------------------------------------------
USER_00001 5120.00 906.42 3.78 3.53 21.33 99.99822974789029
If the peak spool reaches the maximum limit, then increase the spool limit of the user and if the peak spool is much lower than the maximum spool then checks the skew factor. If the skew factor is high, then query is failing for skewness.
Check if the query failing for Phantom spool space or not. To get more information please visit the following link:
http://www.nazmulhuda.info/2646-no-more-spool-space-for-phantom-spool-space
Before you decide to increase the spool space if the user peak spool space reaches the maximum limit, check if there are multiple people sing in on using same user and takes spool by requesting excessive request using the following query
SELECT othq.*
FROM
(
SELECT *
FROM PDCRINFO.DBQLogTbl_Hst
WHERE QueryID = <Query ID> --Query ID of Failed Spool query
-- and ErrorCode=2646
and logdate='2019-03-05' -- Date
) as spq
INNER JOIN PDCRINFO.DBQLogTbl_Hst as othq
ON spq.LogDate = othq.LogDate
AND spq.Username = othq.Username
AND spq.SessionID <> othq.SessionID
AND spq.StartTime < spq.LastRespTime
AND othq.StartTime < othq.LastRespTime
AND PERIOD(othq.StartTime,othq.LastRespTime) OVERLAPS PERIOD(spq.StartTime,spq.LastRespTime)
AND othq.AMPCPUTime > 1000
;
If the user peak spool space reaches the maximum limit and it’s not because of excessive request or Phantom Spool space or skewness, then following way you can increase the spool limit for the user:
# Run the following query to check whether the user has spool space limited through a profile:
SELECT vproc,maxspool,maxprofilespool,currentspool,peakspool
FROM dbc.diskspace
WHERE databasename = '<database or user name>'
WITH sum(maxspool), sum(maxprofilespool), sum(currentspool), sum(peakspool)
ORDER BY 1 ;
# To increase user’s spool space
MODIFY USER <user-name> AS SPOOL = xxxxx BYTES ;
# To increase profile’s spool space
MODIFY PROFILE <profile-name> AS SPOOL = xxxxx BYTES;
Note: If you increase user spool space but the user using profile then the spool space will be override by the value that configured in the profile.
If the query is skewed check the following way if its for insufficient stats collection or not. If its skewed for insufficient stats collection then collect the statistics.
diagnostic helpstats on for session;
explain
<your query>;
If the query has good statistics but it is skewed or taking lots of spool space, then identify the root cause by examining the explain plan of the query and tune it.