|
| Friday, April 3, 2009 |
| Identifying Long Idle Sessions |
Sometimes users may establish a connection to the database and don’t properly
disconnect. Many organizations consider this to be a security risk. With
a script, you can retrieve session information from the database to determine
whether this is happening, and it is useful to gather as much information as
possible about the session in question.
The time threshold in the monitoring script should be customized based
on the requirements of your organization. Also, situations may occur where
certain programs are allowed to have long idle times. For example, if connection
pooling is being used for the application server, then JDBC thin client
sessions may have large idle times.
The following example script will assist in monitoring sessions that have
been idle for a user-determined amount of time:
#Script used to monitor sessions with a long idle time
#THRESHOLD is the maximum duration that an inactive session
#may remain connected to the database
THRESHOLD=$1
LOGFILE=/tmp/high_idle_$ORACLE_SID.log
sqlplus -s apps/apps << EOF
set heading off
spool $LOGFILE
select distinct '$ORACLE_SID - High Idle sessions above Threshold.'
from v\$session db_session,
v\$process process,
v\$session_wait wait
where process.addr = db_session.paddr
and db_session.sid = wait.sid
and type='USER'
and db_session.username is not null
and db_session.program not like 'JDBC%'
and last_call_et>$THRESHOLD;
-- add data to logfile
select db_session.username,
db_session.osuser,
db_session.terminal,
db_session.sid,
db_session.serial#,
process.spid,
db_session.process,
wait.event,
db_session.program,
to_char(logon_time,'dd-mm-yy hh:mi am') "LOGON",
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "IDLE"
from v\$session db_session,
v\$process process,
v\$session_wait wait
where process.addr = db_session.paddr
and db_session.sid = wait.sid
and type='USER'
CHAPTER 3 ■ MONITORING AND TROUBLESHOOTING 61
and db_session.username is not null
and db_session.program not like 'JDBC%'
and last_call_et>$THRESHOLD
order by last_call_et;
spool off
exit
EOF
RETURN_CODE=`grep "Threshold" $LOGFILE | wc -l`
if [ $RETURN_CODE -eq 0 ]
then
exit 0
else
exit 1
fi
Once sessions that have been idle for a long period of time are identified,
you should contact the user of the session and determine whether the
process should still be executing. If it should not be running, the session
should be killed. Information on killing database and operating system
sessions is provided in Chapter 6 of this guide.
Apps DBA Portal Tip :One method for resolving this problem is prevention—you can configure all database accounts to expire after a specified amount of idle time. This option should only be
used for accounts that are not used to run the application code.Labels: Identifying Long Idle Sessions |
posted by Srinivasan .R @ 3:49 AM  |
|
|
|
|
|
