Chris,
The best way of establishing whether a session is active is by using a
combination of V$SESSION.LAST_CALL_ET, V$SESSION.STATUS and
V$SESSION_WAIT.EVENT. I normally use the scripts below to determine the
status of the identified SID, and it has served me well (this has evolved
from a series of SQLs on ML and this list, so if this looks familiar, it
should :). The Last_call_et will show the time the last call (to
parse/execute) was made - if the status is ACTIVE, then I would look at the
currently excuting SQL from that session...
REM
REM Name: os_proc.sql
REM Purpose: Display the process details given a SID
REM Author: John Kanagaraj, DBSoft Inc/ Aug 2001
REM Notes: Added Session waits 11/17/02
REM
column sid_serial heading "Sid,Ser#" format a10
column spid format 999999 heading "OS Pid"
column username format a17 heading "DB/OSUser"
column status heading "Status" format a8
column program heading "Program" format a31 trunc
column last_call_et format 999.99 heading "LastCallMins"
column logon_time format a18 heading "Logon Time"
column waiting_event format a47 heading "Waiting on event + p1/p2/p3" trunc
select s.sid || ', ' || s.serial# sid_serial, p.spid,
s.username || '/' || s.osuser username, s.status,
to_char(s.logon_time, 'DD-MON-YY HH24:MI:SS') logon_time,
s.last_call_et/60 last_call_et,
w.event || ' / ' || w.p1 || ' / ' || w.p2 || ' / ' || w.p3 waiting_event,
p.program
from v$process p, v$session s, v$session_wait w
where s.paddr=p.addr and s.sid=&Oracle_SID
and w.sid = s.sid;
REM
REM Name: curr_sql.sql
REM Purpose: Display the current SQL for a given SID
REM Author: John Kanagaraj, DBSoft Inc/ Aug 2001
REM Notes: Still needs some more work for formatting, extra details,
etc
REM
col STMT format a75 heading 'Statement'
select SQL.SQL_TEXT STMT
from V$SESSION SES
, V$SQLTEXT_WITH_NEWLINES SQL
where SES.USERNAME is not null
and SES.SQL_ADDRESS = SQL.ADDRESS
and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
and ses.sid = &SQL_for_Session_ID
order by sql.piece
/
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
I don't know what the future holds for me, but I do know who holds my
future!
** The opinions and statements above are entirely my own and not those of my
employer or clients **
> -----Original Message-----
> From: Sarnowski, Chris [mailto:[EMAIL PROTECTED]]
> Sent: Friday, January 31, 2003 11:56 AM
> To: Multiple recipients of list ORACLE-L
> Subject: session idle time
>
>
>
>
> Oracle 8.1.7.2 on Solaris 8.
> I'm looking for a way to see how long a session has been idle
> or whether it's done any work. I've just been looking at
> v$sesstat, specifically 'session connect time' and 'process
> last non-idle time'. But every time I've queried these
> numbers, they were the same for each SID except SMON (that
> is, for a given SID, except the SID assiociated with SMON,
> the 2 numbers are the same). So they must not measure what I
> guessed they measure.
>
> So the immediate question is, are these statistics useful for
> anything?
>
> The actual problem I'm trying to solve is, we are using a
> connection pooling method for Java that seems to allocate far
> more connections than it ever uses, and I am trying to find a
> way to document what is actually going on with these connections;
> i.e. whether some are never used, and how often connections
> are reused.
>
> thanks for any help, and sorry for the legal goop at the end.
>
> -Chris
> --
>
>
> LEGAL NOTICE:
> Unless expressly stated otherwise, this message is
> confidential and may be privileged. It is intended for the
> addressee(s) only. Access to this e-mail by anyone else is
> unauthorized. If you are not an addressee, any disclosure or
> copying of the contents or any action taken (or not taken) in
> reliance on it is unauthorized and may be unlawful. If you
> are not an addressee, please inform the sender immediately.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Sarnowski, Chris
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Kanagaraj
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
