As far as i know Teradata BTET Mode implicitly commits and ANSI needs an explicit commit.
Are AWTs required for commit ? Or if they are required are they required only for ANSI mode ?
I am facing an issue on system where AWTs were exhausted and going through the DBQL logs i found out a lot of COMMIT WORK statements which were executed with changing default database command (i.e. Database XYZ; COMMIT WORK;).
Yes, AWTs are required for a COMMIT operation. They are required regardless of whether it is an implicit or explicit commit. AWTs are required on each AMP that participates in the request. Eg an all-AMP request requires AWTs on each AMP. A single-AMP request requires an AWT on only the one AMP (or two AMPs if it updates data that is fallback protected.
The COMMIT operations are not the cause of your AWT exhaustion. There are AWTs that are reserved specifically for COMMIT and ABORT operations, kept separate from AWTs for normal request work.
Better places to look are total concurrent queries and concurrent utility operations.
Also exhaustion is not automatically bad. If it happens temporarily or comes and goes, then it is normal for a heavy workload.
Thanks Tod. I got it now.
Unfortunately, in our case, system was in flow control situation for an hour and was almost unavilable for use.
Even the sessions limit was reached.
Need to look closely at the concurrency of all work on the system then, queries and utilities. It may be necessary to put some throttles to smooth very high arrival rates of work.
Throttles are there but only at Workload level for Timeshare based. After some investigation it was found that this was an usual surge sent on system which eventually caused session limit to be reached. That unusual surge belonged to a tactical workload and since tactical workloads should not be throttled, the surge eventually started using resources eventually causing system to go in a non-responding state.
An optimized tactical workload (single AMP ops primarily) should flow through quickly and take up few AWTs. It is not indicated to throttle these. But if there are allAMP or longer running work mixed in the tactical stream, it might indeed be appropriate to throttle this work. And generally even a high load of tactical queries will not consume all the resources of the system. So a good look at the other concurrent work and it's consumption of resources is indicated. Also a good look to see if the work was blocked by something - eg a locking issue stacking up a lot of work and not allowing it to complete.