"Idle transactions" or "Open transactions" waiting for a long time (Long Running transactions)

"idle in transaction" means that someone did a "begin", but didn't issuea "commit" or "rollback" yet. It is often a sign of bad application design and you should contact the application developers. Since open transactions may hold locks on tables, the whole application may stop unexpectedly if transactions are left open.


Another possibility is that you've just got a huge workload, e.g. lots of concurrent access to the application so that it has to perform a lot of work, but then you should see SELECT/INSERT/UPDATE/etc. as well, not only "idle in transaction".


Also idle transactions may cause "ora-01555 snapshot too old" errors.So we must find the cause of long running transactions


· We can find the idle transaction with following sql
SELECT
s.username,s.sid,s.serial#,t.start_time,
trunc((sysdate-to_date(T.start_time,'MM/DD/YY HH24:MI:SS'))*24*60) idle_time_in_min, s.status
FROM V$session s, V$transaction t, V$rollstat r
WHERE s.saddr=t.ses_addr
and t.xidusn=r.usn
-- and (s.username like 'DS%' or s.username like 'TF%' or s.username ='DBQUERY')
and trunc((sysdate-to_date(T.start_time,'MM/DD/YY HH24:MI:SS'))*24*60) >10
order by t.start_time;



· also we can write a script that will detect idle_transactions(status<>ACTIVE) and kill then Since killing a transation is a dangerous operation.You must define the users and their spesific idle time accurately .It can run from crontab




#!/bin/ksh

hosttype=`uname`
if [ "$hosttype" == "SunOS" ]
then
userid=`/usr/xpg4/bin/id -u -n`
else
userid=`/usr/bin/id -u -n`
fi
. ~$userid/.profile


MAX_DS_IDLE_TIME=60
MAX_EBANK_IDLE_TIME=500
MAX_DB_IDLE_TIME=500
MAX_BS_IDLE_TIME=30
OUT_FILE=$HOME/mntdir/kill_discoverer_transaction.out
OUT_FILE2=$HOME/mntdir/kill_discoverer_transaction.out2
LOG_FILE=$HOME/logdir/kill_discoverer_transaction.log

sqlplus -s / > $OUT_FILE2 <set arraysize 1
set linesize 1500
set pagesize 0
select 'MAKEGREP',s.sid'#'s.serial#'#'s.username'#' TRUNC((SYSDATE-TO_DATE(T.START_TIME,'MM\/DD\/YY HH24:MI:SS'))*24*60)
FROM V\$session s, V\$transaction t, V\$rollstat r
WHERE s.saddr=t.ses_addr AND t.xidusn=r.usn and
(((s.username like 'DS%' or s.username like 'TF%' ) AND TRUNC((SYSDATE-TO_DATE(T.START_TIME,'MM/DD/YY HH24:MI:SS'))*24*60)>$MAX_DS_IDLE_TIME) or
(s.username='EBANK_N' and TRUNC((SYSDATE-TO_DATE(T.START_TIME,'MM/DD/YY HH24:MI:SS'))*24*60)>$MAX_EBANK_IDLE_TIME) or
(s.username like 'DB%' and TRUNC((SYSDATE-TO_DATE(T.START_TIME,'MM/DD/YY HH24:MI:SS'))*24*60)>$MAX_DB_IDLE_TIME ) or
((s.username='DBQUERY' or s.username like 'BS%') and TRUNC((SYSDATE-TO_DATE(T.START_TIME,'MM/DD/YY HH24:MI:SS'))*24*60)>$MAX_BS_IDLE_TIME ))
and s.status in ('INACTIVE','SNIPED');
exit;
EOF



cat $OUT_FILE2grep MAKEGREPawk {'print $2'}>$OUT_FILE

date >> $LOG_FILE
for l in `cat $OUT_FILE `
do
SID=`echo $lcut -d# -f1`
SERIAL=`echo $lcut -d# -f2`
USERNAME=`echo $lcut -d# -f3`
IDLE_TIME=`echo $lcut -d# -f4`

echo "$USERNAME ($SID,$SERIAL) is idle for $IDLE_TIME munites so It will be killed "tee -a $LOG_FILE
sqlplus -s / >>$LOG_FILE << EOF
ALTER SYSTEM KILL SESSION '$SID,$SERIAL';
exit;
EOF
done