IDLE sessions IMPACT

Database
Enthusiast

IDLE sessions IMPACT

Hi,

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.

Thanks

3 REPLIES
Teradata Employee

Re: IDLE sessions IMPACT

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)
BEGIN

DECLARE v_sesisons_aborted INT DEFAULT 0;

SELECT SUM( AbortSessions(A.Hostid,A.USERNAME,A.SessionNo,'N','N'))
INTO v_sesisons_aborted
FROM
TABLE (MonitorSession(-1,'*',0)) AS A
WHERE
ReqStartTime <> '0000/00/00 00:00:00.00'
AND
CURRENT_TIMESTAMP >= modify_secs_tmstmp( (ReqStartTime (TIMESTAMP(6),FORMAT 'YYYY/MM/DDBHH:MI:SS')),:duration_secs,1)
AND
(USERNAME = :usrname
OR
:usrname IS NULL)
AND
(reqspool/1024**4) >= ZEROIFNULL(:spool_tb)
AND
AMPState='Active';

SET sesisons_aborted=COALESCE(v_sesisons_aborted,0);

SET status=0;

END;

You will need this udf to use the above:

REPLACE FUNCTION SYSLIB.modify_secs_tmstmp
( tmstmp TIMESTAMP(6),
secs INTEGER,
math_type smallint
)
RETURNS TIMESTAMP(6)
SPECIFIC SYSLIB.modify_secs_tmstmp
RETURNS NULL
ON NULL INPUT
CONTAINS SQL
COLLATION INVOKER
INLINE TYPE 1
RETURN
(CASE
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)
END)
Enthusiast

Re: IDLE sessions IMPACT

Thank you. It would be great if someone tell me the minor impacts of the IDLE sessions.

Teradata Employee

Re: IDLE sessions IMPACT

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.

http://teradataforum.com/teradata/20030115_200416.htm