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:
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.
How To