All Forums General
Bhumeet 3 posts Joined 01/12
27 Jan 2012
Spool space taken by Query

Hi,

 

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

 

Thanks & Regards,

Bhumeet

ulrich 816 posts Joined 09/09
27 Jan 2012

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... 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Bhumeet 3 posts Joined 01/12
27 Jan 2012

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

dnoeth 4628 posts Joined 11/04
27 Jan 2012

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

Dieter

Dieter

You must sign in to leave a comment.