RE: v$session question

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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@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@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@PROD> 
SYS@PROD> 
SYS@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@PROD> select count(*) from v$transaction
  2  
SYS@PROD> /
more..

  COUNT(*)
----------
         0

SYS@PROD> 

 
Thanks.

Other related posts: