09-07-2020 05:09 PM - edited 30-07-2020 09:26 PM
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.
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.
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
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In