Spool space taken by Query


Spool space taken by Query


Is there any way to estimate the spool space that will be taken my query to run.

Thanks & Regards,


Senior Supporter

Re: Spool space taken by Query

I think you will not get good estimates as it depends from many things like compression, number of vproc, number of rows, the actual plan etc.

explain gives you estimates on the estimated rows of each spool file. If estimates are good these might give you a best proxi.

If you can figure out which cols are affected in each spool you can sum up the byte lengths and multipy the number of estimated rows. Checking the lifetime of the different spool files might give you an upper limit.

But as mentioned above - a rather difficult business... 

Re: Spool space taken by Query

Thanks for the reply,

One more thing like from table DBQLogTbl we can get how much spool was taken by query, Is there any table where spool per amp is stored which was taken by query

Junior Contributor

Re: Spool space taken by Query

QryLogSteps has some columns about spool usage on step level including spool size for the max/min AMP.