Re: Spool space usage

Strike "may underestimate" from my previous post and make that "almost certainly understates" unless you use Collection Algorithm 3.

The "end of step" value is also after any "last use" spools from prior steps have been released. This results in the SpoolUsage often being significantly less than PeakSpool.

For example, if the first step is a Retrieve that creates a 3GB spool, the second step is a Retrieve that creates a 2GB spool, the third joins the first two spools to produce a 4GB result (and this is the last use for both input spools), DBQL would show 3GB after the first step, 5GB after the second, and 4GB after the third. But the actual peak during step 3 would be 9GB while both input and output spools were present.

Re: Spool space usage

Not necessarily. If you are using DBQL Collection Algorithm 3 then SpoolUsage should hold the peak value during the step, and for a query run in isolation ought to match space accounting PeakSpool. Otherwise, SpoolUsage in StepTbl is the value at the end of the step which might be significantly less - for example, intermediate "spill to disk" files used for sorting would not be included. LogTbl should match the max of the StepTbl values (regardless of collection algorithm).

So using DBQL SpoolUsage may underestimate true PeakSpool (unless you change Collection Algorithm, either the default in dbscontrol or override in all relevant DBQL rules). But even with the old collection algorithm, SpoolUsage values may be good enough as a basis for relative ranking to identify candidates for improvement.

Re: Spool space usage

Strike "may underestimate" from my previous post and make that "almost certainly understates" unless you use Collection Algorithm 3.

The "end of step" value is also after any "last use" spools from prior steps have been released. This results in the SpoolUsage often being significantly less than PeakSpool.

For example, if the first step is a Retrieve that creates a 3GB spool, the second step is a Retrieve that creates a 2GB spool, the third joins the first two spools to produce a 4GB result (and this is the last use for both input spools), DBQL would show 3GB after the first step, 5GB after the second, and 4GB after the third. But the actual peak during step 3 would be 9GB while both input and output spools were present.