Subject: SQL: MONITORING SESSION IDLE_TIMES
Last Revision Date: 09 January 1996
Author:SMASUD
-------------------------------------------------------------------------------
Monitoring Session Idle_Times
=========
Abstract:
=========
This bulletin talks about monitoring session idle_times and provides
a query against the data dictionary to obtain session specific idle_time
related information.
Currently, there is no direct way of querying the data dictionary and finding
out the time for which a certain session has been idle or inactive.
But the database does provide ways to collect this information.
This information is usually very important for the DBAs, as idle sessions
consume resources and hence affect performance.
A session is considered to be inactive or idle when the connection with the
database is still established but there is no communication going on between
the client and the server. In this case, client refers to any tool or
application that allows you to connect to the RDBMS.
This bulletin provides a way of extracting this information from the
data dictionary.
=============
Requirements:
=============
In order to monitor idle times successfully, the following
steps must be carried out.
(1) Set the init.ora parameter RESOURCE_LIMIT to TRUE to enable
the enforcement of resource limits.
(2) Shutdown and startup again to enable the changes made to the
init.ora file.
(3) Create a user_profile and set the idle_time parameter
to a certain value (minutes).
(4) Make this user_profile the default profile of the user
or users whose sessions are to be monitored.
NOTE: Different users may have different user_profiles as
their default profiles.
==============================
Relevent Data Dictionary Views
==============================
V$SESSTAT
V$TIMER
V$STATNAME
=======
Method:
=======
Run the following script from the SYS account in sqlplus:
REM****************************************************************************
REM****************************************************************************
REM IDLE_TIME MONITOR SCRIPT
REM****************************************************************************
REM****************************************************************************
REM
REM
REM THIS SCRIPT PROVIDES AN EASY WAY FOR THE DBA TO MONITOR
REM SESSION IDLE TIMES. THE OUTPUT OF THE SCRIPT SHOWS THE
REM SID FOR EACH SESSION RUNNING AGAINST THE DATABASE, THE
REM LAST TIME THIS SESSION WAS ACTIVE (INCLUDING DATE AND TIME),
REM THE CURRENT TIME AND THE AMOUNT OF TIME (IN SECONDS AS WELL
REM AS MINUTES) ELAPSED SINCE THE SESSION BECAME INACTIVE.
REM THIS SCRIPT IS WRITTEN TO BE RUN FROM THE SYS ACCOUNT
REM IN SQLPLUS.
column sid format 999
column last format a22 heading "Last non-idle time"
column curr format a22 heading "Current time"
column secs format 99999999.999 heading "idle-time |(seconds)"
column mins format 999999.99999 heading "idle-time |(minutes)"
select sid, to_char((sysdate - (hsecs - value)/(100*60*60*24)),
'dd-mon-yy hh:mi:ss') last, to_char(sysdate, 'dd-mon-yy hh:mi:ss') curr,
(hsecs - value)/(100) secs, (hsecs - value)/(100*60) mins
from v$timer, v$sesstat
where statistic# = (select statistic# from v$statname
where name = 'process last non-idle time');
REM****************************************************************************
REM****************************************************************************
REM****************************************************************************
=========
EXAMPLES:
=========
SQL> @idle
idle-time idle-time
SID Last non-idle time Current time (seconds) (minutes)
---- ---------------------- ---------------------- ------------- -------------
1 01-jul-94 12:21:52 06-nov-94 03:38:10 11070977.580 184516.29300
2 01-jul-94 12:21:52 06-nov-94 03:38:10 11070977.620 184516.29367
3 01-jul-94 12:21:52 06-nov-94 03:38:10 11070977.650 184516.29417
4 01-jul-94 12:21:52 06-nov-94 03:38:10 11070977.680 184516.29467
5 01-jul-94 12:21:52 06-nov-94 03:38:10 11070977.710 184516.29517
6 06-nov-94 03:38:07 06-nov-94 03:38:10 3.000 .05000
7 06-nov-94 01:06:27 06-nov-94 03:38:10 9102.970 151.71617
9 06-nov-94 03:36:56 06-nov-94 03:38:10 73.620 1.22700
10 06-nov-94 03:37:49 06-nov-94 03:38:10 20.910 .34850
9 rows selected.
Run the query again after a few seconds:
SQL> @idle
idle-time idle-time
SID Last non-idle time Current time (seconds) (minutes)
---- ---------------------- ---------------------- ------------- -------------
1 01-jul-94 12:21:53 06-nov-94 03:40:15 11071101.930 184518.36550
2 01-jul-94 12:21:53 06-nov-94 03:40:15 11071101.970 184518.36617
3 01-jul-94 12:21:53 06-nov-94 03:40:15 11071102.000 184518.36667
4 01-jul-94 12:21:53 06-nov-94 03:40:15 11071102.020 184518.36700
5 01-jul-94 12:21:53 06-nov-94 03:40:15 11071102.050 184518.36750
6 06-nov-94 03:40:12 06-nov-94 03:40:15 3.400 .05667
7 06-nov-94 01:06:28 06-nov-94 03:40:15 9227.320 153.78867
9 06-nov-94 03:36:57 06-nov-94 03:40:15 197.970 3.29950
10 06-nov-94 03:37:50 06-nov-94 03:40:15 145.260 2.42100
9 rows selected.
Run the query again after a few seconds:
SQL> @idle
idle-time idle-time
SID Last non-idle time Current time (seconds) (minutes)
---- ---------------------- ---------------------- ------------- -------------
1 01-jul-94 12:21:53 06-nov-94 03:40:51 11071138.170 184518.96950
2 01-jul-94 12:21:53 06-nov-94 03:40:51 11071138.210 184518.97017
3 01-jul-94 12:21:53 06-nov-94 03:40:51 11071138.240 184518.97067
4 01-jul-94 12:21:53 06-nov-94 03:40:51 11071138.270 184518.97117
5 01-jul-94 12:21:53 06-nov-94 03:40:51 11071138.300 184518.97167
6 06-nov-94 03:40:11 06-nov-94 03:40:51 39.650 .66083
7 06-nov-94 01:06:27 06-nov-94 03:40:51 9263.570 154.39283
9 06-nov-94 03:36:57 06-nov-94 03:40:51 234.220 3.90367
10 06-nov-94 03:37:49 06-nov-94 03:40:51 181.510 3.02517
9 rows selected.
When the RESOURCE_LIMIT parameter is set to true, then for each user who
has a profile with idle_time defined, oracle records the entry
of V$TIMER as soon as a session of that user becomes inactive. This
recorded value of V$TIMER can later be used to find out the exact amount
of time for which the session has been idle. This is exactly what the
above query and examples demonstrate.
Let us look at SID = 7 in the result of the last query.
The "last non-idle time" for this session is also the time
when this session became inactive, which happened at 06-nov-94 01:06:27.
From the "Current time" column, we know what the current time is.
Hence, it is easy to calculate the time for which the session has been
idle. This information is available from columns 4 and 5.
Notice that V$TIMER starts at 0 when the database is created.
The SIDs 1 to 5 are not relevent in this case as they have their
idle_time timers stopped at t=0, hence they appear to be idle since
the creation of the database. These entries correspond to the
background processes.
NOTE:
----
A session will continue to show as idle even after the idle_time
for that user, as specified in that user's profile, has expired.
When the user attempts to run a transaction against the database after the
idle_time has expired, the database will disconnect the user by terminating
the session. After this, the session will no longer show in the output
of the above query.