I understand that idle sessions have minimal impac TD and only thing may get impact are locks.so I have 2 questions
-> we have workload throttle limit so no more than 5 users can be active at a time and all beyond that limit will go to delay queue.Now as a recent scenario we had hugh delay queues for this workload.As per TD analyst everything except delay(including idle) will be count as active for that workoad throtte.So we have 5 sessions(3 blocked and 2 idle) and 120 in delayed.As per Analyst
Per TDWM throttle, it will have only 2 states ACTIVE and DELAY.
The sessions which are not in Delay will counted as ACTIVE.
-> Is it best practice to abort idle session.Why and after how long to kill an idle session
Idle or Responding sessions do not count against the throttle limit. Blocked queries count only if they are blocked after they actually start executing (as opposed to being blocked at the "First, we lock..." step before execution starts).
Thanks Fred!!Could you also suggest on idle session,we should abort these.if yes,what is ideal time period after which we should abort such sessions
I would first watch to see if the number of idle sessions is increasing significantly over time.
The main thing is that you don't want to force off sessions just because they are idle at the moment you look. You need to look at the length of time they have been idle since the last request. Also, if you have a middle-tier server that uses a session pool, forcing those sessions off might be a bad idea.
If you do decide to abort idle sessions or automate such aborts (e.g. using the Viewpoint Alerts mechanism), I would start out being very conservative - e.g. only consider things that have been idle for multiple days.
Just got confused with what TD shows..I system takes all users to delay even when all running in that workload is in block state.I see these 5 block session was blocked by a session in delay.This delay session try to delete data from table.all block sessions trying to read from same table.This particular delay session was in parsing or active state if i rewind before delay.If i see session details from dbql thousands of query ids coming but same sql from SAS.Now all these session belong to same workload.Our workload throttle allow 5 users at a time but why everything going in block and than delay for same workload and delay queries details is WORKLOAD_DELAYED_QUERY.Please help.Our system was bottleneck today as well due to this
If you have sessions that are issuing multiple SQL requests before committing (e.g. ANSI mode with AutoCommit false, or BEGIN TRANSACTION in Teradata mode), you can get into a situation where a subsequent request within a transaction is delayed and/or causes a deadlock. Generally, it's best to revise the behavior of the application to avoid gradually accumulating locks and holding them through multiple requests.
There is an option in TASM (under General / Other) to not delay queries holding locks. But note that means the throttle limit may be exceeded, and there is a possibility that you may still encounter a deadlock instead of a delay.