I m getting a spool space issue several times in my system for different reports .
But upon checking the system, i notice that there is no other transaction that is running in parellel consuming more spool space. Also the report that is failing is not consuming more spool.
Can you please guide what can be the reason for "no more spool space" issue?
Is it possible for a single AMP to move out of spool ?
The 'no more spool space' (2646) error is when the spool limit for a specific user is reached on a single AMP. What else is running in the system is largely irrelevant - unless you have other queries being run under the same user.
Technically, this error is always due to the limit being reached on a single AMP.
This is usually caused by a spool file being skewed, which (in turn) is usually caused by a lack of statistics or (sometimes) out of date stats. If the failing query has been captured in DBQL step table compare the actual row counts to estimated and look for steps with large number of actual rows and a large delta between actual and estimated. That should point you you where you need to do more investigation.
Thanks a lot! for your guidance .
1. Stats are upto date
2.On verifying the view point . There is a single step which is delaying for a longer time.
"We do an All-AMPs JOIN step from Spool 2867 (Last Use) by way of an all-rows scan, which is joined to Spool 2868. Spool 2867 and Spool 2868 are left outer joined using ahash join of 2 partitions. The result goes into Spool 2854, which is built locally on the AMPs. "
Kindly guide how to proceed the analysis/what might be the reason for the delay of this step. With this can we assume this step consuming most of the spool and making the query to fail.
Which AMP consumed more spool? You might be able to guess by using column "dbc.dbqlsteptbl.maxspoolampnumber" if you can find this query in the DBQL tables, but typically it doesn't matter which AMP it is- typically this will not help you solve the issue.
(One comment on this) If you do find out which AMP and it's the highest numbered AMP in the system then that is usually due to NULL values in the spool file distribution column(s), but that still doesn't directly solve the issue.
You say that 'stats are up to date'. This is good, but do you have all the stats that this query needs?
Are the estimated row counts for these spool files about right? If not then a hash join could be very problematic. If you have DBQL data for this query look in the dbqlsteptbl rows. if you have the full VP output for the query then compare the actual vs. estimated row count numbers.
Start with that and see where we get to.