Spool space is used by user to store intermediate result of queries.They are released as and when a query finishes.
I would like to know is there any way to track the spool space usage of a particular user in the Teradata system.For example, what was the maximum and average spool
used by user 'A' today or a span of time ,say a week.I would like to do a trend analysis and assign a spool space limit to that user based on that usage pattern.
Could you please help?
For the average, you can setup a script that would run, say, every one hour or every 10 minutes, and insert-select from dbc.databases the "currentspool" field, grouped by databasename, into your history table. Then calculate average from there.
For the maximum, it would be more precise to capture the "peakspool" column. For one week analysis, make it zero at the beginning of the week, and then see how high it goes.
p.s. also take Skew into account, because spool space limit is divided equally across the AMPs. So, in reality, it would be more precise to capture maximum value across AMPs also (group by both "databasename" and "vproc" columns in the dbc.diskspace view).