Does anyone have idea about what IDLE session can impact on the System?
One of my Application id loads the data /reads the data and after that most of the sessions are sitting in IDLE state.(Its not holding any locks on any table)Just simply holds the session from TERADATA side, where as the user loginto 3rd part tool.
As this IDLE sessions doesn't fall under Session limit, what could be the effect with these IDDLE sessions on teradata?
If anyone has any specific doc that states about IDLE sessions please pass on.
Appreciate help in Advance.
The impact is very minor. That said if you would like to clean them up you can setup an alert in viewpoint to run a process to do so or could simply schedule a process to do the clean up regularly.
Below is a proc I put together to kill active sessions based on spool usage and duration and it could be easily modified to clean up idle sessions:
REPLACE PROCEDURE abort_spool_duration(
IN usrname VARCHAR(100),
IN duration_secs INT,
IN spool_tb DECIMAL(10,2),
OUT sesisons_aborted INT,
OUT status INTEGER)
DECLARE v_sesisons_aborted INT DEFAULT 0;
SELECT SUM( AbortSessions(A.Hostid,A.USERNAME,A.SessionNo,'N','N'))
TABLE (MonitorSession(-1,'*',0)) AS A
ReqStartTime <> '0000/00/00 00:00:00.00'
CURRENT_TIMESTAMP >= modify_secs_tmstmp( (ReqStartTime (TIMESTAMP(6),FORMAT 'YYYY/MM/DDBHH:MI:SS')),:duration_secs,1)
(USERNAME = :usrname
:usrname IS NULL)
(reqspool/1024**4) >= ZEROIFNULL(:spool_tb)
You will need this udf to use the above:
REPLACE FUNCTION SYSLIB.modify_secs_tmstmp
( tmstmp TIMESTAMP(6),
ON NULL INPUT
INLINE TYPE 1
WHEN math_type='1' THEN (((tmstmp + CAST( (secs / 86400) AS INTERVAL DAY)) + CAST( (secs MOD 86400) /3600 AS INTERVAL HOUR)) + CAST((secs MOD 3600) /60 AS INTERVAL MINUTE))+ CAST((secs MOD 60) AS INTERVAL SECOND)
WHEN math_type='2' THEN (((tmstmp - CAST( (secs / 86400) AS INTERVAL DAY)) - CAST( (secs MOD 86400) /3600 AS INTERVAL HOUR)) - CAST((secs MOD 3600) /60 AS INTERVAL MINUTE)) - CAST((secs MOD 60) AS INTERVAL SECOND)
Here is your answer.
Subj: Re: An Idle question
From: Walter, Todd A
There is no resource impact on Teradata if the sessions are truly idle. No tasks are held. Minimal memory is used to remember the session context. No cost to have the session logged on.
If a user leaves a lock behind though, then there could be a negative effect on other users or jobs. eg "BT; Update Customer Where state='CA'; go home for the day" leaves a write lock outstanding on Customer until they come back and ET; This plus the security issue mentioned in another messages causes most people to set an idle timeout for sessions.