How to determine idle sessions with open transactions

[exa-John]

Background

Idle sessions with open transactions may have some negative implications:
First, there might be more transaction conflicts in parallel sessions, most likely if the idle session has an open write transaction.
Second, the database garbage collection might not be able to reclaim older object versions though increasing storage space usage and backup sizes.

How to determine idle sessions with open transactions

You can use the following SQL statement to add locking information to your session system tables using the EXA_SQL_LAST_DAY statistics:

with
	EXA_SQL as (
		select
			SESSION_ID,
			STMT_ID,
			COMMAND_CLASS,
			COMMAND_NAME,
			SUCCESS
		from
			--EXA_DBA_AUDIT_SQL                   -- delivers more exact results (if available)
			EXA_SQL_LAST_DAY
		where
			SESSION_ID in (select SESSION_ID from EXA_DBA_SESSIONS)
	),
	SESSION_RISKS as (
		select
			SESSION_ID,
			HAS_LOCKS
		from
			(
				select
					SESSION_ID,
					decode(
						greatest(CURRENT_ACCESS, LAST_ACCESS),
						0,
						'NONE',
						1,
						'READ LOCKS',
						2,
						'WRITE LOCKS'
					) HAS_LOCKS
				from
					(
						select
							S.SESSION_ID,
							case
								when
									(S.STATUS not in ('IDLE', 'DISCONNECTED')) OR
									(
										S.COMMAND_NAME not in ('COMMIT', 'ROLLBACK', 'NOT SPECIFIED')
									)
								then
									case
										when
											S.COMMAND_NAME in (
												'SELECT', 'DESCRIBE', 'OPEN SCHEMA', 'CLOSE SCHEMA', 'FLUSH STATISTICS', 'EXECUTE SCRIPT'
											)
										then
											1
										else
											2
									end
								else
									0
							end CURRENT_ACCESS,
							zeroifnull(A.ACCESS) LAST_ACCESS
						from
								EXA_DBA_SESSIONS S
							left join
								(
									select
										SESSION_ID,
										max(ACCESS) ACCESS
									FROM
										(
											select
												SESSION_ID,
												case
													when
														(
															COMMAND_NAME not in ('COMMIT', 'ROLLBACK', 'NOT SPECIFIED')
														)
													then
														case
															when
																COMMAND_NAME in (
																	'SELECT',
																	'DESCRIBE',
																	'OPEN SCHEMA',
																	'CLOSE SCHEMA',
																	'FLUSH STATISTICS',
																	'EXECUTE SCRIPT'
																)
															then
																1
															else
																2
														end
													else
														0
												end ACCESS
											from
												EXA_SQL C
											where
												C.COMMAND_CLASS <> 'TRANSACTION' and
												SUCCESS and
												not exists(
													select
														*
													from
														EXA_SQL E
													where
														E.SESSION_ID = C.SESSION_ID and
														E.STMT_ID > C.STMT_ID and
														E.COMMAND_CLASS = 'TRANSACTION'
												)
										)
									group by
										SESSION_ID
								) A
							on
								S.SESSION_ID = A.SESSION_ID
					)
				where
					SESSION_ID <> 4
			)
	)
select
	HAS_LOCKS,
	case
		when
			DURATION > '1:00:00' and
			STATUS = 'IDLE'
		then
			decode(
				HAS_LOCKS,
				'READ LOCKS',
				'CRITICAL',
				'WRITE LOCKS',
				'VERY CRITICAL',
				NULL
			)
	end EVALUATION,
	S.*
from
		EXA_DBA_SESSIONS S
	left join
		SESSION_RISKS R
	on
		(S.SESSION_ID = R.SESSION_ID)
order by
	EVALUATION desc,
	LOGIN_TIME;

The query determines the approximate lock status of each session and does a risk evaluation on the basis of idle times and open transaction locks. Session with open transactions being idle for over an hour typically start to cause stated effects.

In the following example the session

HAS_LOCKS	EVALUATION	SESSION_ID	USER_NAME	STATUS	COMMAND_NAME	DURATION	...
		4	SYS	IDLE	NOT SPECIFIED	0:00:02	...
NONE		1505059440358261249	GUEST	IDLE	NOT SPECIFIED	3:28:20	...
READ LOCKS		1505059440023663104	ADMIN	EXECUTE SQL	SELECT	0:00:01	...
WRITE LOCKS		1505061190567112340	LOADER	EXECUTE SQL	MERGE	0:11:02	...
READ LOCKS	CRITICAL	1505059543549212162	ANALYST	IDLE	NOT SPECIFIED	1:26:19	...
WRITE LOCKS	VERY CRITICAL	1505061190568112648	TESTER	IDLE	NOT SPECIFIED	2:10:02	...

The sessions 1505061190568112648 and 1505059543549212162 in the example have been idle for some time but did not finish their open transactions.

Additional References

https://docs.exasol.com/database_concepts/session_management.htm?Highlight=idle%20sessions

https://docs.exasol.com/sql_references/metadata/metadata_system_tables.htm?Highlight=idle%20sessions

 https://www.exasol.com/support/browse/EXASOL-2415

https://www.exasol.com/support/browse/EXASOL-1330