PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

When I get an error stating 'NO MORE SPOOL SPACE IN USER' what does it mean?

Query 13) When I get an error stating ‘NO MORE SPOOL SPACE IN USER’ what does it mean?
Example:

IF user has spool space limit defined as 10GB and there are 10 AMPS. i.e. 1GB of spool space is available on each AMP under this user.
Spool space problem depends on
1. Volume of data queried/accessed
2. Index selection on table.
3. Stats on table
4. No of active session per user

If your processing data goes into one AMP which is holding only 1 GB spool then it will fail with ‘NO MORE SPOOL SPACE IN USER’. i.e. data is skewed.
Information on current user’s profile and active sessions:
Session info:
Below query gives, how many queries are running at this moment by user.

Locking row for access
SELECT* FROM dbc.sessioninfo WHERE username=’######’;

Spool space info: Below query gives, how much spool has been allocated to the user:

Locking row for access
SELECT username,spoolspace,profilename
FROM dbc.users WHERE username=’#####’;

Note:
If profilename is not null, then the spool is probably being allocated by the amount in dbc.profiles.
If profilename is null, then spool is being allocated by the amount defined in dbc.users .

SQL: locking row for access select * from dbc.Users where username=’#####’;

Approach to resolve “spool space error”:

Follow below steps to resolve the error.
These are potential causes:

1. Statistics are stale. Run HELP STATS on each table involve in SQL.
2. Verify and confirm (using step 1) that there is no other active session running too many large concurrent queries.
Note: Spool space is shared across session under the user and can cause error if there are sessions running too many large queries.
3. The data is skewed and is running out of spool on one amp. After 10 minutes, you can check PDCRDATA to get skew information.
SQL:

LOCKING ROW FOR ACCESS
SELECT T1.STARTTIME “StartTime”
, T1.FIRSTSTEPTIME “FirstStepTime”
, T1.FIRSTRESPTIME “FirstResponseTime”
, T1.USERNAME “UserName”
, T1.SESSIONID “SessionId”
, T1.QUERYID “QueryId”
, T1.PROCID “ProcessId”
, (
EXTRACT(HOUR FROM ((T1.FIRSTRESPTIME-T1.FIRSTSTEPTIME) HOUR(4) TO SECOND))
*3600 + EXTRACT(MINUTE FROM ((T1.FIRSTRESPTIME-T1.FIRSTSTEPTIME) HOUR(4) TO SECOND)) * 60 +
EXTRACT(SECOND FROM ((T1.FIRSTRESPTIME-T1.FIRSTSTEPTIME) HOUR(4) TO SECOND))
) “FirstStepProcessedTime”
, (
EXTRACT(HOUR FROM ((T1.FIRSTRESPTIME – T1.STARTTIME) HOUR(4) TO SECOND)) *3600 +
EXTRACT(MINUTE FROM ((T1.FIRSTRESPTIME – T1.STARTTIME) HOUR(4) TO SECOND)) * 60 +
EXTRACT(SECOND FROM ((T1.FIRSTRESPTIME – T1.STARTTIME) HOUR(4) TO SECOND))
) “ElapsedTime”
, (
EXTRACT(HOUR FROM ((T1.FIRSTSTEPTIME – T1.STARTTIME) HOUR(4) TO SECOND)) *3600 +
EXTRACT(MINUTE FROM ((T1.FIRSTSTEPTIME – T1.STARTTIME) HOUR(4) TO SECOND)) * 60 +
EXTRACT(SECOND FROM ((T1.FIRSTSTEPTIME – T1.STARTTIME) HOUR(4) TO SECOND))
) “GapTime”
, T1.DELAYTIME “DelayTime”
, T1.NUMOFACTIVEAMPS “NumOfActiveAmps”
, T1.TDWMALLAMPFLAG “TDWMAllAmpFlag”
, T1.TDWMESTTOTALTIME “TDWMEstTotalTime”
, T1.ESTPROCTIME “EstProcTime”
, T1.ESTRESULTROWS “EstResultRows”
, T1.ESTMAXROWCOUNT “EstMaxRowCount”
, T1.AMPCPUTIME “AMPCPUTime”
,T1.TOTALIOCOUNT “TotalIOCount”
,T1.PARSERCPUTIME “ParserCPUTime”
,T1.NUMRESULTROWS “NumResultRows”
,T1.CACHEFLAG “CacheFlag”
,ZEROIFNULL(T1.MAXAMPCPUTIME/NULLIFZERO(T1.AMPCPUTIME/(HASHAMP()+1))) “CPUSkew”
,ZEROIFNULL(T1.MAXAMPIO/NULLIFZERO(T1.TOTALIOCOUNT/(HASHAMP()+1))) “IOSkew”
, ZEROIFNULL(T1.AMPCPUTIME*1000/NULLIFZERO(T1.TOTALIOCOUNT)) “PJI”
, ZEROIFNULL(T1.TOTALIOCOUNT/NULLIFZERO(T1.AMPCPUTIME*1000)) “UII”
,T1.MAXAMPCPUTIME*(HASHAMP()+1) “ImpactCPU”
,T1.MAXAMPIO*(HASHAMP()+1) “ImpactIO”
,T1.MAXCPUAMPNUMBER “MaxCPUAmpNumber”
,T1.MAXIOAMPNUMBER “MaxIOAmpNumber”
,T1.NUMSTEPS “NumSteps”
,T1.SPOOLUSAGE “SpoolUsage”
,T1.ERRORCODE “ErrorCode”
,T1.ERRORTEXT “ErrorText”
,T1.STATEMENTTYPE “StatementType”
,T1.DEFAULTDATABASE
,T1.WDID “WDId”
,T1.FINALWDID “FinalWDId”
, CASE
WHEN CHARS(T2.SQLTEXTINFO) <= 15000
THEN SUBSTR(T2.SQLTEXTINFO,1,15000)
ELSE NULL
END “SQLTextInfo”
, CASE
WHEN CHARS(T2.SQLTEXTINFO) > 15000
THEN T2.SQLTEXTINFO
ELSE NULL
END “LongSQLTextInfo”
FROM DBC.DBQLOGTBL T1
LEFT
JOIN DBC.DBQLSQLTBL T2
ON T1.PROCID=T2.PROCID
AND T1.QUERYID=T2.QUERYID
WHERE T2.SQLROWNO=1
and T1.USERNAME=’GHHUSDWETL’ —-OR T1.SESSIONID=<#####>;
Note: Modify Index column of tables if required.
4. Analyze the volume on each tables involved in SQL.
5. Run the EXPLAIN for the query in verbose mode and collect stats on recommended columns.
6. If incorrect PIs are chosen (Skewed table) , the INSERT staments can also spool out.

diagnostic verbose explain on for session;
diagnostic helpstats on for session;

Conclusion: The above steps are to optimize existing spool space allocated. Alternatively you can also looking at allocating more spool space depending on the user query.

Add a Comment

Your email address will not be published. Required fields are marked *