RE: v$session question

From: Goulet, Richard <Richard.Goulet_at_parexel.com>
Date: Fri, 30 Jan 2009 10:24:26 -0500
Message-ID: <23C4836D8E9C5F4280A66C0C247BC16F23101AC2_at_US-BOS-MX011.na.pxl.int>

Here's the understanding I once got from OTS on what is going on (condensed from a weeks worth of back & forth) and that has proven itself to be correct, or at least it seems that way.  

    When you kill a session Oracle ceases processing of whatever they were doing at that time. Pmon will do the rollback and then try to inform the client that their session has terminated. This you can verify by simply starting a SQLPLUS session, run some long running piece of SQL, and while that's going on kill your session in another sql session. Now try that again, but instead of using a long running query use one that pulls a huge pile of data, at lease three or four screens full, and set pause on. While the screen is paused kill your session again from another sql session. The status will indicate 'killed' until you un-pause the screen at which time you get the terminated message & the v$session record will disappear. The same can be done with an idle session, same results. The larger problem happens when you start that long running sql statement, terminate your client process and then kill the session. Pmon will try for a good long time to inform the now dead client that it is being terminated. It will sooner or later give up & clear out the v$session record, but it certainly can take some time because as it was explained to me, it's not one of the rdbms's primary tasks. Therefore killing the pid in Unix or using orakill at the Microsoft level helps the rdbms out.  

Dick Goulet  

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ram Raman Sent: Thursday, January 29, 2009 5:31 PM To: ORACLE-L
Subject: v$session question

Listers,  

When I kill a session and the status shows as "KILLED" in v$session, I assumed that the rollback is taking place. I am not sure what is happening here:  

SYS_at_PROD> select sid,saddr, serial#, client_info, to_char(logon_time,'DD-MON-YY HH:MIam'), status   2 from v$session
  3 where client_info like '%USER%'     

SYS_at_PROD> /

more..

       SID SADDR SERIAL# CLIENT_INFO

---------- ---------------- ----------
----------------------------------------------------------------

TO_CHAR(LOGON_TIME,'DD-MON-YY
STATUS

---

• -------- 942 C0000000B99775F8 30069 USER,USER,A147946,,psqed.exe, 29-JAN-09 02:48pm KILLED

      1013 C0000000BC9B5C58 4411 USER,USER,A147946,,PSIDE.EXE, 29-JAN-09 03:19pm
KILLED

SYS_at_PROD> 
SYS_at_PROD> 
SYS_at_PROD> select addr, START_TIME, SES_ADDR , USED_UBLK, USED_UREC

  2 from v$transaction
  3 where ses_addr in ('C0000000B99775F8','C0000000BC9B5C58');

no rows selected

SYS_at_PROD> select count(*) from v$transaction   2
SYS_at_PROD> /
more..

  COUNT(*)

---

         0

SYS_at_PROD>  

Thanks.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 30 2009 - 09:24:26 CST