Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-285

How to determine idle sessions with open transactions

    Details

    • Type: How To
    • Status: Published
    • Affects Version/s: EXASOL 6.0.0, Exasol 6.1.0, EXASolution 5.0.0, Exasol 6.2.x
    • Fix Version/s: None
    • Component/s: EXASolution
    • Solution:
      Hide

      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.

      Show
      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.
    • Category 1:
      Database Administration - Transactions
    • Category 2:
      Database Administration - Profiling

      Attachments

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated: