RE: session idle time

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Fri, 31 Jan 2003 19:48:38 -0800
Message-ID: <F001.005414CA.20030131194838@fatcity.com>

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:csarnows_at_CuraGen.com]
> 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: csarnows_at_CuraGen.com
>
> 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: ListGuru_at_fatcity.com (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: john.kanagaraj_at_hds.com

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: ListGuru_at_fatcity.com (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).

Received on Fri Jan 31 2003 - 21:48:38 CST