I'm facing spool failures due to concurrency of the queries , for an application ( Business objects - reporting queries)
We have a limit of 5 queries can run concurrenty in TASM.
When one query is spooling out ( 13TB allocated at profile level) , at times, some of the queries which runs parallelly are also getting spooled out at the time when bad query spooled out.
Most of the time, this query is highly skewed. but when spooled out, tuned queries also getting aborted with spool issue.
Generally when this situation will happen? is it happening because spool space is not releasing immediately after the spool out ?
We have implemented the TASM rules based on the estimated processing times. is there any things it can be resolved through workload management?
Thank you very much for the prompt response.
Unfortunately , as this is a BO application, we don't have a control over execution of the queries. Multiple users can run the adhoc queries during the business window. As a long term, as you said, we are planning to split to use multiple IDs, but with the course of time, we may fall into the same situation again as the # of users grow and create adhoc reports.
And I dont think there is a way to create a filter on the highly skewed queries ...
This also may be due to one query creating very skewed spool. Check on viewpoint if any step shows skew >90% and try to find the skewed join and tune it. So, you get spool issue either when spool is realy less and others have mentioned or when spool is skewed.
Thx ! Samir
In BO (and in most tools) it is possible to provide QueryBands to identify the user, report, ... . These are placed into the DBQL log for later analysis and are also available for use in TASM/TIWM criteria for filters, classification,... .
In Teradata 16.10, there are new space management parameters which allow one to be more flexible about space usage for skewed queries. If the query is very badly skewed, this may not help but if it is already capable of running sucessfully by itself, it may be possible to give some flexibility on the skewed use of spool.